Creating a Spreadsheet for Decision Support - Business Decision Making - ثاني ثانوي

Creating a Spreadsheet for Decision Support Scan the QR code to access the workbook (Chapter 13 data file.xlsx) you'll be using for the rest of this lesson. You will produce a spreadsheet that models the resort's financial situation. Then you will run Scenario Manager twice (in-season only, and year-around) to explore the financial impact of your models and write a report that documents your analysis and findings. Start by creating the worksheet representation of the ski resort's business model. This will cover the three years from 2026 to 2028. Set up each of the following spreadsheet components before entering cell formulas: Constants Inputs . Summary of key results . Calculations Income and Cash flow statements . Debt owed. DEFINITION Debt: An amount of money that is due from a loan. A discussion of each section follows. Constants Section Your spreadsheet should include the constants shown in Figure 13-2. FIGURE 13-2: Constants section AutoSave off File Home Insert Page Layout Formulas Data Review View X Arial 10 AA == Paste BIU 18 A Undo Clipboard Б Font G25 fx 1 Ski Resort 2 3 Constants 4 Cash needed to start next year 5 Daily lift ticket price 6 Annual lift ticket price 7 Average daily money spent on food 8 Average ski school daily price 9 Average daily rental price 10 Number of ski days per year 11 Other daily revenue 12 Off-season potential revenue 13 Operating costs (snow-making) 14 Operating costs (off-season) 15 Daily other in-season operating costs 16 Fixed costs 446 13 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 446 Search Automate Help Acrobat Wrap Text Merge & Center General %-98 Alignment Number Б B C Conditional Format as Cell Formatting Table Styles Styles D Insert Delete Format Cells 2025 111111 1 2026 SAR 15,000,000.00 SAR 375.00 2027 SAR 15,000,000.00 SAR 375.00 SAR 2,500.00 2028 SAR 15,000,000.00 SAR 375.00 SAR 2,500.00 SAR 2,500.00 SAR 100.00 SAR 100.00 SAR 100.00 SAR 175.00 SAR 175.00 SAR 175.00 SAR 100.00 100 SAR 105.00 100 SAR 60.00 SAR 0.00 SAR 7,500,000.00 SAR 1,300,000.00 SAR 55,000.00 SAR 132,000,000.00 SAR 60.00 SAR 3,750,000.00 SAR 6,750,000.00 SAR 1,300,000.00 SAR 55,000.00 SAR 132,000,000.00 SAR 110.00 100 SAR 60.00 SAR 1,000,000.00 SAR 6,750,000.00 SAR 1,300,000.00 SAR 55,000.00 SAR 132,000,000.00 WEO 30/06/2023 14:29

QUICK TIP Assume that the ski resort can always begin the new year with the needed amount. . . . . . . Cash needed to start next year: The government wants the resort to have at least SAR 15 million cash at the beginning of each year. This cash reserve is what the resort will use to operate the facility during the upcoming season. Daily lift ticket price: This is the amount that the resort will charge a customer to ski for an entire day at the resort. Annual lift ticket price: This is the amount (a one-time fee) that a skier will pay for an annual pass to the ski area. Skiers can then ski as many times as they choose during that year. Average daily money spent on food: This is the average amount of money that skiers will spend on food and drink in the concession stands and restaurants at the resort. Average ski school daily price: If skiers choose to take a ski lesson, this is the average price they will pay. Average daily rental price: If skiers rent equipment, such as boots, poles, skis, and helmets, this is the average price they will pay for one day's rental. Number of ski days per year: This is the average number of days in the main season. The resort is open for business for this number of days per year. The ski season is based on the temperatures in the north-western part of the country. Management has forecasted 100 days for the sea- son each year. Other daily revenue: Skiers often forget, lose, or break equipment they need to make skiing pleasurable. The resort has a shop that supplies these items, along with souvenirs. This amount of other revenue is what the resort expects to earn each day. Off-season potential revenue: If the resort's management decides to remain open during the off-season, it can expect this amount of reve- nue. Management would develop other recreational activities to attract visitors during this time when it isn't practical to ski due to hotter tem- peratures. Operating costs (snow-making): Snow-making is a huge expense for the ski industry. However, snow-making equipment continues to become وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 447 The Ski Resort Project 447 30/06/2023 14:29

. . . more efficient, so the costs are not expected to increase. Because Saudi Arabia does not have natural snowfall, this is not an optional expense for the ski resort. Operating costs (off-season): These are the expected costs that the resort will incur if it operates during the off-season. If management decides not to operate during the off-season, these costs will be zero. Daily other in-season operating costs: This is the additional daily cost of operating the resort in-season, beyond the major costs already noted. Fixed costs: This is the amount of fixed costs the resort has each year; this number is not a function of how many days the resort is open each year. Inputs Section Your spreadsheet should include the following inputs for the years 2026 to 2028, as shown in Figure 13-3. FIGURE 13-3: Inputs section AutoSave off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Paste X Calibri BIU -11 AA == Wrap Text General Merge & Center - %88 Undo Clipboard Foot Alignment Б Number 1 H27 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) 23 24 Summary of key results 448 13 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 448 Conditional Format as Cell Insert Delete Format Formatting Table Styles Styles B C D E All years 2026 2027 2028 1% NA NA NA Good NA NA NA NA 0% 0% -3% NA N N N Cells SAR 25,236,450.00 SAR 28.020.207.00 OLD 00.520.050.80 F WE . Rate of change in skiers: After the resort opens, management is hope- ful that the number of skiers increases with time. The rate of change in skiers is a measure of the change in numbers from year to year. This percentage could be positive or negative, depending on how the resort thinks the number of future skiers will change. Format this cell as a per- centage. Economic outlook (Good, Poor): If the economy is doing well and is expected to continue through the year, enter "Good" in the cell. If the economy is weak and is expected to be lackluster through the year, enter "Poor". 30/06/2023 14:29

. . Climate change factor: This percentage factor, expressed as a negative number, indicates the severity of climate change and its effect on the number of skiers. Format this cell as a percentage. Off-season operations (Y or N): If the resort decides to operate during the off-season, enter "Y" in this cell. If not, enter "N". Summary of Key Results Section Your spreadsheet should include the key results labels shown in Figure 13-4. FIGURE 13-4: Summary of key results section Conditional Format as Cell Formatting Table Styles Insert Delete Format G Styles Cells AutoSave Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Calibri 11 A A = Paste BIU- Wrap Text Merge & Center - General % 588 Unda Cipboard Б Font Б Alignment Number H34 fx. 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 change in daily skiers B C D E SAR 25,236,450.00 SAR 28,836,367.00 SAR 25,236,450.00 SAR 54,072,817.00 SAR 26,539,050.80 SAR 80,611,867.80 SAR 0.00 15% SAR 0.00 17% SAR 0.00 16% 2025 2026 2027 2028 1% 2% F WE3 QUICK TIP Cell formulas should not reference a cell with a value of "NA." For each year, your spreadsheet should compute net income, cash on hand at the end of the year, debt owed at the end of the year, and profit margin. These values are computed elsewhere in the spreadsheet and should be echoed here for all years. Calculations Section You should calculate intermediate results that will be used in the Income and Cash flow statement that follows. The Calculations section is shown in Figure 13-5. When called for, use absolute cell references. Values must be computed by formulas in each of the cells. Do not type any numbers into cells C31:E40. وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 449 The Ski Resort Project 449 30/06/2023 14:29

FIGURE 13-5: Calculations section AutoSave off H Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Calibri 11 A A == Wrap Text General Paste BIU- 3 Merge & Center - %98-98 Undo Clipboard Б Font G Alignment Number Б 182 fx Conditional Format as Cell Formatting Table Styles Styles Insert Delete Format Cells 30 Calculations 31 Percentage change in daily skiers 32 Number of daily skiers 33 Number of season-ticket skiers 34 Yearly total skier revenue 35 Daily ski school attendees 36 Yearly ski school revenue B 2025 3000 500 2026 2027 2028 1% 3,030 505 1% 3,060 510 -2% 2,999 515 SAR 114,887,500.00 SAR 116,036,375.00 SAR 113,753,901.25 303 306 300 37 Daily equipment rental users 38 Yearly rental revenue SAR 5,302,500.00 2,273 SAR 5,355,525.00 2,295 SAR 5,248,414.50 2,249 39 Yearly food concession revenue 40 Other yearly revenue SAR 22,725,000.00 SAR 27,315,450.00 SAR 6,000.00 SAR 24,099,862.50 SAR 27,588,604.50 SAR 6,000.00 41 42 Income statement and Cash flow statement Beginning-of-year cash on hand 2025 NA 2026 2027 SAR 7,500,000.00 SAR 25 236.450.00 SAR 24,742,525.50 SAR 27,038,209.55 SAR 6,000.00 2028 SAR 54.072 817.00 WE♦ QUICK TIP =IF($B$20="Good", ($B$19+C21), (($B$19–0.02)+C21)) QUICK TIP =B32+(B32*C31) QUICK TIP =B33+(B33*$B$19) . . Percentage change in daily skiers: This number is a function of the rate of change in skiers, the economic outlook, and the climate change fac- tor. You will use an =IF statement to evaluate this cell. The climate change factor will be added to the rate of change in skiers to determine the Percentage change in daily skiers. For example, if the economic outlook is poor and the climate change factor is -3%, then the total percentage of change in skiers will be the rate of change in skiers minus 2% (for the poor economy) minus 3% (for climate change). If the economy is "Good" the percentage of change would only be the rate of change in skiers minus 3% (for climate change). - - Test: If the economic outlook is "Good", then the Percentage change in daily skiers equals the Rate of change in skiers (B19) + Climate change factor (C21). Otherwise: the Percentage change in daily skiers equals the (Rate of change in skiers - 2%) + Climate change factor. Number of daily skiers: This number is the sum of the Percentage change in daily skiers and the previous year's Number of daily skiers. This year's Number of Daily Skiers = last year's Number of Daily Skiers + (last year's Number of Daily Skiers x Percentage Change in Daily Skiers. = . Number of season-ticket skiers: This number is on the sum of the Percentage change in daily skiers and the previous year's Number of season-ticket skiers. This year's Number of season-ticket skiers last year's Number of season-ticket skiers + (last year's Number of sea- son-ticket Skiers x Percentage change). 450 13 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 450 30/06/2023 14:29

QUICK TIP |=(C32*C10*C5)+(C33*C6) QUICK TIP |=C35*C10*C8 QUICK TIP =C37*C10*C9 QUICK TIP =(C32*C10+C33)*C7*0.9 . . . . Yearly total skier revenue: This number is the product of the Number of daily skiers, the average lift ticket price, and the number of ski days per year, plus the product of the number of annual pass skiers and the annual lift ticket price. Daily ski school attendees: This number is a percentage of the Number of daily skiers (C32). Assume that 10% of the daily skiers will take ski school lessons. You may hard code this percentage into your formula for this cell. Yearly ski school revenue: This number is the product of the daily ski school attendees, the average ski school daily price, and the number of ski days per year. Daily equipment rental users: This number is forecasted to be 75% of the daily number of skiers. You may hard code this percentage into your formula for this cell. Yearly rental revenue: This number is the product of the daily equip- ment rental users, the average daily rental price, and the number of ski days per year. Yearly food concession revenue: This number is the product of the total number of skiers per year and the average daily money spent on food. Assume that 90% of skiers buy food. The formula is as follows: (Number of daily skiers × number of Ski Days + Number of season skiers) x average daily money spent on food x 90%. Other yearly revenue: This number is the product of the other daily revenue (C11) and the number of ski days per year (C10). Income and Cash Flow Statements The forecast for net income and cash flow starts with the cash on hand at the beginning of the year. This value is followed by the income statement and the calculation of cash on hand at year's end. For readability, format cells in this sec- tion as currency with two decimals. Values must be computed by cell formula; hard-code numbers in formulas only if you are told to do so. Do not enter num- bers directly into cells C43:E56. Cell formulas should not reference a cell with a value of "NA". Your spreadsheets should look like those in Figures 13-6 and 13-7. Beginning-of-year cash on hand: This value is the cash on hand at the end of the prior year (from B62). وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 451 The Ski Resort Project 451 30/06/2023 14:29

452 13 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 452 . . . Revenue (skiers): This value is the Yearly total skier revenue and may be echoed here (from C34). Revenue (ski school): This value is the Yearly ski school revenue and may be echoed here (from C36). Revenue (equipment rental): This value is the Yearly rental revenue and may be echoed here (from C38). Revenue (food concession): This value is the Yearly food concession revenue and may be echoed here (from C39). Revenue (other): This value is the Other yearly revenue and may be echoed here (from C40). Revenue (potential off-season): If the resort decides to include off-sea- son operations, then the value for the revenue is echoed here from Off-season potential revenue in the Constants section (from C12). If the resort chooses not to include off-season activities, then the value here is zero. Total revenue: This amount is the sum of the revenue from the skiers, the ski school, the equipment rental, the food concession, other reve- nue, and the off-season revenue C44:C49. Operating costs (snow-making): This value is echoed from the Con- stants section (from C13). Off-season operating costs: If the resort chooses to operate in the off-season, then this is echoed from Operating costs - off-season in the Constants section (from C14). You will need to use an =IF statement for this. - Test: If the Off-season operations is "Y" then use the value from C14 (Operating costs – off-season). - Otherwise: this value is zero. (If the resort chooses not to operate in the off-season, then they wouldn't have off-season expenses). Yearly in-season operating costs: This value is the product of the Daily other in-season operating costs (C15) and the Number of ski days per year (C16). . Fixed costs: This value is echoed from the Constants section (from C16). 30/06/2023 14:29

CHECK YOUR PROGRESS Compare your calculations to those shown. If the values differ, recheck your formulas and cell addresses. . . Total costs: This is the sum of the operating costs of snow-making, the off-season operating costs, the yearly in-season operating costs, and the fixed costs C51:C54. Net income: This is the difference between the total revenue and the total costs. FIGURE 13-6: Income and cash flow statements section AutoSave ON H Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat X Calibri 11 Paste BIU A A == 1-45 Wrap Text General Merge & Center 3-% Undo Clipboard 15 Font 19 Alignment 15 Number Б 382 fx A Income statement and Cash flow statement 43 Beginning-of-year cash on hand 44 Revenue (skiers) 45 Revenue (ski school) 46 Revenue (equipment rental) 47 Revenue (food concession) 48 Revenue (other) 49 Revenue (potential off-season) 50 Total revenue 51 Operating costs (snow-making) 52 Off-season operating costs 53 Yearly in-season operating costs 54 Fixed costs 55 Total costs 56 Net income 57 58 Dosition (NCP) before borrowing and Conditional Format as Cell Formatting Table Styles Styles Insert Delete Format Cells B 2025 2026 D 2027 E 2028 NA NA NA NA SAR 5,302,500.00 SAR 22,725,000.00 NA SAR 27,315,450.00 NA NA SAR 6,000.00 SAR 0.00 NA SAR 170,236,450.00 NA SAR 7,500,000.00 NA SAR 0.00 SAR 173,086,367.00 SAR 6,750,000.00 SAR 0.00 NA SAR 5,500,000.00 NA SAR 132,000,000.00 NA NA SAR 7,500,000.00 SAR 25,236,450.00 SAR 114,887,500.00 SAR 116,036,375.00 SAR 5,500,000.00 SAR 132,000,000.00 SAR 54,072,817.00 SAR 113,753,901.25 SAR 5,248,414.50 SAR 24,742,525.50 SAR 27,038,209.55 SAR 6,000.00 SAR 0.00 SAR 170,789,050.80 SAR 6,750,000.00 SAR 0.00 SAR 5,500,000.00 SAR 132,000,000.00 SAR 145,000,000.00 SAR 144,250,000.00 SAR 144,250,000.00 SAR 25,236,450.00 SAR 5,355,525.00 SAR 24,099,862.50 SAR 27,588,604.50 SAR 6,000.00 SAR 0.00 SAR 28,836,367.00 2025 2026 2027 SAR 32.736.450.00 SAR FLA SAR 26,539,050.80 2028 44.867.80 F WE QUICK TIP =IF(C59<C4,C4-C59,0) We will extend the income statement to also include the resort's Net cash posi- tion and any borrowing from or repayment to the bank. . Net Cash Position (NCP) before borrowing and repayment of debt: This amount is the sum of the Beginning-of-year cash on hand (C43) and Net income (C56). Add (Borrowing from bank): Assume that the resort can borrow from bankers at the end of the year to reach the minimum cash needed to start the next year; this minimum is a value in the Constants section (from C4). You will use an =IF statement in this cell. - Test: If the NCP is less than the minimum cash needed amount, then the resort would borrow enough to start the next year with the min- imum. This amount would be the minimum cash needed-NCP. Otherwise: If the NCP is greater than this minimum, the resort would not borrow any money. وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 453 The Ski Resort Project 453 30/06/2023 14:29

QUICK TIP The syntax for a nested =IF statement is: |=IF(Test1,Result1,IF(Test2, Result2,IF(Test3,Result3, Result if false))) Result1 is the value if Test1 is true. Result if false is the value if all Tests are false ("Otherwise"). QUICK TIP |=IF(C65=0,0,IF(C59<C4,0, IF(C59-C4>=C65,C65, C59-C4))) . . Less (Repayment to bank): The resort will use any excess cash at year's end to pay off as much debt as possible without going below the mini- mum cash threshold. Excess cash is the NCP minus the minimum cash required to start the year. Amounts that exceed the minimum are avail- able to repay debt. The resort wants to repay as much as it can if it has any money available. - - - Test 1: If the Beginning-of-year debt owed is zero, then no repay- ment is needed. This value is 0. Test 2: If the NCP < minimum cash needed, then no repayment can be made. This value is 0. Test 3: If the difference between the NCP and minimum cash needed is greater than, or equal to, the Beginning-of-year debt owed, the resort will pay off the Beginning-of-year debt in full. Otherwise, the resort will pay off an amount equal to the difference between the NCP and Minimum Cash Needed. Equals (End-of-year cash on hand): This amount is the NCP plus any bank borrowing minus any repayments to the bank. FIGURE 13-7: End-of-year cash on hand section AutoSave ON H Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Calibri 11 AA 三川 Paste BIU Wrap Text Merge & Center General *% Undo Clipboard 5 Font 19 Alignment. Б 382 fx A 58 59 Net Cash Position (NCP) before borrowing and repayment of debt 60 Add (Borrowing from bank) 61 Less (Repayment to bank) 62 Equals (End-of-year cash on hand) 63 64 Debt owed fyear debt owed CHECK YOUR PROGRESS Compare your results with the values shown. If yours are different, carefully re-check your formulas and cell addresses. 454 13 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 454 Number 29 Conditional Format as Cell Formatting Table Styles Styles B 2025 2026 D 2027 NA NA NA SAR 7,500,000.00 SAR 32,736,450.00 SAR 0.00 SAR 7,500,000.00 SAR 25,236,450.00 SAR 54,072,817.00 SAR 0.00 SAR 0.00 SAR 54,072,817.00 2025 2026 2027 ALA SAR 7,500,000.00 Insert Delete Format E 2028 Cells SAR 80,611,867.80 SAR 0.00 SAR 0.00 SAR 80,611,867.80 2028 SAR 0.00 F WED 30/06/2023 14:29

QUICK TIP =C65+C66-C67 Debt Owed Section This section shows a calculation of debt owed at the end of a year (see Figure 13-8). Year 2025 values are NA except for End-of-year debt owed, which is SAR 7.5 million. Values must be computed by cell formula. Cell formulas should not reference a cell with a value of "NA". . . Beginning-of-year debt owed: This amount is the debt owed at the beginning of the year and equals the debt owed at the end of the prior year (from B68). Add (Borrowing from bank): This amount is echoed from the Add: Borrowing from bank (from C60). Borrowing increases the amount of debt owed. Less (Repayment to bank): This amount is echoed from the Less: Repayment to the the bank (from C61). Repayments reduce the amount of debt owed. Equals (End-of-year debt owed): This is the amount owed at the begin- ning of a year, plus borrowing from the bank during the year, minus. repayments to the bank during the year. FIGURE 13-8: Debt owed section AutoSave Off File Home Insert Page Layout Formulas Data Review View X Calibri 11 A Paste BIU Undo Clipboard 159 Font J82 fx A 64 Debt owed 65 Beginning-of-year debt owed 66 Add (Borrowing from bank) 67 Less (Repayment to bank) 68 Equals (End-of-year debt owed) 69 70 3 Search Automate Help Acrobat Wrap Text Merge & Center General % Alignment 15 Number 5 Conditional Format as Cell Formatting Table Styles Styles Insert Delete Format Cells B 2025 2026 D 2027 E 2028 F NA SAR 7,500,000.00 SAR 0.00 SAR 0.00 NA NA SAR 7,500,000.00 SAR 0.00 SAR 7,500,000.00 SAR 0.00 SAR 0.00 SAR 0.00 SAR 0.00 SAR 0.00 SAR 0.00 SAR 0.00 WE CHECK YOUR PROGRESS Compare your results with those shown. What does this tell you about the resort's ability to pay its debt? Copy all the formulas from column C (2026) to column D (2027) and column. E (2028). وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 455 The Ski Resort Project 455 30/06/2023 14:29

REVIEW QUESTIONS 456 13 Chapter رة ا Ministry of Education 2024-1446 1. Season tickets offer benefits to: a. the customer b. the business c. the customer and the business d. neither the customer nor the business 2. For the new ski resort to be sustainable, it should be: a. profitable b. growing in popularity each year c. safe d. all of the above 3. In 2027, how much would it cost a visitor to the ski resort to buy a lift ticket, spend an average amount of money on food, attend a ski lesson, and spend the average amount of money on renting equipment? a. SAR 655 b. SAR 755 c. SAR 855 d. SAR 955 4. What is the percentage change in the cost of the services from question 3, between 2027 and 2028? Round your answer to one decimal place. a. 0.7% b. 0.8% c. 0.9% d. 1.0% Business Decision Making S1 S2 S3.indb 456 30/06/2023 14:29