Sensitivity Analysis in Financial Models

A sensitivity analysis financial model reviewers can trust changes one input at a time, by a stated amount, and records how much the output moves. The result tells you which assumptions deserve the most attention, where the plan is fragile and how much room you have before profit disappears. It is simpler than scenario planning, since only one thing changes at a time, and it is often the first test a model should pass. This guide explains one-way and two-way sensitivity tables, tornado ranking and break-even points with a single worked model whose arithmetic you can check, then shows how to build them in Excel and present them. It is part of budgeting and forecasting, and it links with scenario planning for finance teams.

The model

A hypothetical product business has the following base case.

Profit = Units × (Price - Variable cost) - Fixed costs = 10,000 × $48 - $400,000 = $80,000.

One-way sensitivity: change each input by 10 percent

Hold everything else constant and move one input by plus and minus 10 percent.

How the figures arise:

• Price at $108: 10,000 × ($108 - $72) = $360,000 - $400,000 = -$40,000. At $132: 10,000 × $60 = $600,000 - $400,000 = $200,000.
• Variable cost at $79.20: 10,000 × ($120 - $79.20) = $408,000 - $400,000 = $8,000. At $64.80: 10,000 × $55.20 = $552,000 - $400,000 = $152,000.
• Units at 9,000: 9,000 × $48 = $432,000 - $400,000 = $32,000. At 11,000: $528,000 - $400,000 = $128,000.
• Fixed costs at $440,000: $480,000 - $440,000 = $40,000. At $360,000: $480,000 - $360,000 = $120,000.

The tornado ranking

Sort the inputs by the size of the swing, largest first.

A tornado chart displays this as horizontal bars, widest at the top. It is the single most useful picture for a budget review, because it shows where to spend time and evidence. Here, a 10 percent change in price is worth three times a 10 percent change in fixed costs. A model review that spent an hour on the fixed cost line and five minutes on pricing would be misdirected.

Equal percentage changes are a convenient test, not a statement of likelihood. A 10 percent change in price may be far less likely than a 10 percent change in freight cost. To make the ranking realistic, use ranges that reflect plausible variation for each input, based on history and judgment. Record the ranges you used.

Two-way sensitivity: price and volume

A two-way table changes two inputs together and shows the profit at each combination.

Check one cell: 9,000 units at $108 = 9,000 × ($108 - $72) = $324,000 - $400,000 = -$76,000. And 11,000 at $132 = 11,000 × $60 = $660,000 - $400,000 = $260,000.

The table makes trade-offs visible. A 10 percent price cut (to $108) loses money even if volume rises 10 percent (profit -$4,000). A 10 percent price increase yields $140,000 even if volume falls 10 percent. This tells the commercial team how much volume they would need to gain to justify a discount: at $108, the company needs more than 11,111 units to break even (400,000 / 36 = 11,111).

Break-even analysis as sensitivity

Break-even points are sensitivity results with a target of zero profit.

• Break-even units at the base price and cost = $400,000 / $48 = 8,333 units, which is 16.7 percent below planned volume. That is the margin of safety.
• Break-even price at 10,000 units = ($400,000 / 10,000) + $72 = $112, 6.7 percent below the base price.
• Break-even variable cost at the base price and volume = $120 - $40 = $80, 11.1 percent above the base cost.

These figures describe how far each assumption can move before profit disappears. Price is the closest to the edge in percentage terms: a 6.7 percent price decline wipes out the profit, while volume can fall 16.7 percent.

Building it in Excel

One-way table with formulas

Set up the model in cells with inputs separated from calculations. Then create a sensitivity block that recalculates profit with each input changed.

Profit formula in a single cell:
=Units*(Price-VarCost)-Fixed

Sensitivity for price -10%:
=Units*(Price*0.9-VarCost)-Fixed

Use named cells (Price, Units, VarCost, Fixed) for readability.

Data Table (what-if analysis)

Excel's Data Table feature (Data, What-If Analysis, Data Table) can compute one-way and two-way sensitivity tables from a single model cell.

1. Place the output formula (profit) in the top-left corner cell of the table.
2. List the input values across the top row and/or down the left column.
3. Select the range, open Data Table, and enter the row input cell and column input cell (the cells holding Units and Price).
4. Excel fills the grid with profit for each combination.

Data Tables recalculate with the workbook, so keep the model well structured to avoid slowdowns. Set calculation to automatic except tables if the workbook becomes slow.

Presenting the results

1. Lead with the ranking, the tornado chart or a table of swings.
2. State the ranges used and why.
3. Show the break-even points, so the audience sees how much room there is.
4. Name the actions that follow: lock in prices, hedge inputs, monitor leading indicators, build flexibility.
5. Keep the page simple. Two charts and one table are enough.

What sensitivity analysis can and cannot do

Correlated inputs are the main weakness. In reality, price and volume move together, and so do input costs and inflation. For coherent combinations, use scenario planning. For many combinations with probabilities, see Monte Carlo simulation for financial planning.

Using sensitivity analysis to debug a model

An unexpected result can reveal an error. If increasing a cost increases profit, a sign is wrong. If a 10 percent change in price does not change profit, the formula may not be linked. Run sensitivity checks as part of model review. See financial model audit checklist.

Sensitivity for capital decisions

For investment decisions, run sensitivity on net present value: vary the discount rate, volume, price and cost. Report how far each can move before NPV falls to zero. See net present value.

Link to unit economics

The inputs in this example (price, variable cost, units, fixed cost) are the building blocks of unit economics. Knowing the contribution per unit lets you compute sensitivities quickly. See unit economics.

Product companies

For companies that make or buy products, add demand forecast error, lead time and yield as sensitivity inputs. They link directly to inventory and cash. See demand planning and inventory optimization.

Common mistakes

• Changing every input by the same percentage and calling it realistic.
• Forgetting to reset the input after testing.
• Using hard-coded numbers inside formulas.
• Ignoring interactions.
• Presenting a table without interpretation.
• Testing only on the profit line, not on cash.

Related guides

To combine several changes into coherent stories, see scenario planning for finance teams. To measure how accurate forecasts have been, see how to measure financial forecast accuracy.

FAQs

What is sensitivity analysis in a financial model?

A test that changes one input at a time and records the effect on an output such as profit, showing which assumptions matter most.

What is a tornado chart?

A bar chart that ranks inputs by the size of their effect on an output, with the largest effect at the top.

What is the difference between sensitivity and scenario analysis?

Sensitivity analysis changes one input at a time. Scenario analysis changes several together to represent a coherent situation.

How do I do sensitivity analysis in Excel?

Use formulas that vary an input, or the Data Table feature for one-way and two-way tables.

What ranges should I use?

Ranges based on historical variation and judgment for each input, rather than the same percentage for all.