CF 8-4A – Scenario Analysis – Corporate Finance

Let's examine problem eight for a scenario analysis. We're going to look at best case, base case, worst case scenarios for some financial projections. Horizon Creations is considering a new project. Details of the project are below. So we've got a cost of 300 grand, some unit sales it's going to generate, prices and cost information. Project's going to last three years with a required rate of return of 10% and the tax rate's 25%. Depreciation is straight line to zero over the life of the project. So that's giving us depreciation information. We have a $300,000 project. It's going to last three years. That's a hundred thousand dollars per year in depreciation, just 300,000 divided by three. The question says prepare a pro forma income statement based on the projections above and compute the expected net present value of the project. This will be your base case. And in the next one, we're going to have worst case and best case. And so I think a good sensible way to do this is to prepare our income statement in three columns. We're going to have base. We're going to have worst case. And we're going to have best case all sort of side by side by side. So we can look at them all together and then prepare them all together. And there's some efficiencies here in doing it all in sort of one chunk. So, uh, let's get to it with our base case and we'll start with our sales revenue figure. We are expecting to sell 10,000 units, 60 bucks a unit. That would be $600,000 in sales as our base expectation here. Hopefully my math is right there. Yeah, it is. I'm let me just calculate that. Uh, 60 times 10,000 at 600,000. Why am I worried? Um, I just did a long pro problem and I got a number wrong right at the beginning and it screwed everything up and I had to redo it, so I'm a bit shy because of that. Our variable costs here, 20 bucks a unit. So that's 200,000 in variable costs are fixed costs, $80,000. Bringing us down to earnings before interest tax depreciation and amortization of 320, right? Our sales revenue minus our costs give us EBITDA. Uh, our depreciation, we just said was a hundred K. So our EBIT is 220. Our taxes are going to be 25% of 220, 55K, $55,000. Uh, giving us net income profits, 220 minus 55. I should be able to do this in my head, but I can't. One 65. I'm going to say one 65. Let's see. Two 20. Yeah. I feel pretty good about that. One 65. Okay. I still got it. I'm getting old and slow, but, uh, I can still subtract 220 minus 55. Pretty impressive. Uh, okay. Let's continue. Uh, so to figure out, uh, we're, we're asked to do, um, net present value. So of course, to figure that out, we need to figure out our operating cash. So I'm just going to move this over a touch. I guess I could have moved this one over a touch. That would have been smarter. Well, now I've moved them both over a touch. So we're really on fire here. Um, our operating cashflow is, uh, EBIT. We'll do this, the standard method of figuring out operating cashflow. You start with EBIT 220. You add back your depreciation because of course, depreciation is a non-cash item that went into EBIT, uh, which is a hundred thousand. And then you deduct your taxes, which is 55,000. You can do this different ways and you might get there quicker than me. That's fine. 220 plus a hundred minus 55, 265. That is our OCFR operating cashflow. We're almost to the point where we can compute net present value. What do I need to do now? Summarize my cash flows. So in year zero times zero, I have a initial project cost of 300,000, but in years one, year two and year three, I have money coming in, I have 265 coming in. I can see this is going to be positive net present value. So I have to discount these cash flows to discount them by hand. You just divide by one plus the discount rate. One 10% required rate of return, 1.1 to the power of how many years you're discounting. So 1.1 to the zero with power. 1.1 to the first power, 1.1 squared and 1.1 cubed. And there you have it. So when you just add those together, of course we have financial calculator, so I will use that CF 300,000 negative enter. Down arrow cashflow at time one to 65 positive enter down arrow. How many times did that occur? Three times enter. That's it. I hit NPV 10, a 10% discount rate down arrow. I compute on NPV 359016, 359016. Okay. So my base case says, let's do this project. It's a project worth doing. We're going to make a bunch of money above and beyond our required rate of return. Anything positive is certainly worth strongly considering here for NPV. We should do it is the general belief in a finance class, right? Plus E plus EV plus NPV. EV is like gambling talk. Plus net present value is something you ought to do. Okay. Uh, let's look at the best and worst case. So it says, assume that the following variables are accurate to within 10%. Annual sales in units. So it means might be plus or minus 10%. So if this is let's do worst case first, worst case scenario for annual sales in units is down 10%, 10% below what we projected for price per unit. It could vary by up to 10%. So bad case there would be the price is 10% lower. In other words, we're not allowed, not allowed. We're not able to get customers to buy it, uh, at full price. We got a discounted by 10%. Uh, variable costs. What's the worst case scenario there? Well, it's 10% higher and fixed cost. The worst case scenario is 10% higher. Now this question is fairly simple. We could have worst case scenarios where not everything's 10%, right? Worst case scenarios are variable costs go up by 40% and our sales in units go down by 10%. You could project it any which way. Right. Uh, and we're doing it by hand one step at a time, but in a spreadsheet, you can imagine you could play with the scenarios very quickly, right? You can say, well, what about 15%? What if our sales are off 15%? What does that look like? Run the numbers. And it's like, you don't have to rewrite an entire income statement to do all this. You just change one cell of a spreadsheet and there it is. Um, okay. But we're doing it by hand. Cause often in finance tests, you're asked to do stuff by hand. And, uh, I'm a believer just to learn it better doing it by hand. Maybe I'm too old. Uh, you can tell me in the comments. Uh, okay. Uh, assume, uh, the variables are accurate. So annual sales, uh, could drop by 10%. So let's, let's look at best. No, this is not best case. We want to do worst case. Pardon me. Let me change it to worst. So worst case scenario, a project still costs 300,000, by the way, that's another variable we could change. We could make the, uh, project cost more or less, right? Projects overrunning costs all the time. Uh, annual sales in units. Well, uh, again, worst case scenario is 9,000, uh, sales price. Well, 10% down from that is $54. Variable costs rising by 10% is $22. Fixed costs rising by 10% is 88,000. Our project length is three years, 10%, uh, required rate of return, 25% taxes, even in the worst case. Okay. So then it's just like pretend the problem said this and rerun all the numbers. So that's what we're going to do. Uh, our annual sales in units, 9,000 times 54. So we're selling fewer units and we're able to, uh, charge less for what we sell $486,000. Our variable costs are 22, but we're selling fewer units. So 22 times 9,198. That's a place people go wrong. Uh, they go 22 times 10,000. They screw that up. Uh, our fixed costs $88,000 a year. So 46 minus one 98 minus 88 or 86 minus one nine eight minus 88, 200,000. Even that's a amazing, nice, even number. Our depreciation is a hundred and therefore our EBIT is a hundred taxes on a hundred thousand or 30,000. It's got me second guessing things. The numbers are going so smoothly. Oh, uh, 25%. I said 30%, uh, 25,000. A hundred minus 25,000 is 75,000. Okay. So there's our worst case. Uh, EBIT in the worst case is a hundred grand depreciation, a hundred grand, a hundred plus a hundred is 200 minus 25,000 in taxes is 175. So what do our cash flows look like here? Minus 300. Oops. Minus 300 plus one 75 plus one 75 plus one 75. So our cash flows are worse in this scenario. They don't look catastrophic though. Uh, let's crunch the numbers. So CF clear what's in there. CF at time zero is 300 negative enter time one. One 75 positive enter, and there's three of them. So enter three is my frequency. I'm going to, uh, NPV. My, uh, I hear my discount rate is 10 and let's compute NPV. I get one 35, one 99, one, one 35, one 99. So two things popping into my head here. First, if this is as bad as it gets, this is a great project. We ought to be doing right. Like worst case scenario is positive. The second thing though, is, is if everything looks rosy, I don't think, Oh, what a great project. What a great project projector. I am, I think I must've missed something. This is too good. Like this is some wrong with this. So that's just something to be aware of. It's like, just because the numbers are great, doesn't mean the situation is as great as you're projecting. You maybe are missing something. It just seems too easy. And this is borderline seeming too easy. Uh, I would stop here, honestly, if this were my project and I'd be trying to reevaluate some of my judgments. I wouldn't do a best case very often because who cares? Like, I don't know. I don't make project decisions based on everything going perfectly. I suppose you could be saying, well, this is the maximum upside of this project, but I'm a downside guy. Uh, I mentioned this in another video, but I, I, when, when I walk out of a test, the final exam, I'm always the guy saying, Oh, I failed it. Oh, what a disaster. Oh no. Woe is me. And so when I'm projecting some project, I'm working on big financial stakes, I'm always like considering the worst case. I'm not considering what happens if it's an absolute home run, but best case scenario, it's an absolute home run. What does that look like? Okay. Well, our cost for the project is the same. We sell 10% more. We sell 11,000 units, not 10. We are able to charge more $66. It costs us 10% less variable costs. 10% less is 18. The fixed costs are 10% lower. They're 72,000 still three years, still 10%, still 25%. So some of those went up, some of them went down, right? Costs go down. Revenues and sales go up. Um, but there you go. We just have to do one more projection. Let's do it. And, uh, we'll wrap up. So 11,000 times $66, 726,000 in revenue. Variable costs, 11,000 units times 18, 198,000, uh, fixed costs, 72,000, 726 minus one 98 minus 72, 456. Our depreciation is a hundred thou. So that brings us down to three 56 and Abbott. And you can see significantly higher than the other two. And because of that, our taxes are higher. Three 56 times 25%. It's 89,000, three 56 minus 89,000 to 67. All right. So obviously way more profitable, obviously going to have a very high net present value. This is sort of the max upside scenario. Our Abbott here, three 56, add depreciation of a hundred deduct taxes, which were 89,000, three 67. Okay. So what are our cash flows? Minus 300, uh, plus three 60 and plays back within the first year. This is like the no brainer decision of all time. If you could get these numbers and let's figure out where our net present value is after all of that. So cash flows, second function, clear 300,000 negative enter down arrow, uh, three 67 positive enter down arrow. And there's going to be three of those enter. Uh, and now we go MPV. So, uh, the required rate of return is 10 down arrow. And I compute MPV. I get six, one, two, six, seven, five. And, uh, this seems like a no brainer project. We should be doing it right. Like our worst case is making us money. Uh, our best case and base case are expected to be very high, positive net present value. So this is a project worth doing. If I got numbers like this, it would have me questioning the numbers because they're just a little too rosy. I'd be like, what am I missing? So anyway, hope this video helped. Hopefully you're not missing anything and hopefully you're not missing that like button on your way out of here. Hit me with a thumbs up and I'll see you in the next video. Bye bye.