[MUSIC]
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
third years.
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.
[MUSIC]
Ramabhadran ThirumalaiAssistant Professor
