If a financial model is setup using best practice modelling techniques then putting sensitivities and scenarios should be simple. What is the difference between a sensitivity and scenario you may be asking. Well a sensitivity only flexes of one input, whilst a scenario flexes multiple inputs.
About the Inputs/Assumptions
At Video Financial Modelling we usually have one assumption sheet for a simple financial model and two assumptions sheets for complex financial models. The two assumptions sheets for a complex financial model are:
1) A static assumptions sheet – inputs that don’t change over time. For example a scheduled construction end date.
2) A time-series assumptions sheet – inputs that can change over time. For example inflation indicies.
Setting up a sensitivity or scenario analysis
Below are brief instructions to setup a sensitivity or scenario analysis manager.
Step 1: The first step in setting up a sensitivity or scenario analysis is to incorporate, what we like to call a scenario selector. The scenario selector is simply a number between 1 and the “maximum number of scenarios you want to run”. For example say you want to run ten sensitivities/scenarios, then the maximum number you’d put in your scenario selector would be ten.
A simple scenario selector would just look like this.
Not much to it is there? Bear with us though.
Step 2: Now that you’ve got a scenario selector let’s move onto incorporating a simple offset formula. The basis of this formula is to grab the relevant data from the selected scenario. The form of the formula we usually use for this is:
= offset(current cell reference, ,scenario selector) for static pages
= offset(current cell reference, scenario selector,) for time-series pages
Obviously if you have rows/columns in between the actual assumption you use in your model and your input, then you may need to adjust the above formula.
Step 3: So what happens if you want to use scenario 1’s input if you don’t have any data in the selected scenario? Well that is easy, we just add an IF statement in as shown below.
=if(offset(current cell reference, ,scenario selector)=””,scenario 1 cell, offset(current cell reference, ,scenario selector)) – for a static sheet.
Obviously it is nearly the same for a time-series sheet.
The logic behind this is that we don’t have to put all inputs in for every different scenario we do. We just put in the inputs we want to change from the scenario 1 (usually called the base case).
Sensitivity and Scenario Example
You can follow along with the below example by downloading the YouTube video and spreadsheet.
Ok, this might be confusing right now, but once we look at the below example our methodology should become apparent.
Say we had the following inputs and we wanted to find the NPV of equity distributions. Let's assume there are no taxes and our profit is fully distributed to shareholders as there is no debt in the company.
Now let’s put in a scenario selector (Step 1 above). Obviously this scenario selector will not work on its own, so let’s now put in a an offset formula as per Step 2 above. The result is shown below. Now all our inputs will be moved to the 1st scenario and the formula will be placed where we previously had inputs.
Ok let’s now run a scenario, say scenario 2. Let’s put in rev of 55 and ops of 40. What happens? Because we don’t have inflation (and haven’t incorporated Step 3 above) in we won’t have any inflation.
Let’s change the formula as per Step 3 above to get:
Now we have an active scenario manager which we can change and run scenarios on. Putting it all together you should get NPV of equity distributions of 135 for the base case and 101 for scenario 2. See the below picture or download the spreadsheet to see the workings.
Now for all of you out there who think you’ve got it why don’t you try this one. Find the NPV of the equity distributions if the inflation is 2.5% for the first five years and 7% for the next five years. Hint: Your scenarios will need to go down the page as this is now a time-series assumption (i.e. changes over time). The NPV of the equity distributions you should come out to be 137.
If you liked this tutorial, try one of our Financial Modelling or Excel training courses today.
No comments:
Post a Comment