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