Tornado diagrams, or tornado charts, are modified versions of bar charts and are also one of the classic tools of sensitivity analysis used by decision makers to get a quick overview of the risks involved. In other words, a tornado diagram or chart shows a financial analysis for a project.
What is a Tornado Chart?
When you start searching for how to make tornado charts on Google, it will show many results, and most of them will require you to download an add-in. If you don't want to install any add-in, you can still make a good looking tornado chart. Here we will show you a way to do the same.
It is good to ask the decision maker to provide these values. We will use an example where an international cricket ground wants to evaluate the uncertainties that contribute to their profit for the next match. The three uncertainties are as follows:
1) Number of tickets sold: The stadium has a capacity of 45,000 people.The decision maker believes the low-base-high values to be 25,000–35,000–45,000.
2) Average ticket price: The market price for next year is unknown.The decision maker gives the low-base-high values as $10–$15–$20 as what the anticipated average ticket price will be next year.
3) Average cost per game: The estimated average variable costs associated with producing one game are estimated to have low-base-high values of $200,000-$250,000-$300,000
Let's start by transforming the above information into a data table like this:
The data ranges are shown in rows 4, 5, and 6.High to low, the base value column shows average expected values. A low value for an uncertainty is the value where there is only a 10% estimated chance that the realized value will be less than said value. A similarly high value for an uncertainty is the value where there is only a 10% estimated chance that the realized value will be above said value.
How to insert a Tornado Chart?
So, once you set up the data in a table like the one shown, here are the steps to construct a tornado chart:
1. Select the data, excluding the Base column
2. On the Insert ribbon, choose Bar. Pick a Clustered Bar (the first one in the 2-D section).
Right-click on the horizontal axis and choose Format Axis.
3. At the bottom of the Axis Options pane, look for Vertical Axis Crosses. Choose the Axis value radio button, and type your base case value (31 in our example, average of base values).
4. Without closing the window, click on any of the bars, which should bring up the Series Options pane. Adjust the sliders for Series Overlap & Gap Width accordingly.
That's it! Now you can format the chart as you like. The final tornado chart in Excel should look like the chart shown below.
