Tornado charts and sensitivity analysis explained

Guide · Updated October 2026

A tornado chart ranks a model’s uncertain inputs by how far each one alone moves a result: in a building estimate, the ground conditions risk moves the total cost by 150 (USD thousands), more than any cost item, and the structure comes next with 117. In xellstorm each input moves by default from its P10 to its P90 with every other input at its median.

Each bar shows the result with its input at a low and at a high value while the others stay at base values; the widest bar goes on top, which gives the chart its funnel shape. A tornado shows what each input does on its own; the contribution to variance, from simulated trials, shows how much of the actual spread each input explains.

What a tornado chart shows

Sensitivity analysis asks which inputs a result depends on most. A tornado chart answers one input at a time: set every uncertain input to a base value, move one input to a low value and then to a high value, record the result each time, and put the input back. Each input gets a horizontal bar from the result at its low value to the result at its high value, and the bars are sorted with the widest on top.

In xellstorm the low and high values are by default each input’s P10 and P90, the values its draws stay below 10% and 90% of the time, and the base value is its median, the P50. With k inputs that makes 2k + 1 recalculations of the workbook: 35 for the 17 inputs of the building project example. Its cost estimate has 6 cost items, each a three-point PERT range, and 4 risk events that either happen and add their cost or do not; the other inputs are the schedule’s durations. The chart shows the total cost.

How to read a tornado chart

The base line is the result with every input at its median: 2,675. It is neither the simulated mean (2,785) nor the simulated median (2,776). Each risk event here has less than a 50% chance, so its median is “does not happen”, and the base leaves all of them out.

Bar length is how far the result moves between the input’s P10 and P90. The top bar, the ground conditions risk, takes the total cost from 2,675 to 2,825.

Lopsided bars show skewed inputs. The structure lowers the total by 50 at its P10 and raises it by 67 at its P90, because its range reaches further above its most likely cost than below it. Every cost item’s bar is longer on the right.

One-sided bars are risk events. At its P10 a risk does not happen, which is the base, and at its P90 it does, so its bar is its full cost, all on the right.

No bar means the input does not move this result: the 7 schedule durations change the finish date, not the cost.

The bars do not add up. Together they span 794, more than twice the distance from the simulated total’s P10 (2,644) to its P90 (2,937), which is 293. Inputs rarely reach their P90 together, so a tornado tells you which inputs matter, not how wide the range of outcomes is; that comes from the simulation.

What a tornado chart misses

A tornado moves one input at a time and holds the others at their medians. Two kinds of model mislead it.

Events that may not happen. A risk event’s bar is its full cost whether its chance is small or large, as long as the risk is off at the low percentile and on at the high one, and the percentiles decide whether it shows at all. Switch the tornado from P10–P90 to P25–P75 and the bar of the ground conditions risk (25% chance), the widest at P10–P90, shrinks to zero, as does the bar of supplier delay (20% chance), because neither happens at its P75. The design change risk then tops the chart with 90. Nothing about the project changed, only the percentiles.

Inputs that matter together. In the schedule, MEP (mechanical, electrical and plumbing) and finishes run in parallel after the structure, so the finish waits for whichever takes longer: a MAX in the workbook. With finishes held at its median, MEP always sets the pace, and the tornado gives MEP a bar of 2.95 weeks, exactly as long as design’s and foundation’s: the three ranges have the same shape, 2 weeks below the most likely duration and 4 above it. Finishes barely registers, since its P90 of 12.27 weeks is hardly longer than MEP’s median of 12.25: its bar is 0.02 weeks.

In the simulation both trades vary at once, and finishes takes longer than MEP in 15.0% of trials. In those trials the finish follows finishes and MEP’s own duration makes no difference, so MEP explains less of the finish week’s spread than design or foundation, which are always on the path. The tornado cannot see this.

The same effect appears whenever a result takes the larger or the smaller of several inputs: parallel paths that merge in a schedule, or a series system that fails with its first component. Schedule risk analysis explains why merging paths make projects late.

Tornado chart vs rank correlation and contribution to variance

xellstorm also measures sensitivity from the simulated trials, in which every input varies at once. Both measures use ranks (each trial’s position from lowest to highest) rather than values, so they work for any relationship that goes one way, straight or curved.

Rank correlation

Spearman’s correlation between an input and the result over all trials: +1 if the result always rises with the input, −1 if it always falls, near 0 if it follows no consistent direction. For the total cost, the ground conditions risk has 0.55.

Contribution to variance

The share of the result’s variation that goes with each input, from a regression of the result’s ranks on the inputs’ ranks: the squared standardized coefficients, as shares that add up to 100%.

Both put the ground conditions risk first, and most inputs keep their place or move one step. The shares say more than the order. The ground conditions risk’s bar is only 1.28 times as long as the structure’s, yet it explains 32.9% of the variance, about twice as much. Variance averages squared distances from the mean, and a risk’s cost comes all or nothing: in every trial it is either zero or its full cost, the two ends of its bar, while a cost item’s draws bunch around its most likely value. A risk’s bar is also its full cost whatever its chance, as long as that is between 10% and 90%, while its share of the variance grows with the chance up to 50%.

The design change risk, the likeliest of the risk events at 35%, has a bar of 90 against 117 and 116 for structure and MEP, yet explains about the same share of the variance. Worked out exactly from the ranges and chances (a PERT range’s variance is (mean − min) × (max − mean) / 7; a risk’s is p × (1 − p) × its cost squared), structure explains 15.3%, MEP 14.8% and the design change risk 14.4%. In this run’s table, which works on ranks, the design change risk comes out ahead of both: shares this close change order from one run to the next, so treat shares within a point or two of each other as a tie.

Use each for its own question. The tornado answers “what if this input lands at its P10 or its P90?”, with nothing random in it. The contribution to variance answers “which inputs account for the spread of outcomes?”, and because it comes from the trials it reflects probabilities and inputs acting together, as with MEP above. It assumes each input pushes the result one way; when a straight line on the ranks fits the result poorly, the app says so and points you to the tornado. For the total cost the fit is close.

Spider charts

A spider chart moves each input through several percentiles instead of two, the others still at their medians, and draws the result as a line. xellstorm uses P5, P10, P25, P50, P75, P90 and P95: 120 evaluations for this model. Steep lines matter most, and a bent line shows an effect that is not proportional. The P10 and P90 points of each line are the two ends of its tornado bar.

The line for finishes stays on the base up to its P75, 11.37 weeks, which is shorter than MEP’s median of 12.25, and only then rises: 0.02 weeks later at its P90 and 0.52 at its P95. A tornado shows only the end points; a spider shows where along its range an input starts to matter.

How to make a tornado chart in Excel

1. List the uncertain inputs with a low, a base and a high value for each. To match xellstorm, use the P10, the median and the P90. For a PERT range with the min in B2, the most likely value in C2 and the max in D2, the P10 is =BETA.INV(0.1, 1+4*(C2-B2)/(D2-B2), 1+4*(D2-C2)/(D2-B2), B2, D2), and 0.5 or 0.9 in place of 0.1 gives the median or the P90 (see PERT distribution in Excel). For the structure (min 760, most likely 820, max 1,010), =BETA.INV(0.1, 1+4*(820-760)/(1010-760), 1+4*(1010-820)/(1010-760), 760, 1010) returns about 787; the median is about 837 and the P90 about 904, the values xellstorm uses. A risk event is 0 at its P10 and 1 at its P90 if its chance is between 10% and 90%, and 0 at its median if its chance is below 50%.
2. Set every input to its base value and note the result: the base.
3. Set one input to its low value and note the result, then to its high value, then put it back. Repeat for every input. A one-variable data table (Data › What-If Analysis › Data Table) with the input cell as its column input cell does an input’s two recalculations at once.
4. Compute each input’s swing, the high result minus the low result, and sort the inputs by it, largest first.
5. Excel has no tornado chart type. Insert a clustered bar chart with two series, the low result minus the base and the high result minus the base, and set the series overlap to 100%. On the vertical axis, tick “Categories in reverse order” so the widest bar is on top, and set the label position to Low so the names sit left of the bars.

The table has to be redone whenever the model or a range changes, and it answers only the what-if question. How likely each outcome is, and how much of the spread each input explains, takes a simulation; Monte Carlo simulation in Excel shows the formulas and what changes when a tool runs the workbook for you.

Tornado charts in xellstorm

After a run, the Results step shows for each output a tornado, with a Range menu for P5–P95, P10–P90 or P25–P75; the contribution to variance, with each input’s rank correlation beside it; and a spider chart. The tornado and the spider evaluate the workbook at percentiles only, with no random draws; if the workbook has RAND() cells of its own, every evaluation uses the same draw, so the bars show only the inputs’ effects. The HTML report, the PowerPoint deck and the results workbook include the tornado too.

1. Open the building example in xellstorm and run it. There is nothing to install, and the workbook is calculated in your browser.
2. On the Results step, the total cost’s tornado shows the bars of the chart above, plus the two smallest. Set the Range to P25–P75: the ground conditions risk drops from the top to ±0 near the bottom, and the design change risk takes its place.
3. Switch to the finish week and compare MEP’s bar with its share in the contribution to variance.
4. Then open your own workbook, pick its uncertain inputs on the model map, and see which of them drive your result.

Questions

Related

• Project cost and schedule

  How much contingency does a construction project need?

  Worked example: a building estimate’s most likely costs are exceeded in 94.2% of simulated outcomes. Size the construction cost contingency with Monte Carlo.
• Risk management

  How much contingency does a risk register need?

  Probability × likely impact sums to 291; the simulated mean loss is 359 and the worst 5% average 1,436 (USD thousands). Size contingency from the tail.
• Guide

  Schedule risk analysis: why projects finish late

  A building schedule of most likely durations says week 55, yet only 14.3% of simulated outcomes finish by then. Why, and how to set a P80 finish.

xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.