Tornado charts are a popular way to visualize and analyze model sensitivities.
If you look through financial models, pitch decks, or annual reports from large corporates, you’ll find some form of tornado chart to help depict various scenarios.
In this tutorial, I’ll discuss the tornado chart and show you how to create one in Excel.
I’ll also show you how to customize and style the chart so it’s easy to read and understand.
What Is A Tornado Chart?
A tornado chart is a tool used to visualize the impact of sensitivities on value or investment returns. They are often used in business and financial analysis to identify the most significant drivers of change or variation in a particular metric.
The chart enables readers to quickly assess the relative size of different model input variables (e.g. inflation or interest rates) and to identify the sources of variability within a data set.
Tornado charts consist of a bar chart with two sets of bars, usually, one positive and one negative against the reference value. The bars are arranged vertically, with the longest bar representing the greatest difference between the two values. Based on the sensitivities run, the bars will resemble a tornado, hence the name!
Here’s an example of a tornado chart showing the impact of various sensitivities on a reference return of 10%:
How To Make A Tornado Chart In Excel
Here are four easy steps to create a tornado chart, a full example is further down below:
- Highlight the dataset and create a bar chart
- Select a bar, right click and select Format Data Series (or press CTRL+1 for the shortcut)
- Under the Series Options, adjust the Series Overlap setting to 100%
- To make the chart easier to read, you can add Data Labels to the left and right bar charts and adjust the colors as needed
Full Example
Using the example below, we have run various macroeconomic and business driver sensitivities against a baseline return of 10%. These are absolute returns based on sensitivity.
The first step will be to transform the data into a usable form. As these are absolute values, we need to create a set of data that is relative to the 10%.
Using the Sensitivity Chart data, create a bar chart.
Next, you need to format the chart so that it’s readable by the user.
- Select any of the bars and press CTRL+1
- Under the Series Options, adjust the Series Overlap setting to 100%
- Select the axis labels and press CTRL+1
- Under the Labels option, change the Label Position to Low
The last few formatting updates I suggest are:
- Add a helpful chart label
- Add data labels – right click on the bars and click on “Add data label”, do this for the positive and negative results
- Delete the chart legend
- Change the positive to green and the negative to red
Tornado Chart Video Guide
Thanks for reading and I hope you found this helpful!
Other Excel tutorials you may also like:
