BeyondFormulas: Complete MS Excel Techniques & Modeling
About This Course
*** 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:
- Bite-sized and Step-by-step curriculum, so you can learn one skill at a time, combine it all into a single beautiful model output.
- The only Excel techniques and tips you’ll ever need, from over a year of research, and ten years of experience.
- Learn to “manage upwards” so that you, the Modeler, don’t work all night.
- Hands-on case study approach, to practice what you learn on a real problem. All starter materials provided for each course so you can get started quickly.
- Holistic model design focus, so that your models to scale, adapt and evolve easily.
- Keyboard shortcut game, coded in Excel, so you can get around Excel lightning fast.
What are the requirements?
- Most of this training is Excel version agnostic. A few lessons require: Windows PC Excel 2007+ or Mac Excel 2011+
- Requires only a very basic understanding of Excel (getting around the ribbon, formatting and basic IF statements)
What am I going to get from this course?
- Take any business problem, and solve it using structured (consulting-like) problem solving tools
- Analyze data in excel using all of the most useful excel tools for real world data analysis
- Get around excel like a pro with an excel keyboard shortcut game
- Evaluate advanced scenarios and sensitivity analyses with your model in just a few steps
What is the target audience?
- Students / graduates trying to get hired for analytical skills
- Consultants and corporate analysts looking to develop core skills
- Entrepreneurs needing to model their new businesses
- Experienced professionals trying to brush up on new tools
|Section 1: INTRO: Learn to solve problems in excel, instead of just formula syntax|
Why this Excel Course?
[optional] What this course covers: Modeling Approach & Excel Tools
[optional] Consultants, or Managers of analysts: take note
BEYONDFORMULAS – Download all course materials
COURSE HANDOUT – Accompanying slides for each lesson
|Section 2: EXCEL EFFICIENCY: Be faster, more accurate, more impressive & less error-prone|
EXCEL EFFICIENCY Section Intro
EXCEL EFFICIENCY – Course materials
Keyboard shortcut game: Improve speed, save HOURS
What is “Modular Model Design” and why should you care?
Avoid embarrassing yourself: Reduce spreadsheet risk to preempt mistakes
Building a model for stakeholders? Save hours of rework – make it “client-ready”
[optional] ADVANCED: What to do if your model becomes really BIG or really SLOW
|Section 3: PROBLEM SOLVING BASICS: Forget Excel. Problem solve on paper first|
PROBLEM SOLVING BASICS Section Intro
Problem-solving 101 – the basic analytical problem solving process
Define the Problem: Find true north with a problem statement & audience
Case-study: Problem Statement for a Widget Manufacturing Company
Structure the Problem: Create the “Issue Tree” to break down & share your logic
Case-study: Creating the issue tree for the Widget Manufacturing Company problem
Prioritize: Find and prioritize the main business drivers
Plan: Plan every detail of each analysis to go from Issue tree → Plan of Attack
CaWiMak Case study – course materials
|Section 4: DATA BASICS: How to collect, clean, import, format, parse and lookup|
DATA BASICS Section Intro
DATA BASICS – Course materials
Gather real data based on your Analysis & Data Plan
Deal with Bad Data – Process, transform & clean large structured datasets
Got data? Now what? Best practice for importing data into your model
Primer on structuring data & making it useful: Table or “Flat file” format
Merging/joining data using a Primary Key+INDEX+MATCH (instead of VLOOKUP)
Use TABLES – Interact with your data using Excel’s secret data weapon
Using String formulas to parse & manipulate text fields in your data
Date & time parsing, conversion and manipulation
Use Dynamic Named Ranges to give your data & cells easy-to-remember names
|Section 5: DATA ANALYSIS: Summarize your data with Formulas and PIVOT TABLES|
DATA ANALYSIS Section Intro
DATA ANALYSIS – Course materials
The best aggregation formula for analyzing & describing your data
PIVOT TABLES: Everything you need to know to instantly summarize your data
INTERMEDIATE PIVOT TABLES: “Show Values As” options & Calculated fields
Dynamic dashboards & data merging using GETPIVOTDATA, Pivot charts & slicers
Extrapolate CaWiMak sales data to forecast national sales using FORECAST
Next-level LOOKUPS: Using “near match” lookups to group & categorize your data
|Section 6: EXCEL ENGINE ROOM: Create a single, logical model flow to drive everything|
EXCEL ENGINE ROOM Section Intro
EXCEL ENGINE ROOM – Course materials
Issue tree→Excel: Creating a back-of- the-envelope business model “engine”
Modeling the impact of time and economies of scale
Model adoption curves over time instead of modeling a simple growth rate
[Optional] ADVANCED: Create your own user-defined formulas with VBA. It’s easy!
Apply adoption curves to a phased geographic rollout
Supply Chain modeling 101 (Part A): Shipping cost & manufacturing locations
Supply Chain modeling 101 (Part B): Shipping cost & manufacturing locations
ADVANCED: Create a VBA function to calculate the distance between two locations
Supply Chain 201 (PART A): Elegant Optimization? Just add DATA TABLES
Supply Chain 201 (PART B): Elegant Optimization? Just add DATA TABLES
Model Engine→Full multi-period P&L in just a few steps
RECAP: Of everything we’ve accomplished this section
Avoid public humiliation through Auditing and Error-checking
|Section 7: MODELING UNCERTAINTY: Amazing Scenario and Sensitivity analyses made dead simple|
MODELING UNCERTAINTY Section Intro
MODELING UNCERTAINTY – Course materials
You can’t predict the future, but you can glance at the range of possibilities
Upgrade your new P&L model into a scenario crunching machine
Use DATA TABLES to instantly get the answers to all of your scenarios at once
The star chart approach to understanding variable-level sensitivity
|Section 8: SLIDE-READY OUTPUT: Synthesis, Data visualization and instant presentations|
SLIDE-READY OUTPUT Section Intro
SLIDE-READY OUTPUT – Course materials
Create an OUTPUT tab to easily consolidate & manage all model output
Creating the charts you want, and saving your favorite charts with “templates”
|Section 9: CONCLUSION|
CONCLUSION: How to apply what you’ve learned to solve any new problem
COURSE HANDOUT – Accompanying slides for each lesson
- I’ve been building models and teaching excel for the better part of a decade: I learned the science and artistry of Excel problem-solving while at McKinsey and Google, and was consistently recognized for both my ability to analyze business problems as well as my ability to teach others in the firm. I covered a range of industries and functions, gaining experience in global supply chain, route-to-market, organizational design and national education system turnaround strategy.
- I am an experienced technology strategist: While at Google, I continued to refine and apply my excel modeling approach within Google’s Business Operations and Strategy team (Bizops). I worked on a diverse range of strategy problems including local shopping, digital offers and coupons, display ads, digital media and online privacy strategy.
- I am a published author: Frustrated by the sad state of job applications and resumes experienced firsthand through various recruiting activities at McKinsey and Google, I wrote and published a book “Stand out, fit in, get hired.” in order to help candidates better think through how to position themselves for roles to which they are applying.
- I am a proud South African.
- Lectures 0
- Quizzes 0
- Duration 50 hours
- Skill level All level
- Language English
- Students 1116
- Assessments Self