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
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
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
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
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
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
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
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”
CONCLUSION
- CONCLUSION: How to apply what you've learned to solve any new problem
- COURSE HANDOUT - Accompanying slides for each lesson
