When one model handles scenario planning, and when a second file earns its keep

Leadership asks for best, worst, and base. You hit Save As, then Save As again. Six weeks later there’s a folder containing FY26_Model_FINAL_v4_CEO-edits_REVISED.xlsx and nobody can say which file is live.

The cost of that isn’t disk space. It’s structural divergence. Someone fixes a cost-of-goods formula in the Base file and forgets to replicate it in Downside and Upside. Now your three scenarios aren’t comparable. They’re measuring slightly different businesses, and when the CFO asks why the gross margin walk doesn’t reconcile across cases, you don’t have an answer. Worse, leadership notices the files don’t agree, stops trusting the numbers, and starts running their own math in a side spreadsheet.

The fix is one model, not a binary between “duplicate the file” and “give up on real scenarios.”

A scenario is a different set of inputs, not a different model.

The calculation logic (the formulas, the line-item hierarchy, the structure) should be identical across every case. If you can’t express your scenario as a changed input value, you don’t yet understand what you’re modeling. So the question worth your time isn’t “how many files do I keep.” It’s this: does this scenario change my numbers, or does it change my structure? That answer decides whether one model is enough.

When one model wins (most of the time)

If your scenarios differ only in the values you plug in (faster or slower growth, higher or lower churn, fatter or thinner margin), one toggled model handles all three. Here’s how to find what actually varies.

Run a one-variable sensitivity sweep on your existing forecast. Move each major input by ±20% in isolation and rank by impact on earnings before interest, taxes, depreciation, and amortization (EBITDA). The handful that dominate (usually three to five) become your scenario drivers. Everything else gets held at Base and documented as held.

Take a fictional mid-market software company, Acme Cloud, entering the year at $20M in annual recurring revenue (ARR). All figures here are illustrative. The sweep comes back like this:

Input variable	±20% change	EBITDA impact	Driver?
Net new ARR / quarter	±$300K	±$4.1M	Yes
Annual gross margin %	±14 pts	±$3.8M	Yes
Monthly gross revenue churn	±0.3 pts	±$3.4M	Yes
Sales headcount (quota-carrying)	±4 reps	±$1.0M	No (held at Base)
G&A spend	±20%	±$0.4M	No (held at Base)
Committed lease obligations	fixed	$0	No (contractual)

Three drivers explain the overwhelming majority of the variance. Revenue and gross margin lines usually dominate that list, because uncertainty tends to concentrate at the top of the profit and loss statement (P&L). (Not universally. In energy, airlines, or agriculture, a commodity input can swing as hard as revenue, so run the sweep rather than assuming.)

Now every scenario value lives in one place: a dedicated Inputs tab, rows for assumptions, columns for Downside, Base, and Upside, plus a Rationale column in plain English and an Owner column naming who’s accountable. VP Sales owns net new ARR. VP Customer Success owns churn. The CFO owns gross margin. Put a “last updated” date at the top, because a stale scenario presented as current destroys credibility faster than no scenario at all.

A single dropdown cell drives the whole model. One clean way to wire it in Excel:

=CHOOSE(MATCH(Inputs!$B$1,{"Downside","Base","Upside"},0), Inputs!B5, Inputs!C5, Inputs!D5)

MATCH() turns the text selection into 1, 2, or 3. CHOOSE() returns the matching column. Flip B1 from Base to Downside and every downstream output recalculates, no file duplication. (It’s one valid pattern, not the only one. Excel’s Scenario Manager or named ranges can fit larger models better; pick what your team can maintain.)

Then build the deliverable leadership actually wants: a summary page that shows all three at once, wired directly to each Inputs column so it ignores the toggle state.

Metric	Downside	Base	Upside	Spread
EOY ARR	$17.5M	$23.0M	$28.5M	$11.0M
Recognized revenue	$21M	$27M	$34M	$13M
Gross profit	$13.2M	$18.9M	$25.2M	$12.0M
EBITDA	($2.5M)	$4.5M	$10.5M	$13.0M
Free cash flow	($4.0M)	$2.0M	$8.0M	$12.0M

The $13M EBITDA spread, driven by three changed inputs, is the story. The Spread column tells the board the range you’re planning across. Add three plain-English bullets under each case — what each scenario assumes about ARR, churn, and margin — because numbers without narrative are half a deliverable.

When you genuinely need a second model

The toggle stops being enough when a scenario changes your structure, not just your values. “Acquire the company” versus “don’t” can mean different revenue lines, a different capital structure, debt service that doesn’t exist in Base. A new product or new market introduces rows the Base model doesn’t have. In those cases a second tab or file is the right call, not a sign you failed. The rule of thumb: if the scenario changes only numbers, toggle it. If it changes which rows exist or how formulas are built, build a separate tab with a strict naming convention and a clear bridge back to the main model.

What never works is gluing separate files together with cross-workbook links. In shared-drive and email workflows they break the moment a file is moved or renamed, and reconciling them by hand defeats the entire point.

The test you run on your own situation

Before you decide architecture, ask one question of each scenario: can I express this as a changed input value? If yes, it belongs in the toggle. If it changes the shape of the model, it earns its own tab.

Then prove the wiring with a toggle test. Switch Downside, Base, Upside in turn and confirm every key output — revenue, gross profit, EBITDA, ending cash — actually moves. Any output that sits frozen has an orphaned hard-code typed into a formula cell, and you’ve just caught it. Protect the formula cells afterward so a well-meaning colleague doesn’t overtype the CHOOSE(MATCH()) with a number and break the cascade silently.

One more move turns the file from a board-deck artifact into an operational tool: give each scenario two or three observable, leading-indicator triggers. For Acme, Downside fires if Q1 pipeline coverage drops below 2.5x or January logo churn tops 1.8%. When a trigger fires in real life, you already know the EBITDA impact and the levers you’d pull. The scenario stops being a slide and becomes a playbook.

Build the model once, drive it from one dropdown, and reserve the second file for the scenarios that genuinely change its bones.