Welcome to Finance in Excel video number 89.
Hey, if you want to download this work for chapter 9,
click on the link directly below the video
and scroll all the way down to the Finance Excel Class
section.
In this video-- this is our last video for chapter 9--
we're going to talk about sensitivity analysis.
Now, in our last video, we did scenario analysis.
And the difference between the two is, well, scenario analysis
we looked at four variables at a time.
And with scenario analysis, we got
different net present values based
on pessimistic and optimistic assumptions.
So it helped us see what forecast risk there
is in our assumptions.
Forecast risk is just the risk that our assumptions are not
correct.
So we had four variables here.
Sensitivity analysis, we just change one.
So here's our one variable we're going to look at.
We're going to change units sold.
And then we want to see what happens to net present value.
And this will show us the forecast risk for just this one
variable.
All right, here's our assumptions.
Cost of the project is this, salvage 0,
straight line for five years, ROR, tax rate, no net working
capital.
Let's calculate our depreciation, straight line,
as we've mentioned a number of times already.
MACRS is the most appropriate one for our cash flow analysis.
But to make this easier, we're using straight line.
We had a couple great videos earlier in this sequence
that showed MACRS, though.
All right, there's our depreciation.
Now we're going to calculate--
we'll do our base operating cash flow first and then
our net present value.
And we're going to use--
the last couple of videos, we used the tax shield method
for calculating operating cash flows.
So we're going to do that again here.
First, we want to calculate all of the sales
and subtract out the variable cost and fixed cost.
So the way we're going to do that is
first we're going to calculate gross profit per product
for one product.
So the price is $72.00 minus our assumed variable cost.
That tells us the profit--
$17 every time we sell--
gross profit every time we sell one product.
We'll multiply that times our units.
That will give us our gross profit.
F2.
And then we're going to subtract from that
our total fixed costs.
That'll give us our profit in essence
before subtracting out depreciation.
So this amount we need to multiply times 1
minus the tax rate.
So in essence, what we're doing here
is we're saying that's our profit.
For every $1.00, we're going to subtract out $0.34.
The only bit of cash flow information we need still
is our depreciation--
cash benefit from our income, our tax return.
So we say plus the full depreciation number.
That's a non-cash expense thrown onto our tax return.
So it saves us this amount of tax.
So that little bit, that little extra bit right there--
$1,700 cash we're adding back in.
All right, so that's our operating cash flow estimate.
Now, we could lock all these and copy it over and over
and just leave that one cell reference relative.
But I'm going to show you another little trick here.
I'm going to copy this in edit mode.
And then I'm going to come over here
in edit mode and Control-V.
And see that little purple box--
or lavender box?
That's the only one I need to move.
So I'm going to point to the edge.
That's the move cursor.
You can move it wherever you want.
But be sure-- let go of it on the right one.
So Tab, Tab, edit mode, Control-V.
And I'm going to move it.
That's the move cursor.
Wow.
So we have some positive cash operating cash flows.
Now we can calculate our net present value
for each one of these.
The rate, I'm going to click there and lock it.
That's our required return--
comma and then the value.
Oh, we're going to do that same thing
we did last video since we don't have
the whole spread of values.
We're simply going to notice that the net present value
function can allow us to just put in values.
So the first one is this.
And we don't have any net working capital, so we just
put our cash flows in 1 comma--
so I type in a comma to get to the next one, comma, 1, 2, 3,
4, 5, close parentheses.
And then we need to subtract out the cost.
So it's just the cost right there.
And now we can copy this over.
We left all of those relative, so those will move.
And no, no, that one needs to be locked.
So we're going to have to come here and lock it
with the F4 key.
So that one's locked-- the rate and the cost at time 0.
OK, so we have a negative, a positive, and then
a bigger positive.
So as we change just this one variable,
we can see how the net present value changes.
Now, really what we should do is make a chart.
Now, for an xy scatter, I would like
to list units sold this way and the net present value,
because I want to plot it.
But these are horizontal, and I need them to go this way.
So there's a quick way to do this.
You can use the TRANSPOSE function.
Now, you can do it one at a time like this
equals this, Enter, equals this, Enter, equals this, Enter.
But I'm going to show you the TRANSPOSE.
And it's a special kind of function.
You have to highlight all of the cells in advanced.
And you type equals TRANSPOSE, TRANSPOSE.
You got to make sure that 1, 2, 3, 4 rows--
1, 2, 3, 4--
match the 1, 2, 3, 4 columns up here, or else it won't work.
And then I'm going to close parentheses.
And you can't just hit Enter.
You have to hold Control-Shift and then Enter,
because this is a special kind of function
called array function, so Control-Shift-Enter.
And then we'll do the same thing here-- equals TRANSPOSE
Control-Shift and Enter.
It's a special kind of function.
And it's got a special kind of syntax.
See those curly brackets?
You can't type those in.
But that is, when you do Control-Shift-Enter,
you're saying, hey, Excel, I know
this is an array function that goes into many cells at once.
And that's Excel talking back to you saying,
I understood that it's an array function.
All right, so we have our data transposed here.
Now we can highlight just these.
This is going to be the x.
This is the y for a scatter chart with a line.
So I'll go up to Insert, Scatter,
because there's two numbers here.
And then I'm going to plot it.
Looks ridiculous right now.
I'm going to point to the edge and then
click and drag in just to make it a little bit smaller.
The main thing I want to do is change this axis,
because you can see our range is from 9,000 to 11,000.
So I'm going to click on the axis and right click Format
Axis, or Control-1.
And we want Minimum, Fixed, so 8,500--
I went 500 below that one--
and then maximum --
and I'm going to go 500 above that--
close.
OK, so that's already looking much better.
Maybe I'm going to click and drag and make
it a little bit bigger here.
We could see that there--
it is sloping upwards.
This is a direct relationship between the x and the y.
That means as we increase our units,
the net present value increases.
Now let's-- that's not right right there.
So I'm going to come over here.
I don't want those decimals.
I'm going to escape out of the chart.
I'm going to highlight this.
And I'm going to Control-1.
Notice Control-1 is Format Cells or Format Chart Element.
And I'm going to go to General.
You can see that changes it.
So that means the chart is linked to the formatting there.
And I'm going to click here on the title
and then click up here and type an equal sign
and then click on D10, Enter.
With this still highlighted, I'm going
to immediately go up and change it to 8, or whatever font size.
So I change the font size by highlighting that.
All right, so sensitivity analysis-- as units
sold change, what does net present value do?
That's what this is telling us.
That's the definition of sensitivity analysis.
The steeper the line, the more forecast risk
associated with this variable.
Now let's see if we can actually figure out
what that steepness is.
Now, there's a SLOPE function.
So we can try the SLOPE just to estimate what are--
it'll tell us for every one unit change in this line,
SLOPE will tell us for every one unit change in this line
how much it goes up.
And that's what we're interested in here.
Equals SLOPE-- the y's and x's, so you have
to know that this one's the y.
In essence what's happening here is this is the predictor
variable, the independent variable.
This is the one as we change this, this one changes.
So y's first, comma, and then x's.
So we have 37.
So for every one unit change in units sold,
we have 37.61 change in net present value.
All right, that's chapter 9.
We'll see you next chapter.