Welcome to Finance in

Excel video number 83. 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 Class section. Hey, in this video,

we get to see how to estimate

future cash flows and then use those

estimations to calculate net present value and internal

rate of return and payback. Here are a bunch of assumptions. We're assuming based on some

research and our past records that we can sell 30,000

of a new product.

Our price is going

to be $10 each. And let's say these

are boomerangs. So we're going to wholesale

them for $10 each. Our variable cost is $7.50. The life of the project is

going to be three years. Required rate of return

is going to be 0.175. We have fixed cost

of $10,000 a year. Fixed costs are of

course things like rent, administrative

costs, things that are not going to change over

the life of the investment. The investment,

that means the cost, is going to be $110,000

to take on this project. Depreciation method

for this example here, we're going to

assume straight line, although the tax depreciation

method is a more relevant one. We'll see videos

later on for that.

Salvage value is a zero. Removal cost of

that will be $1,000. Those will offset, so the

final value will be zero. Networking capital at time

0 is going to be $10,000. Now, networking

capital– how come we have to consider

networking capital? Well, things like cash to

pay bills for the project, accounts receivable,

inventory, accounts payable– all those things go up

when you take on a new project. So this is like

networking capital is the difference

between current assets and current liabilities. So you always have to include

that in your calculation, just like you have to

include the investment of the long-term asset.

There's short-term

networking capital. Recover– this is

the first time we've seen this in a calculation. So when we bring

on a new project, we're going to have to

increase networking capital. So we're going to– it's a cash flow out. But at the end, we recover it. So that means that once

this project ends, then the inventory, the accounts

receivable, the accounts payable, the cash

associated with the project, will go back down to zero. So at time 0, you subtract

net working capital. At the end, you add it back in. Our tax rate is

going to be 0.34. All right, we have a

lot of calculations to do before we can get

to our bottom line here, which is calculate net

present IRR and payback. All right, well, the first

thing is depreciation. Now, we could just put

0, because salvage value is how much you're

going to sell it for. You're going to

sell it for junk. Removal cost is $1,000. So these offset each other. So straight line, we're going

to say the original cost minus the salvage value.

That's how much we– $1,000 in for it. But we're immediately going

to add back in the removal cost, because that is a cost. Close parentheses divided by and

our life of our project is 3. So there's our annual

straight line depreciation. Now we're going to calculate

what's called pro forma income statement. This means– pro

forma just means we're estimating for the future

and for our relevant cash flow analysis.

To get to cash flows

for net present value, we're not going to

include interest. Interests are

considered other places. That is not a cash

flow from assets. It's a cash flow to

or from creditors. So interest is

not included here. I put– in the textbook,

they say net income. But I like to say net

income not considering interest, because it's

not really net income. All right, our total revenue. Now, all these are going

to be calculation based off of our assumptions.

So our total

revenue, equal sign. And I have to find my– now, there's the units sold. There's the price, and

there's the variable cost. Now notice, price

and variable cost, total revenue, which is from

the price and variable cost. Same order– so I go like this. The units– and I'm

going to hit the F4 key– times price. Right here it'll give me 30,000. But when I copy

down that green one, we'll move down to the 750. Control-Enter, and

then I copy it down. You can see that

worked just fine, because we locked that one.

I can already see I wrecked

my line, that underline there. And I don't think

this will work. Fill– without fill,

fill without formatting. No, it was already wrecked. So I'm going to click

on that cell, Control-1. And I'm going to go to Border. And I'm going to select

this one right here. Click on the top and then

come down here and click OK. Oh, I don't know why

that's not showing up. Let's try that again. Control-1. Something odd going on here. Control-Z that. Well, I clicked the wrong one. That's why- Control-Z.

I must be asleep– Control-Z. There we go. I must be asleep at the wheel. Control-1, it's

here and down here– that one and down here. I'm really asleep. Just to show you what– if you have the formatting

here and you copy it down, it goes away.

But you hit the smart tag and

say Fill Without Formatting. And so it doesn't bring

the formatting down. When you copy

something, it brings the content and the formatting. All right, gross profit– we're going to say,

oh, all of our revenues minus our variable costs. Our fixed costs

are from up here. There's the fixed cost

right there, so $10,000. Enter. Our depreciation equals this. EBIT, earnings before interest

and taxes, gross profit minus– and I'm going to do

the sum of these two. You can go minus

minus if you want. So EBIT– and always

very important to consider taxes

whenever you're doing this incremental cash

flow for valuing it, estimating future cash flow. So our taxes are going to be in. As I mentioned

before, whenever I do the individual

calculation, I am going to use the round,

so EBIT times our tax rate comma 0 to the

integer or dollar. So net income becomes

EBIT minus taxes. Now, what we

learned in chapter 2 was operating cash flow is EBIT. We have to add back the

depreciation and minus taxes. Well, look at this. We're finance people,

not accountants here.

And we're doing

cash flow analysis. So our net income doesn't

have the interest in it. And we've just– to calculate

this, we said EBIT minus taxes. So actually if

there's no interest, our operating cash flow is just

net income plus depreciation. So I'm going to say our net

income plus our depreciation, because we've already

subtracted out that right there. All right, now we're going

to talk about something else that's called the tax

shield approach to calculating operating cash flows. And coming up in a

future video, we'll look at some cost

saving projects. And cost saving

projects just means instead of going out

and buying a new machine to make a new product,

you can buy a new machine to save money. And this calculation

often times, it'll get us the same

exact answer as this. But it'll be a little bit

easier in some situations. Now, look at this. Operating cash flow, we

say sales minus costs. And costs are everything,

all the variable costs and fixed costs. Notice there's no

depreciation there. So it's all of the sales

minus all the costs except for depreciation.

And then you multiply

that by 1 minus the tax, because you have to– for every $1, you

have to take out your taxes, marginal tax rate. And then you add back in

the actual cash inflow of tax savings from

recording depreciation on your income statement. So this is depreciation

times tax rate. That is the amount of

taxes you save by recording a non-cash expense on your tax. So this equation right

here will work just fine. And we can prove it to ourself. Now, here I use round. Or no– up here, we

used round on the taxes. Here I'm not going to. You'll see that it's

a few pennies off. But it's all

estimations, so it's OK.

All right, I'm going

to say in parentheses all of my sales

minus variable costs. Well, I already have

that right here. This is revenue

minus variable costs. So I'm going to click on gross

profit minus my fixed cost. All right, so that calculation

is that right there, sales minus cost– times in parentheses

1 minus the tax rate. And I have to scroll

up to get that. That's B14.

OK, that right there

is the amount– all of our sales and costs

except for depreciation and the tax taking out. Now, the only thing

we have to add back in is our advantage of

non-cash depreciation. So we're going to

say depreciation. And I'm just going

to get it– actually, I'm going to get it from there– times in my tax rate too. OK, so this part is the

advantage of non-cash tax. And this is just all the other

expenses minus the taxes. And you could see, we get

$55,366.67, which rounded up gives us this. All right, and so this is

a perfectly viable option. And actually in some of the

future videos, the cost savings one I'll do this and even

one of the future sales ones.

I'll do it this way too,

because sometimes it's just easier to do this. You can do it all in a

single cell sometimes. And you don't have to make

a whole income statement, and so that's the advantage. All right, oh, I think we're

getting pretty close here. Now we can get down

to our time 0 to 3, because it's three years

out, all of our cash flows. Now we're going to have a

column for operating cash flows, change in net working

capital, capital spending. And then we'll add them all up. So at time 0, no

operating cash flows. But here we have equals. And I'm going to use

this one right here. I'm going to hit the

F4 key, because we've estimated for this

example, this video, it's the same each period. We'll have some videos coming

up where they're not the same each period.

So for each of the

three years, that's our operating cash

flow, cash with a plus because it's positive. Now, at time 0, not

only do we buy an asset, but we also invest in

net working capital. So I'm going to say negative. And I'm going to have to go up

and get my net working capital investment at time 0. Now remember, that's

accounts receivable, inventory, cash,

accounts payable. That has to go up when you

have this new project here. But at the end, we

recover it, because when the project winds down,

you don't need extra cash for that project. Accounts receivable will

eventually get paid off. Accounts payable, inventory,

all that goes away. So you assume that it gets– it's a positive. It literally comes back in. Now, this is a cash flow

out, because, of course, you have to increase to

purchase net working capital.

At the end, we get it back. So it's going to be a positive. Capital spending,

it's just a negative. So we go up and get that– invest at time 0. Now, in this example, we

didn't sell it for anything. But in upcoming

videos, we'll see what happens when we sell it. If you sell it, you maybe have

a gain or a loss and a tax implication. So we'll have to consider

that in future videos. But right here, that's it. That's our cash flows

for this project. Equals SUM– and

I'm going to add up.

Control-Enter and drag it down. I'm also going to add

a cumulative column, so for payback. So we're minus 120. And then down here, it will be

minus 120 plus this right here. So guess what? Just like we saw

an earlier video, I'm going to use

the SUM function. I'm going to click on that. I'm going to hit the

colon key, Shift-Colon. And if I copy this down, this

would be a ridiculous formula.

It's the same cell

to the same cell. But if I lock this

one with the F4 key, that means this is

an expandable range. And sure enough, as

we add going down, right here it'll add

this one and this one, which is the same as saying– doing it individually. Control-Enter. I'm going to drag it down. So here you can see

we just added those. The range expanded. Here you can see

the range expanded. So right between

these two periods.

So year 2– so if I

put a 0, 1, 2, 3– somewhere in the middle of

year 3 somewhere– so 2 and– my mouse is not working– 2 and a little bit

when we get to payback. All right, now let's make

all of our calculations– equals net present value. The rate, I'm going to

have to go up and get that. It is RRR, required

rate of return. This is the rate that is

determined inside the business. They say this is the

return we have to earn, something bigger than this– 0.175. That's our rate period. The values, not 0, because

this is net present value– so 1, 2, 3. And then plus the

negative at time 0– plus the time at– cash flow at time 0.

So there it is. We have a net present

value project. Now, a net present

value project, that's just our first criteria. It says now we better

take a harder look at this project to make sure all

of our assumptions are correct. But that's step one. You get a positive

net present value. That's looking good. Make sure everything's– all

your estimates and everything are looking good before

you proceed further. But this is step one.

And then we could

also look at IRR. That'll tell us the– these cash flow. What is the internal rate of

return from these cash flows? And IRR includes time 0. 21.25. And finally, we could

calculate the payback. It's going to be two

and a little bit. Now, the way we were calculating

this little bit before is we– oh, that's how much

is left to collect. And so we'll compare

this with using division to next period's cash flows.

That's assuming that

there's an even cash flow occurrence throughout the year. I'm going to say minus,

because this is a negative, and then divide it by that– 2.14. All right, so that's

our introduction to estimating future cash flows. As we go forward, we'll

look at MACRS depreciation and some other important

factors that add complications to this first

simplified example. All right, see

you next video for those exciting complications. See you next video..