Friday, September 28, 2007

Excel Tornado Chart example

When building a model you have nearly always have to make major assumptions that drive the output. Obviously you make your best directional estimate based upon available data. However, as you are finalizing the model the right step is to assess the sensitivities of your output to your assumptions.

One good way to show those model sensitivities is in a tornado chart.

First, select two series of data that show your max and min range for each sensitivity variable, and put them in horizontal bar chart, like below.

Remove all the normal Excel chart junk to get a cleaner view of the data.

Next, Format the x-axis and click on the Scale tab - set the "Category axis crosses at" variable to 20 (in this case - whever your midpoint is).

You should have what looks like the above. Now, right click on the right bars and format them to be the same color. Then, click on the options tab and change "overlap" to be 100. It should look like the below.

Here is an excel file with the below tornado chart example.


Jorge said...

Hi. There seems to be a problem with the link.

Consultant Ninja said...

Hi Jorge-

My mistake, I fumbled the URL. Corrected now.

Anonymous said...

this is a great tip. I have never seen such elegant way to create tornado diagram!!! Although so many people can make them in so many ways, they are no match to yours.

It would be nice if you can provide tips for the following enhancement:

can you, in the tornado chart, put numbers to the end of bars, the numbers are the assumptions values corresponding to the Highs and the lows.

Anonymous said...

Hello, I like your simple explanations. just fyi though, this does not work for excel in Windows Vista. I know it had just come out when you posted this.

Vista sucks.

Anonymous said...

This works with Vista, you just need to know where the right buttons are.

Anonymous said...

Tip for question regarding high and low labels:

just select the bar and after right mouse click choose "Add Data Labels".

After that you can modify them any way you like :)

Kristin said...

Thank you so much for explaining this! Found you via web search and this made my finance project much easier - keep up the great work.

tonyk said...

Thank you,clear and I managed to replicate a chart that looked the same.

Friday, September 28, 2007