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:

1. Select the data, excluding the Delta column
2. On the Insert ribbon, choose Bar. Pick Clustered Bar (first one in the 2-D section)
3. 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)
4. 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
5. 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:

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.