Welcome back to our course, decision making and scenarios. I'm still Professor Rick Lambert of the Wharton School. And we're still continuing module 4, where we're talking about a new product venture. In this lecture we're going to be focusing on formulation and evaluation of alternative scenarios. This is in some sense the hardest of all of the tasks in our outline of things to do. So we had started with the intro and the spreadsheet setup. We had forecasted out future cash flows. We did the valuation. Now it's time to rethink all of that. Project valuation and evaluation is very much an iterative process. You're going to go back and forth between several of these steps. We try to translate our project idea into the future economic actions, transactions and events needed to carry out the project. We used our best estimate of the outcomes, we did that to map into future forecasted financial statements, future forecasted cash flows and calculated the NPV. Now we need to rethink all of that. Consider alternative courses of action, alternative scenarios. Reality checks, did these statements really make sense? Is our forecasted sales price realistic? Would people actually pay that much? Why would they pay that much? Can we reasonably expect to sell that many? Even if we could, can we produce that many? What are our constraints in terms of productive capacity? The balances of the receivables, and the inventory, the liabilities. Do those seem plausible as well? We want to check all of those things. So let's start with sales. Remember, this is the source of all of our inflows. So if there's no sales, there's no profits. So remember we assume the following in our spreadsheet with respect to sales. Sales are going to start in period 3. They're going to be 2,000 units year, there's no growth based on that 2,000 unit starting point, the sales price is $100. It stays at $100. It doesn't go up over time, say with inflation or anything like that, and the margin on our product is 55%. These assumptions, at this point, partly reflect our best estimate as to what we think will happen, but also involves some simplifications we probably made just to make it easier to check that our spreadsheet was actually working right. So now let's go into alternative scenarios, what if analysis? Our calculations assumed that volume will be 2000 units a year but what if sales volume is different? How bigger are the profits in present value terms at different sales levels? We can go back to the spreadsheet and change that INITIAL SALES VOLUME cell. It's 2000 now, change it to something else, 2400 say. If you setup the spreadsheet correctly, all the cells will automatically update. So here's some examples of re-calculation of the net present value if the sales volume is 20% higher or 20% lower than our baseline forecasted case. It's always a good idea to do a more optimistic case and a more pessimistic case. So if things turn out better than expected, our NPV is much higher. It goes up to 61,961 and the IRR accordingly higher as well, so we would be perfectly happy if that's the way things turned out. But if the demand is lower, only 20% lower, 1,600 units a year, we lose money. Okay, this is an important thing to note. So, let's go into that in a little bit more detail now. What sales volume still allows us to make money? That is, what is the break even point for sales? Breakeven means earns a Net Present Value of Zero. Equivalently, it means that the Venture Earns an IRR of 6%. Now, we could try to figure this out by trial and error, by putting in different volumes into the spreadsheet and see where we end up with an NPV of zero. We know from the prior slide that it's going to be somewhere around 1600 units, probably a little bit bigger than that. But we can actually use a built in function in Excel called Goalseek to help us figure that out. So Goalseek is an Excel function, and we've set up our spreadsheet in a way that we can take advantage of that. We've got a cell in the spreadsheet that contains the sales volume per year. We could put anything we want in that cell right now, because Goalseek is actually going to change that for us. And then there's another cell in the spreadsheet that is our final answer, the NPV calculation. Go into your spreadsheet. Under the data tab choose what if analysis and Goalseek. And then all we have to do is say we want to choose to set the cell with the NPV formula in it equal to 0 because we want the NPV to equal 0. And how do we get it to zero, by changing the cell with the volume in it. So this asks Excel to find the sales volume per year that results in an overall NPV of zero when discounted back at 6%. Equivalently, it's the sales volume such that the project earns a 6% rate of return after tax. So, if we have Goalseek do that, here we've got the original forecast of 2000, and Goalseek tell us that if the volume falls to 1698, that's where we break even. Any volume less than that and we lose money. Now, this is an important calculation. So in our case we have a margin of safety of approximately 300 units of sales per year, so sales can drop that many units below our forecast and we would still make money. Any further drop though we would start to lose money. But you'll find in many cases, the breakeven point is actually well above your forecasted sales volume. Or said another way, at your forecasted sales volume, you're going to lose money. This means this is not a good idea and you need to think of ways that we can do it better so that we actually do make money at the forecasted sales volume in order to be able to go ahead. Another concern. What are the costs of providing the product or the service? Our calculations assume that we've got a product gross margin of 55%. This means that if our sales price is 100, it'll cost us 45 to make each unit. Leaving a margin of 55 to cover the rest of our costs. But how much margin of safety do we have there? Again we can go in and change that cell, have everything recalculate. So here are some scenarios where we are going to recalculate the NPV, changing what the profit margin is. If it's 65%, better than 55%, the NVP goes up, of course, but this tells you how much it goes up. So that's an important thing to know. If the margin goes down, in this case to 45%, that's enough to make the NVP go negative. So we're losing money. A lot of money if the margin is only 45%. Again, we could use Goalseek to fine tune that to see exactly what it would need to be for us to break even, and then we need to think about what can we do to achieve that margin? Are there other ways to produce or deliver the product, or provide the service that will cost less and provide more profit? How much will sales grow is probably one of the most important considerations in new ventures. Sales often grow, especially in the initial part of the operating phase. In our spreadsheet so far, we've seen zero growth, sales are flat over the times, so let's change that. Let's assume that sales grow at a constant rate, g over time, and change what that growth rate is. So here's an example where we grow sales at 25% per year during the operating phase. Here's our new income statement. You can see the increase in sales. Okay, compounding over time, we're all the way up to 488,000 by the end of year 7, compared to 200,000 as we started in the first year of operations. So, sales are going up dramatically. And interestingly, in the bottom line income goes up even faster. This is because not all of the costs go up, proportionally with sales. Some of the costs are fixed. Here's our cash flow statement. One of the things we see with growth is we need more Working Capital. So unlike the Constant Sales Case, where we had an influx of Working Capital and investment of Working Capital. In period 3, but no further investment. Here we have continued further investment in Working Capital. This is because the scale of operations is getting bigger and bigger and bigger. We've got more receivables this year than we had last year, because we had more sales this year than we had last year. The same with inventory. The same with payables, etc,. So we need to factor this into our spreadsheet, which fortunately we've set it up in a way that does incorporate this. Now let's do the NPVs as a function of sales growth. If we can grow at 25% per year, the NPV goes up dramatically. So this has a huge impact. So this is why we want to pay so much attention to what is the potential growth in our sales. Note though that if sales volume goes up, it goes from 2000 to 4883, we might also want to ask the question, do we have enough productive capacity to handle 4883? If we had set this up to be able to handle a production volume of 2,000 units, can we handle an increase all the way up to 4,883? If not, we're going to need to add productive capacity. Well, when in this process will we do that? How much productive capacity do we need to add? How much will that cost? Will it be worth it? Inflation is another common mistake that people make in doing these calculations. That is, they use the same initial period price and assume that it's going to persist for all future periods. But prices often change overtime, especially in periods of inflation, they go up. So we can incorporate this by building anticipated inflation into the future prices. But be careful, not all prices go up at the same rate. Our tax shield from depreciation is a good example of that. Depreciation is based on the original purchase price of the asset, it doesn't go up systematically with inflation. So we want to be careful about what things we move up and what things we don't. In fact in some sectors of the economy, often in the high tech industry, prices go down over time, not up. So whatever you think the appropriate trend in prices is, you should try to reflect into your spreadsheet. So our spreadsheet allows for inflation, and we're going to assume that it applies just to the sales and the cost of good sold parts of the financial structure. So here's some recalculations of the NPV with different anticipated inflation rates. With the 10% inflation, the NPV goes way up 50% even further, so that's fabulous, that sounds really good. But, if the inflation rate is really going to be that high, shouldn't the discount rate that we used in the NBV calculation be higher than 6%? Remember that the discount rate is also supposed to reflect inflation, so we might need to change that as well. It's a good idea to keep track of whether or not sales revenue is going up because sales price is going up, perhaps due to inflation, or because sales volume is going up. As we saw in the prior example, if that's what is making the sales revenue go up we might also need to think about productive capacity. Other scenarios to explore, we could look at the discount rate. Okay, the discount rate should be higher when expectations about inflation go up. It should also be higher if this is a riskier project. Either way higher discount rates result in lower present values. Other scenarios that we could explore, time related factors. Our credit policy. Suppose we allow people to pay later. Well, collecting later is going to be bad from a present value perspective so that it doesn't sound like a good idea. But maybe allowing people to pay later will allow us to sell to more customers. So now we can put both of those things into our spreadsheet and try to figure out, will the sales increase enough to offset the cost of later collections? We also might want to factor in, if we have more credit sales are we going to run into more customer defaults. We could look at different patterns of growth and decline in sales instead of constant growth. We could look at how long does the operating phase last. This might be a function of how quickly competitors can come in and take away our sales. And we can look at time to market. In our scenario we get in and we start making sales in period 3. But the quicker we can get to market, the higher the present values are going to be. So, quick time to market is something that people pay a lot of attention to as well. Interactions with other products and activities if this is part of a bigger firm. Do the revenues for this new venture eat in to the revenues of some of the other things we do? Or do they actually make our other products more attractive to customers because they like the bundle that we offer now. Does this new product venture cannibalize scarce resources that other products use? Do we learn things in doing this product venture that we can apply to other products? These are tougher things to try to estimate but are important nevertheless. So a tentative summary at this point. Our base line case, no growth, does have a positive NPV which suggests that this is a profitable product venture. But it doesn't take much to go wrong to change that. If sales are expected to grow, things look a lot better. There's many things that are difficulty to quantify, and this where the art mixes in with the science in business strategy discussions. Now, we have one thing more that we want to take a look at, and that's what happens at the end of the project. Or whether in fact, this is the end. So we're going to take a look at that next.