8. Tornado Charts Part 1
Tornado charts are a great way of finding out which which parameters most affect our output. However, they are difficult to build and require substantial data analysis.
- 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
SHIFT + →: Select next cell
ALT + A , W, T: Create data table
ALT + E , S , V: Paste values
ALT + E , S , F: Paste formulas
Now that we have finished the first iteration of our model let's find out which of our variables have the largest impact on our outcome. In the previous course, we used sensitivity tables to understand the impact that incremental changes in a parameter can have on our outcome. In the next two lessons, we're going to take a step further and build a tornado chart that allows you to compare the impact of changes in multiple variables all in the one chart. In my experience, Tornado charts are one of the most insightful diagrams you'll create in a model and very effective in conveying assumption risks to clients and managers. When creating Tornado charts, a number of Excel plugins can be bought that automatically generate these types of charts. In this course, however, I am going to assume you don't have any of these plugins and we're going to build the tornado chart from scratch in Excel. Let's start by identifying the variables I'd like to include in the tornado chart. Starting off, I'll limit our tornado chart to five variables and these five variables will be market price, variable cost, reference plant cost, commercial plant cost and probability of success. For each of these variables I'd like to run the model twice. The first for an input value of 10% percent lower than the base case and the second for an input value of 10% percent greater. So I'll start by simply writing minus 10% and plus 10% in these two cells. I'll then create the desired inputs, by writing equals market price multiply by one plus the percentage change. Then I'll copy for the other cell. Next, I'll run the model for both of these values. In the previous course, we ran multiple versions of the model using our sensitivity analysis. This time around we'll also use data tables to run our model but we only need to run our model for one row of data. To do this, we put our desired output in this cell, we'll select these cells and then press Alt + A, W, T to bring up the row input cell which in this case is market price. I'll then press OK to run the model. Looking at the output values, we can see that there is a problem with this calculation. A market price of 10% percent higher should result in a higher return on investment but it's giving us a value of 10.3 which is lower than the base case of 10.7. And the reason for this is that the inputs in our data table are actually connected to the market price in the formula. For a data table to run correctly, there must be no link between the data table inputs and the row input cell. So to break this link in the formula, I'll simply copy and then paste values. And now we have a return on investment which makes much more sense. I'll now repeat this calculation for the variable cost input. I'll first get my inputs, by taking variable costs and multiplying by one plus the percentage change. I'll paste this value for the second cell, and then I'll convert to values with Alt + E, S, V and this breaks the link in the formula. I'll link our desired output to this cell. I'll select the cells and create a table. And the row input cell will be variable cost. And off camera, I'll complete this for the three remaining variables. 31 00:03:58.11 --> 00:04:03.03 We've now run the model, 10 different times, twice for each chosen variable.
These 10 values will be used in the next lesson to create our tornado chart.