May8
Tornado Charts in Excel 2007 Update
This is an update to my previous post on tornado charts.
As reader Shaun pointed out, the instructions were created under Excel 2003. Let’s refresh them to Excel 2007.
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:
- 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 something like this:
Final Tornado Chart
Keep reading to learn more, to watch a screencast, or download the Excel file
How to read the tornado chart?
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, 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, he 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.
I hope this entry helps you use tornado charts in your decision support models.
Click on the image below to watch the screencast.
No related posts.
How do you add the title to the chart? Thanks.
I would also add – print function to make useful instruction available off-line
Very, very, very VERY useful!!!!
Thank you!!!
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.
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
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!
[...] Tornado Charts in Excel 2007 Update [...]