Welcome back to corporate finance. Last time we applied our different decision criteria to our forecasted free cash flows to come up with a decision as to what to do with our tablet project. This time I want to push those assumptions around. Forecast drivers and other assumptions. See how robust our decision is in a process called sensitivity analysis. Let's get started. Everyone, welcome back to corporate finance. Today we're going to be talking about decision criteria. But as always, let's start with a recap of our last lecture. So last time we talked about decision criteria. In particular, we asked, what do we do with our free cash flow. And there were several things we could do. We could compute an NPV, we could compute an IRR, or we could compute a payback period. And we talked about how to implement those decision criteria and what decision to make, and we also alluded to some of the shortcomings of some of those decision criteria. Today I want to turn to sensitivity analysis. In other words, having set up our DCF and having completed all the inputs, now, let's push it around a little bit and just see how robust and sensitive our valuation is. So we can make the most informed decision possible. So let's get started. And we're going to start with something called break even analysis. Break even analysis finds the parameter value that sets the NPV of the project equal to zero holding fixed all other parameters. So let's look at our cost of capital and initial investment forecast drivers. The base, and I've got base up here to represent the base case. We're going to look at alternative cases in just a minute. You can see our base case assumption for the cost of capital was 12, just a little over 12%. And our initial investment was $227.7 million. The corresponding NPVs for those assumptions were 708.42. Right, it's the same as the base case. That's what we computed in our previous lectures. Okay, the Break Even values, in other words the Value for the cost of capital, such that the NPV, right here, is zero, is 43.72. But that should look familiar because the Break Even point for the cost of capital is nothing more than than the IRR. And as we discussed in the last lecture, on decision criteria, you can see there's a lot of room here before this project looks bad, at least on the cost of capital dimension. For the initial investment, our initial investment would have to be closer to $1 billion before this project started to turn negative. Now how did I get these? Well, I just used Goal Seek. What I did is I set the NPV cell, for example this cell, to zero and I would change the input parameter. And Goal Seek goes around by iterating it to find the zero value. Now I did this for all of our inputs and I can only show you a subset otherwise things get ridiculously small and doesn't fit well on the screen. But what I like to do when looking at the break even [COUGH] estimates is gauge how much room I have at least in a partial equilibrium notion. Because remember I'm only changing one parameter at a time and I'm going to emphasize that again in a second. I'm going to look at how much breathing room I have until the project turns south or becomes negative MPV. And so you can see these purple arrows identify parameters for which there seems to be a fair amount of scope for error. A large margin for error. Take a look at the PPE liquidation value here. I'm assuming I'm getting $0.50 on the dollar when I liquidate all of my plant and equipment at the end of the five years. It would take a loss of 2253% before this thing turned negative MPV. I'd have to be dealing with some kind of, maybe, nuclear waste, right, something like that. But that's not the case here. Similarly, you know, the initial unit price, unless we're going to price this thing at $77, just over $77 per unit. This project is positive NPV. This is in contrast to some of the parameters for which there doesn't seem to be quite as much a margin for error, right? So if I look at my initial market size, I'm looking to the initial market size of a million units. But if there's not a lot of enthusiasm, say a little over half a million, this project becomes NPV negative. Again, holding all other parameters constant. So, this break even analysis I think is a useful tool for gauging how much room we have one dimension at a time before the project becomes value destructive. And I want to emphasize that this is partial equilibrium analysis. It assumes the parameters are independent, right? I'm changing one parameter at a time and in some cases that's an unreasonable assumption. It's not to say the break even analysis is uninformative, it's just that we have to recognize the limitation of that analysis. Now let's turn to some comparative statics. Comparative statics are going to quantify the sensitivity of the valuation to variation in a parameter holding fixed all other parameters. So, what I'm going to do is I'm going to look at how does valuation change parameter variation from what I'll call a worst case to a best case scenario for each parameter. And again I'm going to focus on the cost of capital and the initial investment. And by my estimates and discussion with treasury, right? The sort of range of estimates for my cost of capital vary from a low of 9.61 which is a best case scenario, right, very inexpensive cost of capital, to a higher 15.01%. Likewise, my ops people tell me that this the initial investment, is likely to be $227 million, but it could go as high as 284, it could go as low as 185, depending upon short term variation in materials and labor, etc. What I see underneath each parameter estimate is the corresponding NPV. So when I change the cost to capital 15.01%, the NPV of the project is $594 million. When I Increase the cost of the initial investment from 227 to 284, the NPV moves from 708, our base case, to 649.47. So I actually like to look at this table, one, as, to get some sense of the sensitivity of the valuation's specific, to changes in specific parameters. But I also like to use it as a gut check, a reality check to make sure that the valuation varies sensibly with variation in the parameters. If I had found that as I increase the cost of capitol that the valuation here, the numbers actually went up. I've got an error somewhere in my model. So how did I get this? I just used an Excel data table. So these are inputs right here so all you do is highlight the matrix, the NPV of the project is here, in the bottom left. The parameters are in the top row. And you choose a row input cell and enter the parameter cell. That's it. Nothing more than a data table. And I did this for all the parameters, though I don't show you all the parameters here. And it's just useful to move through them to make sure things make sense. So, if I look at Initial Market Share, for example, right here. What I see is a worst case scenario of 15%, or our penetrations only 15% versus an initial penetration of 35, sorry, an initial penetration of 35 in a best case scenario. And you can see that the valuations increase with our penetration, which makes sense. We're selling more units. And notice they actually increase by a lot. We're going to see that that's an important value driver. So, what is the elasticity of the valuation with respect to each parameter? So, it's similar to what we did, but it puts a little bit more structure on the changes in the parameter, so we can compare the sensitivity of the valuation to different parameters a little bit more sensibly. So what is the elasticity? Well, it's just defined as the percentage change in the NPV divided by the percentage change in the Parameter, or Delta. Remember, this is nothing more than change. The change in the MPV divided by the change in the parameter times the ratio of the parameter to the MPV. Let's look at an example. Here's my cost of capital. What I've done is I've gone computed a cost of capital that is 1% higher and 1% lower. And I've computer the corresponding NPVs. So, to estimate the elasticity of the NPV with respect to the cost of capital, I simply looked at the delta NPV. Sorry, hello. Get rid of that guy. Delta NPV over delta parameter, delta in the cost of capital. So I've taken this guy, 703.53 minus the 713.34, and I've divided that by the difference by 12.13 minus 11.89. Then I multiply that times the average of the two numbers. So this is really just the average of the parameter values. This is the average of the NPVs, and obviously these halves cancel one another. But I wanted to put them there to emphasize that I'm just taking the average of these two points. And the reason I'm doing this is because the elasticity will vary depending upon the direction in which we calculated. Not by much, but by a little, so this is one possibility or one approach for computing it. And the elasticity I get is -0.69. All right, so a 1% increase in the cost of capital. A 1% relative increase, I should add, in the cost of capital is associated with a 0.69% decrease in the NPV of the project. And I can do that for all of the parameters. Again I'm just showing you a few, but you can see a couple parameters start popping out very quickly, such as the initial market size, our assumption on the market growth rate, right? Our initial market share. Our assumption on cost of goods sold, right? These, again, are going to correspond pretty closely to what we saw with our break even analysis, and they're really identifying that the key value drivers, the things we should really be focused on when discussing this project. But, again, like our previous analysis break even and our little scenario analyses. Well, not scenario. Sorry, our comparative statics. Comparative statics implicitly assume parameters are independent of one another. We're varying one parameter at a time, and sometimes that just doesn't make sense. So that's going to lead us to scenario analysis. What scenario analysis is going to do, it's going to quantify the sensitivity of the valuation to variation along multiple dimensions. We can can vary multiple parameters. So what I've done here, going back to that best and worst case scenario, is I said, what's the worst cast scenario for all of the parameters here? And the best case scenario for all the parameters. Now, that's not necessarily the optimal thing to do, but it's illustrative. You might want to say, what's sort of the worst case scenario if the economy goes south, right? How is that going to effect each parameter? It may not be the worst case for each parameter individually, but, jointly, it might correspond to some sort of bad economic state of the world. Having said that, I've computed what, I've set up each of these scenarios, worst, base, and best, and I've just done a little scenario analysis. And what I find is that, in the worst case scenario, this project is over half a billion dollars negative NPV. The best case scenario is $33 billion positive NPV and relative to our base scenario. Now, I wouldn't read too much into those numbers, again, because I've taken the worst case scenario for each parameter independently, and that doesn't make a lot of sense. Again, what you want to do when you're constructing these best and worst case scenarios is think, okay. Let's think about a bad economic state of the world where maybe not everything goes horrible in the project, but where some things go bad. And maybe other things are good. So, if it's a bad state of the world, maybe labor costs go down, right? And it becomes cheaper to build the plant. You want to think about these things and how parameters are related and what really represents a plausible best and worst case scenario. Now, how did I do this? I just used scenarios in Excel. That's one way to do it. That's how I initially did it, but that's actually really inefficient. A much better way is to set up a lookup table and a toggle that you can just switch all the parameters between. Much more efficient. So, here's a question. What else can we do with sensitivity analyses? We can answer some important questions that are going to come up in discussions. Here's one for example. Imagine strategy wants to reduce the price by $30. In order to increase the initial market penetration from 25% to 30% by their estimates. Does this make sense? Well, we can answer that question. In this table, what I've done is I've set up a little two way table. I've got quantity, our initial market share, right here, our initial penetration, and price here. So these are all prices. Here are all my market shares. Here's our base case scenario in which we're assuming 25% initial market share at a unit price of $200. And marketing's asking what's going to happen if we change these. Well, in particular they want to lower the price $30 from 200 to $170, and they argue that's going to increase our market share upwards of, say, 30%. Well, what does that mean from a valuation perspective? Well, if we look in this box right here, we see that the NPV is going to be somewhere between $776 million and $851 million. So what's the answer to the original question? It's a good thing, right? NPV is going to go up relative to our base. Here was our base. Marketing says drop the price. That increases penetration, and sure enough, valuations go up. So it sounds like a sensible thing to do. Marketing, on the other hand, is concerned about uncertainty surrounding the market for tablets, and wants to understand if we can shed any light on that. Can we provide some information? So here's another two way table. I've got market growth rates here. And market growth rates there. I've got market size assumptions here, initial market size. Right? Here's our base case indicated by the black outlined boxes. So the initial market size was going to be a million units. And we were assuming 2500% growth in that next year, but what this picture shows is it shows that as that growth varies, and as that market size vary, what happens to the valuation? The cells inside are NPVs, and I've color coded them so that green is at or better than our baseline, right. You can see our baseline is all along, I've held the baseline constant along the diagonal here, all 70842. Yellow is positive NPV, but worse or below the base case NPV, and red is negative NPV. And so, this provides some picture of how much slack we have, at least, along the initial market size and market growth rate assumptions. Finally, I want to talk about simulation analysis. Simulation Analysis, we're going to perform the valuation for a large number of scenarios. A whole bunch of different parameter values. What I did is I selected 500 different scenarios. What I did is I randomly drew parameter values from a distribution constrained by the best and worst case scenario for each parameter, and I did that 500 times. And I computed the NPV for all 500 draws, and I plotted them here in a histogram. So, I have here the NPV along the horizontal axis, and the y-axis is the fraction of the 500 simulated scenarios corresponding to each NPV. And this red-dashed line is the zero NPV value. And what you can see up here is 77.78% of the simulations are positive NPV, 22.2% are negative NPV. So the takeaway is, it looks like a pretty good bet. This project looks like a pretty good bet. Conditional on all of the assumptions that we've made to get there. We've been able to push it around through a host of different scenarios and this thing tends to be a positive MPV project value accretive. That said, I want to emphasize that the parameters were drawn independently of one another, and that's not ideal. It's going to lead to some implausible outcomes. And it's not a particularly reasonable assumption especially when it comes to certain parameter pairs, such as price and quantity. That's just one. Ideally you want to draw from some large, multivariate distribution, but now we're moving way outside the scope of this course into evaluation exercise. What I want to do is instead emphasize the importance of simulation analysis. Even small scale simulation analysis, say you just laid out by hand 10, 15 different scenarios. Computing power's cheap, compute the MPV and just look at how those MPV's look across the different scenarios. All right. So, let's summarize this and bring it all back together. I really want to emphasis that no DCF is complete without a sensitivity analysis. It's really an integral component of any evaluation, any sort of capital budgeting or broad evaluation I should say. It's going to help us identify where value is created or destroyed. It's going to identify the key value drivers, where we should really focus our time and effort In terms of our discussions for making the decision. It's also going to help us quantify and assess our risk exposure. How much can we lose? How frequently can we lose it? And it's going to help us understand the robustness of the profitability of the project. So, next time we're going to turn to a new topic, return on investment, which is actually closely related to our decision criteria. We're really going to hone in on IRR and look at some of its strengths and weaknesses. So, thanks again for listening, and I look forward to seeing you in the next lecture.
