Please confirm that you want to add BeyondFormulas: Complete MS Excel Techniques & Modeling to your Wishlist.
*** This training teaches you:
1) All of the crucial excel techniques you should care about: including model structure, data handling, smart lookups, aggregation & basic statistics, tables, pivot tables, charts, VBA macros & custom formulas, and many more.
2) Unlike other training, I'll teach you how to combine these tools to solve real world problems, and help you stand out in any analytical environment.
*** Learn, then immediately apply your new knowledge:
I’ve been “the excel model guy” for a large number of analytically demanding clients at McKinsey, and for internal senior executive stakeholders at Google and other companies, I’ve learned the very hard way that when it comes to building models, there are certain “cycles of violence” that play themselves out over and over again, to the tune of your your wailing and gnashing teeth.
Although these cycles are not entirely avoidable, I guarantee that the approach I teach in this training will help deal with all of them, and most importantly will reduce the amount of stress, rework and sleepless nights you suffer as a result.
You start working pretty hard initially. Then complexity increases and your work hours get worse. Interim presentations don’t go as well as they should because your stakeholder has a few suggestions which you have to refactor into your model. And a few weeks before the final readout, your sleep plummets, and your stress multiplies.
Sound familiar? Trust me when I say there is a better way. And it’s accessible to you through this training, if you’re willing to change the way you think about solving a stakeholder problem with excel
Instead I’m going to teach you how to use a problem solving method to frontload the work. You’ll spend time on building consensus and getting buy-in for your approach early on, and you’ll use the tools to focus everyone’s energy on answering the question at hand
Here you can find the BeyondFormulas course handout, consisting accompanying slides with step-by-step details for each lesson.
Learn all of the most useful excel shortcuts, and practice them through this excel-based game.
Excel shortcuts for PC & Mac.
NOTE: To play the game you must have MS Office:
Create an issue tree for the CaWiMak case, which you will continue to use for the rest of the training.
This PDF file contains the case material for the California Widget Maker (CaWiMak) case, which we will use throughout the rest of this training
Why Excel is the swiss army knife of data processing, manipulation and referencing. You'll learn how to:
All of the “RAW” companion files.
OpenRefine
To download and install OpenRefine from OpenRefine.org
Worksheet Copy feature, Bulk editing worksheets, Freeze Rows/columns, Grouping.
The 5 RAW data files:
The Data Explorer Add-in and the “unpivot columns” feature
The “&” operator to concatenate text, basic pivot table count, INDEX, MATCH, IFERROR
The “CaWiMak case - 4.5 - Data - Join datasets.xlsb” file
TABLES and associated features
The “CaWiMak case - 4.6 - Data - Tables.xlsb” file
IsText, IsNumber, IsOdd, IsEven, IsNonText, IsBlank, VALUE, INT, LEFT, RIGHT, MID, LEN, TRIM, SEARCH, SUBSTITUTE
The “CaWiMak case - 4.7 - Data - Text parsing.xlsb” file
TEXT, DATEVALUE, LEFT, RIGHT, SEARCH, YEAR, MONTH, DAY, WEEKNUM, WEEKDAY, DATE, Date formatting, TIMEVALUE, HOURS, MINUTES, SECONDS, TIME
The “CaWiMak case - 4.8 - Data - Dates and times.xlsb” file
Become a data crunching ninja with a few simple tools, and use those skills to solve problems:
The “CaWiMak case - 5.1 - Analysis - Basic Aggregation Starter.xlsb” file
The “CaWiMak case - 5.2 - Analysis - Pivot Tables starter.xlsb” file
The “CaWiMak case - 5.3 - Analysis - Intermediate Pivot Tables starter.xlsb” file
The “CaWiMak case - 5.4 - Analysis - Getpivotdata and Trendline starter.xlsb” file
The “CaWiMak case - 5.5 - Analysis - Forecasting starter.xlsb” file
The “CaWiMak case - 5.6 - Analysis - Next level lookup starter.xlsb” file
How to build a really great model, including:
This approach emphasizes using “encapsulation” to build a modular series of simple steps that result in an elegant and sophisticated end product.
The “CaWiMak case - 6.1 - Modeling - Back-of-the- envelope starter.xlsb” file
The “CaWiMak case - 6.2 - Modeling - Time and scale starter.xlsb” file
The “CaWiMak case - 6.3 - Modeling - Adoption Curves starter.xlsb” file
The “CaWiMak case - 6.4 - Modeling - User Defined Formulas starter.xlsb” file
The “CaWiMak case - 6.5 - Modeling - Phased rollout starter.xlsb” file
The “CaWiMak case - 6.6 - Modeling - Supply Chain 101 Model Starter.xlsb” file
The same file you have been working on so far in the previous video lesson - “CaWiMak case - 6.6 - Modeling - Supply Chain 101 Model Starter.xlsb” file
The “CaWiMak case - 6.6B - Modeling - VBA distance calc starter.xlsb” file
The “CaWiMak case - 6.7 - Modeling - Supply Chain 201 Optimization Starter.xlsb” file
The same excel file as you worked on in the previous lesson - “CaWiMak case - 6.7 - Modeling - Supply Chain 201 Optimization Starter.xlsb” file
The “CaWiMak case - 6.8 - Modeling - Multi-period PnL starter.xlsb” file
The “CaWiMak case - 6 Complete - Modeling Basics - Completed.xlsb” file
How to apply the data tables tool we’ve already learned to supercharge your P&L model with advanced scenario and sensitivity capabilities:
The “CaWiMak case - 7.2 - Scenarios - Scenario powerhouse starter.xlsb” file
The “CaWiMak case - 7.3 - Scenarios - Data tables starter.xlsb” file
The “CaWiMak case - 7.4 - Scenarios - Sensitivity Star Chart starter.xlsb” file
All the fundamentals required to record, format and present your data and analyses to your stakeholders:
The “CaWiMak case - 8.1 - Output Ready - Output tab starter.xlsb” file
The “CaWiMak case - 8.2 - Output Ready - Charting fundamentals starter.xlsb” file
[This is a repeat of the material in Lecture #5]
Here you can find the BeyondFormulas course handout, consisting accompanying slides with step-by-step details for each lesson.