ModeldrillScenario switch
A scenario switch is one input cell (1, 2, 3 or a name) that every assumption reads with CHOOSE or INDEX/MATCH, so changing it flips the whole model. =CHOOSE($B$1, base, upside, downside) picks by number; =INDEX(cases, MATCH($B$1, case_names, 0)) picks by name.
Syntax
=CHOOSE(case_number, value1, value2, value3) =INDEX(case_range, MATCH(case_name, name_range, 0))| Part | What it is |
|---|---|
case_number | The switch cell: 1 for base, 2 for upside, 3 for downside |
value1... | The assumption for each case, usually three adjacent cells |
case_name | A text switch, often a data validation drop-down: Base, Upside, Downside |
Examples
Growth cases: base 5%, upside 8%, downside 2%; switch in B1 = 2
=CHOOSE($B$1, 5%, 8%, 2%)Result: 8%
By name: B1 = "Downside", names in C3:E3, values in C4:E4
=INDEX(C4:E4, MATCH($B$1, C3:E3, 0))Result: 2%
Block of assumptions in rows: year 1 revenue cases in C10:E10
=INDEX(C10:E10, $B$1)Result: the case in column number B1
Common mistakes
- CHOOSE returns #VALUE! because the switch is 0 or text; it needs an integer from 1 to the number of values.
- Scenario values hard-coded inside the CHOOSE formula instead of in visible cells, so nobody can see or audit the cases.
- Forgetting to lock the switch reference ($B$1) when copying the formula across years.
Practice Scenario switch
These drills check your answer on live data, free, no account needed.
- Scenario switch with CHOOSEThree growth cases sit in rows 5 to 7. The number in B2 picks one. Fill the live growth row (row 8) with CHOOSE so it follows the switch, then revenue (row 9) grown by the live rate.
- Scenario lookup by namePick a case by typing its name in B2. Fill the live case row (row 9) with INDEX and MATCH so each column pulls its value for the named case. Write B9, fill right to E9, then work out operating profit in B10: (price less unit cost) times units, less fixed costs.
Questions
CHOOSE or INDEX for scenarios?
CHOOSE is simplest for 2 to 4 cases in a row. INDEX/MATCH scales to many cases, works with named drop-downs and lets you add a case without editing every formula.
How do I run all scenarios at once?
Use a one-variable Data Table with the switch cell as the column input and 1, 2, 3 down the side; each row shows the outputs for that case.
Related
- Sensitivity tableHow to build a sensitivity table with Data, What-If Analysis, Data Table: layout, row and column input cells, exact NPV examples and fixing wrong results.
- Revenue growth and CAGRCAGR = (end value / start value)^(1 / years) - 1, or =RRI(years, start, end) in Excel. Year over year growth, exact examples and the off-by-one trap in n.
- DCF model stepsThe six steps of a DCF: forecast unlevered free cash flow, set WACC, compute terminal value, discount it all, bridge to equity value and divide by shares.
- Operating leverageDegree of operating leverage = contribution margin / EBIT = % change in EBIT / % change in sales. An example where a 10% sales rise lifts EBIT 40%, and why.