Section Learning Objectives
Three Types of What-If Analysis
"What-if analysis" is the umbrella term for techniques that change inputs to see how outputs respond. There are three main types, each with increasing complexity and realism.
Sensitivity Analysis
Change one or two inputs at a time
Inputs: 1–2 variables
Output: Table of results
Example: What happens to NI if revenue growth varies from −5% to +15%?
Scenario Analysis
Change multiple inputs simultaneously
Inputs: 3–10+ variables
Output: Bull/Base/Bear cases
Example: What happens if revenue, margins, AND CapEx all change together?
Simulation (Monte Carlo)
Run thousands of random scenarios
Inputs: Probability distributions
Output: Probability ranges
Example: "There's a 90% chance NI will be between $50M and $120M"
Identifying Key Model Drivers
🎯 Which Inputs Matter Most?
Not all assumptions are equally important. Focus your sensitivity analysis on the variables with the biggest impact on output.
| Driver | Impact | Typical Range |
|---|---|---|
| Revenue Growth Rate | ⭐⭐⭐ Very High | −5% to +20% |
| Gross Margin | ⭐⭐⭐ Very High | ±3–5 percentage points |
| CapEx % of Revenue | ⭐⭐ High | 3% to 15% |
| Working Capital Days (DSO, DIO, DPO) | ⭐⭐ High | ±10 days |
| Interest Rate on Debt | ⭐ Moderate | ±1–2% |
| Tax Rate | ⭐ Moderate | 20% to 35% |
The CHOOSE Function Approach
🔄 CHOOSE: The Professional Scenario Switch
The CHOOSE function lets you toggle between scenarios using a single switch cell. This is the industry-standard approach used at investment banks.
Example — Revenue Growth Rate:
=CHOOSE($C$3, 3%, 8%, 15%)
Where C3 = scenario selector (1=Bear, 2=Base, 3=Bull)
Example — Gross Margin:
=CHOOSE($C$3, 38%, 42%, 45%)
Setup:
1. Create a scenario selector cell (e.g., cell C3 = 1, 2, or 3)
2. Replace each hardcoded assumption with a CHOOSE formula
3. Change C3 to switch between Bear (1), Base (2), Bull (3)
4. All outputs update automatically!
Always keep the scenario selector cell prominently visible at the top of your model. Use conditional formatting (green/yellow/red) so users instantly know which scenario is active. Label it clearly: "1 = Bear | 2 = Base | 3 = Bull".
📥 Hands-on: Scenario Manager Utility
Download the provided CSV template to practice building a dynamic Scenario Selector in Excel using the CHOOSE function.
Dynamic Scenario Toggle (CHOOSE Function)
• 1 - Bear Case: Rev Growth 5%, GM 30%, CapEx 12%
• 2 - Base Case: Rev Growth 10%, GM 35%, CapEx 10%
• 3 - Bull Case: Rev Growth 15%, GM 40%, CapEx 8%
Question: Using the downloaded CSV file, set up a dynamic "Active Scenario" switch in Cell C9. Use the `CHOOSE` function to dynamically pull the correct driver values into cells B13, B14, and B15 depending on the value entered in C9.
2 into this cell to act as the "Base Case" toggle. Make this cell stand out with a yellow background and bold blue text.=CHOOSE($C$9, B4, C4, D4)(Assuming B4=5%, C4=10%, D4=15%)
This tells Excel to look at C9 (which is 2), and output the 2nd value in the list (C4 = 10%). Notice the absolute reference `$C$9` so we can copy this cell downwards.
B14 will become:
=CHOOSE($C$9, B5, C5, D5) for Gross Margin.B15 will become:
=CHOOSE($C$9, B6, C6, D6) for CapEx.Change cell C9 to
3 and ensure all values instantly jump to the Bull Case inputs (15%, 40%, 8%).Key Takeaways
- Sensitivity = change 1–2 inputs, Scenario = change many inputs, Simulation = thousands of random inputs
- Focus on key drivers: Revenue growth and gross margin typically have the largest impact
- CHOOSE function is the professional way to build scenario switches into your model