Last updated October 2026
Give each uncertain input a low and a high value, then recalculate the output with one input at a time moved to each end while the others stay at their base values. Rank the inputs by how far the output swings, draw that ranking as a tornado chart, and use a Data Table to vary two inputs at once.
In Excel that takes six steps:
This guide works through one example, and every number in it is in a free workbook: download it and follow along. Everything except the last step works in any version of Excel with no add-in.
A company is deciding whether to launch a new product. The model is a five-year net present value (NPV): the initial investment at the start, then each year's cashflow of volume × (price − variable cost) − fixed costs, discounted at the cost of capital. With every input at its best guess, the base-case NPV is €155k, so the project looks worth doing. The question is which inputs could turn that into a loss, and which ones deserve more research before the decision.
In the workbook the base values are named cells (Investment, Volume, Growth, Price, VarCost, Fixed and Rate), and the NPV cell is called NPV. Names make the formulas below readable, and they stop a sensitivity table from silently pointing at the wrong cell when rows are inserted.
Investment
Volume
Growth
Price
VarCost
Fixed
Rate
NPV
Use the same definition for every input, for example a value you would be surprised to see beaten in one case out of ten at each end. A consistent definition is what makes the ranking fair. Ranges can be lopsided: here the variable cost can rise by €5 but fall by only €2, and that asymmetry is real information.
Avoid moving every input by the same ±10%. It ranks inputs by the size of their base value rather than by how uncertain they are: a discount rate of 9% ±10% moves less than one point, while the real uncertainty may be several points.
Write one row per input with its low value, its high value and the output for each. The output cell needs the model's result with that one input swapped, and there are two ways to get it.
Excel's one-variable Data Table. List the values to test down a column, put =NPV in the cell above and to the right of the first value, select both columns and choose Data → What-If Analysis → Data Table, with the input's cell as the column input cell. Excel recalculates the whole model for each value. It is the quickest way to scan one input across many values, but it needs one Data Table per input.
=NPV
A formula per row. For a tornado chart it is simpler to write the output as one formula and swap one name for the low or high cell. In the workbook the base-case NPV in one line is:
=-Investment+SUMPRODUCT((Volume*(1+Growth)^({1,2,3,4,5}-1)*(Price-VarCost)/1000-Fixed)/(1+Rate)^{1,2,3,4,5})
For the row “Year 1 sales volume, low”, replace Volume with the cell that holds the low volume, and so on for each row. The {1,2,3,4,5} array covers the five years, so no helper columns are needed. Keep one check row with nothing swapped: it must equal the model's NPV, or the one-line formula has drifted from the model.
{1,2,3,4,5}
The result, sorted by swing (the distance between the two NPVs):
Four inputs can push the NPV below zero on their own: first-year volume, variable cost, price and the initial investment. Fixed costs and the discount rate matter much less within their ranges.
A tornado chart is a bar chart of the one-way table, sorted so the input with the biggest swing sits at the top. Excel has no tornado chart type, but a clustered bar chart becomes one in five steps:
=SORTBY(A2:C8,D2:D8,-1)
RANK
INDEX
MATCH
Read it from the top. The longer the bar, the more that input matters to the decision within the range you gave it. A bar that crosses the break-even line belongs to an input that can turn the project into a loss on its own. Here that is the first-year volume, the variable cost, the price and the investment, so they are where better data is worth paying for.
This is a deterministic tornado chart: it moves one input at a time. A tornado chart from a Monte Carlo simulation ranks the inputs with all of them varying together, and it can rank them differently.
Put a reference to the output in a corner cell, one input's test values across the row to its right and the other input's values down the column below it. Select the whole block, choose Data → What-If Analysis → Data Table, and give the input cell for the row values and the one for the column values.
In the workbook the corner cell holds =NPV, the row holds first-year volumes and the column holds prices. The row input cell is the base volume and the column input cell is the base price. Two rules trip people up: the input cells must be on the same sheet as the Data Table, and Excel fills the table with a {=TABLE(…)} array that can't be edited cell by cell.
{=TABLE(…)}
NPV (€k) by price per unit and first-year sales volume
Red figures are losses, and the bold figure is the base case. The line between the red and the black is the trade-off the decision turns on: at €48 the product needs a little over 18,000 units in its first year to break even, and every euro off the price adds roughly 1,000 units to that.
Data Tables recalculate the whole model for every cell, so a large model with several of them gets slow. If yours show zeros or stale values, Excel is probably set to Automatic except for data tables: press F9, or change it under Formulas → Calculation Options.
Use Goal Seek. Choose Data → What-If Analysis → Goal Seek, set the NPV cell to 0 by changing one input, note the value it finds, then undo and repeat for the next input. The break-even value, sometimes called the switching value, says how much room each input has.
Price has the least room: a 4% cut wipes out the NPV. The tornado chart ranks volume first because its range is wider, while break-even values ignore the ranges altogether. Both views are worth having, and neither says how likely any of these outcomes is.
It can't tell you how likely a loss is. It moves one input at a time, so it never sees several inputs going wrong together, and it reports the base case as if it were the expected outcome. When most ranges lean towards the bad side, as they do here, the base case is optimistic.
To see all of that, give every input a distribution between its low and high values and let a Monte Carlo simulation vary them all at once. With PERT distributions on the same ranges, the example gives:
The mean NPV is about €74k, half the base case, and there is a 37% chance of losing money. One project in ten would end below −€194k and one in ten above €348k. None of that is visible in the one-way table, the tornado chart or the Data Table, because each of them only ever moves one or two inputs.
In ModelRisk, replace each input with a distribution, mark it with VoseInput and mark the NPV with VoseOutput. For the volume, for example: =VoseInput("Year 1 sales volume")+VosePERT(16000,20000,24000). Run the simulation, then choose Insert → Tornado → Conditional Mean in the Results Viewer. It ranks the inputs by how much each one moves the mean NPV across its range, with all the others still varying.
VoseInput
VoseOutput
=VoseInput("Year 1 sales volume")+VosePERT(16000,20000,24000)
The workbook's Path B sheet is this model, set up for ModelRisk, with a table of reference results to check your run against. Our article on reading tornado charts from a simulation covers the traps that make them mislead. ModelRisk is one of several Excel add-ins that do this; our comparison of risk analysis add-ins for Excel covers features and published prices side by side.
Download the sensitivity analysis in Excel workbook (.xlsx). It is free and needs no registration. To run Path B, start the free 15-day ModelRisk trial. The trial is fully functional.
Simulate every input at once, see the chance of a loss, and rank what drives it with tornado and spider charts, inside the spreadsheet you already use. Try it free for 15 days.