Stratrix
Start here

The room argues about the smallest bar

Picture the meeting. A team wants $3 million to launch a subscription product. The model says the launch is worth $3.2 million after paying back the investment, over five years. Then someone from finance says the discount rate should be 12 percent, not 10, and the next forty minutes go to the discount rate.

Run the numbers at 12 percent and the launch is still worth $2.8 million, about $0.4 million less. Even the whole range finance might fight over, 8 to 12 percent, moves the answer by only $0.8 million, and never comes close to changing the decision. Meanwhile, nobody asked the question that could have: how many customers sign up in the first year? Move that one number from 2,000 down to a pessimistic 1,200 and the launch is worth minus $1.1 million. Up to 2,600, and it is worth $6.4 million. One assumption swings the answer by $7.5 million, nine times the entire range of the one the room fought over.

That example is invented, and the figures come from the workbook on this page. But the pattern is worth knowing before you build your own: the assumption people argue about is the one they understand, and that is not the same as the one that decides. Sensitivity analysis is the habit of finding out which is which before the meeting.

A model's answer is a guess. Its ranking of what matters is the part you can act on.

Start here

What a sensitivity analysis answers

A sensitivity analysis changes one assumption at a time, holds the rest at their base values, and records how far the answer moves. Done for every input, it produces a ranking: the inputs that move the answer most at the top, the ones that barely matter at the bottom. Decision analysts draw that ranking as a tornado chart (also called a tornado diagram), named for its resemblance to a tornado: one horizontal bar per input, the widest range at the top and the narrowest at the bottom 2.

The ranking answers three questions a decision-maker actually has:

  • Which assumption could change the decision? Not which is uncertain, since all of them are, but which one, at a plausible extreme, flips the answer from yes to no.
  • Where should the next week of work go? The top bar is where research, a pilot or a customer test earns its cost. The bottom bars can be left at a sensible guess.
  • What would we have to believe? The break-even value of the top input, the point at which the answer is zero, turns a forecast into a question someone can check: do we believe we will sign at least this many customers?

Notice what it does not answer. A tornado chart moves one input at a time, so it misses inputs that move together, such as price and volume, where a higher price usually means fewer customers. For those, run a scenario instead, or a two-way table (both covered below). And it says nothing about how likely each extreme is. That is a judgment the team has to make when it sets the ranges.

In practice

Set the ranges before you look at the answer

The ranking is only as honest as the ranges. Give one input a tight range and another a wide one, and you have decided the chart before drawing it. Three rules keep the ranges fair.

  1. Use the same meaning of low and high for every inputA workable standard is a value you would be surprised to see beaten on each side: a low you would bet against nine times in ten, and a high to match. Whatever you choose, say it on the chart and apply it to every bar.
  2. Ask the person who knows, not the person who wantsThe sales lead's low case for first-year customers is rarely the low case. Ask whoever has seen the last three launches, or take the range from a comparable launch, and write down where each number came from.
  3. Write the ranges down firstFill in the low, base and high columns before you open the results. A range adjusted after seeing the chart is a range chosen to produce the chart.
Input (fictional launch)LowBaseHigh
Customers in year one1,2002,0002,600
Customer growth a year10%25%40%
Price per customer a year$1,000$1,200$1,400
Fixed cost a year$1.0 million$1.2 million$1.5 million
Cost to serve a customer a year$250$300$400
Upfront investment$2.5 million$3.0 million$4.0 million
Discount rate8%10%12%
In practice

Build the tornado chart in Excel

You need the model on one sheet, with every assumption in its own input cell, and the answer (here, net present value) in one output cell. If an assumption is typed inside a formula, pull it out into a cell first; a number buried in a formula is an assumption nobody can test.

  1. List the inputsOn a new sheet, one row per input: its name, low, base and high values.
  2. Compute the answer at each extremeFor each row, recalculate the output with that input at its low value and everything else at base, then again at its high value. With a small model you can write the formula once per row; with a large one, use a one-variable data table for each input (next section) or copy the model's calculation onto the row.
  3. Measure the swingSwing is the absolute difference between the high and low results: =ABS(high - low). Sort the rows by swing, largest first.
  4. Draw the barsInsert a clustered bar chart of the two result columns, each measured as its distance from the base answer (result minus base). Set the series overlap to 100% so the two bars share a line, and set the vertical axis to cross at zero. Excel draws bar charts bottom-up, so tick 'Categories in reverse order' on the vertical axis to put the widest bar on top.
  5. Label what mattersShow the base answer as the center line, and add the values at each end of the top three bars. Title the chart with the finding, not the method: 'First-year customers decide this launch' rather than 'Sensitivity analysis'.

The workbook on this page does the first four steps with formulas: change any low, base or high value and the ranking, the swings and a text bar for each input update on their own. The chart itself is the five-minute last step above.

In practice

Data tables: one input across many values

A tornado chart tests two values per input. When the top bar needs a closer look, a data table runs the model across a whole column of values at once, with no copying. In Microsoft's words, "A data table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem" 1. It handles one input or two, no more 1.

  1. Lay out the valuesIn a column, type the values to test: for example, first-year customers from 1,000 to 3,000 in steps of 250.
  2. Point at the answerIn the cell one row above and one column to the right of the first value, type a formula that refers to the output cell, such as =NPV_cell.
  3. Run itSelect the whole block (the values and the formula column), then Data > What-If Analysis > Data Table. Leave 'Row input cell' blank, and set 'Column input cell' to the model's input cell for first-year customers. Excel fills in the answer for every value 1.
  4. Go two-way when two inputs move togetherPut one input's values down the column and another's across the row, with the output formula in the corner cell, and fill both input cells in the dialog. This is the honest way to test price against volume.
In practice

Turn the top bar into a question someone can check

The most useful number in the whole analysis is often the break-even: the value of the top input at which the answer is exactly zero. In Excel, Goal Seek finds it (Data > What-If Analysis > Goal Seek: set the output cell to 0 by changing the input cell).

In the fictional launch, the break-even price is about $933 a year, against a planned $1,200, and the break-even for first-year customers is about 1,400, against a planned 2,000. Put that way, the question for the room is no longer 'is the model right?' but 'will at least 1,400 customers sign in year one?', which a pilot or a pre-sale campaign can answer in weeks. A forecast invites debate. A threshold invites a check.

  • Find the break-even for the top two or three inputs, not all of them. The bottom bars cannot flip the decision within their ranges, so their break-evens do not matter.
  • Say how far the base case sits from each break-even. 'Price can fall 22 percent, or first-year customers 30 percent, before the launch stops paying' is a sentence a board can weigh.
  • Name who will check it, and by when. The analysis is finished when the top assumption has an owner and a test, not when the chart is drawn.
Advanced

Mistakes that make the chart lie

  • Plus or minus 10 percent on everything. It looks fair and is not: a 10 percent move in the discount rate (10 to 11 percent) is small, while a 10 percent miss on first-year volume is optimistic. Ranges should reflect what is plausible for each input, not one percentage for all.
  • Testing outputs instead of inputs. 'Revenue' is not an assumption; it is price times customers. Vary the inputs the team is actually guessing.
  • Forgetting links between inputs. If a lower price brings more customers, test them together in a two-way table or a scenario, or the chart will overstate the risk of each alone.
  • Leaving out the input nobody wants to discuss. The one assumption the sponsor never put in a cell (the launch date, a regulator's approval, a key hire) is often the widest bar of all. Put it in the model and give it a range.
  • Showing the chart without the decision. End with what it means: which number is being tested, by whom, and what result would stop the project.

When several inputs are uncertain at once and you need a sense of the odds, the next step up is a Monte Carlo simulation, which draws every input from a range many times over. It needs more care with the ranges, not less. Start with the tornado chart; it tells you which ranges deserve that care.

Free editable template · XLSX

Sensitivity analysis and tornado workbook

A free, editable Excel workbook built from this guide: the template itself, how to fill it in, and the worked example and review checklist where the guide has them. Stratrix branded, and sent to your work email.

We'll email you a link to open it. Requesting it adds you to the Stratrix email list; unsubscribe anytime. Privacy policy.

Questions

What is a sensitivity analysis?
A sensitivity analysis changes one assumption in a model at a time, holding the others fixed, and measures how much the result moves. Ranking the assumptions by that movement shows which ones could change the decision and which barely matter.
How do you do a sensitivity analysis in Excel?
Put each assumption in its own cell, then recalculate the result with each assumption at a low and a high value. A one-variable data table (Data > What-If Analysis > Data Table) runs one input across many values; a two-variable data table tests two inputs together. Rank the swings and draw them as a tornado chart.
What is a tornado chart?
A tornado chart is a horizontal bar chart with one bar per assumption, showing how far the result moves when that assumption goes from its low to its high value. Bars are sorted widest first, so the chart narrows from top to bottom like a funnel cloud.
What is the difference between sensitivity analysis and scenario analysis?
Sensitivity analysis moves one input at a time to see which matters most. Scenario analysis moves several inputs together to describe a coherent future, such as a recession case. Use sensitivity analysis to find the inputs that matter, then build scenarios around them.
How do you find a break-even value in Excel?
Use Goal Seek: Data > What-If Analysis > Goal Seek, set the result cell to zero, and choose the input cell to change. Excel finds the value of that input at which the result is exactly zero.

Sources

  1. Microsoft (2024). Calculate multiple results by using a data table. Microsoft Support.
  2. Kattan, M. W. (ed.) (2009). Tornado diagram, in Encyclopedia of Medical Decision Making. SAGE Publications.

For information and strategic analysis only, not investment advice. Stratrix does not recommend buying, selling or holding any security.

Tools to try

More from Stratrix

Strategy Packs
The Road Not Taken

“What did the decision you did not take actually cost?”

One decision per pack: the framework, a working model, the sourced cases and the board material.

See the pack — $499 →

Stratrix Team

Every Casebook, three Strategy Packs a seat each year and Ask Stratrix, for teams that face these decisions all year.

$1,999 a year · $499 a seatWhat is included →

Weekly email

Stratonomics, every Tuesday

One data story every Tuesday: what happened, what it means, and the figures you can quote. Free.

We'll email you a link to confirm. Unsubscribe anytime. Privacy policy.