Sensitivity analysis

Key takeaways

Vary the inputs you're least certain about. Watch how much the output moves. The inputs that move the output the most are the ones to argue about — and the ones to sensitise to.
Use odd dimensions (5×5, 7×7) so there's a true center cell. The center cell must equal your base-case output — that's the sanity check.
Build axes symmetrically around the base case: [base − 2×step, base − step, base, base + step, base + 2×step].
No linear approximations. Each cell must be a full model recomputation for that input combination, not = base × (1 + Δ).
Sensitivity ≠ scenario. Sensitivity moves one or two variables at a time. Scenario analysis moves a coherent set of variables together (bear / base / bull). Use both — they answer different questions.

Why it matters

Every financial model output is a function of dozens of assumptions. A DCF looks like a single number — "$19.99 implied share price" — but that number is the output of WACC, terminal growth, revenue trajectory, margin path, capex intensity, and a dozen other inputs. Each is an estimate. Reporting the single number without showing how it moves under plausible alternatives gives the IC, the partner, or the client false precision.

Sensitivity analysis is the formal way to honest. It takes the inputs you're least sure about, varies them across plausible ranges, and shows what the output looks like across that surface. Two-variable tables are the standard format because most output uncertainty is dominated by two or three drivers — sensitising to all of them at once would be unreadable.

Two-variable data tables — the standard format

Excel's Data Table feature does the heavy lifting. The mechanics:

Place the model output in a single cell — $B$2, say. This is the formula being sensitised; everything in the model points to it.
Lay the table out with the output formula in the top-left corner: =$B$2.
Put the column-axis values across the top row of the table (e.g., WACC values: 8%, 9%, 10%, 11%, 12%).
Put the row-axis values down the left column (e.g., terminal growth: 1.5%, 2.0%, 2.5%, 3.0%, 3.5%).
Select the table including the corner formula. Data > What-If Analysis > Data Table.
Row input: the cell in the model that holds the column-axis variable (the WACC cell). Column input: the cell that holds the row-axis variable (the terminal growth cell).
Excel substitutes each axis value into the input cell, recomputes the model, and writes the output value into the table.

This is not a manual exercise. Hand-typing values into the table or building =base × adjustment formulas defeats the point — you want each cell to reflect the actual model under those inputs, not a linear approximation.

Why odd dimensions, why symmetric

A 5×5 table has 25 cells with a true center. A 4×4 table has 16 cells with no center — the base case falls between cells and there's no sanity check. Always use odd dimensions: 5×5 is the standard, 7×7 if you need finer granularity.

Build the axes symmetrically around the base case:

Axis = [ base − 2 × step, base − step, base, base + step, base + 2 × step ]
Example: WACC base 10%, step 1% → [8%, 9%, 10%, 11%, 12%]

The middle cell — at the intersection of the base WACC and base terminal growth — should produce a value identical to the model's base-case implied share price. If it doesn't, the table is wrong. Either the data table inputs are pointing at the wrong cells, or the model's base case isn't where you think it is. This is the single most useful sanity check on the entire model.

Format the center cell with a medium-blue fill (#BDD7EE) and bold font so it's visually obvious which combination is the base.

Picking the right axes

Two-variable tables earn their cost when the two axes are the variables that actually drive the output. Sensitising to inputs that barely move the answer is wasted effort. Common pairings by model type:

Model	Axes	Output
DCF	WACC × Terminal growth	Implied share price
DCF	WACC × Exit EV/EBITDA multiple	Implied share price
DCF	Revenue growth × EBITDA margin	Implied EV
LBO	Entry multiple × Exit multiple	IRR
LBO	Entry multiple × Exit multiple	MOIC
LBO	Hold period × Exit multiple	IRR
LBO	Revenue growth × EBITDA margin	IRR
NPV (project)	Discount rate × Year-1 cash flow	NPV
Returns (real estate)	Cap rate × Rental growth	Total return

Tornado charts — when one variable is the question

A tornado chart sensitises one variable at a time across a fixed range and ranks the inputs by absolute impact. The most-sensitive variable goes at the top, the least-sensitive at the bottom — producing a tornado-shaped pattern of bars.

Mechanics: pick a list of inputs (revenue growth, gross margin, capex, WACC, terminal growth, ...). For each, vary it from base − 2σ to base + 2σ — or pessimistic-to-optimistic plausible range — and record the output at each end. Plot the resulting bars sorted by total swing.

Tornado charts answer a different question than two-variable tables: "if I had to pick the single most important variable to nail down, which one?" Two-variable tables answer "given that I'm uncertain on these two, what's the range?". Use tornado for variable selection; use two-variable tables for the actual output range.

Scenario analysis vs sensitivity

These are different concepts and worth being precise about:

	Sensitivity	Scenario
What changes	One or two variables at a time	A coherent set of variables together
Other variables	Held at base	Move with the scenario logic
What it tests	Which assumptions matter	Plausible states of the world
Output	A surface or a tornado	Discrete points (bear / base / bull)
Best for	Stress-testing assumptions	Communicating range of outcomes

A bear-case scenario doesn't hold capex at base while only revenue drops — it might also assume tighter working capital, lower margins, and higher WACC, all together. That coherence is the point. Sensitivity tells you which assumption is most painful to be wrong about; scenarios tell you what happens if everything goes the same direction.

Both belong in a serious model. Sensitivity tables sit at the bottom of the valuation sheet; scenarios live in the assumptions sheet with an INDEX-based case selector that pulls the active scenario through the model.

The most common sensitivity errors

Sensitising to inputs that don't move the output. If the output is flat across the table, you picked the wrong axes — and the table tells the reader nothing.
Asymmetric or nonsensical ranges. A WACC table running 5% to 18% says nothing useful about a company whose plausible WACC is 9–11%. Centre the range on the base, step in plausible increments.
Linear approximations. =base_price × (1 + (base_WACC − this_WACC)) is a shortcut that produces wrong numbers — the relationship between WACC and EV is highly non-linear, especially near the perpetual growth rate. Use a real data table.
Center cell doesn't match base. If the centre of your sensitivity table doesn't equal your base-case output, the table is hooked up wrong. Trace the data-table inputs.
Conflating sensitivity and scenario. A "downside" column that holds capex at base while only stressing revenue is a sensitivity, not a downside scenario. Be precise about which one you're presenting.
Treating corner cells as predictions. The corner of the table is a stress test: what if WACC is 12% AND terminal growth is 1.5%?. It's not a forecast. Frame the table as a range, not a fan of forecasts.
Too many tables. Three is plenty. Five is sprawl. If every variable has its own sensitivity, the reader can't see what matters.

How Smalt AI builds it

Sensitivity tables ship alongside the base case in every model — never as an afterthought. For a DCF, that means three 5×5 tables (75 full-recomputation cells) covering WACC × Terminal growth → share price, WACC × Exit multiple → share price, and Revenue growth × EBITDA margin → EV. For an LBO, two 5×5 tables on Entry × Exit multiple → IRR and MOIC.

Real data tables. Cells contain Excel =TABLE(row_input, column_input) array formulas (entered as Ctrl+Shift+Enter) that recompute the entire model for each input combination. No linear approximations, no = base × adjust shortcuts.
Symmetric axes built around the base case at sensible step sizes (typically 1% steps for WACC, 0.5% for terminal growth, 1.0× for multiples).
Center-cell sanity check — the formula in the centre cell is verified to equal the base-case output before the workbook is delivered. If it doesn't match, we trace the data-table inputs and fix it.
Center cell formatted with medium-blue fill (#BDD7EE) and bold font so the base is visually obvious.
Color coding for returns tables in LBOs: green > 20% IRR, yellow 15–20%, red < 15%. Visible at a glance which corners of the surface are interesting.
Scenario framework co-exists alongside sensitivity. Bear / Base / Bull blocks in the assumptions sheet, an INDEX consolidation row pulling from the active block, and a single case selector cell to toggle. See the DCF and LBO write-ups for how this plumbs through.