Sensitivity Analysis in Excel

What does the worked example model?

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.

How do you build a one-way sensitivity table in Excel?

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.

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:

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.

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.

How do you make a two-way sensitivity table with Excel's Data Table?

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.

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.

What can't a sensitivity table tell you?

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.

One simplification: the low and high values are one-in-ten values, not true extremes, so using them as the minimum and maximum of each PERT makes the spread too narrow. The real chance of a loss is, if anything, higher than 37%.

How do you get a simulation tornado chart with an add-in?

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.

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.