Speaker 1: The first thing we're going to do in this model is we're going to set it up so it can handle multiple scenarios. As you'll notice right now, and this may be the case when you inherit a model from someone, it's only set up to run off one set of assumptions. So you'll notice there's only one case for revenue growth. There's only one case for depreciation. And what we want to do is allow for other alternatives. And since each scenario is going to be identical in structure and layout, what we can actually do is copy the current scenario that's in place and paste it. And then I'm going to copy it one more time and paste it in yet again. So what you'll notice now is very quickly what I've done here is I've expanded it so that there's the original scenario that the model's running off right here. And then I've created a place where we'll have two other scenarios. And we can actually go ahead now and give these names. So we can call this Upside Case. And we can call this Downside Case. And then what we'll have to do is label this last one Live Case. So that tells the user that whatever is in this area here is the live case running through the model. And then these other two up here will be static assumptions. But we want to create this as a dynamic model. We don't want to just have to copy this information here and paste it down here whenever we want to change the scenario. That could result in errors, it's time consuming, and it's not a good structure for the model. So what we're going to do is set this up to be dynamic. And I'm going to type here the phrase Live Case. And next to it I'm going to put a switch. We've created a switch here where you can type 1 or 2 or whatever you like in it. And we're just going to format it a bit so it really stands out. So I'm going to give it a bit of a blue shading. And I'm also going to change the font color. I'm going to change the font color to a hard-coded blue number. So now this stands out as being the live case. And we've got two cases here, 1 or 2. To make it very clear to the user what we can actually do is go into this name here and put Scenario 1 so that we know what's contained in cell J4 corresponds to the scenario name and then Scenario 2. So our upside case is Scenario 1, our downside case is Scenario 2. Now we've got the basic structure in place. Let's go to the Additional Assumptions section. And let's copy the information from Scenario 1 and paste it in the upside case here. These numbers are actually the same, but this is just a helpful exercise to see what it would look like if you were setting up new assumptions. But then Scenario 2 does have different numbers. So we're going to copy the assumptions from Scenario 2 and paste them where they belong in Scenario 2. So now you'll see we have two options that can run through this model. And you'll notice that in setting this up I was very deliberate in leaving the initial assumptions in their original place. And the reason I did that is that all the formulas in the model are already linking to these assumption cells up here. So I don't want to move or change that setup. And what I can do now is, believe it or not, I can actually just take this whole section and delete it. And then I'm simply going to use a Choose function. And a Choose function, as we've covered in our Excel crash course, allows you to pick between multiple scenarios. So I'm going to say Choose. And the first thing that Excel wants me to do is to tell it where to choose from, or what determines the choice, rather. So cell J4 tells us what to choose from. So I need to lock that in place with a 4. And then we need to tell Excel what the options are. So it's saying, take the first option, currently we're telling it to take the first option of option A and option B. And then I'm going to close the bracket. And I'm going to change this to be a black font color. Because it is no longer a hard code, it is now a formula. So it's saying to take scenario 1 of the two options. Let's see what happens if we type a 2 in here. Let's make sure this is linking properly. If I type a 2, now we're getting 5% there, because that's scenario 2. And that's how the Choose function works in Excel. It picks from the choices that you give it. So I'm going to set this back to option 1, since that's what the initial numbers were. Now what I can do is, I can actually do this very quickly. I can copy what's here, and I can actually paste it across the entire area by saying Alt-E-S, which is for paste special, F for formulas. And what I've done now is I've preserved the original formatting that was there, and I've only copied across the formulas. Now all I have to do is simply set it to a black font color. And let's check that this is working properly. You'll notice that none of the original numbers have changed, and it's still referring to the live case. It just so happens now that the live case is a series of formulas instead of assumptions. And that is all controlled by this switch up here, where we can easily toggle between scenario 2 and scenario 1, and those numbers automatically flow through the model. It's literally that easy to set up scenario analysis if you get the structure correct. Now that we've got the scenarios properly set up, let's analyze some of the results. But first, let's just double check that everything's working properly. One of the first things that we can do is use, under the formulas tab, the trace precedence and trace dependence function to make sure everything's working. So I want to trace the precedence of the live case. And you'll notice that if I trace precedence, it takes me into three cells. One is this scenario, the second scenario, the other is the first scenario, and then the last thing is the case that's driving it. So that's excellent. Let's check another one. Let's check the precedence of this cell. Again, it's referring to the driver of the case, and it's linking to scenario 1 and scenario 2. That's perfect. Let's check a bit further down. Let's check capital expenditures. Trace precedence. Same thing. I see CapEx and scenario 2, I see CapEx and scenario 1, and I see that they're both driven by this cell here. Let's remove those arrows now. Another thing that we can do is watch how this flows through the model by tracing the dependence this time. So it goes directly to revenue. That's great. Let's do that again. And what I can do now is continue pressing trace dependence, and I'm going to keep clicking it until I can't go any further. At this point, I can see everything that's dependent on that initial assumption, and it should ultimately arrive at the intrinsic value per share, the equity value of this business. So if I continue scrolling down, I see that these arrows take me all the way to the equity value per share, which is where they stop. That's perfect. That means all of the assumptions and formulas are working properly in this model and flowing through the way they should. So at this point, you can scroll back up to our live scenario, remove the arrows, and know that this model is working properly. Now that we know that it's working properly, let's look at some of the actual numbers. One way to look at some of the numbers is to take revenue, for example, copy the numbers that are currently running in scenario one, paste them as values, alt esv, right next to the live scenario. And what this allows us to do is go in this cell here and press scenario two, and we can directly compare scenario one revenue in the year 2020 with scenario two revenue in the year 2020 to see the results. If I go back to scenario one, you'll see there's no difference. In scenario two, I see that flow through. So that would be one example of a quick spot check to see the impact. But ultimately, what we're interested in here with this investment is the intrinsic value per share. And right now, in scenario one, it's $34.85. So what I'm now going to do is press two and see that the price drops from scenario one to scenario two. So what I could do is I could actually note here alternative scenario. And if I copy this number here and I paste it as values, and I add two decimal points to it, I'll have the same number that I have in the live case. But now I can change it back to scenario one. And what I've done here is I've preserved the other case, and I can have an easy comparison. Because it's a hard-coded number, I need to format it as blue. And now what happens is if I change to scenario two, of course, I get the number that's in the alternative scenario. If I go back to scenario one, I get 34, and I can easily compare the difference between the two scenarios. The key will be to remember that this is a hard-coded cell, which is why it's blue. And therefore, it does need to be updated if any changes are made to the scenario two assumptions. But as you can see, this is a very powerful way of doing analysis on a company and a great way of assessing the impact of two different scenarios for the business.