Guide for Modeling Risk Sensitivities

As we enter the financial modeling phase of our venture plans, I thought it would be appropriate to share some modeling tactics for assessing risk. Risk sensitivities are helpful analysis tools as they effectively show the upside/downside movement of the model's most important variables. Below are some methods of conducting your own risk-sensitivity analysis (these are very quick-hit explanations and I can definitely provide more information to those who may be interested):

1) Worst/base/best case scenarios: The base-case scenario uses reasonable assumptions to forecast the most likely outcome for your projections. The best-case, often referred to as "management case," should reflect your most bullish assumptions that you expect to achieve if everything goes according to plan. The worst-case, or "pessimistic case," should utilize the assumptions that reflect the greatest downside. This model should be dynamic, in the sense that when you change your assumptions it will simply run through the model. You can then use the "Choose" function in excel to choose the scenario that you want to run through the model, as shown in the below example. When "1" is filled in the box on the left, the model will then run through the worst-case scenario.

Sample assumptions tab using various scenarios

2) Data tables: This is the method of choice used on Wall Street for conducting risk-sensitivities. These are a little trickier to get used to, but really paint a great picture. In order to run a data table in excel, you typically choose two variables (one horizontal and one vertical) and plug them into the table as shown below.  The shortcut for data tables is alt-a-w-t and you need to plug the formula for the main variable (the result variable) in the top left corner of the table.

The below example displays the accretion impact on earnings per share by looking at potential changes in purchase price and synergies. As indicated by the red circle, when the purchase price is 70 EUR per share alongside annual synergies of 441 EUR, we expect the deal to be 0.55 EUR accretive to shareholders (this is the base-case). However, if you were to raise the purchase price to 90 EUR and reduce synergies to 241 EUR, then the deal would only be 0.02 EUR accretive (this is another form of the worst-case). Inversely, a best-case result can be achieved by landing in the bottom left hand table of the table. By using this one table, the viewer now has a good understanding of what happens to earnings if the deal structure is altered.

Sample data table used for M&A analysis

3) Tornado Diagrams and Spider Plots: These are statistical tools used in excel to convey the coefficient of each variable, or its importance relative to other variables. In order to run these in excel you will need to install the SensIt Sensitivity Analysis Add-In. The below figure displays a tornado diagram, which intuitively shows the viewer that miles per gallon is the most important variable as it can greatly change the NPV of the project. On the other hand, $/kWh is the least important and only has a minimal impact on NPV.

Sample Tornado Diagram

The below example is a spider plot. This works similar to that of the tornado diagram, in which one can observe that miles per gallon has the steepest slope and therefore has the greatest impact on NPV. Although both spider plots and tornado diagrams make it simple for the audience to visually comprehend results, neither method is used much outside the academic environment.

Sample Spider Plot

Hope this was helpful. Again, feel free to reach out if you would like to dive into more details.