Charts & Visual Data Modeling
Tornado Sensitivity Chart Formula: Base-Centered Downside/Upside Deviations & Swing Share %
Build a DCF / project-finance Tornado Diagram: center Low-Case and High-Case NPV outputs around Base Case (Y - Y_base), calculate Total Swing |Y_high - Y_low|, and compute Squared-Swing Variance Contribution %.
Center bars at Y_base and sort by Total Swing |Y_high - Y_low| to form a true Tornado!
In valuation, capital budgeting, and engineering risk analysis, a Tornado Diagram varies one uncertain driver at a time (e.g., WACC, Unit Price, CapEx) between its P10 Low Case and P90 High Case while holding all other drivers at Base Case Y_base. Plotting centered deviations Delta_Low = Y_low - Y_base and Delta_High = Y_high - Y_base on a 100%-overlapped Clustered/Stacked Horizontal Bar Chart anchors the vertical spine directly at 0 (= Y_base), even for inverse drivers like Discount Rate where a higher input lowers NPV!
Sorting drivers in ascending order of Total Swing S_i = ABS(Y_high,i - Y_low,i) (since horizontal bar charts plot the last row at the top unless Categories in Reverse Order is checked) produces the iconic funnel silhouette, while Squared-Swing Contribution S_i^2 / SUM(S_k^2) approximates each independent input’s percentage share of total output variance!
| Sensitivity Driver (Base NPV = $120M) | Low-Input NPV ($M) | High-Input NPV ($M) | Left/Right Deviations vs Base | Total Swing S_i ($M) | Variance Share S_i^2 / SUM(S^2) |
|---|---|---|---|---|---|
| Unit Selling Price | 82 | 162 | -38 / +42 | 80 | 66.67% |
| Discount Rate (WACC, Inverse!) | 145 | 100 | +25 / -20 | 45 | 21.09% |
| Initial CapEx (Inverse!) | 136 | 102 | +16 / -18 | 34 | 12.04% |
Total One-Way Sensitivity Swing Range S_i = ABS(Y_high - Y_low)
Computes the absolute NPV spread |C2 - B2| regardless of whether the driver acts positively (Price) or inversely (WACC, CapEx).
=ABS(C2 - B2)Approximate Variance Contribution Share (%) = 100 * S_i^2 / SUM(S_k^2)
Evaluates 100 * (C2 - B2)^2 / SUMPRODUCT((C2:C4 - B2:B4)^2) to show which single variable dominates model uncertainty.
=IF(SUMPRODUCT((C2:C4 - B2:B4) ^ 2) <= 0, "Zero Sensitivity Swing Across All Drivers", ROUND(100 * ((C2 - B2) ^ 2) / SUMPRODUCT((C2:C4 - B2:B4) ^ 2), 2))Try the example
Use the original values or change them to test your understanding.