Switch scenarios with a single cell: pick the active assumptions with the CHOOSE and INDEX functions by the scenario number, and guard the number with a check flag.
The scenario switch: the number in a single cell
The scenario switch: the number in a single cell
A scenario switch is a single cell with the scenario number that the model's assumptions depend on. In the Zerno coffee shop's model, the number is in B1: 1 — base, 2 — upside, 3 — downside. The assumptions of all the scenarios sit side by side in rows (B4:D5), and the “Active” column (E) takes one value from each row — from there on, the model calculates only from it.
A worked example — the active margin in E5: =CHOOSE($B$1,B5,C5,D5). CHOOSE(number, value1, value2, …) returns the value with that number: when B1 = 1, it's B5, that is, 20%; when B1 = 3, it's D5, that is, 16%. The number is locked with $, like the assumption in lesson 1.3: the formula is copied to the neighboring rows, and the reference to B1 must not shift.
INDEX(range, number) from lesson 3.4 does the same job: it takes the value with that number from the row. The calculation rows know nothing about scenarios: revenue 2027 = revenue 2026 × (1 + active growth), EBIT = revenue × active margin. Change the number in B1, and the whole model recalculates.
A number outside the list breaks the model, so a flag goes next to B1. AND(B1>=1, B1<=3) is TRUE only when both conditions hold; nested inside IF, it picks “OK” or “ERROR.”
Zerno's scenario assumptions (rows 4–5 of the sheet)
The active scenario's number is in B1, and the active values are in column E.
| Assumption | Base (1) | Upside (2) | Downside (3) |
|---|---|---|---|
| Revenue growth | 10 % | 15 % | 3 % |
| EBIT margin | 20 % | 22 % | 16 % |
The switch chain
- 1B1
Scenario number: 1, 2, or 3
- 2E4, E5
Active growth and margin: CHOOSE and INDEX by $B$1
- 3B8
Revenue 2027 from the active growth
- 4B9
EBIT 2027 from the active margin
- 5D1
Flag: a number from 1 to 3 — “OK,” otherwise “ERROR”
Lesson notes
The scenario switch: the number in a single cell
A scenario switch is a single cell with the scenario number that the model's assumptions depend on. In the Zerno coffee shop's model, the number is in B1: 1 — base, 2 — upside, 3 — downside. The assumptions of all the scenarios sit side by side in rows (B4:D5), and the “Active” column (E) takes one value from each row — from there on, the model calculates only from it.
A worked example — the active margin in E5: =CHOOSE($B$1,B5,C5,D5). CHOOSE(number, value1, value2, …) returns the value with that number: when B1 = 1, it's B5, that is, 20%; when B1 = 3, it's D5, that is, 16%. The number is locked with $, like the assumption in lesson 1.3: the formula is copied to the neighboring rows, and the reference to B1 must not shift.
INDEX(range, number) from lesson 3.4 does the same job: it takes the value with that number from the row. The calculation rows know nothing about scenarios: revenue 2027 = revenue 2026 × (1 + active growth), EBIT = revenue × active margin. Change the number in B1, and the whole model recalculates.
A number outside the list breaks the model, so a flag goes next to B1. AND(B1>=1, B1<=3) is TRUE only when both conditions hold; nested inside IF, it picks “OK” or “ERROR.”