Speaker 1: Scenario and Sensitivity Analysis Model Let's look at the components of the Scenario and Sensitivity Model. It has everything in the 3-statement model, plus everything in the DCF model, and then on top of that it's got additional sets of assumptions so that we can run multiple scenarios through the model. The scenarios will be selected using a choose formula. We'll also layer onto that a direct and indirect method of performing sensitivity analysis. We can then use data tables and Goal Seek to see how the model performs under different scenarios and different assumptions. Let's look at the purpose of this type of model. It helps evaluate multiple economic and operational scenarios. It lets us assess the upside and downside, or risk and reward, of different strategic alternatives that a company may have. It helps us measure how sensitive a model is as inputs change, how much do outputs, like free cash flow or share price, move around as the drivers of the model change. And finally, instead of arriving at just one value for what we think the company is worth, we can arrive at a reasonable range of values that we think are likely for the company to be between. If you want to learn how to build this model from scratch, you can take the Scenario and Sensitivity Analysis course. Here we are inside the Scenario and Sensitivity Analysis model. We have a cover page, as always, describing what the model contains, and on the table of contents we can click through to the actual model itself. We've got all the sections, as we always do in a model, starting with assumptions, then the three financial statements, supporting schedules and additional calculations, then the DCF model, and finally the sensitivity analysis. Let's open up all the sections here. One thing you'll notice right away is that unlike the basic models that only have one set of assumptions, this one has two distinct scenarios. And we can switch between the scenarios by using a toggle here to type in either 1 or 2. The number that we enter there flows down into the live case. Let's switch this back to Scenario 1. For sensitivity analysis purposes, Scenario 1 only uses a single revenue growth rate assumption, whereas Scenario 2 has different revenue growth rates in different years. We'll explain why that matters later. But for now, we're leaving it on Scenario 1, which is flowing through the model. Beneath that, we get the three financial statements, income statement, balance sheet and cash flow. We get the supporting schedules, and then we can have our DCF analysis. These assumptions here drive the DCF model, and we arrive at the company's intrinsic value per share, which we can compare to the market value per share, and thus calculate the internal rate of return of buying the stock at its current market price. Below that, we have the sensitivity analysis section. We can see what happens to the share price of the company as we have different revenue growth rate assumptions and different terminal value assumptions for the EV to EBITDA exit multiple. So this is the range of share prices from lowest in the top left corner to highest in the bottom right corner, and you can see that it's quite a wide range. Stocks are very sensitive to growth rates and to valuation multiples. Then below that, we have some calculations here that drive the tornado chart below. The tornado chart is showing us visually how sensitive the share price is to changes in underlying assumptions. So we can see that revenue growth really moves the share price, as well as cost of goods sold, EBITDA exit multiple, and the discount rate. So we're going to show you in the following lesson some examples of how all of this flows through. If you want to learn how to perform this analysis step by step, every calculation, then you can take the Scenario and Sensitivity Analysis course. Let's take a closer look at how this model functions. The scenario is chosen using a choose function that links to a switch. In the switch, we can type a number 1 or 2, and based on the number that's in that cell, it will then pick option number 1 or option number 2, and place it in the live scenario. So we've structured the model to have this live scenario category that mirrors the way the scenarios are set up. All that happens here is you're bringing forward whatever scenario you want to run through the model. Then, all of the statements are linked to the live scenario, and nothing is directly linked to each of these individual scenarios. This is by far the easiest and most effective way to set up scenarios. Scrolling down to the Sensitivity Analysis, you'll see that we have two different kinds. The first kind here is what we call Direct Sensitivity Analysis. The way Direct Sensitivity Analysis works is it takes the numbers that we have in this table here, such as these different revenue growth rates and these different exit multiple assumptions, and it plugs these assumptions directly into the model, hence the term Direct Sensitivity Analysis. It directly takes whatever revenue growth rate assumption it is, and it plugs it into this cell right here. That's how these data tables are working. Below that, we have what we call the Indirect Method, which is these numbers here. The way that these Indirect Methods work is we'll show you using formulas and trace dependents. What happens is that number that's in that cell gets pushed up to the live revenue number. You can see that the reference is actually contained in the formula here. We're saying that the revenue growth is going to be equal to the prior year multiplied by the revenue growth rate in the scenario plus some other variable. It's sort of indirectly impacting the revenue growth. It's set to zero initially because we don't want it to affect the live case in the model. But what happens is Excel then takes this number, and this number, and this number, and plugs them in as discrete scenarios. We can see what happens to the share price. As you'd expect, of course when it's at zero, there's no impact. When the revenue growth is 5% less than the live scenario, the share price goes down, and when it's higher, the share price goes up. The same thing happens with cost of goods sold, the discount rate that's used for calculating share price, and the exit multiple assumption. Those then flow down to populate this tornado chart. As you can see, this is a very powerful setup for performing extensive sensitivity and scenario analysis in a DCF model. Please refer to the full sensitivity analysis course to learn how to build it step-by-step.