Excel Finance Class 89: Sensitivity Analysis For Cash Flow & NPV Calculations

welcome to finance and Excel video number 89 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 finance Excel class section in this video this is our last video for chapter now 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 assumption so in 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 changed one so here's our one variable we're going to look at we're gonna 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 zero straight-line for five years are our tax rate no net working capital let's calculate our depreciation straight-line as we've mentioned a number of times already makers is the most appropriate one for our cash flow analysis but to make this easy we're using straight-line we had a couple great videos earlier in this sequence that showed makers though alright there's our depreciation now we're gonna 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 use the tax shield method for calculating and operating cash flow 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 gonna calculate gross profit per product for one product so the price is 72 – our assumed variable cost right that tells us the profit seventeen bucks every time we sell if gross profit every time we sell one product well multiple time ply that times our units that will give us our gross profit f2 and then we're going to subtract from that our total fixed cost that'll give us our profit in essence before subtracting out depreciation so this amount we need to multiply times one minus the tax rate so in essence what we're doing here is we're saying that's our profit we're gonna for every $1 we're gonna subtract out 34 cents the only bit of cash flow information we need still is our depreciation cash benefit from the our income our tax return so we say plus the full depreciation member that's a non-cash expense thrown on to our tax return so we saves us this amount of tax so that little bit right 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 and over over and over and just leave that one cell reference relative but I'm gonna show you another little trick here I'm gonna 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 that's the or lavender box that's the only one I need to move so I'm gonna 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 gonna 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 gonna do that same thing we did last video since we don't have the whole spread of values we're simply gonna 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 one comma I'm typing a comma to get to the next one comma one two three four five 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 I wouldn't have to come here and lock it with f4 key so that one's locked the rate and the cost at time zero okay 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 a XY scatter I would like to list units sold this way and the net present value cause I want to plot it but I these are horizontal 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're type equals transpose transpose you've got to make sure that one two three four rows one two three four match the one two three four columns up here or else it won't work and then I'm gonna close parenthesis and you can't just hit enter you have to hold ctrl 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 to 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 a rate that's Excel talking back to you saying I understood that it's an array function alright 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 gonna plot it looks ridiculous right now I'm gonna 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 9000 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 fixed and I'm gonna go 500 above that close okay so that's already looking much better maybe I'm gonna 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 gonna come over here I don't want those decimals I'm gonna escape out of the chart I'm going to highlight this and I'm going to control 1 you notice control 1 is format cells or format chart element and I'm gonna go to general you can see that changes it so that means the chart is linked to the formatting there I'm going to click here on the title and then click up here and type an equal sign and then click on d-10 enter with this still highlighted I'm gonna mmediately go up and change it to 8 or whatever font size so I change the font size by highlight not 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 our 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 wise and exes so you have to know that this one's the why in essence what's happening here is this is the predictive variable the independent variable this is the one as we change this this one changes right so the wise first comma and then exes so we have 37 so for every one unit change in unit sold we have 37 point six one change in net present value all right that's chapter nine we'll see a next chapter

test attribution text

Add Comment