Scenario Reporter, Tornado and Spider Diagrams

This page explains how to make different presentations of scenario and sensitivity analysis using tornado diagrams and spider diagrams.  A tornado diagram can be a good risk tool because it shows the importance of different variables and it demonstrates whether there is more downside or upside risk. A spider diagram can be used when sensitivity variables are expressed as percentages (e.g.120% or 90%). Then a two way data table can be used with the percentages and the various different input variables. In creating a tornado diagram you need add a whole bunch of scenarios where each sensitivity has only variables that differ from the base case.

Creating a tornado diagram quickly involves using a combination of the data table tool and the index function. The process is helped with the TRANSPOSE function. The video below illustrates the process

Fundamentals of Creating a Tornado Diagram from a Scenario Analysis using a One-Way Data Table

Video and the file below demonstrate how to create a tornado diagram in just about any file.  You need to set up a scenario analysis with a a base case, low case and high case.  When you do this, the key is to use the INDEX function and put a code number for the scenario that is operating. Then you attach the inputs from your file to the INDEX function results in the scenario diagram.  After making this basic scenario diagram, you should make your scenario analysis into a sensitivity analysis. To do this, use the TRANSPOSE function with the titles for all of the items in the scenario analysis. This is just like creating a whole bunch of new scenarios.

Sorting the Tornado Diagram from the Highest from the Lowest

Creating a Tornado Diagram with a Two-Way Data Table

 

Making a Flexible Tornado Diagram that can Select Different Variables over Time from a Financial Model

 

Resources that you can find on the library related to tornado charts are listed below.  Send me an e-mail at edwardbodmer@gmail.com to get the files.