Sign in or start a free trial to avail of this feature.
11. Scenarios and Sensitivity Analysis
All valuations should be subjected to both scenarios and sensitivity analysis. In this lesson, I show you how to accomplish both of these tasks, testing growth rates, exit multiples and WACC.
To explore more Kubicle data literacy subjects, please refer to our full library.
Lesson Goal (00:04)
In this lesson, we’ll calculate the equity value for MarkerCo and examine its sensitivity to key variables.
Calculating the Equity Value (00:18)
After calculating the enterprise value, we can easily calculate the equity value. To do this, we subtract debt from the enterprise value, and add cash. The debt and cash figures can be found on the Balance Sheet. These figures should come from the final year of our actual financial statements, in other words the year before our financial projections begin.
Sensitivity Analysis for Equity Value (01:27)
After calculating the equity value, we can use a sensitivity analysis to identify how the equity value changes when the key variables in the model change. We create a table containing various possible values for the exit multiple and the discount rate, and we use a data table to calculate the equity value for each combination of these values. If we were using the perpetuity method of calculating the terminal value, we would replace the exit multiple with the stable growth rate in the data table.
In our case, we find that a higher exit multiple leads to a higher equity value, and a lower discount rate leads to a higher equity value. Both of these findings are as we would expect. We find that the exit multiple has a bigger impact on equity value than the discount rate. This illustrates the importance of understanding market cycles, as exit multiples are likely to be significantly higher during a market expansion than a market contraction.
Scenario Analysis for Equity Value (03:34)
A scenario analysis can be used to see how the output of a model changes when one or more variables are changed. Generally, we create three scenarios: Optimistic, Base, and Pessimistic. We then create a switch towards the bottom of the model that allows the user to select which scenario is selected. We use Data Validation to create a dropdown containing the three scenario names.
In our model, our scenarios change only one of the variables, namely the assumed revenue growth rate for consulting. We create three rows containing the growth rates in the three different scenarios. We then create a row called live, which is the row used in the model. This row uses a nested IF statement to identify the selected scenario from the switch cell, and identify the appropriate growth rate assumptions. As a result, when the user selects a different value from the switch, the whole model updates based on the changed growth rate assumption. This changes the equity value produced at the end of the model.
In the previous lesson we calculated enterprise value using two different methods for calculating the terminal value, the exit multiple and the perpetuity method. We can now calculate the equity value for the business. As you'll remember, the equity value is the value of all the shares outstanding and to calculate the equity value, we subtract debt which is going to be zero and add cash and cash equivalents which we can find on the Balance Sheet. So, let's first scroll up to the Balance Sheet and as you can see I have grouped each section of the model to make navigation a little easier. Let's open the Balance Sheet, Alt + A J and I'll link cash and cash equivalents for 2015 to this cell.
Here we go and the value is 69 million. To calculate equity value, I simply add these values together.
And I'll do a little bit of formatting to clean this cell.
Once we have equity value calculated, let's examine its sensitivity to some key variables. I'm currently using the exit multiple to calculate the terminal value, so this will definitely be one of those variables and the other of course will be the weighted average cost of capital. In this table I have set aside some sample values for exit multiple and the discount rate and I've connected my equity value into the top left-hand corner. Let's now select these cells, Alt + A W T for a data table. My row input cell will be the discount rate and my column input cell will be the exit multiple.
And when I press OK, this calculates my equity value under all of these scenarios. Let's now apply some conditional formatting by selecting my cells, alt H, L, I'll select green to red.
It looks good. From the conditional formatting we can see that a higher exit multiple gives us a higher valuation and a lower WACC gives us a higher valuation as well. It also appears that the exit multiple has a bigger impact on my equity value than the discount rate.
And under all discount rate scenarios, the exit multiple range is greater than 50 million. This is very interesting because it shows us how important it is to be able to pick the correct exit multiple but also to understand market cycles as market go is much more likely to fetch a higher exit multiple during a market expansion than during a market contraction. Needless to say, if using the perpetuity method to calculate the terminal value, we would replace the exit multiple with our growth assumption in the sensitivity table. When evaluating your model, it's also worth creating some scenarios for your model that affect the key variables. Typically you will create three scenarios, optimistic, base and pessimistic. For me in this model the biggest assumption regards the future consulting growth rates, so let's scroll up and see this section of the model. I've created three new rows corresponding to a different set of assumptions for optimistic, base and pessimistic for the consulting growth rate. I've also renamed the existing row live. Off camera I'll fill in values for these three rows.
In cell D194 I've created a switch for these scenarios and I use that switch in my formula for the live row, so I'll start by writing an IF function that checks if cell D194 is equal to optimistic.
And I'll make sure to anchor this cell.
If it is optimistic, I'll take the value from the optimistic row, otherwise I'll enter another IF function that checks if D194 is equal to pessimistic.
And if it is, I'll enter the pessimistic value and if it isn't, I'll go with the base value.
And I'll copy across for the remaining cells.
Now, let's implement my switch in cell D194 and I'll go with a dropdown.
I'll select list, and type in my options.
And when I press OK, I can now select my scenario.
Now let's test out the scenario by checking the enterprise value when I switch scenario. And as you can see, it updates accordingly.
In this example, I've only switched one variable as part of my scenarios but needless to say, you can apply multiple variables to a scenario. Just employ the exact same technique I used here for consulting growth rate.