[MUSIC] In this exercise, we will go through how to use Scenario Manager to try out different values in your variables and compare results. There are many ways to accomplish this task, such as making copies of the same workbook and changing the input values in different versions of the same workbook. However, the simplest way to do this is to use another tool in the What-If Analysis called Scenario Manager. Scenario Manager sets the parameters we want to a certain value, calculate the results, then instantly recalls and replaces the input value with different sets of numbers based on the scenarios you have set up. For our problem, the business development team has asked us to explore the potential profitability and growth of a new flight route under different market conditions. Here is a simplified version of a income statement. In orange are historical numbers for the Ticket Revenue, Fee Revenue, as well as Cost of Sales and SG&A Expenses from the past three years. A simple calculation will tell us that the company has achieved steady growth and operating income on this new road expansion from 2014 to 2016. In row 66 to row 68, the starting sheet has already calculated the Annual Growth Rate, the Growth Margin, as well as the SG&A Expense as a percentage of ticket revenue. On the right side of the table, we have projected revenue, costs, and expenses, if revenues, costs, and expenses grow at the rate specified from the assumption table in cells F66 to J68. The CEO has requested that we determine how the projected performance of the company will look if there are changes in how the company grows revenue and manages costs. In other words, we want to see how the projected income statement changes if the numbers in cell F66 to F68 change. In addition to the scenario shown in this example, there are three additional scenarios we need to consider. All the different sales Growth Rate, Growth Margins, as well as different SG&A Expenses, as a percentage of ticket revenue numbers. To set up your Scenario Manager, let's highlight the cells that are going to change. And then go to the Data tab > What-If Analysis > Scenario Manager. Let's first create a scenario to capture what we have on the table as a baseline for comparison. In the Scenario Manager pop-up window, click on ADD to create a new scenario. A new window called Add Scenario will appear. First, let's give this scenario a name called Baseline. Now, because we have already selected the cells containing the variables we want to change, the Changing cell field is already filled out for us. However, if you had not selected the cells before going to Scenario Manager, or would like to change your selection, you can also set the parameters here. If you would like to attach a note on any clarification or background you want to provide for the scenario, you can do so in the comment box here. However, it is not required. We believe it is best practice to leave some comments just in case you or someone else wants to review your assumptions in the future. However, in the interest of time, we're going to leave this blank. Lastly, there are also options to Prevent changes and Hide the scenario if you like. For our purpose, let's keep the default setting and click OK. Then, you will be taken to the final step of setting up the scenario, defining the value of each cell to change in the scenario. The numbers that are already in cell F66, F67, and F68 are the numbers we want to keep for the scenario, so we can simply click OK. That will take us back to our Scenario Manager pop-up window, so we can see the Baseline scenario that we just set up in the list of scenarios. Now, let's set up the other three scenarios using the same method. To set up our second scenario, first, we'll click on Add and type in our scenario name, High Growth with Margin Impact. The Changing cells in this scenario do not change from the scenario we just defined. So click OK in the Edit Scenario window. In the Scenario Values window, we need to update the values of the Changing cells for this particular scenario. Based on the scenario definition, we want to change the first cell, which is the Revenue Growth Rate, to 0.08. The second cell, which is the Growth Margin, to 0.45. And keep the SG&A expenses as a percentage of ticket revenue the same at 0.3. After the variable's values have been updated, click on OK to complete the second scenario. Notice how the results of the income statement changed based on our new assumptions. Operating Income in year 5 changed from around $1.7 million to around $2.2 million. The third scenario will be very similar. Let's click on Add and type in your third scenario name, Low Growth with Margin Impact. The changing sales are the same as the previous two scenarios, so we will simply click OK here. In the Scenario Values window, we can update the values of the changing sales for this scenario. And in this alternative feature, sales have dropped to 3%. Growth Margin is lowered to 32%. And SG&A Expenses as a percentage of ticket revenue is not impacted. So we will keep this at 30% After the variable's values have been updated for this scenario, click on OK to finish creating the third scenario. Now, it's time to set up our last scenario, click Add, And type in the name of our final scenario, Exploding Market Growth. The last scenario is a little bit different because we're considering an additional variable which is the 2% year over year growth as a result of the overall industry market growth, specified in cell K66. Therefore, in a changing cell field, let's add cell K66 and click OK. Because revenue, margin, and expenses stay the same as the original scenario, we will keep those numbers from the Baseline scenario at 8% Revenue Growth Rate, 45% Growth Margin, and 30% SG&A Expense as a percentage of ticket revenue. Our last field is for the year over year growth rate, which we'll set to 2%. After the variable's values have been updated for this scenario, click OK to complete our fourth and final scenario. Now that everything's set up, there are two ways to view the result of your scenarios. The first way is to show the result one scenario at a time. To do that, simply click on the scenario name in the Scenario Manager window, then click Show. As you can see, the projective portion of the income statement, which is dependent on a variables that we have specified in our scenario, has just updated. We can toggle between the different scenarios by simply clicking on another scenario name and clicking Show. However, this view does not allow you to easily compare and contrast the result of all of your scenarios. In order to see the results of all the scenarios at once, we can create a summary by clicking on Summary on the right side of the Scenario Manager window. For Report type, choose Scenario Summary, and let's include Gross Profit, Operating Income, Compound Annual Growth Rate, also known as CAGR, as the metrics to view on the report for the period. One we click OK, the summary will be generated on a new tab. As you can see, the Gross Profit, Operating Income, and CAGR results from each of the four scenarios are listed on this table view. On the left side, there's an outline grouping tool. If we click on the minus sign, it will collapse the rows to only show the bottom row in the same grouping. When you click on the plus sign, everything will reappear. This allows you to view only the part of the summary that you're most interested in. Now, it's turn to practice. [MUSIC]
