About this lesson
A scenario switch with CHOOSE or INDEX in Google Sheets
In Google Sheets, put each scenario's assumptions side by side and pull the live one with a switch cell: =CHOOSE($B$1,C4,D4,E4) or =INDEX(C4:E4,1,$B$1), where B1 holds 1, 2 or 3. Every formula in the model reads the live column, so changing B1 reruns the whole forecast.
Using Excel? Do this lesson in Excel.
=CHOOSE($B$1,C4,D4,E4)What you'll practise
Build a scenario switch into Waratah Wines' forecast so one cell flips every assumption between base, upside and downside, first with CHOOSE and then with INDEX.
4 steps · 40 points · the cells outlined in gold below
- scenario analysis
- CHOOSE
- INDEX
- scenario switch
- sensitivity
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Scenario (1, 2 or 3) | 1 | |||
| 2 | |||||
| 3 | Assumption | Live | 1 Base | 2 Upside | 3 Downside |
| 4 | Volume growth | 4% | 8% | -3% | |
| 5 | Price increase | 2.5% | 3.5% | 1% | |
| 6 | Gross margin | 38% | 40.5% | 35.2% | |
| 7 | |||||
| 8 | FY26 forecast | ||||
| 9 | FY25 revenue ($m) | 520.0 | |||
| 10 | FY26 revenue ($m) | ||||
| 11 | FY26 gross profit ($m) |