Welcome to Finance in Excel video number 82.
Hey, if you want to download this workbook for chapter 9
or the PowerPoints for chapter 9,
click on the link directly below the video,
and scroll all the way down to the Finance Excel Class
section.
Hey, we're in chapter 9.
And we've already done two videos.
We're talking about capital budgeting in more detail.
In essence, what we're talking about
is how to estimate the cash flow so we
can do net present value, internal rate
of return calculations.
So in turn, we can have good information or decision
criteria about whether to buy a new asset or not.
Now, let's go over to PowerPoints here.
We got to talk about cash flows, estimating cash flows.
And if you're going to estimate cash flows,
you got to know what the relevant cash flows
for a project are.
Here is our definition of a relevant cash flow--
incremental--
what does that mean?
The changes, the incremental cash flow changes,
the difference between the future cash
flows with a project and without.
Well, let's look at some examples.
Sunk costs.
All right, so here we are.
We're the firm.
And we're going to investigate whether we
want to start a new project.
So we do some marketing.
And we spend $500,000 on marketing, or whatever it is.
We spent it.
We paid the marketer.
They did the research.
Now we're going to go and decide what kind of assets to buy.
But guess what?
The marketing research is already paid for.
It has nothing to do-- it is part of the cost for the firm.
And guess what?
It'll happen whether or not we take this project.
Opportunity costs-- for example, if you
have some land that you paid for, $5 million,
you paid for it five years ago.
And now all of a sudden you want to use
that land for a new project.
Well, the opportunity cost, you can't use the historical cost.
Guess what you have to figure out--
at minimum, what the market value,
what you could sell it for.
And then, because we could sell it and we're not--
we're going to take on this new project--
that's an opportunity cost.
So if it was worth $6 million, we
could sell it for $6 million, we have
to subtract that from our cash flows,
because we're giving up that opportunity
to sell that land for $6 million.
Side effects/erosion-- another relevant incremental cash flow.
For example, if we're selling a new product
and we're going to lose sales from an existing product,
that means-- that's called erosion.
Or not only could we have erosion,
we could actually-- because we're
going to market our new product, it
could increase the sales of our existing products.
So that would be a side effect.
But those have to be considered, and we'll
see an example of that coming up.
Oh, changes in net working capital--
what is net working capital?
Remember, that's current assets minus current liabilities.
That means things like cash.
We might need extra cash to run this project.
We might have extra accounts receivable.
We might carry extra inventory.
We might have extra accounts payable.
Those are all net working capital.
Those are relevant.
This is just like earlier in chapter 2,
we did cash flows from assets.
We had operating cash flows.
We had to know what the cash was for any new assets and this,
in essence, the difference between current assets
and liability, which hopefully is somewhat current assets.
So it's kind of like a short-term asset.
Got to include it in your calculations.
Guess what?
Financing costs are not going to be included here.
That's an external variable that's
considered somewhere else.
So when we look at our relevant cash flows for the project,
we're not going to include financing costs.
So before we did our earnings before interest and tax.
Then we calculated our interest and tax.
Well, guess what?
We're not going to consider interest expense.
All right, so those are just some examples
of relevant cash flows.
Let's go over to Excel, and we're
going to see examples of most all of these right here.
Now let's take a look here at this example here.
We have land we bought six years ago for $5.5 million.
Today we have a market value on this land of $6 million.
We're considering building a manufacturing
plant on the land, which would cost us $19 million.
And we have to fix up the land.
We got to do some grading, get the excavators
and the bulldozers out there and fix up the land, which
is going to cost $500,000.
So what kind of costs are these?
Well, this is a historical cost we paid for this, this land,
six years ago.
It's a sunk cost--
absolutely not part of our calculation.
This is the relevant calculation,
because this is an opportunity cost.
This means if we take on this project,
we're giving up the $6 million we could sell this for.
And when looking at an opportunity cost, at minimum,
you look at what you could sell the asset for.
This is going to be a cost going out.
And same with this.
This is included in the price of the asset.
This is not relevant.
And the rest of these are totally relevant for our cash
flow--
estimation of future cash flows in doing our net present value,
et cetera.
Now let's go ahead and figure out
what the total cash flow out at time 0 is for this asset.
This is not considering net working capital, just
the fixed asset.
Well, we certainly are going to have to include our opportunity
cost.
We gave up that amount.
We are going to certainly include
the cost of our estimations for building the plant and also
our grading with all the excavators and bulldozers.
So $25,500,000 would be our cash flow out at time 0.
Now let's go look at some other examples.
I'm going to click on sheet 4.
We'll talk about erosion.
Oh, we're considering building a new bulldozer.
And we have two existing bulldozers-- a D9 and a D9Q.
Now, we did a market study, and we got some numbers.
So for our new bulldozer and our existing bulldozers,
we estimated these were the units we would sell.
Here's the price for each bulldozer.
And we can go ahead and calculate our sales
for each one--
whatever the units are times the price.
And then I copy this down.
But these are not the relevant cash flows.
These are relevant for the firm as a whole.
But when you're analyzing a project,
like considering building a new bulldozer,
you only look at the incremental cash flows.
The stand alone principle says we
can analyze all the relevant cash flows for this one project
alone and then come to some decision.
Certainly these hard cash flows are great for the whole firm.
But we're only interested in the change, the change
if the new line of bulldozers right here are introduced.
Well, this is erosion.
We're going to lose 700 sales of this D9Q.
But why would we get some increase?
Well, sometimes you have a new marketing campaign.
You got this new bulldozer, the buzz about it.
And so they discover your product.
So in this market study, we got an increase of 3,000
for existing D9, but a drop here.
All right, so we're going to calculate our incremental cash
flows associated with this project.
They are not going to be these.
And guess what?
It's not going to be this either.
This marketing cost, sure, the information we got from this
help us create all of our estimates
and were an integral part of deciding whether or not
we should go ahead with this project.
But forget it.
We did the marketing study.
We paid them.
That's a sunk cost.
It's already incurred, because guess what?
We paid for this.
If we decide to take this new bulldozer project,
we have to pay it.
It's already paid.
If we don't decide to take this new bulldozer project,
we still have to pay it.
So it's not relevant.
It doesn't change in regard to taking this or not.
All right so the relevant cash flows for our D9
is the change times our price.
And now actually-- yeah, and then hit Enter.
And that's going to be an increase, which we like.
But this will be a decrease, some erosion in one
of our products.
And then the relevant cash flows for our new bulldozer
is just the total right there.
All right, so when we add all of these together, Alt-Equals,
there is the relevant sales cash flow.
So that's in example number 1.
Let's go do example number 2 for erosion.
We're talking about relevant cash flows.
Now, this one's a little bit more involved.
Well, we have an old product-- let's see if I can make this
a little bit bigger--
old printing machine, the price of the old printing machine,
the old printing machine current yearly units
sold, which we'll use to estimate
any differences in the future.
If the new unit, which is this new printing machine here,
is sold, old printing machine yearly units sold.
So they will go down.
Here's the new product.
We have a new printing machine.
Notice if we get this new printing machine,
we're still going to sell some of the old machine.
It's just the sales are going to go down a bit.
The price of the new printing machine,
expected sales units for the new printing machine.
And we're going to manufacture this printing machine.
Variable cost, that's VC, variable cost,
as a percentage of sales--
oh, so for our whole company, we estimate
whenever we make a printing machine,
the variable costs are going to be 55% of the sales
price or total sales.
The depreciation on the new printing equipment,
so that's the whole point of this.
Do we take on this new--
buy all the equipment to make this new printing machine?
The project is whether we want to start making
this new printing machine.
All right, so we have depreciation on that.
And fixed cost for all of the equipment
that we're considering buying is a million.
Our tax rate is 0.34.
And in this example, we just want
to figure out if we could calculate the operating cash
flow.
Well, first we got to figure out our erosion in sales
from the old printing machine.
So well, we're going to take the difference
between this and this, units before we
took on this new project, estimation after.
So we're going to lose some.
So that's the difference.
And the sales price is right there.
So that's going to be ultimately a negative.
We'll do all our negatives down here.
That's a reduction.
Erosion in variable costs--
wait a second.
Actually let's go ahead make this a negative.
Now, wait a second.
We're going to lose some sales.
Oh, but when we make a sale, we sell this one machine.
If we don't sell one machine, we also
don't have that variable cost.
So in this example here, this is the total sales
we're going to lose.
But if we also can avoid some cost,
this is going to be a plus.
So I'm going to say equals 0.555 times--
and then I'm going to do a negative to make
this a positive, so 55%.
We're going to lose these sales, but we're
going to avoid these costs.
So what that means is a cost is going
to be added, the incremental cost we avoid,
the incremental sales we lose.
All right, sales from new printing machine--
so there is our new price times our estimated sales.
We're also going to have to calculate--
and that is going to be a plus.
And I'll do this as a negative.
How about that?
That one times 55, that'll give us our variable cost
for the new printing machine.
OK, total fixed costs for the new, that's an outflow.
So there's our fixed costs for the new printing machine.
And these are all estimates, estimates
per year for a number of years.
In this first couple examples, we're
going to just stick to the one year.
Depreciation for new printing machine.
For tax purposes before we figure out the tax,
we're going to have to subtract this.
So there it is.
That's the depreciation for the new printing machine.
And now we can calculate EBIT, earnings
before interest and tax.
Now notice, we're not going to include interest,
because that is not a relevant cash financing, whether used
equity or debt, it's going to be considered somewhere else, not
in the cash flow analysis for a new project.
All right, so I'm going to say--
I think we can just straight add all these.
There's a subtraction and sales there.
It's this cost saving.
That's the cash flows from this new machine.
Variable costs, we're going to have to subtract that,
S, subtract.
So we can just add these, Alt-Equals.
So that's EBIT.
And as-- I don't think I mentioned when we were looking
over in the PowerPoints, incremental cash flow
means after-tax incremental cash flow.
In all situations, since any new revenue or any new cost
incurred, which is deductible on your tax statement,
is going to have a cash flow implication.
So here we're going to figure out the taxes.
And now I'm actually going to be a little
inconsistent in this chapter.
So far in this class whenever we've calculated a tax,
we've used the ROUND function.
And I am going to use it right here.
I'm going to say EBIT times our rate comma 0--
means round to the dollar.
However, later in this class, we're
going to use-- learn a tax shield approach to calculating
operating cash flow.
And when I do those, I'm not going to use the ROUND.
Ultimately it's all estimates here anyway.
But in this chapter whenever I'm doing the individual tax
in this method, I'll use ROUND.
Otherwise, I won't.
All right, so we have our tax.
The operating cash flow then becomes--
oh, look at this--
our EBIT minus our taxes.
And what do we have to do?
We have to add back depreciation.
Well, this is a negative.
So I'm going to subtract it.
Now you add back the depreciation,
because that's a non-cash expense.
Hey, this looks exactly like what we did in what?
Chapter 2 when we did our cash flow analysis--
EBIT minus taxes plus add back the depreciation.
All right, so there is our estimate
for operating cash flow.
In this video, we looked at what relevant costs--
they are the incremental cash flows.
And we saw some great examples.
We saw sunk cost.
We saw opportunity cost.
We saw erosion.
In this video, all we did was calculate operating cash flow.
Our ultimate goal is to estimate all of the actual cash
flows associated with a project and then calculate
net present value.
Well, we'll do that for the first time next video.
See you then.