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 email@example.com to get the files.
- Tornado Exercise.xls
- tornado chart template.crtx
Scenario and Tornado Exercise Finished.xlsx
Scenario and Tornado Exercise Two Way Table.xlsm