In this video we will focus on sensitivity analysis.
We'll have you study how changing the value of some projections
while holding others a constant affects the project's NPV.
You can use the Data Table function in Excel to do this analysis easily.
I should emphasize that for you to be able to use the Data Table function
all cells in the spreadsheet except the input ones highlighted in yellow should be
connected through formulas, otherwise the sensitivity analysis will be wrong.
We will use the same example from last time.
We calculated the expected NPV of the project to be a negative 211,618.
We can expect revenue projections to be a critical input for NPV calculation.
What if our projection of $500,000 in the first year in the best case scenario
is too high or too low?
How does it affect the expected NPV?
Link an empty cell cell in your spreadsheet to the cell with the value for
the expected NPV.
In the column to the left of the cell and
starting one row below, enter a number of values that sales can take.
I'm inputting values from 0 to 800,000 at increments of 50,000.
Next, highlight both columns and go to the data tab.
Under the data tab, look for what if, and click on it.
Select data table.
Leave row input cell empty.
In the column input cell, click on the cell that has the value of 500,000 as
the revenue projection in the first year, click on okay.
You should see a number of values in the second column
below the original expected NPV.
These are the NPVs for various values of first year sales revenues.
As you can see the expected NPV becomes positive when first year sales
revenues in a best case are at $600,000.
At all values less than $600,000, the expected NPV stays negative.
So this is clearly an important input to NPV calculations.
If we are underestimated revenues for
any reason by at least $100,000 we will be rejecting a project that we would have
otherwise accepted if our sales projections had been more accurate.
A second assumption we could look at is a sales growth of 15% per year
over the second and third years.
What if those numbers were higher or lower?
We could again use Data Table for a range of values from 0% to 30% at 3% increments.
We find that NPV's positive for growth rates of 21% and higher.
The project is unprofitable at lower growth rates.
A third assumption to examine could be the perpetual growth rate for
FCF beyond five years.
We assume that number to be 5%.
Let's look at the sensitivity of expected NPV to changes in this growth rate.
We again use Data Table over a range of values between 0% and
9% at increments of 1%.
The expected NPV is positive for growth rates of 7% and higher.
Otherwise it is still negative.
These examples give you an idea as to what is sensitivity analysis.
We can repeat this for pretty much every projection or assumption we have made.
We can do this for values in the worse case scenario also.
Though here we are focused only on the best case of high sales.
We can repeat the sensitivity analysis by changing the values of
two inputs simultaneously while holding other values are constant.
Data Table and Excel will allow us to do this.
Let's do this for first-year sales and sales growth rate of the second and
Link an empty cell, to the cell with the value for the expected NPV of the project.
In the same column below the cell, input a range of values for first-year sales.
I'm taking values between 0 and 800 thousands.
At increments of 50,000.
In the same row as the cell linked to the expected NPV input a range of values for
the sales growth.
We keep it between 0% and 30% at increments of 3%.
Then select all these cells, click on what if under the data tab and
select Data Table.
Since we have growth rates in the top row of the highlighted table,
grow input cell should reference the cell with the sales growth rate.
Column input sell should reference the cell with the first year sales.
Click on okay.
Now, you should see all cells filled with values of expected NPV.
A large number of these values are negative.
The project is attractive only if revenues are much higher or
sales growth is much higher.
We can do the sensitivity analysis for any pair of assumptions or projections.
A two way analysis like this,
helps us identify the interplay between our projections.
In this video,
we try to understand which are the critical assumptions that affect our NPV.
How confident we are of our cash flow projections, there are a number of factors
beyond the company's control that will impact these cash flows.
2. Performing sensitivity analysis
Sensitivity analysis can be performed using multiple techniques. The basic premise is to change one or several assumptions and see what impact such change has on the outcome.
In financial reporting, for example, sensitivity analysis would generally be based on changes in assumptions regarding discounts, interest or exchange rates, prices, pension obligations, etc. However, if a profit estimate is more sensitive to changes in other economic assumptions, such as development or operating costs, the sensitivity analysis should be based on changes in those assumptions.
For instance, if we are talking about price sensitivity and Company ABC’s profit is $15 million as well as revenue is $100 million, then price sensitivity will be $15,000,000 ÷ $100,000,000 x 100% = 15%. In other words, if the company decreases its prices by 15% and all other assumptions remain the same, the company will have a zero profit.
Alternately, sensitivity may show how a relative increase or decrease in the input data would impact the cash flow under consideration. For instance, if Company ABC has borrowings for $20 million with the average fluctuating interest rate of 4%, then a 5% interest rate would reduce the annual income by $1 million (i.e., 20 million multiplied by 5%).
As another example, if Company ABC’s Euro balance is 5 million, Euro strengthening (weakening) by 10% would result in a $0.5 million ($5 million multiplied by 10%) increase (decrease) in profit.
In the case of pension expenses, sensitivity analysis is provided to the company by an actuary and may read as follows: “The Company’s 20X3 total pension expense was $9 million using a discount rate of 4.3%. An increase in the discount rate would reduce pension plan expense, and vice versa. As an indication of the sensitivity, 1 percent increase in this assumption would have reduced total pension plan expense for 20X3 by approximately $0.85 million. A 1 percent increase in the discount rate for this same plan would have reduced pension expense for 20X3 by approximately $0.19 million.”
3. Advantages of using sensitivity analysis
A sensitivity analysis is an easy and quick tool that provides useful information for decision-making. It helps to identify those critical assumptions that give rise to volatility of assets, liabilities and consequently financial results. By the means of sensitivity analysis, the attention of management and users of financial statements is brought to the most risky areas. If risks and uncertainties were not considered in financial statements, too much confidence might have been placed on the financial results of an entity.
Not a member?
See why people join our
online accounting course:
Free Study Notes
Download free accounting study notes by signing up for our free newsletter (example):
We never share or sell your e-mail to third parties.
Ask a Question
Suggest a Topic
Suggest it to be answered on Simplestudies.com: