Scenario and Sensitivity Architecture: Building a Model That Can Be Stress-Tested

The toggle pattern

The standard implementation is a single scenario selector cell — 1 for base, 2 for downside, 3 for upside — with every scenario-dependent assumption pulled from a block of alternatives using INDEX or CHOOSE.

Lay the scenario block out as a table: one row per assumption, one column per scenario, with the live assumption in a separate column that reads from the selector. So revenue growth has a row with 4%, minus 6% and 8% across the three columns, and the live cell reads INDEX over that row using the selector as the position argument.

Everything downstream references the live column only. Nothing in the model should reference the scenario block directly, because the moment something does, changing the selector stops changing the whole model and you have a silent inconsistency.

CHOOSE is the more readable of the two functions for a small fixed set; INDEX scales better when the scenario block is a proper table. Either is fine. What matters is that exactly one cell controls the state.

• One selector cell, clearly labelled, prominently placed on the assumptions tab.
• A scenario name that displays somewhere on every output page, so a printed page always states which case it is.
• Every scenario-dependent assumption in one block, none scattered through the schedules.
• Downstream formulas reference the live column only, never the scenario table.

Choosing what actually varies by scenario

Not every assumption should be scenario-dependent. A model where forty inputs switch is unmanageable and, more importantly, produces incoherent scenarios — a downside where the tax rate also changes is not modelling anything real.

Restrict scenario switching to the drivers that genuinely differ between states of the world: revenue growth, margin, working capital behaviour, capex, and where relevant the exit multiple. Everything else — tax rates, interest rates on fixed-rate debt, depreciation schedules — stays constant unless there is a specific reason.

The test for including an assumption: would this genuinely be different in the world the scenario describes? If yes, switch it, and make sure it switches consistently with the others.

A downside case has to be about something

The most common failure is a downside built by reducing every input by a uniform percentage. It produces a lower number, and it tells a reviewer nothing, because no real downside works that way.

A credible downside starts from a cause: a specific demand shock, the loss of a major customer, a price war, an input cost spike. Then the consequences follow from the business’s actual structure. Revenue falls; the fixed and variable cost split determines how much of that drops through to EBITDA; working capital gets worse rather than scaling down proportionally, because receivables slow and inventory builds; capex can be cut, but only to maintenance levels.

That internal consistency is what a lender or investment committee is testing. A downside where EBITDA falls 30% but working capital improves in line with revenue is a downside nobody believes, and it undermines confidence in the base case too.

Sensitivity tables: mechanics and pitfalls

A two-variable data table shows one output across a grid of two inputs — typically IRR across entry and exit multiple, or valuation across WACC and terminal growth. It is the single most useful output in most models because it converts a point estimate into a range a decision-maker can reason about.

The mechanics catch people out. The output cell must sit in the top-left corner of the table, the row and column input cells must be the actual live input cells rather than copies of them, and the whole thing must live on the same sheet as those inputs unless you add a helper cell to bridge sheets.

Always verify a table by manually reproducing one corner: set the two inputs to the corner values, read the output, and confirm it matches. Tables that reference the wrong cell produce entirely plausible numbers, which is exactly what makes the error dangerous.

If a workbook is slow, data tables are usually why — they recalculate the whole model once per cell. Setting calculation to "Automatic except data tables" while building, then refreshing before output, solves it.

Presenting scenarios so they are actually used

A scenario summary page showing the key outputs for all three cases side by side, without anyone needing to flip the toggle, is what turns the architecture into something decision-makers engage with. Revenue, EBITDA, leverage at exit, returns or valuation — one column per case.

Pair it with the breakeven analysis, which is often more useful than any individual scenario: how far can the key driver move before the answer changes? How much can EBITDA fall before the covenant breaks, or before the IRR drops below hurdle? A single number that says "this deal works unless volumes fall more than 18%" is worth more than three scenarios.

And label the cases honestly. A "downside" that most participants would call the realistic case, with a "base" that is management’s ambition, is a labelling problem that misleads everyone including the person who built it.