Creating a Spider Diagram with a Two-Way Data Table

This page shows how to create a spider diagram in excel.  The spider diagram is created with a two way data table where you use percentage factors when you make your scenario analysis.  Spider diagrams have the percentages on the x-axis as illustrated below.  To create spider diagrams you need to isolate on one output variable such as the value of the company or the IRR etc. Then, you can see how this variable changes when you change the sensitivity percent.  But you want to do this for many variables.  This means you should set-up a two-way data table.  The graphs are directly made from a two way data table. Examples of the process are included in the files that you can download by clicking on the buttons below.


Excel File with Illustration of How to Create a Spider Diagram from a Financial Model and a Two Way Data Table



To create the spider diagram it may be silly to change all of the variables by the same percentage.  Variables with a higher volatility should have a higher range.  For example if you are varying the oil price you could examine the volatility of oil prices and then move from the largest to the smallest by some kind of standard deviation.  The variation in the EPC cost may be much less if you have a fixed price contract and the range may be only a few percent above and below the expected level.  You could even put a different downside and upside variation for a variable.  To do this you can set-up a column of percentages for each variable as shown in the screenshot below.

The bottom of the table shown on the screenshot is the key to making the spider diagram work. You need to put the percentages in a column. This can be different for each variable.  Then at the bottom of the column you can list variable number codes for each different variable.  These codes correspond to the variable number at the top.  The means when you change the variable number at the top it defines the only variable that will be the basis for the sensitivity.  It is kind of like the normal old scenario number.  But this time it turns the sensitivity on for one variable and off for all of the rest of the variables.  Note in the example that the variable number is 2 and at the bottom, the only variable that has a true value is variable number 2 or the price variable.  When the test is false, all of the sensitivities are set to 1.0 or the base case number.  This just uses an IF statement with the form IF(Variable Number = Code,Index,1).



The screenshot below illustrates the creation of the data table used to create the diagram. The data table uses two code numbers. The second line and the second column can be hidden.  On the column, the various sensitivity percentages are shown and this corresponds to the scenario variable.  On the row the code numbers that change variables one by one are included.  This turns on one variable at a time and drives the TRUE/FALSE thing. To make the data table work, you need to turn on and off the percentage factors.  For example the investment is multiplied by 50%, 60% 70% …. 100% …. 130%, 140%.  But this percent is only implemented when the defined row number is equal to 1.