Section 1: INTRO: Learn to solve problems in excel, instead of just formula syntax


Lecture 1


04:36


Why this training?  Learning excel is like learning a language. Memorizing formulas, as most other trainings tend to focus on, equips you to solve complex analytical problems about as well as memorizing the dictionary equips you to write sonnets.
 Purchase this training if you'd like to learn the most powerful and useful excel problem solving techniques and secrets, perfected in over a decade of analysis in the corporate crucibles of McKinsey, Google and some of the world's top corporations.
I have built this training from the ground up to accommodate all levels and skills:  The "Choose your own adventure" case study approach allows you to move at your own pace, and focus on the content you would most like to learn, while getting some handson practice in the process.
 Built for all skill and knowledge levels: Whether you're a student or recent graduate trying to make yourself more employable, an analyst, associate or professional modeljockey, or a more experienced professional trying to upgrade your personal toolkit, I guarantee that this training will help expand your current abilities and take your approach to solving problems to the next level.
You'll be able to do amazing things in Excel by the time you complete this course:  My own special excel model recipe: You will learn my own special excel modeling recipe from start to finish, combining 10 years of hardearned and painful corporate modeling experience with over a year of intensive research into the best and most coherent set of excel techniques and tools available.
 Handson case study approach: We'll start with a real world business problem statement and solve it from scratch through a holistic structured problem solving approach that blends critical paperbased fundamentals, with advanced excel methods.
 By the end, you would have actually built for yourself a powerful and scalable model engine for generating business insights, with amongst other things, a detailed multiperiod optimization model, an elegant Scenario approach to quantifying uncertainty, and a gloriously simple approach to creating and formatting the results.


Lecture 2


03:42


This BeyondFormulas excel training series is broken into 8 main sections:  The introduction section, I'll give you an overview of everything covered throughout the course, and offer some personal wisdom, learned through many late nights, of how to leverage a solid problem solving approach to dramatically improve your work life balance, by getting it right the first time, more often
 In section 2, we'll cover tools and tips to make you more efficient in excel tactics. You’ll learn and practice all of the most important keyboard shortcuts through a special excel game I’ve designed for you, you’ll learn how to avoid making a fool of yourself by reducing the risk of mistakes, you’ll learn how to improve your lifestyle by reducing rework, and I’ll cover how to make better clientready models that you would be proud to hand over to a client
 In Section 3, we’ll cover problemsolving basics  how to take a problem statement, break it down into it’s core components using an issue tree, then finally how to use the issue tree as the basis of your data and analysis plan, which will form the foundation stone of your excel modeling approach.
 In Section 4, we’ll cover the basics of data sourcing, management, cleaning and processing. You’ll learn how to analyze and interrogate large datasets with ease, as well as how to name and reference those datasets throughout your model.
 In Section 5, we’ll learn about ways to aggregate and synthesize your data, both through formulas and pivot tables. I’ll also show you how to use graphs and formulas to identify relationships in your data, and extrapolate them out to achieve basic forecasting.
 In Section 6, we’ll start building your actual model, based on the issue tree, analysis plan and actual datacentered analytics required as inputs. You’ll learn advanced techniques for layering on incremental complexity, resulting in a sophisticated optimization model that can answer real business questions.
 In Section 7, we'll cover approaches to handling uncertainty with scenarios and sensitivities. You’ll learn how to layer additional capabilities onto your optimization model to evaluate multiple complex future scenarios simultaneously, and run detailed pervariable sensitivities.
 Finally in Section 8, we’ll cover how to pull it all together to create beautiful, robust and meaningful visual output that gives you robust and consistent outputs in the face of everchanging business and leadership requirements.


Lecture 3


03:49


RIP  Don’t end up like the model guy 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. Here’s the lifestyle of typical “the model guy”: 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 I'll teach you a better way: 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 buyin for your approach early on, and you’ll use the tools to focus everyone’s energy on answering the question at hand 

Lecture 4

BEYONDFORMULAS  Download all course materials

00:10


Lecture 5


232 pages


Here you can find the BeyondFormulas course handout, consisting accompanying slides with stepbystep details for each lesson. 
Section 2: EXCEL EFFICIENCY: Be faster, more accurate, more impressive & less errorprone


Lecture 6


01:03


What you'll learn during this section:  Speed up your work by mastering Excel Shortcuts.
 Reduce rework with smart, modular model design principles.
 Reduce embarrassing mistakes through good excel discipline, and by making your models more readable and auditable.
 Build “clientready” models that you can easily hand over to your stakeholder.
How to download the required file(s):  Download the “BeyondFormulas  Section 2  All files.zip” compressed file from the "EXCEL EFFICIENCY  Course Materials Lecture".


Lecture 7

EXCEL EFFICIENCY  Course materials

00:06


Lecture 8


02:55


The goals of this section are to: Learn all of the most useful excel shortcuts, and practice them through this excelbased game. Tools & Formulas: Excel shortcuts for PC & Mac. NOTE: To play the game you must have MS Office:  for PC 2007+
 for Mac 2010+
Download the required file(s) from the area above:  The “Keyboard Shortcut Olympics vFinal.xlsb” file.


Lecture 9


04:36


What you'll learn in this video:  Don’t make “spaghetti bowl” models of jumbled data, analysis and assumptions.
 Think Modular: Understanding the different components of a model (encapsulation).
 Design each component with room to grow.


Lecture 10


06:50


The goals of this lesson are to:
 Understand spreadsheet risk and how it will impact you.
 Identify and prevent the 4 sources of risk in your models.
 Learn specific tips on how to make your model more readable and more auditable, to help others check your work.
 Avoid the Excel “cardinal sins” most likely to cause model errors.


Lecture 11


13:47


The goals of this lesson are to:  Build your models so that your stakeholders can easily take them over.
 Generate a set of instructions for your model.
 Learn a consistent formatting scheme which can convey consistent meaning.
 Learn how to use the Starter Template file.
 Stop stakeholders from messing with your model using protections.
Download the required file(s) from the area above:  The “ BeyondFormulas  Modeling STARTER template.xlsm” file.


Lecture 12


06:45


The goals of this lesson are to:
 Learn how to improve speed and reduce the size of your excel models.
 Learn about volatile functions which slow down your model.
 Avoid highly memory or resource intensive operations.
 Use the more efficient new file types.
 Improve the speed of VBA code and macros.

Section 3: PROBLEM SOLVING BASICS: Forget Excel. Problem solve on paper first


Lecture 13


00:58


What you'll learn in this section:
 Understand why a robust problemsolving approach is the critical first step in creating great excel models, through a handson casebased approach.
 Understand how to create a problem statement, and analyze the context in which the problem is being solved to help you generate a more informed solution.
 Understand what it takes to create a MECE issue tree, and practice on the CaWiMak case example.
 Use the issue tree to prioritize your analytical efforts, and plan specific analyses you need to do to crack the case.


Lecture 14


04:06


The goals of this lesson are to:  Get a high level overview of the Problem Solving Process.
 Learn how to apply this set of powerful tools to both to be more rigorous in your thinking, AND to reduce the amount of work you do.
 Target just the right analysis and data you actually need.


Lecture 15


04:55


The goals of this lesson are to:
 Walk through the first step of the problemsolving process.
 Learn how to start solving any problem with the Problem Definition Template.


Lecture 16


03:28


The goals of this lesson are to:  Learn about the California Widget Maker (CaWiMak) and the problem you’re being asked to solve.
 Learn how to apply the Problem Definition Template to the CaWiMak case example that we will work on for the rest of the training.
Download the required file(s) from the area above:  The “BeyondFormulas  CaWiMak Case Details.PDF” file.


Lecture 17


07:04


The goals of this lesson are to:
 Understand how to create and use an issuetree.
 Learn how to break any problem down into its Mutually Exclusive & Comprehensively Exhaustive (MECE) components.
 Understand the difference between an issue tree without numbers (a hypothesis tree) and one more focused on solving a mathematical problem.


Lecture 18


02:20


The goal of this lesson is to:
Create an issue tree for the CaWiMak case, which you will continue to use for the rest of the training.


Lecture 19


03:43


The goals of this lesson are to:
 Use the issue tree developed in the previous video to identify and prioritize the most critical levers in the model.
 Start identifying which of the levers you can approximate, and which you need to perform a more rigorous analysis on.
 Start sharing your logic and prioritization with your stakeholders in tree form, to promote early discussion and feedback.


Lecture 20


04:23


The goals of this lesson are to:
 Plan out the intricate details of the analysis and type of data you will need to evaluate each branch of your prioritized issue tree.
 Share plans with and reach agreement with stakeholders on exactly what constitutes “sufficient” evidence to avoid “boiling the ocean”.
 Understand the difference between knowable data, and unknowable assumptions.


Lecture 21


8 pages


This PDF file contains the case material for the California Widget Maker (CaWiMak) case, which we will use throughout the rest of this training 
Section 4: DATA BASICS: How to collect, clean, import, format, parse and lookup


Lecture 22


01:23


What you'll cover in this section:
Why Excel is the swiss army knife of data processing, manipulation and referencing. You'll learn how to:
 Use your prioritized analysis tree to identify data sources to support your analysis.
 Identify and deal with bad data.
 Accurately import and process your newly cleaned data into your model.
 Convert table data into flat file (database) data structure, for later analysis.
 LOOKUP data the right way for merging.
 Use Excel’s awesome TABLES feature to work with your data.
 Use excel’s text parsing and data formatting formula.
 Correctly work with dates and times.
 Use named ranges and dynamic named ranges to create powerful drop down lists.
How to download the required file(s):
 Download the “BeyondFormulas  Section 4  All files.zip” compressed file from the "DATA BASICS  Course Materials Lecture".


Lecture 23

DATA BASICS  Course materials

00:06


Lecture 24


07:13


The goals of this lesson are to:
 Walk through the process of finding and gathering the right data you need for your planned analyses, including “knowable” inputs, and “unknowable” assumptions.
 Learn what to consider in choosing data sources, what to look for once you’ve got it, and how to record and annotate everything so that you don’t get caught off guard by an uppity stakeholder later.
 Reliably combine all of the raw data files you’ll need for this exercise, and start to delve into what the various fields actually mean.
You’ll need:
All of the “RAW” companion files.


Lecture 25


05:20


The goals of this lesson are to:
 Teach you a few basics on data cleaning. (NOTE: You’ll learn just enough to make you dangerous).
 Learn how tell to tell good data from bad data.
 Learn a few basic steps to clean and correct bad data.
 Learn how to install and use OpenRefine to process and clean your data in advance.
Tools & Formulas:
OpenRefine
You’ll need:
To download and install OpenRefine from OpenRefine.org


Lecture 26


08:04


The goals of this lesson are to:  Understand the importance of keeping raw data (in terms of structure) the same structure as you found it.
 Learn how to most reliably combine multiple data sources into one workbook.
 Learn how to quickly format and process newly imported data for use in your model.
Tools & Formulas: Worksheet Copy feature, Bulk editing worksheets, Freeze Rows/columns, Grouping. You’ll need: The 5 RAW data files:  RAW DATA  CaWiMak Sales data 2013,
 Four Census files  RAW DATA  Census 2010  land area / population 2010 / population est 2012 / housing units 2010


Lecture 27


05:39


The goals of this lesson are to:  Teach you the difference between a flat file or “database format” data file, and a Summary Table.
 Teach you how to easily convert data from one format to the other, depending on your need.
Tools & Formulas: The Data Explorer Addin and the “unpivot columns” feature You’ll need:  The “CaWiMak case  Video 4.4  Data  Flat File.xlsb” file
 Microsoft Data Explorer Excel Addin  available from the link in the course description


Lecture 28


16:23


The goals of this lesson are to:  Learn how to create a unique identifier for each row, and to use a pivot table to test the uniqueness.
 Learn how to do lookups the right way (with INDEX+MATCH) to merge multiple data sets.
Tools & Formulas: The “&” operator to concatenate text, basic pivot table count, INDEX, MATCH, IFERROR You’ll need: The “CaWiMak case  4.5  Data  Join datasets.xlsb” file 

Lecture 29


06:24


The goals of this lesson are to:  Learn why TABLES are awesome for data management and analysis.
 Learn about the best TABLE features, including formatting, referencing them with formula, and analyzing with pivot tables.
Tools & Formulas: TABLES and associated features You’ll need: The “CaWiMak case  4.6  Data  Tables.xlsb” file 

Lecture 30


07:32


The goals of this lesson are to:  Learn about the difference between how Excel interprets text strings and values.
 Learn how to test the contents of a cell, how to convert data between text and values.
 Learn how to parse and manipulate the text in a field of the real case data.
Tools & Formulas: IsText, IsNumber, IsOdd, IsEven, IsNonText, IsBlank, VALUE, INT, LEFT, RIGHT, MID, LEN, TRIM, SEARCH, SUBSTITUTE You’ll need: The “CaWiMak case  4.7  Data  Text parsing.xlsb” file 

Lecture 31


08:51


The goals of this lesson are to:  Teach you how excel understands dates and times.
 Parse and convert simple text into date serials, and apply this to our CaWiMak sales dataset.
 Use excel’s date formatting and perform operations on dates.
 Apply the same approach to manipulating time.
Tools & Formulas: TEXT, DATEVALUE, LEFT, RIGHT, SEARCH, YEAR, MONTH, DAY, WEEKNUM, WEEKDAY, DATE, Date formatting, TIMEVALUE, HOURS, MINUTES, SECONDS, TIME You’ll need: The “CaWiMak case  4.8  Data  Dates and times.xlsb” file 

Lecture 32


17:46


The goals of this lesson are to:  Learn about naming cells and ranges, and why they are useful in creating formula.
 Create a dropdown list based on a range of values, for reliable data entry.
 Format your new drop down list to actually look like one, and change color based on value.
 Create a Dynamic Named Range, which grows as you add more data to it (plus a more advanced way of using dynamic named ranges for large data sets).
Tools & Formulas:  Naming Cells and ranges and using those names in formulas, Data Validation with Lists, Name Manager, Custom text formatting, Conditional Formatting, OFFSET, COUNTA, INDEX
You’ll need:  The “CaWiMak case  4.9  Data  Dynamic named ranges.xlsb” file
 You'll need this text  “@ * ▼” (I'll explain in this video)

Section 5: DATA ANALYSIS: Summarize your data with Formulas and PIVOT TABLES


Lecture 33


01:59


What you'll learn in this section: Become a data crunching ninja with a few simple tools, and use those skills to solve problems:  Basic Aggregation & Summary formula to describe your data  SUM, AVERAGE, MEDIAN, STD DEV, etc.
 Creating your first pivot table to manipulate, summarize very large data sets in a few clicks.
 Taking pivot tables further with slicers, filters, sorting, grouping, calculated fields & more.
 Create dashboards with Pivot Charts & Slicers.
 Use GETPIVOTDATA to let your model interact with your pivot tables more dynamically.
 Create an XY Scatter plot to explore the relationship between two variables and measure correlation.
 Use historical data to FORECAST new values by extrapolating known relationships in your data.
 Use Near Match Lookups to categorize large data sets into useful buckets.
How to download the required file(s):  Download the “BeyondFormulas  Section 5  All files.zip” compressed file from the "DATA ANALYSIS  Course Materials Lecture".


Lecture 34

DATA ANALYSIS  Course materials

00:06


Lecture 35


19:58


The goals of this lesson are to:  Use a range of aggregate, conditional summary and comparison formula to analyze a datasets.
 Learn how to create a list of unique values from a large dataset.
 Describe your data, to infer meaning and identify patterns that bring understanding and insight.
Tools & Formulas:  Aggregation formula (SUM, AVERAGE, MEDIAN, COUNT, COUNTA, COUNTBLANK, MAX, MIN, STD DEV)
 Conditional aggregation (SUMIF/S, AVERAGEIF/S, COUNTIF/S)
 Comparison (RANK, PERCENTILERANK, PERCENTILE, SMALL, LARGE)
You’ll need: The “CaWiMak case  5.1  Analysis  Basic Aggregation Starter.xlsb” file 

Lecture 36


16:13


The goals of this lesson are to:  Create your first pivot table, and learn the basics of adding and manipulating fields.
 Take advantage of Excel’s more advanced pivot features: Field settings, formatting, filtering, sorting & slicing.
 Make use of dates in your summaries, using the powerful grouping feature.
Tools & Formulas:  Pivot Tables, Sorting & Filtering, Grouping, Formatting and Pivot Slicers
You’ll need: The “CaWiMak case  5.2  Analysis  Pivot Tables starter.xlsb” file 

Lecture 37


15:13


The goals of this lesson are to:  Use the “Show Values As” feature to calculate “% of Totals” and “% of Row” representations of the analysis.
 Create a Pivot Chart from your Pivot Table, and use the Slicer to create a powerful dashboard.
 Learn how to add a Calculated Field, for creating a formula using multiple other fields.
Tools & Formulas:  More advanced pivot table features: “Show Values As”, Pivot Charts, Slicers and Calculated Fields
You’ll need: The “CaWiMak case  5.3  Analysis  Intermediate Pivot Tables starter.xlsb” file 

Lecture 38


15:50


The goals of this lesson are to:  Understand when to use pivot tables VS formula VS a hybrid approach.
 Use GETPIVOTDATA to extract data from a pivot table dynamically, to treat it like a database.
 Use XY Scatter plot to measure correlation between two variables, including a fitted “trendline” and a measured Rsquared.
 Combine Slicers with Pivot Charts to create dynamic, visual dashboards
 Learn advanced GETPIVOTDATA techniques and tricks.
Tools & Formulas:  Pivot Tables, GETPIVOTDATA, Pivot Charts, XY Scatter Plot, Trendlines, and advanced GETPIVOTDATA tricks
You’ll need: The “CaWiMak case  5.4  Analysis  Getpivotdata and Trendline starter.xlsb” file 

Lecture 39


16:03


The goals of this lesson are to:  Use an identified relationship between two variables together with the FORECAST formula to extrapolate values for an unknown field.
 Estimate CaWiMak’s national sales potential using the GETPIVOTDATA and the new formula you’ve learned.
Tools & Formulas:  FORECAST, SUMIF, IFERROR, GETPIVOTDATA
You’ll need: The “CaWiMak case  5.5  Analysis  Forecasting starter.xlsb” file 

Lecture 40


13:37


The goals of this lesson are to:  Use a Near Match Lookup, together with a sorted table, to categorize a large amount of data into buckets based on values.
 Combine other formula tools you have learned to sense check the distribution of values you are interested in across your newly created buckets.
Tools & Formulas:  “Near Match” lookups, IF, SUMIF, COUNTIF, PERCENTILE
You’ll need: The “CaWiMak case  5.6  Analysis  Next level lookup starter.xlsb” file 
Section 6: EXCEL ENGINE ROOM: Create a single, logical model flow to drive everything


Lecture 41


01:12


What you'll cover in this section: How to build a really great model, including:  Building a simple “backofthe envelope” model to go from your paperbased plan to a day1 answer and test your logic.
 Identifying the variables in your model impacted by time and scale, and coming up with elegant ways to model each using my own mix of techniques & tools.
 Expanding your basic model to a full multiperiod P&L in layers.
 Check your work and Diagnose errors, to avoid public humiliation.
This approach emphasizes using “encapsulation” to build a modular series of simple steps that result in an elegant and sophisticated end product. How to download the required file(s):  Download the “BeyondFormulas  Section 6  All files.zip” compressed file from the "EXCEL ENGINE ROOM  Course Materials Lecture".


Lecture 42

EXCEL ENGINE ROOM  Course materials

00:06


Lecture 43


04:09


The goals of this section are to:  Create a backoftheenvelope excel model from your issue tree to drive day1 insights.
 Share your logic and results early with stakeholders, in order to get feedback that helps ensure you get the right answer.
Tools & Formulas:  Issue Tree, 1page excel model
You’ll need: The “CaWiMak case  6.1  Modeling  Backofthe envelope starter.xlsb” file 

Lecture 44


03:53


The goals of this lesson are to:  Identify all variables in your backof theenvelope model that are affected by either time or economies of scale, and plan the analysis required to move to a multiperiod model.
 Identify the ideal timeframe and period duration for your model, based on stakeholder needs and expected forecast accuracy.
You’ll need: The “CaWiMak case  6.2  Modeling  Time and scale starter.xlsb” file 

Lecture 45


10:02


The goals of this lesson are to:  Understand the differences between a periodic growth approach, and an adoption curve approach.
 Create a set of adoption scenarios using simple linear interpolation.
Tools & Formulas:  Simple linear interpolation, Adoption curve
You’ll need: The “CaWiMak case  6.3  Modeling  Adoption Curves starter.xlsb” file 

Lecture 46


18:46


The goals of this lesson are to:  Teach you about the Visual Basic Editor, and how to use it to create macros, subroutines and functions.
 Teach you the basics of creating and editing macros for automation.
 Teach you the basics of creating functions, so that you can turn any mathematical algorithm you like into a User Defined Function in your model  useful when excel doesn’t have a built in formula that does what you need.
Tools & Formulas:  Visual Basic Editor, VBA, Macros, Functions, Subroutines
You’ll need: The “CaWiMak case  6.4  Modeling  User Defined Formulas starter.xlsb” file 

Lecture 47


24:33


The goals of this lesson are to:  Understand the logic of modeling sales forecasts with variable adoption curves, seasonality, and persegment launch dates.
 Use the OFFSET formula to programmatically refer to a cell relative to a fixed cell.
Tools & Formulas:  Data validation, INDEX+MATCH, RIGHT, OFFSET, SUMIF
You’ll need: The “CaWiMak case  6.5  Modeling  Phased rollout starter.xlsb” file 

Lecture 48


21:56


This is the first of two videos for Lesson 6: Supply Chain modeling 101. The goals of this lesson are to:  Understand and implement a basic multilocation manufacturing supply chain model.
 Set up a scenario based approach to logistics modeling that lets you try different scenarios really easily.
 Understand how to create a User Defined Function to measure the driving distance between two points.
 Minimize Shipping Cost per parcel by combining multiple shippers offerings.
Tools & Formulas:  Data Validation, INDEX+MATCH, IF + Logical Operators, COLUMN, SUMIFS, Named Ranges, MIN, User Defined VBA function, IFERROR, GETPIVOTDATA
You'll need: The “CaWiMak case  6.6  Modeling  Supply Chain 101 Model Starter.xlsb” file 

Lecture 49


20:27


This is the second of two videos for Lesson 6: Supply Chain modeling 101. The goals of this lesson are to:  Understand and implement a basic multilocation manufacturing supply chain model.
 Set up a scenario based approach to logistics modeling that lets you try different scenarios really easily.
 Understand how to create a User Defined Function to measure the driving distance between two points.
 Minimize Shipping Cost per parcel by combining multiple shippers offerings.
Tools & Formulas:  Data Validation, INDEX+MATCH, IF + Logical Operators, COLUMN, SUMIFS, Named Ranges, MIN, User Defined VBA function, IFERROR, GETPIVOTDATA
You'll need: 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 

Lecture 50


05:56


The goals of this lesson are to:  Understand how to create a User Defined Function to measure the driving distance between two points.
Tools & Formulas:  User Defined VBA functions
You’ll need: The “CaWiMak case  6.6B  Modeling  VBA distance calc starter.xlsb” file 

Lecture 51


19:26


This is the first of two videos for Lesson 7: Supply Chain 201. The goals of this lesson are to:  Learn about about 1 and 2dimensional data tables.
 Learn to apply data tables to your model, to create optimizations and multiperiod models with ease.
Tools & Formulas:  DATA TABLES (1dimensional & 2dimensional), INDEX
You'll need: The “CaWiMak case  6.7  Modeling  Supply Chain 201 Optimization Starter.xlsb” file 

Lecture 52


16:35


This is the second of two videos for Lesson 7: Supply Chain 201. The goals of this lesson are to:  Learn about about 1 and 2dimensional data tables.
 Learn to apply data tables to your model, to create optimizations and multiperiod models with ease.
Tools & Formulas:  DATA TABLES (1dimensional & 2dimensional), INDEX
You'll need: The same excel file as you worked on in the previous lesson  “CaWiMak case  6.7  Modeling  Supply Chain 201 Optimization Starter.xlsb” file 

Lecture 53


23:13


The goals of this lesson are to:  Practice converting your backofthe envelope model into your full multiperiod P&L, by adding in your newly modeled perperiod line items.
 Witness the benefits of building models in a modular, and encapsulated way.
Tools & Formulas:  Copy Paste, Named Ranges, INDEX, IF+logical operators
You’ll need: The “CaWiMak case  6.8  Modeling  Multiperiod PnL starter.xlsb” file 

Lecture 54


02:53


The goals of this summary are to:  Give you a brief recap of the immensely powerful model and tools we've built together during this section.
 Help generalize the approach and lessons learned during this training, so that you can easily apply them to your own problems and models.


Lecture 55


04:48


The goals of this lesson are to:  Avoid public humiliation in front of your bosses or main stakeholders!
 Learn how to check your work regularly, by comparing outputs to “real world” comparisons, and build in checkcells to monitor for errors.
 Learn how to diagnose mistakes or logical errors in your model, when all else fails.
You’ll need: The “CaWiMak case  6 Complete  Modeling Basics  Completed.xlsb” file 
Section 7: MODELING UNCERTAINTY: Amazing Scenario and Sensitivity analyses made dead simple


Lecture 56


01:01


What you'll learn in this section: How to apply the data tables tool we’ve already learned to supercharge your P&L model with advanced scenario and sensitivity capabilities:  Define a set of powerful future scenarios using Premortems and the Scenario Hypothesis Tool, and represent them in your model.
 Convert your P&L into a scenario machine with the Scenario Control Panel.
 Use data tables to instantly see the desired outcomes to all your scenarios at once (but beware).
 Apply data tables creatively to create a robust sensitivity analysis for each of your most important model drivers.
How to download the required file(s):  Download the “BeyondFormulas  Section 7  All files.zip” compressed file from the "MODELING UNCERTAINTY  Course Materials Lecture".


Lecture 57

MODELING UNCERTAINTY  Course materials

00:06


Lecture 58


07:32


The goals of this lesson are to:  Understand the interconnectedness of your model variables. Also called interdependency, or autocorrelation.
 Learn how to conduct a premortem to assess the possible causes of death of your business before it happens.
 Use the Scenario Hypothesis Tool to come up with a set of key scenarios you’d like to evaluate using your model.
Tools & Formulas:  Scenario Hypothesis Table


Lecture 59


12:40


The goals of this lesson are to:  Understand how to supercharge your encapsulated P&L model with an elegant and powerful scenario framework.
 Consolidate all of your model drivers into a single control panel, driven by a simple scenario selector using data validation.
 Include multiple scenario axes, and allow for your main scenario set to include scenarios within scenarios.
Tools & Formulas:  Data Validation, INDEX, Conditional formatting
You’ll need: The “CaWiMak case  7.2  Scenarios  Scenario powerhouse starter.xlsb” file 

Lecture 60


06:11


The goals of this lesson are to:  Understand how to use data tables to calculate all of your important outputs across all scenarios at once.
 Understand the limitations of data tables, and the importance of auditing and checking your work.
Tools & Formulas: You’ll need: The “CaWiMak case  7.3  Scenarios  Data tables starter.xlsb” file 

Lecture 61


09:27


The goals of this lesson are to:  Understand how to use data tables to convert your Scenario Machine into a Sensitivity Analysis machine, in order to evaluate the impact of individual variables on your desired outcome.
 Use a line chart to represent your sensitivities as a star chart.
Tools & Formulas: You’ll need: The “CaWiMak case  7.4  Scenarios  Sensitivity Star Chart starter.xlsb” file 
Section 8: SLIDEREADY OUTPUT: Synthesis, Data visualization and instant presentations


Lecture 62


00:51


What you'll learn in this section: All the fundamentals required to record, format and present your data and analyses to your stakeholders:  How to create an Output Sheet (presentation layer) that gives you full control over which data is presented, and how it is combined and formatted.
 Great presentation hygiene  keep a copy of your inputs and outputs powering each presentation “deck” so that you can track changes easily from presentation to presentation.
 How to create beautiful and clear charts using Excel’s most basic & fundamental charting features.
How to download the required file(s):  Download the “BeyondFormulas  Section 8  All files.zip” compressed file from the "SLIDEREADY OUTPUT Course Materials Lecture".


Lecture 63

SLIDEREADY OUTPUT  Course materials

00:06


Lecture 64


08:04


The goals of this lesson are to:  Learn to create and format a simple output sheet.
 Use text parsing formulas to capture the current filename.
 Understand the importance of recording the outputs and inputs for each presentation or deliverable you’re responsible for, so that you can easily go back.
Tools & Formulas:  Output tab, TODAY(), CELL, SEARCH, MID, Paste Values
You’ll need: The “CaWiMak case  8.1  Output Ready  Output tab starter.xlsb” file 

Lecture 65


18:02


The goals of this lesson are to:  Learn how to create & format a basic chart, including adjusting the axes to enhance your message.
 Learn to use a dynamic named range in your charts, so they grow with your data.
 Learn to save & apply chart templates.
 Learn to use “Linked Images” for creating live copies of underlying regions of your workbook as an image.
Tools & Formulas:  Chart creation, Chart layout and formatting, Secondary Axes, Chart Axes formatting, Using Named Ranges and Dynamic Named Ranges in charts, Chart Templates, Linked Images
You’ll need: The “CaWiMak case  8.2  Output Ready  Charting fundamentals starter.xlsb” file 
Section 9: CONCLUSION


Lecture 66


03:56


Wrapping it all up:  A summary of everything you need to be a really great Modeler
 Taking it to the next level: Extend your model to solve additional business problems, using the same methodology
 For any Excel problem you’ve ever had, or ever will have, someone has already solved it and posted the answer online


Lecture 67


232 pages


[This is a repeat of the material in Lecture #5] Here you can find the BeyondFormulas course handout, consisting accompanying slides with stepbystep details for each lesson. 