Welcome to Finance in

Excel video number 89. Hey, if you want to download

this work for chapter 9, click on the link

directly below the video and scroll all the way down

to the Finance Excel Class section. In this video– this is our

last video for chapter 9– we're going to talk about

sensitivity analysis. Now, in our last video,

we did scenario analysis. And the difference between the

two is, well, scenario analysis we looked at four

variables at a time. And with scenario

analysis, we got different net

present values based on pessimistic and

optimistic assumptions. So it helped us see

what forecast risk there is in our assumptions. Forecast risk is just the risk

that our assumptions are not correct. So we had four variables here. Sensitivity analysis,

we just change one. So here's our one variable

we're going to look at. We're going to

change units sold. And then we want to see what

happens to net present value.

And this will show us the

forecast risk for just this one variable. All right, here's

our assumptions. Cost of the project

is this, salvage 0, straight line for five years,

ROR, tax rate, no net working capital. Let's calculate our

depreciation, straight line, as we've mentioned a

number of times already. MACRS is the most appropriate

one for our cash flow analysis. But to make this easier,

we're using straight line. We had a couple great videos

earlier in this sequence that showed MACRS, though. All right, there's

our depreciation. Now we're going to calculate– we'll do our base operating

cash flow first and then our net present value. And we're going to use– the last couple of videos,

we used the tax shield method for calculating

operating cash flows. So we're going to

do that again here. First, we want to

calculate all of the sales and subtract out the

variable cost and fixed cost.

So the way we're

going to do that is first we're going to calculate

gross profit per product for one product. So the price is $72.00 minus

our assumed variable cost. That tells us the profit– $17 every time we sell– gross profit every time

we sell one product. We'll multiply that

times our units. That will give us

our gross profit. F2. And then we're going

to subtract from that our total fixed costs. That'll give us our

profit in essence before subtracting

out depreciation. So this amount we need

to multiply times 1 minus the tax rate. So in essence, what

we're doing here is we're saying

that's our profit. For every $1.00, we're

going to subtract out $0.34. The only bit of cash flow

information we need still is our depreciation– cash benefit from our

income, our tax return. So we say plus the full

depreciation number. That's a non-cash expense

thrown onto our tax return. So it saves us

this amount of tax. So that little bit, that

little extra bit right there– $1,700 cash we're

adding back in.

All right, so that's our

operating cash flow estimate. Now, we could lock all these

and copy it over and over and just leave that one

cell reference relative. But I'm going to show you

another little trick here. I'm going to copy

this in edit mode. And then I'm going

to come over here in edit mode and Control-V. And see that little purple box– or lavender box? That's the only

one I need to move. So I'm going to

point to the edge. That's the move cursor. You can move it

wherever you want. But be sure– let go

of it on the right one. So Tab, Tab, edit

mode, Control-V. And I'm going to move it.

That's the move cursor. Wow. So we have some positive

cash operating cash flows. Now we can calculate

our net present value for each one of these. The rate, I'm going to

click there and lock it. That's our required return– comma and then the value. Oh, we're going to

do that same thing we did last video

since we don't have the whole spread of values. We're simply going to notice

that the net present value function can allow us

to just put in values. So the first one is this. And we don't have any net

working capital, so we just put our cash flows in 1 comma– so I type in a comma to get to

the next one, comma, 1, 2, 3, 4, 5, close parentheses. And then we need to

subtract out the cost. So it's just the

cost right there. And now we can copy this over. We left all of those

relative, so those will move. And no, no, that one

needs to be locked. So we're going to have

to come here and lock it with the F4 key.

So that one's locked– the

rate and the cost at time 0. OK, so we have a negative,

a positive, and then a bigger positive. So as we change just

this one variable, we can see how the net

present value changes. Now, really what we

should do is make a chart. Now, for an xy

scatter, I would like to list units sold this way

and the net present value, because I want to plot it. But these are horizontal, and

I need them to go this way. So there's a quick

way to do this. You can use the

TRANSPOSE function. Now, you can do it one

at a time like this equals this, Enter, equals

this, Enter, equals this, Enter. But I'm going to show

you the TRANSPOSE. And it's a special

kind of function. You have to highlight all

of the cells in advanced.

And you type equals

TRANSPOSE, TRANSPOSE. You got to make sure

that 1, 2, 3, 4 rows– 1, 2, 3, 4– match the 1, 2, 3, 4 columns

up here, or else it won't work. And then I'm going

to close parentheses. And you can't just hit Enter. You have to hold

Control-Shift and then Enter, because this is a

special kind of function called array function,

so Control-Shift-Enter. And then we'll do the same

thing here– equals TRANSPOSE Control-Shift and Enter. It's a special kind of function. And it's got a special

kind of syntax.

See those curly brackets? You can't type those in. But that is, when you

do Control-Shift-Enter, you're saying,

hey, Excel, I know this is an array function that

goes into many cells at once. And that's Excel talking

back to you saying, I understood that it's

an array function. All right, so we have

our data transposed here. Now we can highlight just these. This is going to be the x. This is the y for a

scatter chart with a line. So I'll go up to

Insert, Scatter, because there's

two numbers here. And then I'm going to plot it. Looks ridiculous right now. I'm going to point

to the edge and then click and drag in just to

make it a little bit smaller. The main thing I want to

do is change this axis, because you can see our range

is from 9,000 to 11,000. So I'm going to click on the

axis and right click Format Axis, or Control-1.

And we want Minimum,

Fixed, so 8,500– I went 500 below that one– and then maximum — and I'm going to

go 500 above that– close. OK, so that's already

looking much better. Maybe I'm going to

click and drag and make it a little bit bigger here. We could see that there– it is sloping upwards. This is a direct relationship

between the x and the y. That means as we

increase our units, the net present value increases. Now let's– that's

not right right there. So I'm going to come over here. I don't want those decimals. I'm going to escape

out of the chart. I'm going to highlight this. And I'm going to Control-1. Notice Control-1 is Format

Cells or Format Chart Element. And I'm going to go to General. You can see that changes it. So that means the chart is

linked to the formatting there. And I'm going to click

here on the title and then click up here

and type an equal sign and then click on D10, Enter.

With this still

highlighted, I'm going to immediately go up and change

it to 8, or whatever font size. So I change the font size

by highlighting that. All right, so sensitivity

analysis– as units sold change, what does

net present value do? That's what this is telling us. That's the definition

of sensitivity analysis. The steeper the line,

the more forecast risk associated with this variable. Now let's see if we

can actually figure out what that steepness is. Now, there's a SLOPE function. So we can try the SLOPE

just to estimate what are– it'll tell us for every one

unit change in this line, SLOPE will tell us for every

one unit change in this line how much it goes up.

And that's what we're

interested in here. Equals SLOPE– the y's

and x's, so you have to know that this one's the y. In essence what's happening

here is this is the predictor variable, the

independent variable. This is the one as we change

this, this one changes. So y's first,

comma, and then x's. So we have 37. So for every one unit

change in units sold, we have 37.61 change

in net present value. All right, that's chapter 9. We'll see you next chapter..