Excel Finance Class 88: Scenario Analysis For Cash Flow & NPV Calculations

Welcome to Finance in
Excel, video number 88. Hey, if you want to download
this workbook for Chapter 9, click on the link
directly below the video. And scroll all the way down
to the Excel Finance class section. Hey, we're talking
about net present value and estimating cash flows. In this video, we'll get to see
the tax shield method again, for estimating
operating cash flows, but more importantly, we'll
see scenario analysis. And all this means
is, we're creating all of these estimates, right? So it might be a good idea
to go on the low side, the pessimistic side.

And we can pick
whatever spread we want. But we're going to say 10% here. So we'll do our base
case for cash flows and net present value. We'll stretch the numbers
10% down, and then 10% up. And this will give us a
range of net present values we can look at, which
gives us more information. So if we want to
spread the numbers– because it's all estimations– this process will work. We'll also get to see
Excel Scenario Manager. Now, here's our situation. We have a cost for a new asset
of 1.2 million for five years, straight line
depreciation down to zero. We'll calculate our
depreciation expense. Here's our unit sales
price, variable cost, and fixed cost, our base case. And then we'll go down
by 10% and up by 10%– optimistic, pessimistic,
our tax rate, our required rate of return,
and our net working capital. All right, let's first
calculate our depreciation. Simply a straight line– again, Makers is the
one to really use.

We're using this for a
simplified example here, so we can see how
scenario analysis works. All right, so we're going to
have that depreciation expense. We have our unit
price, et cetera. Now, we could go ahead and
create a pro forma income statement and then calculate
our cash flows, which I did do down here. But if you're doing this
over and over and over, at some point, you can
make single formulas in a single cell to
estimate everything. So that's what we're
going to do here. And we're going to use
the tax shield method. Remember, the tax shield
method says, all the revenues and subtract all
the costs, except for non-cash depreciation. Figure out what the
tax is, based on that.

And then add the tax shield
from our non-cash depreciation expense. So right here I
have to calculate, in essence, our
revenues, variable costs, and fixed costs. So I'm going to go ahead and
do that right in the cell here. First off, I'm going to notice
that price and variable costs– the difference between
these will actually give me the gross profit. So I'm going to say, that
price minus this variable cost. These are per unit. So if I enter this in
parentheses, I can see– 14 bucks.

That's the profit
from each sale, not considering fixed cost. So I can get my total gross
profit units times that spread there. But that's not right,
because I still need to subtract fixed costs. So from that, I can
subtract fixed costs. Now all this is
going to give us– it's going to give us revenue
minus all of the costs except for non-cash

Well, we know that has
to be multiplied by our 1 minus the tax rate. So I'm going to put in
parentheses around here, 1 minus our tax rate. So that– if we don't
include depreciation, that's going to be
our after-tax profit. But now we have a
benefit of depreciation. So that's where the
name of this method comes from– tax shield method. We have our shield, which is
our depreciation shielding us from paying taxes, so
that times our tax rate.

And that gives us
the extra benefit from that non-depreciation
expense on the tax return. So there it is– our
operating cash flow. If you do it one
step at a time, you can figure out how to do that. And you can see over
here, those were– right there, if we
did it the long way. All right, now, we're going
to do net present value. And get this– usually we
need a bunch of values, right, like this? But no. We're going to see that
the net present value– we can actually create all these
values right in our formula. So I'm going to say, equals– let me scroll up here. And I'm going to
do the non-cash– let's do net present
value and the rate.

There is our
required rate, comma. Now, in every example we've
done in this class so far, we just highlight a
bunch of values, right? But we're not going
to do that here. We're going to use
the option that we can put an individual
value separated by commas. Now, you have to
be careful here. We've estimated the cash
flows, assuming they're the same for each period. So we can just go value one. But we have to think– is there
anything else at times one? No, there's not. So we go comma, value two, that
one, value three, value four. We're up to four. Remember, there's a
total of five, so comma and the fifth one.

But there's something
that happens at the end of the project. We get our net
working capital back. So we're going to
have to add it. So we, in essence, created
our cash flows right within– whoops– that one right there. We created our cash flows right
within our net present value. Now, we close parentheses. And we just have to subtract
time zero cash flow. So minus our initial cost
and our initial investment in net working capital. There you go– all
in one formula.

Pretty convenient to know
how to do that if you're doing a lot of
calculations, instead of using that much
spreadsheet real estate. Now– so we've done
our base case here. Now we need to estimate,
change some variables, and then actually
we'll be able to copy these formulas over here
without recreating them. But the first
thing is, we've got to talk about when
you're going down to the pessimistic
side, units and price. Well, if you're going
to be pessimistic then you want to say, oh, I'm
probably going to sell less. If you want to be pessimistic
you're going to say, oh, I probably can't
sell it for that much. So for both of
these numbers, we're going to take these numbers
and multiply them by one minus our 10%. That's for both
units and our price. So our price would be– I'm going to lock
this, right there– that one right there.

So I'm going to hit the
F4 key, Control-Enter. Copy it down. All right, and that one worked. Now, what about costs? OK, so our costs in
the base case are 29. But if we're going
to be pessimistic, what are we going to do? We're going to assume
that they're more, right? So for both of these costs,
we're going to say times one plus 10%. We're going to assume
that they go up. So on the down side,
you have to do something different to the units and
price then to the costs. So now we went up. We've assumed it's gone up.

And also, we're
going to assume– I did it again. The B3, just hit F4. I copy it down. And so now I got it right. The 10% times that right there. You can't see it because
that's a blue box there. All right, now let's
do the optimistic. All right, so we
have units and price. Well, if you're going to be
optimistic you're saying, oh, I'm going to
increase my units. Oh, I'm going to get
to increase the price. So for both of
these, we're going to say that times
one plus, right? So now you see– well, let's enter this.

So I'm going to not forget– lock that. So on the optimistic side, we're
adding 10% for units and price. On the downside,
we're subtracting. All right, let's go
ahead and do our costs. Well, what's the optimistic? Well, if it's 29 here, if
you're super-optimistic, then we'll pay less, right? And again, this is just a
way of spreading the numbers so you're not just stuck
with your estimations. You're going to have
some wiggle room on the low side and the upside. This doesn't really
happen perfectly like this in the real world. But as an estimation
technique, it's pretty good. All right, variable costs– I'm going to say
that times and one. And we're optimistic. So we're going to subtract 10%. That means our costs
should be lower. And now I'm going to hit the F4,
and same with our fixed costs. All right, now I'm
going to copy this.

Notice, these are all
relative cell reference. So I'm going to Control-C,
Control-V for paste– Control-V for paste. And then I'm going
to come down here. They're all relative
cell references, right? So when we copy and
paste over here, it'll be looking at the
same relative cells up here. So I'm going to copy two cells. So I click on the top one– Control-V, click on the top
one, Control-V. And there we have done our
scenario analysis. Pessimistic– ooh, look at that. So we've got big fat
negatives for operating cash flow and net present value. Positives here, but
so we have the base. And then on the upside, we have
two million net present value, down here we have
minus 1.6 million. All right, so that's how
to spread the numbers. Let's go over and we'll see
a slightly different way to do this using an Excel
feature called Excel Scenario Manager.

Here's our numbers, right? And what we're going to do is
we're going to memorize these. And then we're going to
type these numbers in here and memorize a second set. And then we're going to
type these numbers in here and memorize a third set. What scenarios do is
that they can memorize a certain number of cells. And then you just go to a
dropdown and you click on it and say which set you want. So you go up to Data, and
What-if analysis, and then up here, Scenario Manager. And you click Add. We've already
highlighted the cell. So it's got it there. And I'm going to say base. Click OK. Click OK. There it is. Now, I'm going to close this. And I'm going to come over here. And I'm going to actually copy– these are going to be
called pessimistic. I'm going to copy this. And instead of
pasting, I'm going to right-click Paste Special.

And I'm going to say,
Paste Values Only. It'll paste just the values. It'll keep all that formatting. All right, now I'm
going to highlight this. Actually, I don't
think you need to. You just go back up to
Data, What-if scenario. Now you say Add. And we're going to name this. And then click OK. Click OK. So now we have two of them. And we can show any one we want. It's a way of memorizing
formula inputs. And finally, Copy,
right-click Paste Special. And I want values. I'm going to highlight this. Actually, I'm going
to try to go over here, see What-if analysis,
Scenario Manager, Add. Look at that– it
remembers, right? And so I'm going
to call this one– it's just saying
whatever's there right now, I'm going to memorize it. I'm going to call
this optimistic. Click OK. Click OK. And now you can come
up to Scenario Manager and show the base,
show the pessimistic, show the optimistic.

And it all changed. So it memorizes formula inputs. Still further, you can
right-click the QAT. Sometimes you see the
QAT up in the top. Yours may be above, up here. But I'm going to say Customize
Quick Access Toolbar. And I'm going to
go– it's right here. And I'm going to come
over here to All. I'm going to come down here. I'm going to click in here
and type S to get to the S's. And I need to find Scenario. Went too far– not
Scenario Manager, Scenario. Click Add. It adds a button over here. Click OK. Now, mine doesn't have
a little icon picture. But now, you can just go here. And there it is– is
that totally cool? So you can just
switch back and forth. All right, scenario analysis for
net present value calculations and for the operating cash
flow, we'll see you next video..

test attribution text

Add Comment