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

depreciation.

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