Excel Finance Class 83: Estimating Cash Flows For NPV calculation

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

test attribution text

Add Comment