◆ Formulas II · drill · Spreadsheet Formulas & Data track
Scenario switch with CHOOSE
a self-referencing IF snapshots each case as you visit it — and freezes it, so a revised assumption moves the live model while the summary holds the old number until you go back.
Build the scenario switch the way a real model does it. One cell drives the page: a driver block reads it and repositions every assumption, the model runs off the driver instead of off the case columns, and a capture row snapshots each case as you visit it so the summary shows all three side by side. Then the deal team revises a case after you have already snapshotted it — and the summary will not move until you go back for it.
The shortcuts in this drill
Shift+↓⇧↓ on macExtend selection one cell
Ctrl+D⌘D on macFill down from the cell above
Build the logic once in the top cell, select down, and stamp it — the standard way to fill a schedule.
Shift+→⇧→ on macExtend selection one cell
Nudge a selection one cell at a time when the block you need doesn’t end at a data edge.
Ctrl+R⌘R on macFill right from the cell to the left
Same as fill down, but across — one quarter’s formula becomes the whole year in two keystrokes.
functions you'll type: CHOOSE() · IF()
The optimal line
the par-setting sequence, straight through:
driver C9 =CHOOSE($C$3,C5,D5,E5) · fill C9:C11 ↓ · revenue D14 =C14*(1+$C$10) · fill D14:G14 → · EBITDA C15 =C14*$C$11 · fill C15:G15 → · capture D19 =IF($C$3=D$18,$G$15,D19) · fill D19:F19 → · C3 → 1 · 2 · 3 · then E6 → -0% and C3 → 3 · ctrl+s
More Formulas II drills