BeyondFormulas: Complete MS Excel Techniques & Modeling

What you'll learn during this section:

• Speed up your work by mastering Excel Shortcuts.
• Reduce re-work with smart, modular model design principles.
• Reduce embarrassing mistakes through good excel discipline, and by making your models more readable and auditable.
• Build “client-ready” 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".

The goals of this section are to:

Learn all of the most useful excel shortcuts, and practice them through this excel-based 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.

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.

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.

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.

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.

What you'll learn in this section:

• Understand why a robust problem-solving approach is the critical first step in creating great excel models, through a hands-on case-based 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.

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.

The goals of this lesson are to:

• Walk through the first step of the problem-solving process.
• Learn how to start solving any problem with the Problem Definition Template.

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.

The goals of this lesson are to:

• Understand how to create and use an issue-tree.
• 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.

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.

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.

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.

This PDF file contains the case material for the California Widget Maker (CaWiMak) case, which we will use throughout the rest of this training

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:

1. Use your prioritized analysis tree to identify data sources to support your analysis.
2. Identify and deal with bad data.
3. Accurately import and process your newly cleaned data into your model.
4. Convert table data into flat file (database) data structure, for later analysis.
5. LOOKUP data the right way for merging.
6. Use Excel’s awesome TABLES feature to work with your data.
7. Use excel’s text parsing and data formatting formula.
8. Correctly work with dates and times.
9. 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".

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.

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

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

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 Add-in and the “unpivot columns” feature

You’ll need:

• The “CaWiMak case - Video 4.4 - Data - Flat File.xlsb” file
• Microsoft Data Explorer Excel Add-in - available from the link in the course description

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

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

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

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

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)

What you'll learn in this section:

Become a data crunching ninja with a few simple tools, and use those skills to solve problems:

1. Basic Aggregation & Summary formula to describe your data - SUM, AVERAGE, MEDIAN, STD DEV, etc.
2. Creating your first pivot table to manipulate, summarize very large data sets in a few clicks.
3. Taking pivot tables further with slicers, filters, sorting, grouping, calculated fields & more.
4. Create dashboards with Pivot Charts & Slicers.
5. Use GETPIVOTDATA to let your model interact with your pivot tables more dynamically.
6. Create an XY Scatter plot to explore the relationship between two variables and measure correlation.
7. Use historical data to FORECAST new values by extrapolating known relationships in your data.
8. 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".

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

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

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

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 R-squared.
• 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

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

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

What you'll cover in this section:

How to build a really great model, including:

1. Building a simple “back-of-the- envelope” model to go from your paper-based plan to a day-1 answer and test your logic.
2. 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.
3. Expanding your basic model to a full multi-period P&L in layers.
4. 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".

The goals of this section are to:

• Create a back-of-the-envelope excel model from your issue tree to drive day-1 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, 1-page excel model

You’ll need:

The “CaWiMak case - 6.1 - Modeling - Back-of-the- envelope starter.xlsb” file

The goals of this lesson are to:

• Identify all variables in your back-of- the-envelope model that are affected by either time or economies of scale, and plan the analysis required to move to a multi-period 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

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

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

The goals of this lesson are to:

• Understand the logic of modeling sales forecasts with variable adoption curves, seasonality, and per-segment 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

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 multi-location 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

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 multi-location 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

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

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 2-dimensional data tables.
• Learn to apply data tables to your model, to create optimizations and multi-period models with ease.

Tools & Formulas:

• DATA TABLES (1-dimensional & 2-dimensional), INDEX

You'll need:

The “CaWiMak case - 6.7 - Modeling - Supply Chain 201 Optimization Starter.xlsb” file

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 2-dimensional data tables.
• Learn to apply data tables to your model, to create optimizations and multi-period models with ease.

Tools & Formulas:

• DATA TABLES (1-dimensional & 2-dimensional), 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

The goals of this lesson are to:

• Practice converting your back-of-the- envelope model into your full multi-period P&L, by adding in your newly modeled per-period 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 - Multi-period PnL starter.xlsb” file

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.

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 check-cells 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

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:

1. Define a set of powerful future scenarios using Pre-mortems and the Scenario Hypothesis Tool, and represent them in your model.
2. Convert your P&L into a scenario machine with the Scenario Control Panel.
3. Use data tables to instantly see the desired outcomes to all your scenarios at once (but beware).
4. 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".

The goals of this lesson are to:

• Understand the interconnectedness of your model variables. Also called interdependency, or autocorrelation.
• Learn how to conduct a pre-mortem 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

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

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:

• Data Tables

You’ll need:

The “CaWiMak case - 7.3 - Scenarios - Data tables starter.xlsb” file

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:

• Data Tables, Line Chart

You’ll need:

The “CaWiMak case - 7.4 - Scenarios - Sensitivity Star Chart starter.xlsb” file

What you'll learn in this section:

All the fundamentals required to record, format and present your data and analyses to your stakeholders:

1. 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.
2. 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.
3. 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 "SLIDE-READY OUTPUT Course Materials Lecture".

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

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

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

[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.