Tornado of the Mean: What Drives the Profit

The business question

A plant makes a coated industrial part on one line and has to commit to a profit target for next year.

Seven quantities are uncertain: the demand, the selling price, the price of the resin the part is made from, the price of energy, the fixed costs, how far the curing oven's temperature drifts from its set point, and whether a major breakdown stops the line.

The drift is the unusual one: parts cured too hot and parts cured too cold both fail inspection, so scrap rises with the square of the drift, whichever way it goes. Management wants to know which uncertainties matter most — where better estimates, contracts or controls would pay.

A tornado chart answers that, and VoseSimSensitivityMean builds it on the worksheet: for every input it splits the simulated samples into groups by the input's value and reports the lowest and the highest mean profit of the groups. This example shows the function at work, how to enter it, and what it sees that a correlation-based tornado misses.

Who it is for

Anyone who wants to rank the drivers of a simulation output on the worksheet itself: analysts, model reviewers, and users of the Results Viewer's tornado plot who need its numbers in cells.

Main outputs

The profit's mean and percentiles · the ranked tornado table from VoseSimSensitivityMean (all inputs, with captions) and its chart · the three main drivers with five tranches · the tornado against rank correlation.

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 VoseSimSensitivityMean.

1. 1

   Inputs sheet

   Seven uncertain inputs: demand, selling price, resin price, energy price and fixed costs as three-point estimates (PERT). The oven's temperature drift as a Normal distribution centred on the set point; and the days lost to a major breakdown as a risk event, zero in most years. Each one is marked with VoseInput, which is what makes VoseSimSensitivityMean rank it.

2. 2

   Model sheet

   Units sold = demand less the sales lost while the line is down. Scrap = 3% + 1.2% × drift², so a drift either way adds scrap. Units made = units sold ÷ (1 − scrap). Profit = revenue − resin and energy for the units made − repairs − fixed costs.

   The plan repeats the calculation with most likely values, no drift and no breakdown.

3. 3

   Results sheet

   The profit's mean, percentiles and the chance of missing the target. The tornado table {=VoseSimSensitivityMean(Profit,10,1,TRUE)} with its chart; the three main drivers with five tranches. And the tornado against the rank correlation of each input with the profit.

ModelRisk techniques

• Tornado of the mean VoseSimSensitivityMean
• Inputs to rank VoseInput
• Rank correlation for comparison VoseSimCorrelation
• A drift that hurts both ways
• A risk event VoseRiskEvent

Find similar models

Product

• ModelRisk

Type

• Simulation

Application

• Statistical techniques

Uses

• PERT
• Normal
• Risk events
• Sensitivity analysis (tornado)

Inputs and assumptions

Every assumption in the model, with its uncertainty and where it comes from. The figures are illustrative.

Units and conventions

Money

US$ thousand, next year's prices; unit prices in US$.

Volumes

Thousand units a year.

Tornado

Low and High are the lowest and the highest mean profit over ten groups (tranches) of samples of each input, each holding a tenth of the samples: VoseSimSensitivityMean with NumBins = 10.

Rank

1 = the input whose groups give the widest range of mean profit.

Key assumptions and limitations

Independence

The seven uncertain inputs are independent of each other.

Scrap

3% at the set point plus 1.2% for each °C² of drift, at most 50%. Scrapped parts use resin and energy but are not sold.

Breakdown

At most one major breakdown a year, in one year out of four. The days lost cut the year's sales in proportion and cost US$12 thousand a day to repair and restart.

Data

The plant and all figures are invented for this example.

How to use it

1. Run a simulation from the ModelRisk ribbon (10,000 samples with a fixed seed are set). The Results sheet fills in, the tornado table and chart included.
2. Read the tornado on the Results sheet: the longer an input's bar, the more the expected profit depends on it.
3. Change an input on the Inputs sheet — for example narrow the oven drift to ±2 °C — simulate again and watch the ranking change.
4. To use the function in your own model, mark its uncertain inputs with VoseInput, select a range 4 columns wide, type =VoseSimSensitivityMean(output,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

VoseSimSensitivityMean

The tornado table: every VoseInput ranked by how far the mean profit moves across its range. Entered twice: all seven inputs with captions, and the top three with five tranches.

VoseInput, VoseOutput

Mark the seven uncertain inputs — the cells VoseSimSensitivityMean ranks — and the profit.

VosePERT, VoseNormalAlt, VoseRiskEvent

The uncertain inputs: three-point estimates; the oven drift from its 2.5th and 97.5th percentiles; the breakdown, which may not happen.

VoseSimMean, VoseSimPercentile, VoseSimProbability

Statistics of the profit.

VoseSimCorrelation

The rank correlation of the profit with each input, to compare with the tornado.

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.
• Taguchi, G., Chowdhury, S. & Wu, Y. (2005). Taguchi's Quality Engineering Handbook. Wiley: the quadratic loss function.