Excel Finance Class 88: Scenario Analysis For Cash Flow & NPV Calculations

welcome to finance and 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 yield method again for estimating operating cash flows but more importantly we'll see scenario analysis and all this means is we're creating all these estimates right so it might be a good idea to go on the low side the pessimistic side and we could 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 will 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 million 1.2 million for 5 years straight line depreciation down to 0 we'll calculate our depreciation expense here's our unit sales price variable cost and fist costs 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 but we're using this for a simplified example here so we could see how scenario analysis works alright so we're going to have that depreciation expense we have our units price etc 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 you know if you're doing this over and over and over at some point you can make you know single formulas in a single cell to estimate ate everything so that's what we're going to do here and we're going to use the tax shield method remember the tax yield method means says take 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 cost 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 parenthesis I can see 14 bucks that's the profit from each sale not considering fixed costs so I can get my total gross profit units times that spread there right but that's not right because I still need to subtract fixed costs so from that I can subtract Fisk fix 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 r1 minus the tax rate so I'm going to put in parentheses around here one minus our tax rate all right so that is 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 all right if you do it one step at a time you can figure out how to do that and you can see over here those who are 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 could 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 up or let's do net present value and the rate there is our required rate comma now 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 in individual value separated by commas now you have to be careful here we do and we've estimated the cash flows are going to be assuming they're the same for each period so we can just go value one but we have to think is there anything else that time one no there's not so we go comma value to that one value three value for we're up to four remember there's 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 times 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 you know 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 but the first thing is we got to talk about when you're going down to the pessimistic sigh 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 1 minus our 10% that's for both units and our price so our price will be no 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 okay so our cost in the base case or 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 x 1 plus 10% we're going to assume that they go up so there's 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 cuz that's a blue box there all right now let's do the 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 x 1 plus right so now you see well let's enter this I'm going to hit not forget lock that so on the optimistic side we're adding 10% for units and price on the downside we're subtracting alright 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 number so you're not just stuck with your estimations you're going to you know have some wiggle room on the low side and the upside right this probably done this doesn't really you know 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 x + 1 and we're optimistic so we're going to subtract 10 percent that means our cost 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 ctrl c ctrl v for paste ctrl V for paste and then I'm going to come down here control there are 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 ctrl V to click on the top one ctrl V and there we have done our scenario analysis pessimistic oh look at that so we got big fat negatives for operating cash flow and net present value positives here but so we go so we have the base and then on the upside you know 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 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 number in here and memorize it a third set what scenario scenarios do is that they can memorize a certain number of cells and then you just go to a drop-down 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 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 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 could show anyone 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 an arrow manager ad I 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 click OK and now you can come up to scenario Manor 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 quat sometimes you see the quat up in the top so you can yours maybe 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 we come down here I'm going to click in here and type s to get to the SS 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 cash flow operating cash flow we'll see you next video

test attribution text

Add Comment