A scenario selector reduces a model's case assumptions to one input cell. Base, Upside and Downside sit as columns of driver values in a single table, the selector names the column to read, and every assumption in the model points at that column. Move the drop-down and revenue, costs, working capital, tax, debt and cash move together, because each one was reading the same active drivers.

The pattern that often replaces it is three hard-coded copies of the statements with a switch that toggles row visibility. That arrangement survives the first month and then drifts. One copy gets edited, the balance sheet is rebuilt for two of the three cases, and the file stops being something a reviewer can sign off cheaply, because no single cell explains which case the numbers represent.

The switching mechanism itself is worth treating as its own piece of work. How to lay out the driver table, the two formulas that do the switch, why the switch belongs at driver level, how to carry the chosen case onto every report page, how to freeze a snapshot so a board pack stops moving, and the specific ways a selector breaks.

Structuring the Scenario Driver Table

The driver table is the whole mechanism, so the layout matters more than any formula written on top of it. One row per driver, one column per scenario, and a header row carrying the scenario names as plain text. Everything else in the model reads this block.

Four properties decide whether the table works. Scenarios run across columns, so a new case is a new column and never a new block of statements. Negative values sit in brackets, which is the convention a reviewer expects and which keeps a percentage column readable. The header row holds text and nothing formatted, because a formula reads those cells as names. And the table stays contiguous, with no blank rows, merged cells or subtotal lines inside the block.

Give the block two range names. ScenNames covers the header row. ScenSel points at a single cell on the assumptions sheet, which is where the drop-down goes.

Drivers sheet

     C                  D          E          F
3    scenario header    Base       Upside     Downside     <- ScenNames ($D$3:$F$3)
4    Volume growth      0.040      0.080      (0.020)
5    Price uplift       0.025      0.040       0.000
6    Monthly churn      0.012      0.008       0.024
7    Cost inflation     0.030      0.045       0.020
8    Wage growth        0.035      0.040       0.030
9    Term (years)       5          5           5

B2   ScenSel            <- named cell holding the validated drop-down

Set the selector up through Data Validation, choose List, and point the Source at =ScenNames. The drop-down is then bound to the same header row the formulas read, so a scenario that exists in the table is selectable and nothing else is. Fill the cell amber so the one input that moves the whole model is obvious when the file is opened, and leave it unlocked if the sheet is protected.

The Switch Formula With CHOOSE and MATCH

The selector holds a name, and the driver needs a value. MATCH converts the text in ScenSel into a position against ScenNames, and CHOOSE returns the argument sitting in that position.

Active volume growth (Inputs!C4)

=CHOOSE(MATCH(ScenSel, ScenNames, 0),
        Drivers!D4, Drivers!E4, Drivers!F4)

The zero in MATCH forces an exact match, which matters here. A scenario name matched approximately or with a wildcard returns the wrong column without any warning, and the model will keep calculating. CHOOSE takes the index and returns the corresponding argument, so the formula reads as three explicit choices with the selector deciding between them.

The values behind the switch, for a business with prior-year revenue of $10,000,000, look like this.

Base is $10,000,000 multiplied by 1.04 and then by 1.025. Upside is $10,000,000 multiplied by 1.08 and then by 1.04. Downside is $10,000,000 multiplied by 0.98, with the price line flat at zero.

The formula repeats three cell references on every driver row. A thirty-row driver table carries ninety references to maintain, and each new scenario adds one more to each of those rows. That cost is what the next formula removes.

Scaling Beyond Three Scenarios With INDEX and MATCH

INDEX reads a contiguous span and MATCH supplies the offset into it, so the formula never names a scenario at all.

Active volume growth (Inputs!C4)

=INDEX(Drivers!$D4:$F4, MATCH(ScenSel, ScenNames, 0))

ScenNames = Drivers!$D$3:$F$3

Adding a fourth case means inserting a column inside $D:$F and widening ScenNames to $D$3:$G$3. Every driver formula keeps working, because each one reads the header row at run time. The CHOOSE version of the same model needs an edit in every driver row to place the new argument correctly, and the failure mode of a missed row is silent: a single driver stays on the old case while the rest of the model moves to the new one.

The crossover sits around six scenarios. Below that, CHOOSE is easier to review because the case names are visible in the formula itself. Above it, INDEX and MATCH is the version that stays maintainable, and it also survives a reordered driver table without a formula change.

A scenario label cell reads the same header row, so a report can print the case name in words.

Scenario label (Cover!C6)

=INDEX(ScenNames, MATCH(ScenSel, ScenNames, 0))

Two rules keep that label trustworthy. The scenario order in the driver header row must match the order of the value columns, because MATCH resolves the name and INDEX then counts across the same span. And the selector cell should only ever hold a value from ScenNames, which the drop-down enforces as long as the validation source stays bound to the named range.

Switching at Driver Level So the Statements Move Together

Every figure in a three-statement model traces back to a driver. Volume and price feed revenue, churn feeds the revenue base, wage growth feeds payroll, and cost inflation feeds the cost base. Put the switch on the driver values and those effects flow through the profit and loss, the balance sheet and the cash flow in a single recalculation.

Put the switch on an output cell instead, say a revenue line on the profit and loss, and the drivers behind debtors, tax, capital expenditure and debt stay on whichever values the table last held. The pack then claims the upside case while the balance sheet and cash flow are built from base assumptions, and the tell is a cash flow that fails to reconcile to the movement in the balance sheet.

The checks that keep a switch at driver level are quick to run.

• Every assumption cell contains a formula, a driver reference, or a marked constant. A typed number sitting in an output row is the first thing a reviewer flags.
• Scenario values live in one place. Where the same wage growth figure appears twice, one copy is already stale.
• The cash flow is built from the movement in the balance sheet balances rather than from an independent schedule, so the two views cannot disagree under a case change.
• The retained earnings roll-forward ties to the profit and loss for whichever case is live, checked month by month.
• Tax, dividends and interest are calculated from the drivers of the active case, so a scenario change moves the balance sheet in the same recalculation as the profit line.

Presenting the Live Scenario Across a Report Pack

A case label on the cover is not enough. Every page that carries a number should carry the case it belongs to, because pages get printed, emailed and screenshotted apart from the file.

Copy the header block to each sheet, reference the same cells, and format the scenario line with conditional rules so the case is visible at a glance: an amber fill for Downside, a green fill for Upside, and neutral for Base. The rules use a formula test against the label cell, which means the colour follows the switch without any manual step.

Two small additions make the pack safer. A validation cell that reports when the selector is empty, using a formula such as =IF(ScenSel="","Scenario not selected",""), catches the case where someone clears the cell and leaves every driver returning an error. And a driver table version constant gives the reviewer something to point at when two versions of a pack are compared, since the numbers can differ because the case changed or because an assumption was edited.

Snapshotting a Scenario for the Board Pack

A live switch is a problem the moment the file travels. A workbook sent to a board still holding the selector will show a different case when it is opened next month, and a PDF loses the driver table that produced it.

The fix is a values snapshot. With the case live, copy the driver table and the report ranges, then paste values onto an archive sheet named for the case and the period, for example Archive 2026-09 Upside. Move the selector back to Base afterwards, and the archive stays where it was, because nothing on that sheet is a formula.

Three details turn a frozen copy into a usable record. Store the driver values beside the snapshot numbers, so the case can be rebuilt and explained months later. Record the scenario label, the driver table version and the date taken in the archive header. And lock the archive sheets against edits, since a record anyone can overwrite is not a record.

One archive sheet per period keeps the workbook navigable. Name the sheet after the case and the month, leave the earlier snapshots in place, and a full year of board packs sits in one file that a reviewer can open without touching the live model.

Pitfalls That Break a Scenario Switch

Hard-coded outputs sitting over driver formulas. A pasted number in a revenue or cost row survives the switch and overrides it. The test takes ten seconds: move the selector and confirm the figure changes.

Hidden columns used as the switch. Hiding the inactive scenario columns is a presentation trick that changes what a person sees and leaves every formula reading the same cells. Keep the driver table in full view and let the selector do the switching.

A two-variable data table sharing the switch cell. A data table writes an input value, recalculates, and reads the output for each row. Point its row input at ScenSel and the table physically moves the live case as it runs, which can leave the selector sitting on the last scenario it evaluated. Drive sensitivity tables from a separate input cell that nothing else reads.

Circularity when a case changes the debt balance. A downside case that assumes a covenant breach and a facility repayment introduces a loop, because the repayment reduces interest, which changes cash, which changes the debt balance the repayment was calculated from. Build the debt schedule as opening balance plus drawdowns less repayments, keep the repayment rule as a driver, and switch the rule with everything else.

Scenario names drifting away from the header row. Rename a scenario in the driver header without updating the validation source and the selector holds a text value MATCH cannot find, so every driver returns #N/A. Flip through each case after any rename.

A selector cell inside a protected sheet. Protect the sheet with the selector still locked and the drop-down becomes read-only, which freezes the model on one case until someone unlocks the sheet.

A scenario selector is a small piece of engineering: one named cell, one header row of case names, and one INDEX and MATCH per driver. What it buys is a model where a single action sets the case, where the three statements cannot disagree about which case is live, and where a board pack can be frozen with the numbers and the drivers that produced them. Build the driver table first, point every assumption at it, and the switch becomes one cell the rest of the file simply follows.

Related reading

• Scenario Analysis & Sensitivity Testing in Financial Models
• Driver-Based Financial Forecasting in Excel
• How to Create and Use 3-Way Financial Models