Sensitivity Analysis in Excel: A Guide with Data Tables and Tornado Charts
A sensitivity analysis in Excel answers how strongly a target metric such as EBIT reacts to a change in a single driver. The standard route is data tables (what-if analysis): a formula is automatically recalculated across a range of input values - one-variable or two-variable. Combined with a tornado chart, this produces a ranking of your most important value levers. This article shows the setup in four steps - and where the single-variable logic leads you astray.
Step 1: Prepare the model
The prerequisite is a model in which the target metric is calculated from drivers via formula chains - not a graveyard of numbers, but a small value driver tree: input cells for drivers (price, volume, cost rates), formula cells for the calculation, one result cell. Without this separation, the data table has nothing to work with.
Step 2: One-variable data table for a single driver
Write the input values in a column (e.g. price deviation from -10% to +10% in 2.5% steps), link the result cell at the top right, select the range, Data > What-If Analysis > Data Table, assign the input cell. Excel recalculates the result for every value. The output is the classic sensitivity curve for one driver.
Step 3: Two-variable data table for driver pairs
For two drivers at once (e.g. price and volume), use the matrix form: one driver down the side, one across the top, the result formula in the corner. The resulting matrix reveals combination effects - for instance, at which price-volume combination the contribution margin flips. More than two variables is beyond the data table; that is its hard limit.
Step 4: Tornado chart as the management view
For communication, the tornado chart has proven itself: per driver, the EBIT impact across a plausible range (e.g. +/-10%) as a horizontal bar, sorted descending. Building it in Excel: take the min and max results per driver from the data tables and plot them as a stacked bar chart with an invisible base. The result shows at a glance which three to five drivers deserve the discussion.
| Tool | Variables | Output form | Typical use |
|---|---|---|---|
| One-variable data table | 1 | Sensitivity curve | Effect of a single driver |
| Two-variable data table | 2 | Result matrix | Combination effects |
| Tornado chart | Many, one at a time | Bar ranking | Prioritizing value levers |
| Goal Seek | 1 | Single value | Break-even of one driver |
The limits: when drivers are not independent
The single-variable logic assumes all other drivers stay constant - in reality they move together: when raw material prices rise, sales prices and demand often come under pressure at the same time. Such consistent pictures of the future are the domain of scenario analysis, not sensitivity math. Add the error risk: according to spreadsheet research by Raymond Panko (University of Hawaii), 94 percent of audited operational workbooks contain at least one error - and a sensitivity analysis on a faulty model delivers precisely wrong answers.
Structurally, the rule is: the cleaner the driver model, the more reliable the sensitivities. According to the BARC Planning Survey, only around 40 percent of companies use driver-based approaches - yet the driver model is exactly the infrastructure that turns one-off analyses into a repeatable steering routine. The free Driver Tree Assistant generates an interactive starting point in 60 seconds.
Frequently asked questions about sensitivity analysis in Excel
What is the difference between sensitivity analysis and scenario analysis?
Sensitivity analysis varies one driver in isolation and measures the effect on the target metric. Scenario analysis combines several consistent assumptions into one picture of the future. In practice they belong together: sensitivities show which drivers deserve a scenario in the first place.
Why does my data table not update?
Most common causes: the calculation option is set to “Automatic except for data tables”, the input cell is assigned incorrectly, or the result formula does not reference the input cell through a continuous formula chain.
How many drivers should I analyze?
Run all input drivers of the model once, but put only the top 5 to 7 into the tornado chart. In practice, 10 to 15 core drivers explain most of the variance in results - the sensitivity analysis shows which of them are the critical ones.
Can I do sensitivity analysis without Excel?
Yes - on simulation platforms it is a built-in function of the driver model: move a driver, see the effect live, without data-table mechanics. The advantage lies less in the calculation than in repeatability and the connection to actuals.