This blog is going to run through how to incorporate multiple assumption scenarios into your DCF model. Scenario analysis is a pretty simple feature to incorporate into a model that can add a lot of value to your analysis of a company. Let's run through the basics of setting this up now.
Open up excel and set your worksheet up to look like the following example:
Next we need to data-validate cell C3 to be our trigger to switch between the scenarios. Data validation involves limiting what types of information can be displayed in a certain cell. Click on cell C3 then go to the Data Menu, then select Data Validation. This should bring up the following menu:
From the Allow menu featured in the picture above, select List. This should bring up the following screen:
Next we need to click on the small icon with the red arrow under the Source menu, and then select cells D1:E1. This should result in you seeing the following screen:
Select OK and you will have data-validated cell C3 so that it can now only contain the values that appear in cells D1:E1.
For the time being lets set cell C3 to equal 1 as per the example below:
Alright, now lets populate our scenarios with values so we can get this up and running. Put 5.00% in cells G7:I7, and put 3.00% in cells K7:M7 as per the example below:
Next is our formula. Go to cell C7 and type in the formula:
This formula says that if the active scenario is set to 1 (C3) then select the growth rate for scenario 1, and if the active scenario is set to 2 select the growth rate for scenario 2. For a two scenario set-up there are shorter ways of doing this, however this formula style is best if you have lots of scenarios as you can keep adding scenarios 3, 4, 5 etc to the same formula without increasing the complexity of the formula or breaching excel's capacity to handle nested if statements (if you don't know what these are don't worry, not essential for understanding these concepts anyhow). Let's copy and paste that formula across cells C7:E7 and your screen should appear as per the example below:
And if you change cell C3 to 2, your screen should appear as per the next example:
You can then use the same formula to choose from a different set of operating assumptions for each line item you are modelling in your DCF model.
Your Financial Statement outputs will then link back to the active case, and you can compare how the company will have performed under different DCF modelling scenarios.
To learn more about Financial Modelling we encourage you to sign up for one of our courses today!