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.
- This technique is ideal for testing two variables that affect the final Equity Value
- In this example, I've picked Exit Multiple and WACC
- The Exit Multiple ultimately has a bigger bearing on the final valuation
- This emphasises the need to watch for market cycles, when buying or selling a company
How a Sensitivity Analysis Works (re-cap)
- A sensitivity table re-runs the model multiple times for a range of input values
- Up to two input variables can be changed in a sensitivity table
- The resultant table of output values helps you understand the relationship between inputs and outcomes
- Scenarios work especially well if you want to change many variables at one time
- In this lesson, I change the consulting growth rate projections over the 5-year period
- Growth rates are especially important as they often have the biggest impact on value
- You can apply as many variables as you want to your scenario analysis
- With too many however, it can be hard to tell which variable is causing changes in valuation
ALT + H , H , T: Color cells white (remove gridlines)
ALT + H , B , T: Add a thick border around selected cells
ALT + A , W , T: Insert Data Table
ALT + H , L: Open conditional formatting menu
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.