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.