Excel Finance Class 89: Sensitivity Analysis For Cash Flow & NPV Calculations

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..

test attribution text

Add Comment