ModelRisk example · General techniques
Tornado of a Percentile: What Drives a Bad Year
What drives the downside of the profit, not just its average? A worked example of VoseSimSensitivityPercentile.
From the model’s saved run
Profit next year
The plan (forward price, most likely values) against the simulation.
| Measure | Unit | Value |
|---|---|---|
| Profit at the forward price (the plan) | US$ million | 65.0 |
| Mean profit (simulated) | US$ million | 57.3 |
| A bad year: the profit's 10th percentile | US$ million | −64.0 |
| Chance of a loss | probability | 28.8% |
| Measure | Unit | 10th pct | Median | 90th pct | Mean |
|---|---|---|---|---|---|
| Profit | US$ million | −64.0 | 47.1 | 186.1 | 57.3 |
Histogram of Profit from the saved simulation.
The business question
A copper mine will produce about 50 thousand tonnes of copper next year and sell it at the market price. The forward price is US$9,000 a tonne, so the expected revenue is known fairly well. What the analysts disagree on is how volatile the price will be — their estimates range from 10% to 35% a year.
Output, cash cost, freight and fixed costs are uncertain too. The board wants to know what drives a bad year — the profit the mine falls below one year in ten — because that decides how much cash to hold.
A mean-based tornado cannot answer that: the volatility does not move the average price, so it does not move the mean profit, and the mean tornado ranks it near the bottom.
VoseSimSensitivityPercentile builds the tornado of a percentile instead: for every input it splits the simulated samples into groups by the input's value and reports the lowest and the highest 10th percentile of the profit across the groups. This example shows the function at work and what it sees that the mean tornado misses.
- Who it is for
- Analysts and risk managers who look at the downside (P10, P5) or the upside of an output rather than its mean, and users of the Results Viewer's tornado plot by percentile who need its numbers in cells.
- Main outputs
- The profit's mean, percentiles and chance of a loss · the tornado table of the profit's 10th percentile from VoseSimSensitivityPercentile (with captions) and its chart · the top three of the 90th percentile · the mean tornado (VoseSimSensitivityMean) beside it.
How the model works
The model's three sheets, read from left to right: each one feeds the next. The Function guide sheet at the end explains VoseSimSensitivityPercentile.
Inputs sheet
Six uncertain inputs: copper output, cash cost, freight and fixed costs as three-point estimates (PERT). The copper price as a Lognormal whose mean is the forward price and whose standard deviation is forward × volatility; and the volatility itself, uniform between 10% and 35%. Each is marked with VoseInput, which is what makes VoseSimSensitivityPercentile rank it.
Model sheet
Profit = output × (price − cash cost − freight) − fixed costs, in US$ million. The plan repeats the calculation at the forward price and the most likely values.
Results sheet
The profit's mean, percentiles and chance of a loss; the tornado table {=VoseSimSensitivityPercentile(Profit,0.1,10,1,TRUE)} with its chart; the top three of the 90th percentile; and the mean tornado {=VoseSimSensitivityMean(Profit,10)} beside it, to compare the rankings.
ModelRisk techniques
Find similar models
- Product
- Type
- Application
- Uses
The results in detail
What drives a bad year: VoseSimSensitivityPercentile at P10
One array formula, entered with Ctrl+Shift+Enter over B:E: all six inputs ranked by their effect on the profit's 10th percentile, with the captions the function returns.
Tornado chart: the inputs ranked by how far the output’s 10th percentile moves across each input’s range.
Array formula over the 4 × 7 range below: {=VoseSimSensitivityPercentile(Profit,0.1,10,1,TRUE)}
| Input Name | Low | High | Rank |
|---|---|---|---|
| Copper price | −155.5 | 190.4 | 1 |
| Price volatility | −114.7 | −5.3 | 2 |
| Cash cost | −85.1 | −47.9 | 3 |
| Freight and treatment | −75.0 | −56.8 | 4 |
| Fixed costs | −72.3 | −55.7 | 5 |
| Copper output | −72.9 | −60.6 | 6 |
- Input Name: the name given in VoseInput. Low and High: the lowest and the highest 10th percentile of the profit over the ten groups (tranches) of the input's samples. Rank: 1 for the widest range from Low to High.
- The chart draws each bar from the overall 10th percentile of the profit: to High and to Low.
The upside: the 90th percentile, top three
Simulation number and Show Caption left out (1 and FALSE): no caption row, so the sheet has its own header.
Array formula over the 4 × 3 range below: {=VoseSimSensitivityPercentile(Profit,0.9,10)}
| Input | Low | High | Rank |
|---|---|---|---|
| Copper price | −64.3 | 390.3 | 1 |
| Price volatility | 125.4 | 268.3 | 2 |
| Cash cost | 154.3 | 205.3 | 3 |
- The volatility drives the good years too: a volatile price can also end far above the forward price. It widens the profit's distribution at both ends, so it matters to every percentile away from the middle — and to none of the means.
What the mean tornado would miss
The same inputs ranked by the mean profit (VoseSimSensitivityMean), then both rankings side by side.
Array formula over the 4 × 6 range below: {=VoseSimSensitivityMean(Profit,10)}
| Input | Low | High | Rank |
|---|---|---|---|
| Copper price | −103.7 | 273.0 | 1 |
| Cash cost | 31.3 | 74.2 | 2 |
| Fixed costs | 46.3 | 62.2 | 3 |
| Freight and treatment | 48.2 | 62.1 | 4 |
| Copper output | 50.4 | 63.5 | 5 |
| Price volatility | 54.7 | 61.5 | 6 |
| Input | Range of the P10 | Range of the mean | Rank at P10 | Rank by the mean |
|---|---|---|---|---|
| Copper price | 345.8 | 376.7 | 1 | 1 |
| Price volatility | 109.4 | 6.8 | 2 | 6 |
| Copper output | 12.3 | 13.1 | 6 | 5 |
| Cash cost | 37.2 | 42.8 | 3 | 2 |
| Freight and treatment | 18.2 | 13.8 | 4 | 4 |
| Fixed costs | 16.6 | 15.8 | 5 | 3 |
- Price volatility is number 2 at P10 but number 6 of 6 by the mean: its range of mean profit is about the size of the sampling noise.
- The reason: the price's mean is the forward price whatever the volatility, so the volatility does not move the average profit. It moves how far the price, and the profit, can stray from that average — the tails. Only a percentile tornado sees that.
- The copper price itself is first in both rankings: a low price makes every year worse, the average one and the bad one alike.
How to read these results
- Each bar of the P10 tornado shows how far the profit of a bad year moves when one input is known to lie in one tenth of its range, with every other input still uncertain.
- A percentile of a tranche comes from a tenth of the samples — 1,000 here — and a tail percentile is noisier than a mean. Use more samples for percentiles further out (P5, P1), or fewer tranches.
- Ask the question that matters for the decision: the mean tornado for the expected profit, the P10 tornado for the cash to hold against a bad year, the P90 tornado for the upside.
- The tables and the chart are filled in after every simulation. Change an input on the Inputs sheet, simulate again, and the rankings follow.
See it move. Open the workbook, change an input on the Inputs sheet and run the simulation: every table and chart on this page updates in Excel.
Inputs and assumptions
Every assumption in the model, with its uncertainty and where it comes from. The figures are illustrative.
Production 1 input
How much copper the mine produces next year.
| Assumption | Unit | Uncertainty | Low | Most likely | High |
|---|---|---|---|---|---|
| Copper outputMine plan; the ore grade may disappoint, little room above plan. | thousand tonnes | PERT | 44.0 | 50.0 | 52.0 |
Copper price 3 inputs
Where the price is expected to be, and how far it may move.
| Assumption | Unit | Uncertainty | Low | Most likely | High |
|---|---|---|---|---|---|
| Forward price for next yearFrom the futures market today: the mean of next year's price. | US$ per tonne | Fixed | 9,000 | ||
| Price volatilityAnalysts disagree: from 10% to 35% a year. The uncertainty about the uncertainty. | a year | Uniform | 10% | – | 35% |
| Copper price next yearMean = the forward price whatever the volatility; standard deviation = forward × volatility. | US$ per tonne | Lognormal · mean, st. dev. | – | – | – |
Costs 3 inputs
What it costs to mine, process and ship the copper.
| Assumption | Unit | Uncertainty | Low | Most likely | High |
|---|---|---|---|---|---|
| Cash costMining, processing and site costs; energy and wages may rise. | US$ per tonne | PERT | 5,600 | 6,000 | 6,900 |
| Freight and treatmentShipping and smelter charges. | US$ per tonne | PERT | 350 | 400 | 520 |
| Fixed costsOverheads, maintenance and sustaining capital. | US$ million / year | PERT | 60.0 | 65.0 | 78.0 |
Units and conventions
- Money
- US$ million; prices and costs per tonne in US$.
- Volumes
- Thousand tonnes of copper a year.
- Tornado
- Low and High are the lowest and the highest 10th percentile of the profit over ten groups (tranches) of samples of each input, each holding a tenth of the samples: VoseSimSensitivityPercentile with Percentile = 0.1 and NumBins = 10.
Key assumptions and limitations
- Independence
- The inputs are independent of each other, apart from the price, whose spread is set by the volatility.
- Price
- Lognormal around the forward price: the volatility changes how far the price may move, not where it is expected to be.
- Hedging
- None: the whole output is sold at next year's market price.
- Data
- The mine and all figures are invented for this example.
How to use it
- Run a simulation from the ModelRisk ribbon (10,000 samples with a fixed seed are set). The Results sheet fills in, the tornado tables and chart included.
- Read the P10 tornado on the Results sheet: the longer an input's bar, the more the bad years depend on it.
- Narrow the volatility range on the Inputs sheet — for example to 20%–25% — simulate again and watch the volatility fall down the P10 ranking while the mean tornado hardly changes.
- To use the function in your own model, mark its uncertain inputs with VoseInput, select a range 4 columns wide, type =VoseSimSensitivityPercentile(output,0.1,10,1,TRUE) and press Ctrl+Shift+Enter. The Function guide sheet explains every argument.
The tornado chart fills in after a simulation with ModelRisk’s classic engine.
ModelRisk functions used
- VoseSimSensitivityPercentile
- The tornado tables of a percentile: every VoseInput ranked by how far the profit's 10th (and 90th) percentile moves across its range.
- VoseSimSensitivityMean
- The mean tornado, for comparison.
- VoseInput, VoseOutput
- Mark the six uncertain inputs — the cells the tornado functions rank — and the profit.
- VosePERT, VoseLognormal, VoseUniform
- The uncertain inputs: three-point estimates; the price around the forward price; the volatility.
- VoseSimMean, VoseSimPercentile, VoseSimProbability
- Statistics of the profit.
Further reading
- Sensitivity analysis in Excel: a tornado chart step by step (Vose guide)
- Vose, D. (2008). Risk Analysis: A Quantitative Guide, 3rd edition. Wiley: tornado and spider plots.
- Hull, J. C. (2022). Options, Futures, and Other Derivatives, 11th edition. Pearson: volatility and lognormal prices.
Download the workbook and run it yourself. It opens in Excel with ModelRisk. The free trial runs every example model.
Related example models
- ModelRisk★★★★★Probability of RuinCould this year's risks ruin the company?Ten risk events with fixed costs: the chance that their total ruins the company, simulated and exact.
- ModelRisk★★★★★Correlation Through a Common Factor (Look-up Table)How much later does a project finish when bad weather hits every activity at once?One shared random factor and a look-up table make six durations move together, without a correlation matrix.
- ModelRisk★★★★★Envelope Correlation: Interest and Mortgage RatesHow do you make one variable follow another when you only know its range at a few points?Correlating two variables from expert ranges: the second's range follows the first.
An illustrative example: the figures are invented for teaching and do not describe a real company, project or asset. Last updated 30 October 2026.