Tornado diagrams or tornado chart are modified version of bar charts and are also one of the classic tool of sensitivity analysis used by decision makers to have a quick overview of the risks involved. In other words a Tornado Diagram/chart shows a financial analysis for a project.

When you will start searching how to make tornado charts on Google, it will show many results and most of them will be requiring you to download an add-in. If you don’t want to install any add-in you can still you can still make a good look tornado chart, here we will show you a way around 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 45,000 seats. The decision maker believes the low-base-high values to be 25,000-35,000-45,000.

2) Average ticket price: The price that the market will allow next year is unknown. The decision maker gives the low-base-high values as $10–$15-$20 for 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 data table like this:

Rows 4, 5 & 6 shows the data ranges High to low, base value column show average expected values, 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, 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.

So, once you set up the data in a table like the one shown, here are the steps to contruct a tornado chart:

1. Select the data, excluding the Base column

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

3. 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 (31 in our example, average of base values)

4. Without closing the window, click on any of the bars, which should bring the Series Options pane. Adjust the slider 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.