9. Tornado Charts Part 2
In this lesson, we'll finish building our chart and identify some valuable insights that the chart provides.
- Identifies what variables can most affect your outcome values
- Great tool for identifying your biggest source of risk
- Referred to as a tornado chart given its shape (plot largest outcome changes at top)
How to build tornado charts in Excel
1 Select variables to be included on tornado chart
2 For each variable, create a data table that accepts two values, +/- 10% from base case
3 Record the two outcome values obtained from each data table
4 Subtract base case outcome from these two outcome values and plot on chart
Note: Make sure inputs to Data Table are not connected to row input cell
ALT + E , S , F: Paste formulas
CTRL + →: Move to the last cell in the data region
ALT + N , B: Insert barchart
In the previous lesson, we ran our model 10 different times adjusting each of our five variables plus or minus 10% percent of the base case. This left us with 10 values for the return on investment for each of these variable changes. In this lesson, we'll use this data to create our tornado chart. Off camera, I've named this outcome cell Return on Investment, because we'll be using it repeatedly in our formulas. I've also moved the data below the input parameters and created an area for our chart to input data. 6 00:00:39.01 --> 00:00:43.13 This new area is where our chart information will be stored.
So how do we turn our 10 model outputs into a tornado chart? Well if we look at the chart, we can see that the values plotted are the differences between our 10 outputs and the base case. So to create the chart, we simply subtract the base case from our 10 output calculations. So I'll start with market price. I'll take the value, and subtract the return on investment from this value. And I'll copy and paste for the next cell. I'll do the same for variable cost and off camera, I'll complete it for the remaining three variables. We're now ready to create our chart, so I'll select the data and then Alt + N, to go to the insert tab and B for Bar chart. I'll select the Stacked Bar chart and when we create this chart, you can see that we have an upside down tornado and we'll need to make some additional formatting changes to make the chart more readable. Let's start by removing the vertical lines and turning the tornado upside down. To do this, we'll select the vertical axis, right click and format axis and then click Categories in Reverse Order. I'll also move the horizontal axis labels to the left and to do this, we'll go to Axis Labels and select Low. And if I close this dialogue box you can see that our tornado chart is now much more readable. Let's now add some data labels, so I'll right-click, add data labels and do the same for the base minus 10% percent and extend the width of the chart and move it into position and extend its length. We may need to format the data labels so they are more readable and we'll add a title to the chart. I'll make both of these changes off camera. We now have a completed Tornado chart for the first version of our model. Although our model is still very simple, we can immediately identify that the market price and variable cost will be the critical assumptions in our model. We need to be very confident of our assumptions on these two variables. In contrast, the probability of success and the commercial plant cost will not have nearly as big an impact on our investment decision. Using tornado charts to identify the variables that will most affect your outcome helps you prioritize your work and more importantly helps the company understand what variables, estimates, and market forces most affect their investment decisions. When you create tornado charts from scratch like this without using plugins or VBA macros, one disadvantage is that the chart won't update automatically when you change the inputs. In the next lesson, I'll show you how to create update warnings when this occurs.