Most financial models end with a sensitivity table. Revenue growth plus and minus ten per cent, margin plus and minus ten per cent, discount rate plus and minus one point. The table is dutifully produced, glanced at and filed. It rarely changes a decision, because it was never built to inform one.
Start from the decision
A sensitivity exists to answer a question: what would have to be true for the answer to change? If a project is approved because its net present value is positive, the useful question is how far each assumption would have to move for the value to fall to zero. That is a break-even analysis on each input, and it tells a decision-maker far more than a symmetrical grid does.
Framed this way, the output is not a range of values but a set of thresholds. Volume could fall by a third before the project destroys value; the price could fall by only five per cent. The second assumption now deserves the attention, whatever its position in the model.
Choose ranges from evidence
Flexing every input by the same percentage treats them as equally uncertain, which they almost never are. A contracted price may be known within a narrow band; next year's volume in a new market may be uncertain by half. The range for each input should come from evidence — historical volatility, the spread of analyst estimates, the terms of a contract, the realistic best and worst case of the people who run the operation — and should be stated with its source.
When ranges are chosen this way, the ranking of inputs by their effect on the output becomes meaningful. A tornado chart built on honest ranges shows which assumptions actually drive the result. One built on uniform percentages mostly shows which inputs happen to be large.
Separate sensitivity from scenario
Sensitivity analysis moves one or two inputs at a time. Scenarios move several together, because in reality they move together: a downturn lowers volume, squeezes price and lengthens customer payment terms at once. Both are useful, but they answer different questions, and presenting a set of single-input sensitivities as if it described a bad year understates the risk.
A sound model has a small number of coherent scenarios — each with a short narrative explaining why the inputs take the values they do — and a sensitivity analysis on the few inputs that matter most within the base case.
Build it so it cannot mislead
- Drive sensitivities from the inputs sheet. A sensitivity that overrides a calculation cell directly bypasses the model's logic and can produce results the model itself would never reach.
- Check the extremes. At the edges of each range, confirm that the model still behaves — that working capital does not turn negative in an impossible way, that debt does not become an asset.
- Report the thresholds in words. "The investment remains value-creating unless volume falls more than thirty per cent below plan" is a sentence a board can act on. A table of twenty-five numbers is not.
The test of a good sensitivity
After reading it, a decision-maker should know which one or two assumptions the answer depends on, how far they could move before the answer changes, and how confident the team is that they will not. If the analysis does not leave them knowing those three things, it has produced numbers without producing understanding.