Ski Resort Deliverables - Business Decision Making - ثاني ثانوي

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 457 Ski Resort Deliverables The exciting new ski resort business needs to carefully manage its profit margins to ensure its success. This is important for any business (and therefore a common KPI), but especially important for a business that is trying to establish itself in a country where skiing is so unusual. As we have seen, the situation can be even more complicated when we consider the effects of climate change and the global economy. Your task is to create a spreadsheet that will help to support the decision making of the ski resort's executives, so you can help them to explore different financial models before making a recommendation. It is now time to model those scenar- ios so you can compile your report. The managers of the ski resort would like you to explore three scenarios with optimistic, neutral, and pessimistic outlooks. They will receive all of your data, but they only want to see the profit margin projections in your report. The man- agers would like you to explore each of these three scenarios twice: once where the business is closed in the off-season, and again where the business is open all year round. However, the business will not be ready to run in the off-season until 2028, so the results will be the same, except for in 2028. You will need to run Scenario Manager for each of the three scenarios. Set up Scenario Manager by entering the input values for the desired scenario before you run the tool. The first time you run Scenario Manager, make sure the input value for Off-Season Operations are all set to "N", and enter the inputs for the Optimistic scenario (described below). As you work through each successive scenario, start by setting the input values. The input values you will use are as follows: . Optimistic: The number of skiers will increase each year by 1%, and the economic outlook is good. The climate change factor will be zero in 2026 and 2027, and will be -3% in 2028. Neutral: The number of skiers will remain the same (no percentage increase or decrease), and the economic outlook is good. The climate change factor will be -2% in 2026 and 2027, and will be -6% in 2028. Pessimistic: The number of skiers will decrease each year by 3%, and the economic outlook is poor. The climate change factor will be -8% in 2026, 2027, and 2028. The Ski Resort Project 457 30/06/2023 14:29

Start Scenario Manager by first clicking the Data tab on the ribbon. Then, select the What-If Analysis tab and choose Scenario Manager (Figure 13-9). FIGURE 13-9: Select Scenario Manager File AutoSave Off Home Insert Page Layout Formulas Data Review View Automat From Text/CSV From Web From Picture Recent Sources Get Data From Table/Range Existing Connections J82 Get & Transform Data XV fx 458 13 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 458 Queries & Connections 项目 Group Properties Refresh All What-If Forecast 项目 Ungroup Edit Links Queries & Connections Analysis Sheet Subtotal bis utline Scenario Manager... Goal Seek... The Scenario Manager window will pop up (see Figure 13-10). Click on the Add button to add your first scenario. FIGURE 13-10: Click the Add button to add a scenario Scenario Manager Scenarios: ? х Add... Delete Edit... No Scenarios defined. Choose Add to add Merge... Summary... Changing cells: Comment: Show Close Enter "Optimistic" in the Scenario Name box. Enter the range of your changing cells. Note the range highlighted in yellow in Figure 13-11. As you can see, the changing cells are noncontiguous, so you will enter the two ranges separated by a comma (as shown in Figure 13-12). 30/06/2023 14:29

FIGURE 13-11: Use the data in the highlighted cells to create the Optimistic scenario AutoSave Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Calibri 11 A A = Wrap Text General Paste BIU A Merge & Center %88 Conditional Format as Cell Formatting Table Styles Insert Delete Format Undo Clipboard 19 Foint Alignment Б Number E Styles: Cells 140 fx A 18 Inputs 19 Rate of change in skiers 20 Economic outlook (Good, Poor) 21 Climate change factor 22 Off-season operations (Y or N) وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 459 D E All years 1% 2026 2027 2028 NA NA NA Good NA NA NA NA 0% 0% -3% NA N N N FIGURE 13-12: Enter the two ranges separated by a comma Add Scenario ? Scenario name: Optimistic Changing cells: B19:820,C21:E22 Ctrl+click cells to select non-adjacent changing cells. Comment: Protection Prevent changes Hide OK Cancel 2E3 Scenario Manager will fill in the values of the changing cells from the values it finds in your worksheet (Figure 13-13). This is why you should enter the appro- priate input values for each scenario before running Scenario Manager. Check each of these values carefully and make corrections if necessary. Click OK to save the scenario. FIGURE 13-13: Check the Scenario Values carefully Scenario Values Enter values for each of the changing cells. 1: $B$19 0.01 2: $B$20 Good 3: $C$21 0 $D$21 0 5: $E$21 -0.03 Add ? OK Cancel The Ski Resort Project 459 30/06/2023 14:29

Repeat the process for the Neutral and Pessimistic scenarios (see Figure 13-14). Remember to update the original Input values for each scenario before creating the next scenario in Scenario Manager. FIGURE 13-14: Enter the correct values for the other scenarios Scenario Manager Scenarios: Optimistic Neutral Pessimistic Changing cells: $B$19:$B$20, $C$21:$E$22 Comment: ? Add... Delete Edit... Merge... Summary... Show Close You can check your work by selecting one of the three scenarios in the Scenario Manager window and clicking Show. Look at the Inputs on your worksheet. When you click Optimistic, they should change to the Optimistic values. Click Pessimistic and they will change to the Pessimistic values. If all appears to be correct, click the Summary button. Scenario Manager will display the Scenario Summary windows. Click Scenario Summary for the Report Type. Then, enter the Summary of Key Results values you are interested in. As management wants to know what their net income, cash on hand, debt owed, and profit margins will be in 2028, select the cell range E25:E28 (Figure 13-15). FIGURE 13-15: Enter the correct cell range in the Scenario Summary window Comments Shar File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat From Ing CSV Prim Pictuer D Cha Gr Solver Data Analysis Clabe All Properti BLE LING What Camicies 31 Altraod Colum Analp Get & Transfarm D Queries & Connections Stata Types Sort & Filar Data uch Forecast Outerve Analysis E25 Add (Borrowing from bank) 24 Summary of key results 25 Net income 26 End-of-the-year cash on hand 27 End-of-the-year debt owed 28 Profit margin 29 30 Calculations 31 Percentage change in daily skiers 32 Number of daily skiers 33 Number of season-ticket skiers Yearly total skier revenue attendees 460 13 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 460 C D SAR 1,769,650,00 -SAR 15.582,497.00 -SAR 31,445,829.97 SAR 15,000,000.00 SAR 15,000,000,00 SAR 15,000,000,00 SAR 13,230,350.00 SAR 28,812,847.00 SAR 60,258.676.97 Scenario Summary Report type Scenar jummary 1% -12% -28% O Scavan votTable report Besult.co 2025 2026 2027 2028 3000 500 -13% 2.610 -13% 2,271 -13% 1.976 OK Canol 485 470 456 SAR 99,087,500.00 SAR 86,327,375.00 SAR 75,222,428.75 261 227 G X 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 461 Scenario Manager will create a new worksheet named "Scenario Summary". Right-click on this tab and rename it "In-Season". The Scenario Summary shows the results for the Optimistic, Neutral, and Pessimistic conditions. The summary could benefit from some polishing. Highlight Column D, right- click, and delete it. Highlight rows 19-21, right-click, and Clear Contents. Highlight the cell range D3:G18 and center the text. Highlight cells C15:C18, right-click, and select Clear Contents. Copy and paste the descriptive labels from your worksheet into cells B15:B18. You may need to adjust the column widths of columns B and C to fit the descriptive labels. Your finished summary should look like the one shown in Figure 13-17. Figures 13-16 and 13-17 show the Before and After of this polishing. FIGURE 13-16: The unpolished Scenario Summary AutoSave Off File Home Insert Page Layout Formulas Data Review View Help Acrobat Queries & Connections Properties Get From From From Table/ Recent Existing Data Text/CSV Web Range Sources Connections Refresh All- Get & Transform Data Edit Links Queries & Connections Stocks Currencies Geography Data Types 1 D29 B E F 5 1 234 Scenario Summary Current Values: Optimistic Neutral Pessimistic 4 Changing Cells: 56 5 $B$19 1% 1% 0% -3% $B$20 Good Good Good Poor 7 $C$21 0% 0% -2% -8% 8 $D$21 0% 0% -2% -8% $E$21 -3% -3% -6% -8% 10 $C$22 N N N N 11 $D$22 N N N N 12 13 23 $E$22 N N N N Result Cells: 14 15 16 $E$27 17 84 $E$28 SAR 0.00 16% SAR 0.00 16% $E$25 SAR 26,539,050.80 SAR 26,539,050.80 SAR 10,071,532.00 -SAR 31,445,829.97 $E$26 SAR 80,611,867.80 SAR 80,611,867.80 SAR 49,035,882.00 SAR 15,000,000.00 SAR 0.00 7% SAR 60,258,676.97 -28% 18 19 20 21 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. The Ski Resort Project 461 30/06/2023 14:29

FIGURE 13-17: The polished Scenario Summary Save Off Home Insert Page Layout Formulas Data Review View Help Acrobat X Cut Calibri 11 A A ab Wrap Text General Copy BIU Merge & Center ▾ V Format Painter Clipboard F Font 12 Alignment 2 B F Number 23 Scenario Summary Optimistic Neutral Pessimistic 567 Changing Cells: Rate of change in skiers 1% 0% -3% Economic outlook (Good, Poor) Good Good Poor 8 Climate change factor 2026 0% -2% -8% 9 Climate change factor 2027 0% -2% -8% 10 Climate change factor 2028 -3% -6% -8% 11 17 18 19 1234567 Off-season operations 2026 Off-season operations 2027 Off-season operations 2028 Result Cells: N N N N N N N N N Net income End-of-the-year cash on hand End-of-the-year debt owed Profit margin SAR 26,539,050.80 SAR 10,071,532.00 -SAR 31,445,829.97 SAR 80,611,867.80 SAR 49,035,882.00 SAR 15,000,000.00 SAR 0.00 SAR 0.00 SAR 60,258,676.97 16% 7% -28% 462 13 Chapter رة ا Ministry of Education 2024-1446 5 This set of scenarios forecasts key results for operation only during ski season. Management would like a second set of scenarios where the resort is open year- around. Remember that the resort will need to set up for off-season operation and will not be able to open off season until 2028. Repeat the entire process for the year-round operation. Modify your scenarios (Optimistic, Neutral, and Pessimistic) with a "Y" for Off-season operations in 2028. Carefully check all of your scenario parameters. When all appears to be correct, click on the Summary button. Scenario Manager will create a new work- sheet. As before, rename this by right-clicking the tab and changing the name of the worksheet to "Year-Round". Repeat the same steps you did above to polish the look of the summary report. Business Decision Making S1 S2 S3.indb 462 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 FIGURE 13-18: The polished Scenario Summary for year-round operations Business Decision Making S1 S2 S3.indb 463 AutoSave Off File Home Insert Page Layout Formulas Data Review View Help Acrobat Get Data From From From Table/ Recent Existing Text/CSV Web Range Sources Connections Queries & Connections Properties Refresh AllEdit Links Get & Transform Data Queries & Connections Stocks Currencies Geography Data Types G26 fx 2 1 2 1 B C E 236 Scenario Summary Optimistic Neutral Pessimistic Changing Cells: 67 Rate of change in skiers 1% 0% -3% Economic outlook (Good, Poor) Good Good Poor 8 Climate change factor 2026 0% -2% -8% 9 Climate change factor 2027 0% -2% -8% 10 Climate change factor 2028 -3% -6% -8% 11 12 13 14 . 15 16 17 18 25670 Off-season operations 2026 Off-season operations 2027 Off-season operations 2028 Result Cells: N N N N N N Y Y Net income End-of-the-year cash on hand End-of-the-year debt owed Profit margin SAR 26,239,050.80 15% SAR 9,771,532.00 -SAR 31,745,829.97 SAR 80,311,867.80 SAR 48,735,882.00 SAR 15,000,000.00 SAR 0.00 SAR 0.00 SAR 60,558,676.97 6% -28% 19 You might find it useful to copy out Table 13-1 to record the results. TABLE 13-1: Ski Resort Project results 2025 2026 2027 Optimistic Neutral Pessimistic a D 2028 (Off-season) 2028 (Year-round) It is now time to present your findings to the ski resort's management in the form of a short report. Scan the QR code to access the report template (Report template.dotx). 1. Start by scanning the QR code and opening the report template. 2. Click on the text "Report Title." Use the drop-down menu to select the report title. 3. Click on the text "Your Name" and enter your full name. 4. Click on the text "Date." Use the calendar menu to select the correct date. The Ski Resort Project 463 30/06/2023 14:29

464 13 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 464 5. First, complete the Problem Statement section. Go back to the beginning of this chapter to refresh your memory, then try to state the problem as briefly as possible and in your own words. 6. Explain the techniques you used to get your results in the Methodology sec- tion. Think about how you built your spreadsheet, calculated data, and used Scenario Manager. 7. Next, complete the Results section. It isn't necessary to present all of your results to the management team—instead, copy out the following sentences and fill in the blanks with the correct information: The neutral profit margin for 2026 is The pessimistic profit margin for 2027 is The optimistic profit margin for 2028, if the business closes for the off-season, is The pessimistic profit margin for 2028, if the business stays open all year round, is 8. As we expected, the optimistic scenario produces the best results for the company's profit margins. But because some of the factors are out of our control, we don't know whether the reality will match the optimistic, neu- tral, or pessimistic outlooks. Complete the Analysis section by answering the following question: Should the business close during the off-season, or should it stay open all year round? In your answer, consider all of the data for each scenario, including your results for net income, cash on hand, debt owed, and profit margin. Give a detailed response of at least one paragraph. 9. Now you have completed the body of the report, you can complete the Executive Summary section by following the guidance in the report tem- plate. Remember to keep it brief, but informative. 10. Your report is now complete. Save it by selecting Save from the file menu, and use the file name format: YourName - Ski Resort - Report.docx. 30/06/2023 14:29