Tornado Charts in Excel 2007/2010 Update

Tornado diagrams are a classic tool of sensitivity analysis to provide decision makers a quick overview of the risks involved.  A tornado chart to show a financial analysis for a project may look like this:

Tornado chart - a sensitivity analysis tool

In this particular case, we are assuming that the tornado shows the NPV of a project. We expect the project can be valued at $7 billion (the point where the vertical axis crosses), subject to uncertainties.

The tornado helps visualize these uncertainties. In the example, Conversion (i.e. how many of the people that shop for our product become a customer) is the largest uncertainty. We believe 35% of the shoppers would convert. If only 25% convert, the project’s NPV would drop to $4 billion, from the base case, $7 billion. On the other hand, if 45% convert, we have a large upside and the NPV would be $12 billion.

Next in relevance would be pricing, $25,500 in the base case. If it goes down to $20,500 the NPV would reduce to $5 billion. If we can raise price up to $29,500 due to a favorable competitive environment, then the upside is $4 billion from the base case.

By now, you can follow the logic of the chart, with the other variables.  Do you still have questions?  Go ahead and drop us a line.  We are happy to help.

Tornado diagrams are not used as frequently as one would expect, given how clearly they help showing the impact of different variables on a geven outcome. As suggested by Ted Eschenbach on a recent article of Engineering Economist, (issue of 06/22/2006), perhaps this is due to difficulties in constructing them.

Sensitivity analysis is needed to address the inherent uncertainty in engineering economy applications because (1) time horizons are measured in years or decades and (2) much economic analysis is done at the feasibility and preliminary design stages. This is often shown using relative sensitivity analysis charts or spiderplots, which have a long and rich history in practice and texts (they are described in 10 of 18 texts reviewed, including Blank and Tarquin (2002), Canada et al. (1996), Eschenbach (2003), Lang and Merino (1993), Park (2002, 2004), Sullivan et al. (2003), Thuesen and Fabrycky (2001), White et al. (1998), Young (1993). Tornado diagrams are not new, but they have not been used nearly as frequently. Only one of the 18 texts included a tornado diagram (Eschenbach, 2003)–

 

Searching Google on how to make tornado charts, you’ll get many results, most of them requiring you to download an add-in. Keep reading to see how you can create tornado charts with plain Excel in just 5 steps… very easy and straightforward!!

There is no need for any external add-ins for making good-looking tornado charts. And it takes only 5 steps. Let’s start with data in a table like this:

Data set up for tornado chart

The data in rows 3 to 7 show the key levers or sources of uncertainty for the model, as explained before.

In columns B and C we put the lowest and highest values for the model output, changing only the given variable.  As discussed above, for conversion, if it is 25% (down from 35% base case), the NPV is $4b, not $7b. This is why $B$4 contains a 4.  If conversion is 45%, the NPV is $12b

So, once you set up the data in a table like the one shown, here are the promised 5 steps:

  1. Select the data, excluding the Delta column
  2. On the Insert ribbon, choose Bar. Pick Clustered Bar (first one in the 2-D section)
  3. Right-click on the horizontal axis, choose Format Axis. 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 (7 in the example)
  4. Without closing the window, choose the vertical axis of the chart. Again on Axis Options, check the box Categories in reverse order, and set the Axis Labels menu to Low
  5. Without closing the window, click on any of the bars, which should bring the Series Options pane. Move the slider Series Overlap completely to the right (Overlapped).

That’s it! Now you can format the chart as you like. The final result should look like the chart shown above.

Now, watch a screencast, or download the Excel file

I hope this entry helps you use tornado charts in your decision support models.  Watch the screencast

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>