Practicing with Scenario Manager - Business Decision Making - ثاني ثانوي

Lesson 3 Chapter 10 378 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 378 Practicing with Scenario Manager Imagine you have an uncle who works for a large company. He has a good job and makes SAR 300,000 a year. At age 60, which he will be in three years, he must retire from his company and start drawing his pension. DEFINITION Pension: A regular payment received by retired people to support living costs. However, the company has an early-out plan in which employees are paid to retire early. The company pays all employees in the plan a bonus in the year they retire and each year thereafter, until their official retirement date. If your uncle left the company early, he could find a part-time job to make ends meet in the years before taking his normal pension. The opportunity to leave early is open for three years after the end of the current year, which is now ending. That means your uncle could leave the company any time in the next three years, and receive the early-out bonuses in the years he has retired early. If he works another year, he would lose the bonus for that year. If he works two more years, he would lose two years of early-out bonuses, and so on until he must retire. Your uncle is also deciding whether to continue his gym membership. He likes the gym's amenities, but it is expensive. The retirement decision can be revisited each year, but the decision about the gym membership must be made now. If your uncle does not withdraw now, he will remain a member (and be charged) indefinitely. Your uncle asks you to make a spreadsheet model of his situation in Scenario Manager. Your spreadsheet would let him play "what if?" with the possibilities and see his various projected personal finances for each of the next three years. With each scenario, your uncle wants to know what cash on hand will be avail- able for each year in the period. 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 379 Complete the spreadsheet for your uncle. Remember that your Summary of business indicators, Calculations, and Income statement and Cash flow state- ment section cells must show values by using cell formulas, so do not type amounts in those sections. Also, do not use the address of a cell if its contents are "NA" in any of your formulas. Use the spreadsheet (Chapter 10 data file 2.xlsx), you can find it by scanning the QR code. 3-1 Constants Section This section explains the contents of the spreadsheet's Constants section (see Figure 10-28). FIGURE 10-28: Constants section values AutoSave Off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 11 A A == Paste B I U 3 3 Wrap Text Merge & Center General Font Б Alignment Б Undo Clipboard Б K19 XVfx 3 % 08 08 Condit Format Number HIN B D E F 1 Chapter 10 Lesson 3 2 Constants Year 0 Year 1 Year 2 3 Salary increase factor NA 0.03 0.03 4 Part-time wages expected (retired) NA SAR 37,500.00 SAR 38,250.00 5 Buyout amount NA 6 Cost of living (not retired) NA 8 7 Gym dues 9 Inputs 10 Retired [R] or Working [W] NA SAR 168,750.00 SAR 153,750.00 SAR 45,000.00 SAR 112,500.00 SAR 157,500.00 SAR 48.750.00 Year 3 0.02 SAR 39,375.00 SAR 37,500.00 SAR 161,250.00 SAR 52,500.00 Year 0 NA Year 1 Year 2 Year 3 1. Salary Increase Factor Your uncle's salary, as of the current working year (Year 0), is SAR 300,000 and raises are expected each subsequent year; for example, a 3% raise is expected in the upcoming year (Year 1). If your uncle does not retire in a year, he will get his salary (including a raise) for the next year. 2. Part-time Wages Expected (Retired) For the next three years, your uncle has estimated what his part-time wages would be when he is retired from the company and working part-time (Years 1-3). For example, he thinks he could earn SAR 37,500 in part-time wages in Year 1 if he retired. 3. Buyout Amount The amounts for the company's preretirement plan are shown. For example, if your uncle retires now (and does not work for the company in Years 1-3), he gets SAR 168,750, SAR 112,500, and SAR 37,500 in those years. If he works another year and leaves before Year 2 starts, he will give up the SAR 168,750 payment for Year 1 but will get SAR 112,500 and SAR 37,500 in Years 2 and 3. Decision Support System Fundamentals 379 30/06/2023 14:28

380 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 380 4. Cost of Living (Not Retired) Your uncle has estimated how much cash he needs to meet his living expenses, assuming he continues to work for the company. His cost of living would be SAR 41,000 in Year 1, increasing each year thereafter. 5. Gym Dues Gym dues will be SAR 45,000 for Year 1. Dues increase each year thereafter, as shown. 3-2 Inputs Section This section explains the contents of the spreadsheet's Inputs section (see Figure 10-29). FIGURE 10-29: Inputs section AutoSave Off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 10 AA Wrap Text General Paste BIU 3 Merge & Center v - % 9 Undo Clipboard Б Font F Alignment E Number J17 fx A 9 Inputs 10 Retired [R] or Working [W] 11 Stay in gym? [Y] or [N] B C D E Year 0 Year 1 Year 2 Year 3 NA W W R NA Y NA NA 1. Retired or Working Enter an "R" if your uncle would be retired in the year or a "W" if he would still be working. If he is working the next three years, you should enter the pattern "W W W" in cells C10:E10. If he plans to retire now you would enter the pattern "R R R". If he works for one year and then retires, you should enter the pattern "W R R". 2. Stay in Gym? If your uncle remains a member of the gym, you should enter a "Y" in cell C11. If your uncle leaves the gym now, you should enter an "N". The deci- sion applies to Years 1-3. 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 381 3-3 Summary of Business Indicators Section Your spreadsheet should show the results in Figure 10-30. FIGURE 10-30: Summary of business indicators section AutoSave Off H Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 10 A A Wrap Text General ୯ Paste BIU 3 3 Merge & Center ▾ 3 % 9 v Undo 123 Clipboard FS Font E Alignment E Number fx A 13 Summary of key results 14 End-of-year cash on hand B Year 0 NA Year 2 Year 1 Year 3 SAR 222,750.00 SAR 334,770.00 SAR 254,145.00 Each year's End-of-year cash on hand value is echoed from cells in the spread- sheet body. 3-4 Calculations Section Your spreadsheet should calculate, by formula, the values shown in Figure 10-31. Calculated amounts are used later in the spreadsheet. FIGURE 10-31: Calculations section AutoSave Off Search File Home Insert Page Layout Formulas 12. Data Review View Automate Help Acrobat Arial 10 Paste BIU Y AA A 4 Wrap Text Merge & Center General K-% LA Alignment E Number Undo Clipboard 5 Font 123 EXV fx A 16 Calculations 17 Cost of living 18 Yearly salary or part-time wages 19 Gym dues paid B Year 0 NA Year 1 D Year 2 E Year 3 SAR 153,750.00 SAR 157,500.00 SAR 105,000.00 SAR 300,000.00 SAR 309,000.00 SAR 318,270.00 SAR 39,375.00 NA SAR 45,000.00 SAR 48,750.00 SAR 52,500.00 20 1. Cost of Living In any year that your uncle continues to work for the company, his cost of living is the amount shown in the Cost of living (not retired) field of the Constants section (C6:E6). But, if he chooses to retire, his cost of living will be SAR 56,250 less than the amount shown in C6:E6. 2. Yearly Salary or Part-time Wages If your uncle keeps working, his salary will increase each year (due to expected pay rises). The yearly percentage increases are shown in the Constants section. Assume that the salary earned in Year 1 would be 3% Decision Support System Fundamentals 381 30/06/2023 14:28

382 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 382 more than that earned in Year 0, salary earned in Year 2 would be 3% more than that earned in Year 1, and salary earned in Year 3 would be 2% more than that earned in Year 2. If your uncle retires, he will make the part-time wages shown in the Constants section. 3. Gym Dues Paid If your uncle leaves the gym, the dues are zero each year; otherwise, the dues are as shown in the Constants section. 3-5 Income Statement and Cash Flow Statement Section As in the previous example, this section begins with the cash on hand at the beginning of the year, followed by the income statement, and concluding with the calculation of cash on hand at the end of the year. The format is shown in Figure 10-32. FIGURE 10-32: Income statement and Cash flow statement section AutoSave Off H Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial <-10 A A = 群 Wrap Text General Paste BIU 3 Tu Merge & Center 3 %88 Undo Clipboard 1993 Font Б Alignment Б Number 141 141 fx 21 Income statement and Cash flow statement 22 Beginning-of-year cash on hand 23 Salary or part-time wages 24 Buyout income 25 Total cash inflow 26 Gym dues paid 27 Cost of living 28 Total costs 29 Net income 30 End-of-year cash on hand 31 32 33 Conditio Formatti Б B Year 0 Year 1 NA NA D Year 2 SAR 112,500.00 SAR 222,750.00 SAR 309,000.00 SAR 318,270.00 E Year 3 SAR 334,770.00 NA SAR 0.00 SAR 0.00 SAR 39,375.00 SAR 37,500.00 NA SAR 309,000.00 SAR 318,270.00 SAR 76,875.00 NA SAR 45,000.00 SAR 48.750.00 SAR 52,500.00 NA SAR 153,750.00 SAR 157,500.00 SAR 105,000.00 NA NA SAR 198,750.00 SAR 206,250.00 SAR 157,500.00 SAR 110,250.00 SAR 112,020.00 -SAR 80,625.00 SAR 112,500.00 SAR 222,750.00 SAR 334,770.00 SAR 254,145.00 . Beginning-of-year cash on hand: This amount is the cash on hand at the end of the prior year. Note that cash on hand at the end of the cur- rent year is SAR 112,500. Salary or part-time wages: This amount is a yearly calculation, which can be echoed here. Buyout income: This amount is the year's buyout amount if your uncle is retired that year. Buyout amounts are shown in the Constants section. Total cash inflow: This amount is the sum of salary or part-time wages and the buyout amount (if retired). 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 383 . . ● . Gym dues paid: This amount is a calculation, which can be echoed here. Cost of living: This amount is a calculation, which can be echoed here. Total costs: This amount is the sum of the Cost of living and Gym dues. paid (if a member). Net income: This amount is the income after costs and dues are paid. End-of-year cash on hand: This amount is the Beginning-of-year cash on hand plus the year's Net income. 3-6 Scenario Manager Analysis Set up Scenario Manager and create a Scenario Summary sheet. Your uncle wants to look at the following four possibilities: . retire now, stay in the gym ("Retire-In"). • retire now, leave the gym ("Retire-Out"). work three more years (that is, do not take any buyout bonuses), staying in the gym ("Delay-In") • work three more years, leave the gym ("Delay-Out"). You should enter the non-contiguous input cell ranges as follows: C10:E10, C11. The only output cell should be the Year 3 End-of-year cash on hand cell in the Summary of business indicators section. Your uncle knows that the highest Year 3 End-of-year cash on hand would be realized if he continues to work and cancels his gym membership. However, he wants to see what the End-of-year cash would be in the other scenarios. These will help him decide if he can retire, and when. The Scenario Summary (Figure 10-33), presents the four different options that your uncle is considering. The four scenarios are shown in columns C-F. Row 10. shows the amount of cash your uncle would expect to have at the end of Year 3 for each of these options. For many decisions, the choice isn't as simple as pick- ing the scenario with the highest (or lowest) value. Your uncle would like to retire early, and he would like to maintain his gym membership. Your decision model gives your uncle a projection for each of these scenarios. It will be up to him to decide how much cash on hand he is willing to sacrifice for the enjoyment of an early retirement. He will need to determine if he can afford to maintain the gym membership and take the early buyout. Decision Support System Fundamentals 383 30/06/2023 14:28

FIGURE 10-33: Scenario Summary File 92 AutoSave Off Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial Paste BIU Undo Clipboard E Font K25 fx 10 A A Y Wrap Text General Merge & Center Alignment Г 3 %9 Number B C D E 1 Scenario Summary 2 Retire In Retire Out Delay In Delay Out 4 Changing Cells: 5 Retired or Working, Year 1 R R W W 6 Retired or Working, Year 2 R R W W 7 Retired or Working, Year 3 R R W W 8 Stay in gym? N 9 Result Cells: 10 End-of-year cash, Year 3 SAR 96,375.00 SAR 242,625.00 SAR 445,655.40 SAR 591,905.40 11 REVIEW QUESTIONS 1. You can calculate End-of-year cash on hand by adding the Beginning-of-year cash on hand to what? a. Total costs b. Cost of living c. Gym dues d. Net income 2. What does NA stand for? a. No accounting b. Not applicable c. Net amount d. Number above 384 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 384 30/06/2023 14:28