In this post we will cover the topic of sensitivity analysis and uncertainty related to expected return of an investment.
When evaluating an investment, it’s important to do as much data gathering and analysis as possible. Good analysis will help you have a greater understanding of the associated costs and returns. However, even the most well-prepared analysis must face the uncertainty of the future. No matter how large your spreadsheet is, no one can predict the future with certainty.
Uncertainty means that some of your assumptions regarding costs, reinvestment, expenses, returns, etc. may be over- or under-estimated. No. It is almost certain your assumption will be wrong. But if we’re certain that we’ll be wrong, how can we make a useful evaluation?
With uncertainty or sensitivity analysis we can use statistical models to tell us which of the parameters in our model will have the biggest impact on the investment evaluation. Furthermore, statistical analysis will help us have a better understanding of the variance in outcomes
Simply put, sensitivity analysis is nothing more than setting up several “What-if” scenarios, testing those assumptions and see how they impact the outcome of the evaluation. For example:
- What if the revenue is 15% lower than expected?
- What is the impact if our margin is 5 base points lower than expected?
- What happens if we’re only able to raise 60% of the funds we need?
Defining a what if scenario allows you to reconsider your assumptions. It’s likely that you have a gut feeling which of the parameters will influence your evaluation most. But to increase confidence you can run the numbers.
There are many ways to do sensitivity analysis. For small projects, simply running the numbers manually in an excel sheet will work just fine. For larger projects you may want to consider professional software.
Below you can find one example of a basic sensitivity analysis and a more advanced sensitivity analysis.
Sensitivity Analysis Example (Basic)
For the basic sensitivity analysis, I use an example of an equity valuation of a Singaporean mobile app. For this project, it was important to make assumptions about all related costs on an annual basis. The result was an equity valuation using the discounted cash flow model.
In my base model, the valuation of the project was approximately SG $35.6M.
To do sensitivity analysis, I described 10 additional scenarios varying one or more of the eight different parameters. I ran the numbers for each scenario and laid them out in a separate excel sheet. As you can see, the equity valuation now ranges between SG -$8.5M and SG $35.6M with an average value of SG $18.8M.
Sensitivity Analysis Example (Advanced)
For the advanced sensitivity analysis, I use a share valuation of AMD I made in February 2019. This share valuation is a lot less detailed than the equity valuation for the Singaporean app as I use only three value drivers: growth rate, operating margin and re-investment rate.
- Growth rate: expected average annual revenue growth for the next 5 years, then converging to inflation rate by year 10.
- Operating margin: targeted pre-tax operating margin, converging from the current margin to target margin by year 10
- Sales to capital ratio: the required reinvestment cost to support the revenue growth
The share valuation is based on the current value of the company and number of outstanding shares.
Using Oracle Crystal Ball we create assumptions for each of the three parameters by determining the distribution and its factors. Then we run 1,000 to 20,000 trials. The software will use random values for the three parameters and calculate the share value.
As output, we get an expected distribution of share value as well as the associated statistical information. In addition, Crystal Ball also provides additional information on which of the three parameters impact the valuation most. In this case, operating margin is the dominant parameter.