Investment Decision (5): Sensitivity Analysis

sensitivity analysis

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

What-If Scenario

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)

basic sensitivity analysis

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)

basic sensitivity analysis

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.

Investment Decision (4): Return

return on investment

The return on an investment is perhaps the most obvious aspect of the investment decision for corporate finance. However, there’s more to evaluating an investment than just looking at the earnings. In this post we will talk more in detail about how to evaluate an investment in light of its potential returns.

First Things First: Cash flow

Before we get into the process of evaluating returns, we must consider how we are measuring returns. There are various ways of quantifying earnings, but which one makes sense?

We use cash flow to calculate the returns of an investment. Cash flow allows us to both quantify the returns and evaluate the timing of the returns. Furthermore, cash is something we can actually spend as opposed to earnings which are merely available on the books.

Return mantra: time-weighted, incremental, cash flow return

  • Cash flows, because you cannot “spend” earnings
  • Incremental, because you want to evaluate returns related to the investment
  • Time-weighted, because early returns are more convenient than later returns

What Impacts Returns?

The next question to ask is what actually impacts returns. Turns out there are several factors:

  • Revenues may be overestimated
  • Revenues may be delayed
  • Operating costs and capital expenditure may be underestimated
  • Tax rate may increase
  • Interest rates may increase
  • Risk premiums and default spreads may increase
  • There may be unanticipated opportunity costs
  • Synergy within the company may be overestimated

All of the variables above will have an impact on the evaluation of the return of an investment and must be considered before approving a new investment

Estimating Cash flow Returns

The two main drivers of estimating the return are projected revenues and projected expenses.

Estimating operating income

The revenues include all income related to the investment.

The expenses include the capital expenditure required to initiate the project (such as buying land or machines) as well as fixed and variable operating expenses. Also take into account the depreciation, amortization, G&A costs and the taxes to be paid on the operating income as these will directly affect the cash flows.

After-Tax Operating Income = 
+ Revenues
- Operating Expenses
- Depreciation
- Amortization
- G&A expense
- Taxes

From operating income to cash flow to firm

The result of is an estimate of after-tax operating income related to the project. To get the cash flow view of the project we add back the non-cash charges, less the capital expenditures and change in non-cash work capital such as inventory and accounts receivable.

Cash flow to firm = 
+ After-Tax Operating Income
+ Depreciation
+ Amortization
+ Tax benefits received
- Capital Expenditures
- Change in non-cash work capital

From cash flow to incremental cash flow

To calculate the incremental cash flows on the project, we include the pre-project sunk investment, less the tax on depreciation and add back tax on allocated G&A.

Incremental cash flow to firm = 
+ Cash flow to firm
+ Pre-project sunk investment
- Pre-project depreciation * tax rate
+ Non-incremental allocated expense (1 - tax rate)

From incremental to time-weighted cash flow

Now that we have an overview of the incremental cash flows to the firm, it’s time to factor in the time value of the cash flows. As said, we consider the timing of the cash flows because early returns are more convenient than later returns.

Net Present Value (NPV)

To calculate the time-weighted cash flow, we use Net Present Value of the cash flows. The NPV is the sum of the present value of all cash flows on the project including the initial investment.

The cash flows are discounted at the appropriate hurdle rate. Use cost of capital if the cash flows return to the firm, or use cost of equity of the cash flows return to equity investors.

If the Net Present Value is higher than Zero, then the project is acceptable.

Internal Rate of Return (IRR)

The internal rate of return is the discount rate for which the net present value is zero.

If the Internal Rate of Return is higher than the hurdle rate, then the project is acceptable

Salvage Value

If a project or investment is finite and short life, then you can calculate a salvage value. It is the expected proceeds from selling all of the investment in the project at the end of the project life. Typically the salvage value is sum of the book value of fixed assets and the working capital.

Terminal Value

For a project with undetermined or very long life, it is sometimes not reasonable to estimate the present value of all cash flows. It is reasonable to compute a terminal value which constitutes the present value of all cash flows beyond the estimated cash flows.

To calculate the terminal value, it is reasonable to use the inflation rate as growth rate. The terminal value in year n is then:

TV(n) = (Cash Flow in Year n+1) / (cost of capital – growth rate)

Uncertainty and Returns

In the beginning of the post we outlined the various factors that may impact the revenue. At the beginning of a project, we forecast revenues and costs as best as possible. But since we can never be sure about the future, there is always a certain degree of uncertainty. What can we do about it?

A simplistic approach to this problem is to calculate how quickly we earn back our money. Based on the projected cash flows we can determine the timing of investment payback.

Sadly, this method does not help us understand, value and mitigate the uncertainty. What degree of inaccuracy of which assumptions will are NPV and IRR most sensitive to?

With sensitivity analysis and what-if questions we can determine this. As sensitivity analysis can get complicated, we will cover this topic in a different post.

Side Costs and Benefits

Most projects or investments create side costs and benefits to the business that are not directly linked to the project.

The opportunity cost is allocated resources of a firm to a project that could otherwise have been allocated to another project. It represents the loss of opportunity of a project that could not be invested in.

Within the organization, multiple projects could create synergies that are not captured in the traditional capital budgeting analysis.

The returns on a project should incorporate these cost and benefits.