Tornado diagrams are a classic tool of sensitivity analysis to provide decision makers a quick overview of the risks involved. A tornado chart to show a financial analysis for a project may look like this:
In this particular case, we are assuming that the tornado shows the NPV of a project. We expect the project can be valued at $7 billion (the point where the vertical axis crosses), subject to uncertainties.
The tornado helps visualize these uncertainties. In the example, Conversion (i.e. how many of the people that shop for our product become a customer) is the largest uncertainty. We believe 35% of the shoppers would convert. If only 25% convert, the project’s NPV would drop to $4 billion, from the base case, $7 billion. On the other hand, if 45% convert, we have a large upside and the NPV would be $12 billion.
Next in relevance would be pricing, $25,500 in the base case. If it goes down to $20,500 the NPV would reduce to $5 billion. If we can raise price up to $29,500 due to a favorable competitive environment, then the upside is $4 billion from the base case.
By now, you can follow the logic of the chart, with the other variables. Do you still have questions? Go ahead and drop us a line. We are happy to help.
Tornado diagrams are not used as frequently as one would expect, given how clearly they help showing the impact of different variables on a geven outcome. As suggested by Ted Eschenbach on a recent article of Engineering Economist, (issue of 06/22/2006), perhaps this is due to difficulties in constructing them.
Sensitivity analysis is needed to address the inherent uncertainty in engineering economy applications because (1) time horizons are measured in years or decades and (2) much economic analysis is done at the feasibility and preliminary design stages. This is often shown using relative sensitivity analysis charts or spiderplots, which have a long and rich history in practice and texts (they are described in 10 of 18 texts reviewed, including Blank and Tarquin (2002), Canada et al. (1996), Eschenbach (2003), Lang and Merino (1993), Park (2002, 2004), Sullivan et al. (2003), Thuesen and Fabrycky (2001), White et al. (1998), Young (1993). Tornado diagrams are not new, but they have not been used nearly as frequently. Only one of the 18 texts included a tornado diagram (Eschenbach, 2003)–
Searching Google on how to make tornado charts, you’ll get many results, most of them requiring you to download an add-in. Keep reading to see how you can create tornado charts with plain Excel in just 5 steps… very easy and straightforward!!
There is no need for any external add-ins for making good-looking tornado charts. And it takes only 5 steps. Let’s start with data in a table like this:
The data in rows 3 to 7 show the key levers or sources of uncertainty for the model, as explained before.
In columns B and C we put the lowest and highest values for the model output, changing only the given variable. As discussed above, for conversion, if it is 25% (down from 35% base case), the NPV is $4b, not $7b. This is why $B$4 contains a 4. If conversion is 45%, the NPV is $12b
So, once you set up the data in a table like the one shown, here are the promised 5 steps:
- Select the data, excluding the Delta column
- On the Insert ribbon, choose Bar. Pick Clustered Bar (first one in the 2-D section)
- Right-click on the horizontal axis, choose Format Axis. On the bottom of the Axis Options pane look for Vertical Axis crosses:, choose the Axis value radio button, and type your base case value (7 in the example)
- Without closing the window, choose the vertical axis of the chart. Again on Axis Options, check the box Categories in reverse order, and set the Axis Labels menu to Low
- Without closing the window, click on any of the bars, which should bring the Series Options pane. Move the slider Series Overlap completely to the right (Overlapped).
That’s it! Now you can format the chart as you like. The final result should look like the chart shown above.
Now, watch a screencast, or download the Excel file
I hope this entry helps you use tornado charts in your decision support models. Watch the screencast
Pingback: Easy creation of tornado charts in Excel - 5 steps, no add-ins | Juan Carlos Méndez-García
in the screencast it doesnt show how you got the data labels to represent to the max/mins of the y axis
Just added them to make clear to the audience the values on the assumptions that change the results
I understand why you got the data labels for the min/max/base values of the lever varaibles, but in the screencast I couldn't decipher the steps to get those values on screen.
Very elegant solution, by the way. Thanks!
How do you move the axis labels to the left. You don't mention it in the excel 2003 version either.
Hi – when you choose "set the Axis Labels menu to Low" (end of step 4) the labels should move to the left
Very simple and useful! thanks a lot!!
Quick question: tornado charts assume that each assumption does not interact with the others.
Do you have simple models that would combine all assumptions (by affecting a probability % on each assumption for example)?
@guillaume – You are right – the tornado shows independently the effects of each assumption. Unfortunately there isn't a one-size-fits-all for combining the assumptions. I've used Monte Carlo simulation as an effective way to show these "combined effects". That usually requires your audience to be very familiar with modeling, the particular problem, and you doing a good job at communicating the analysis in a simple way. Usually I try to "peel back" the root-cause analysis when assumptions are not too orthogonal. Conversely, when two assumptions are too coupled, I just show one of them in the tornado and verbally mention the second one, explaining why in fact they are considered together.
Very, very, very VERY useful!!!!
Thank you!!!
I would also add – print function to make useful instruction available off-line
How do you add the title to the chart? Thanks.
John – I use the regular text tool on Excel
The bar with value 4 is bigger then the bar with value 6.
Try doing 2 horizontal axes, one with Vertical Axis crosses = 0 min = -14 max=14.
Inverse categorys in only one of them.
So then you have the correct bar scales.
Hugo L. Mendes (from Brazil)
Ola Hugo. Obrigado pela messagem.
I'm not sure what you mean by saying that the bar with value 4 is bigger than the one with value 6 (do you mean as "value" the delta?), but I can assure you the example is fully correct as of how a tornado chart for sensitivity analysis of a model should look like.
There are other charts named tornado, and they are used to compare two statistical distributions. You can see examples on Peltier's pages http://peltiertech.com/WordPress/tornado-charts-a… — from your comment I'm guessing this is what you are trying to do, and in that case, you only invert the category of one axis.
For the type of tornado chart that I'm showing, only one horizontal scale is used and all the values shown by the bars are a comparison against the "base case" (7 in the example). Then you read each bar as the variation versus the base case when a parameter changes.
JCM
I have a problem, when I paste the chart into Power Point the vertical axis reverts back to the far right side and this ruins the chart…. I then tried manually switching it back in Power Point, which solved it. BUT then when I print it, it prints it as if I had not fixed it. I.e. it's PRINTING the screwed up version while SHOWING the right version on the screen. WEIRD.
Any explanations, solutions?
I have almost everything working now, thanks!
However, I would like to link the base case value to a specific cell reference so that it updates automatically. Otherwise the chart looks very strange.
Any idea how to do this?