Electric Car Deliverables - Business Decision Making - ثاني ثانوي

وزارة التعليم Ministry of Education 2024-1446 Electric Car Deliverables Ahmad wants to get through the next three years without incurring significant debt. In three years, the current notes payable will be nearly paid off, and Ahmad hopes the company will be profitable enough to start Phase 3 of its plan. Ahmad asks you to consider three scenarios for the three-year period from 2023 to 2025: Optimistic, Pessimistic, and Stable. The input values (B11, C12:E15) for these scenarios are as follows: . Optimistic: The cost of gasoline is Up, the claim rate in each of the three years is 1%, the cost reduction factor in each of the three years is 3%, there are 30 new charger locations each year, and the momentum effect each year is 7%. Neutral: The cost of gasoline is Stable, the claim rate in each of the three years is 5%, the cost reduction factor for each year is 2%, there are 20 new charger locations each year, and the momentum effect each year is 4%. Pessimistic: The cost of gasoline is Down, the claim rate in each of the three years is 10%, the cost reduction factor for each year is 1%, there are no new charger locations each year, and the momentum effect each year is 2%. You will use the Scenario Manager tool to evaluate these sets of inputs. You can access Scenario Manager by clicking on Scenario Manager under What-If Analysis on the Data ribbon (see Figure 14-13). FIGURE 14-13: Accessing Scenario Manager from the Data ribbon Business Decision Making S1 S2 S3.indb 481 ced 翻 Text to Columns What-If Forecast V Analysis Sheet Data Tools E 目 Group tungroup Scenario Manager... Goal Seek... Data Table... Subtotal utline H La The Electric Car Project 481 30/06/2023 14:29

Follow the same process that you used in Chapter 13 to build Optimistic, Neutral, and Pessimistic scenarios. Use B11, C12:E15 as your Changing cells. Remember to enter the Input values for each scenario into these cells on your worksheet before building the scenario. This way, Scenario Manager will populate the Scenario Values for you. Update the input values in the worksheet to the next scenario (see Figure 14-14) and repeat. FIGURE 14-14: Updating the input values in the worksheet AutoSave File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Get Data- T FonWe Eron Pictones Recent Sources Ramesh M-> Duettes & Commections Properties La Queries & Connections Get & Transform Data K85 BEY STONE Stocks Cancies EL Som Data Types FRe Sort & Filter Atwood Columns Data Tools Te Comments Share PEP What-Feat Unamop 2. Solver Data Analysis Sheet Forecast Outine Analys A B C D E 12 10 Inputs 11 Cost of gasoline (D/S/U) Guarantee claim rate 2022 2023 2024 2025 Scenario Values Enter values forach of the changing cells U NA NA NA 11 sest u NA 1% 1% 1% SCS12 0.01 13 Unit cost reduction factor NA 3% 3% 3% 14 Number of new charger locations 3 50512 0.01 NA 30 30 30 15 Units (Market momentum effect) NA 7% 7% SES12 0.01 7% 16 31 SCS13 201 17 Summary of key results 2022 2023 2024 2025 18 Net income NA 19 End-of-year cash on hand The owed NA SAR 1,439,370,000.00 SAR 2,345,327,133.60 SAR 7,466,369,260.17 SAR 3,750,000,000.00 SAR 3,750,000,000.00 SAR 3,750,000,000.00 25 344 042.866.40 SAR 25.377 673 482 Chapter 14 Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 482 7 Cancel Carefully review these values for each of the three scenarios (Click the Show but- ton to populate the Inputs section with the values in the scenario). Click Summary to generate a Scenario Summary worksheet (see Figure 14-15). FIGURE 14-15: Click Summary to generate the worksheet Scenario Manager Scenarios: Optimistic Neutral Pessimistic Changing cells: SBS11,$C$12:SE$15 Comment: ? X Add... Delete Edit... Merge... Summary... Show Close 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 Follow the same steps that you used in Chapter 13 to polish the data shown in your Scenario Summary report. See the before (Figure 14-16) and after (Figure 14-17) examples below. FIGURE 14-16: The unpolished Scenario Summary File AutoSave Off Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Business Decision Making S1 S2 S3.indb 483 Get Data L34 From Text/CSV From Picture Queries & Connections From Web Recent Sources Properties From Table/Range Existing Connections Refresh All Get & Transform Data Queries & Connections fx Stocks Currencies Data Types D 21 22 Z↓ Sort Filter Ad Sort & Filter 2 A B E 1 2 Scenario Summary 3 Current Values: Optimistic Neutral Pessimistic 5 Changing Cells: 6 $B$11 U 7 $C$12 1% 1% 5% 10% 8 $D$12 1% 1% 5% 10% 9 $E$12 1% 196 5% 10% 10 $C$13 3% 3% 2% 1% 11 $D$13 3% 3% 2% 1% 12 $E$13 3% 3% 2% 1% 13 $C$14 30 30 20 0 14 $D$14 30 30 20 0 15 $E$14 30 30 20 0 16 $C$15 7% 7% 4% 2% 17 $D$15 7% 7% 4% 2% 18 $E$15 7% 7% 4% 2% 19 Result Cells: 20 21 22 23 24 25 -SAR 754,078,090.22 -SAR 2,991,151,716.50 $E$18 SAR 7,466,369,260.17 SAR 7,466,369,260.17 $E$19 SAR 3,750,000,000.00 SAR 3,750,000,000.00 $E$20 SAR 25,377,673,606.23 SAR 25,377,673,606.23 SAR 39,838,173,240.62 SAR 44,245,512,863.90 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. SAR 3,750,000,000.00 SAR 3,750,000,000.00 عد H The Electric Car Project 483 30/06/2023 14:29

484 14 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 484 FIGURE 14-17: The polished Scenario Summary AutoSave off File Home Insert Page Layout Formulas Data Review View From Text/CSV From Web Get From Picture Search Automate Help Acrobat Recent Sources Queries & Connections Properties Cleat Reapply Refresh Stocks Currencies Z↓ Sort Filter Data From Table/Range Existing Connections AllEdit Links Advanced Get & Transform Data Queries & Connections Data Types Sort & Fitter L32 fx B 1235 6 7 8 Scenario Summary Changing Cells: Cost of gasoline (D/S/U) Guaranty claim rate 2023 Guaranty claim rate 2024 Optimistic Neutral Pessimistic B S D 1% 5% 10% 1% 5% 10% 9 Guaranty claim rate 2025 1% 5% 10% 10 Unit cost reduction factor 2023 3% 2% 1% 11 Unit cost reduction factor 2024 3% 2% 1% 12 Unit cost reduction factor 2025 3% 2% 1% 13 Number of new charger locations 2023 30 20 0 14 Number of new charger locations 2024 30 20 0 15 Number of new charger locations 2025 30 20 0 16 Market momentum effect 2023 7% 4% 2% 17 Market momentum effect 2024 7% 4% 2% 18 Market momentum effect 2025 7% 4% 2% 19 Result Cells: 20 Net income SAR 7,466,369,260.17 21 End-of-year cash on hand SAR 3,750,000,000.00 22 End-of-year debt owed SAR 754,078,090.22 SAR 2,991,151,716.50 SAR 3,750,000,000.00 SAR 25,377,673,606.23 SAR 39,838,173,240.62 SAR 44,245,512,863.90 SAR 3,750,000,000.00 23 Notes: Current Values column represents values of changing cells at 24 25 time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. You might find it useful to copy out Table 14-1 to record the results. TABLE 14-1: Electric Car Project results Optimistic 2023 Net income Debt to bank Cash on hand 2024 Net income Debt to bank Cash on hand 2025 Net income Debt to bank Cash on hand Neutral Pessimistic By 2025, Electric Car wants: . to see a positive net income for the year to owe nothing to the bank the company's cash on hand to exceed SAR 3.75 billion. This outcome would tell Ahmad that the three-phase plan is still viable. H 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 485 If this isn't possible, Ahmad could accept some amount owed to the bank, as long as the company was making money in 2025. Management would conclude that the company still has a good chance of succeeding. It is now time to present your findings to Ahmad 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. 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. In 2023, the optimistic cash on hand figure will be In 2024, the stable debt to bank figure will be In 2025, the pessimistic net income figure will be 8. There are two stages of analysis for you to complete in your report. First, answer the following question: In your opinion, is Ahmad's three-phase plan still viable? In your answer, consider all of the data for each scenario. If the plan isn't viable, Ahmad could accept some amount owed to the bank, as long as the company is making money in 2025. Management would con- clude that the company still has a good chance of succeeding. The Electric Car Project 485 30/06/2023 14:29

486 14 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 486 With this information in mind, answer the following question: How likely is it that the company can make it through 2025 without having to raise or borrow any new capital? Provide a detailed answer of at least one paragraph, explaining your reason- ing using evidence based on your results. 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 - ElectricCar - Report.docx. 30/06/2023 14:29