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

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 421 ance for driver behavior allows for a KPL of 50. Management decides to take advantage of this to help improve its overall fleet KPL. The question for the company's marketing managers is: How many of each kind of vehicle should the company try to make and sell in five years to maximize profits while complying with regulations? The company must meet the new KPL standard, but the company must be prof- itable as well—in fact, management wants the gross margin to be at least 30% of gross revenue in the coming years. Several of the company's senior marketing executives think that the market will change significantly in the coming years. Here is a summary of how their think- ing differs from the company's strategy as a whole: . Younger people are much more environmentally conscious than drivers. of previous generations. Younger people do not enjoy driving as much as their parents and grandparents do. They may prefer to hire a driver or taxi. Younger people have shown interest in driverless cars. When such cars become viable they will likely be electric, rather than gasoline powered. All of these factors argue for more sales of electric cars and smaller cars than the majority of marketing managers think is warranted. The smaller group of man- agers would like to see further analysis that examines their point of view. This analysis, they think, might suggest a need for greater changes in the company. Creating a Spreadsheet for Decision Support In this lesson, you will produce a spreadsheet that models the production mix of vehicles needed to meet new air-pollution standards. First, you will create a spreadsheet to model the base case. Then, you will create a second spreadsheet to model the extension case (which will be the analysis for the smaller group of managers). You will then prepare a report detailing your data analysis and rec- ommendations based on your findings. Scan the QR code to access the workbook (Chapter 12 data file.xlsx) you'll be using for the rest of this lesson. The Car Production Project 421 30/06/2023 14:29

422 12 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 422 DEFINITIONS Base case: A model of future performance that is conservative, based on what is most likely to happen according to data already held. Extension case: A way of modeling based on reasonable, informed speculation and prediction. Your spreadsheets should include the cells explained in the following pages. You will set up the spreadsheets before entering cell formulas. Your spreadsheets will also include decision constraints, which you will enter using Solver. Your spread- sheets should have the following sections: . Changing cells · Constants Calculations Income statement. Creating the Spreadsheet for the Base Case A discussion of each spreadsheet section follows. The discussion explains how to set up each section and explains the logic of the formulas in the section's cells. This time, you will need to set up the spreadsheet yourself. 1. Changing Cells Section Your spreadsheet should have the Changing cells shown in Figure 12-1. These show the number of vehicles that the company should plan to pro- duce. Enter a "1" for each of these now. This will give Solver a starting point to calculate from. These values will change to show the recommended pro- duction quantities. FIGURE 12-1: Changing cells section File AutoSave Off Home Insert Page Layout Formulas Data Review 20 Arial Paste BIU 3 3 Undo Clipboard F13 A X✓ fx 1 Car Production Font 10 AA B EX 2 3 Changing cells 4 Number of trucks 5 Number of SUVs 1 1 6 Number of sedans 7 Number of compacts 8 Number of electrics 1 9 Constants 30/06/2023 14:29

QUICK TIP You can force Solver to solve with integer values when specifying the constraints by using the Int operator in the Add Constraint window. You will ask Solver to compute how many of each type of vehicle to make and sell five years from now, in order to meet the KPL standard and to maxi- mize gross profit. 2. Constants Section Enter the constants shown in Figure 12-2 into your worksheet. Use the same cell addresses so your worksheet is consistent with the examples shown in this chapter. FIGURE 12-2: Constants section AutoSave Off File Home Insert Page Layout Formulas Data Review Vie وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 423 Arial 10 A A R Paste B I A Undo Clipboard Б Font H39 fx A B 10 Constants 11 Average selling prices: 12 Trucks Б SAR 225,000.00 13 SUVs SAR 206,250.00 14 Sedans SAR 150,000.00 15 Compacts SAR 112,500.00 16 Electrics SAR 131,250.00 17 Average cost of manufacture: 18 Trucks SAR 112,500.00 19 SUVS SAR 105,000.00 20 Sedans SAR 123,750.00 21 Compacts SAR 101,250.00 22 Electrics 23 Average KPL: 24 Trucks SAR 138,750.00 25 SUVs 26 Sedans 27 28 12188 10 12 14 20 36 Compacts Electrics 29 Max production capacity: 30 Trucks 31 SUVs 32 Sedans 33 Compacts 34 Electrics 35 . . 30,000 90,000 50,000 20,000 10,000 Average selling prices: The selling prices expected in five years are shown in Table 12-1. Average cost of manufacture: The costs of manufacture expected in five years are shown in Table 12-1. Average KPL: The expected KPLs for each type of vehicle in five years are shown in Table 12-3. Max production capacity: The production limits in five years are shown in Table 12-2. The Car Production Project 423 30/06/2023 14:29

QUICK TIP When working with spreadsheets, numeric values should ONLY be entered in Constants and Inputs. All other cells must either calculate a value (using a formula) or echo a value (copy the value from another cell reference |=F34). CHECK YOUR PROGRESS Your worksheet should calculate the values shown in Figure 12-3. If your worksheet shows different results, check your 3. Calculations Section You will set your worksheet up to calculate values based on the inputs you've provided. Each of these calculations must use a formula—do not type num- bers into cells B38:B50. Revenue: Revenue is the product of the number of vehicles sold and the expected selling price (Number of vehicle type sold × Selling price of the vehicle type). Cost of manufacture: Costs are the product of the number of each type of vehicle made and the expected cost of manufacture for that type of vehicle (Number of trucks made × Cost of manufacture TRUCK). Total number of autos made: This value is the sum of all the vehicles made (B4:B8). ☑ Fleet KPL: This value is the weighted average KPL of all the vehicles made and sold (Number of trucks sold x Truck's average KPL) + (Number of SUVs sold × SUV's average KPL) + (Number of Sedans sold × Sedan's average KPL) + (Number of Compacts sold × Compact's average KPL) + (Number of Electric cars sold × Electric car's average KPL) / Total number of vehicles made (B4:B8). FIGURE 12-3: Calculations section AutoSave off ☑ File Home Insert Page Layout Formulas Data Review Vi 3 AA A- 101 1014 formulas carefully. 424 12 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 424 Arial 10 Paste BIU 3 Undo Clipboard Б Font 164 fx A 36 Calculations 37 Revenue 38 Trucks 39 SUVS B SAR 225,000.00 SAR 206,250.00 40 Sedans SAR 150,000.00 41 Compacts SAR 112,500.00 42 Electrics SAR 131,250.00 43 Cost of manufacture 44 Trucks SAR 112,500.00 45 SUVS SAR 105,000.00 46 Sedans SAR 123,750.00 47 Compacts SAR 101,250.00 48 Electrics SAR 138,750.00 49 Total number of autos made 5 50 Fleet KPL 18.40 30/06/2023 14:29

CHECK YOUR PROGRESS If you've set your calculations up properly, your Income Statement values should match those shown. If not, go back through this section and check your formulas and cell references carefully. 4. Income Statement Section Next, you will create a basic income statement that shows your revenues, cost of production and the Gross margin. As with the other calculations, you must use a formula in cells B53:B55 and B57 (do not type in numbers). An explanation of the line items follows. Total revenue: This is the sum of the revenues for all vehicles sold. These revenues are values from the Calculations section (B38:B42). • Total cost of manufacture: This is the sum of the costs of manufacture for all vehicles produced. These costs of manufacture are values from the Calculations section (B44:B48). . Gross margin: This is the difference between total revenue and total cost of manufacture (B53-B54). Gross margin percentage: This is calculated as (Total Revenue - Total Cost of Manufacture) / Total Revenue (B55/B53). The company wants a gross margin of at least 30%. FIGURE 12-4: Income statement section AutoSave Off File Home Insert Page Layout Formulas Data Review Vi Arial <10 AA Paste BIU 3 3 Undo Clipboard FE Font F66 fx A B 52 Income statement 53 Total revenue SAR 825,000.00 54 Total cost of manufacture SAR 581,250.00 55 Gross margin 56 57 Gross margin percentage SAR 243,750.00 30% 58 50 101 100 QUICK TIP The marketing department's goals for vehicle sales should be included as constraints in your model. This way, Solver will try to find a solution that meets these objectives. 5. Constraints and Running Solver Next, you must determine the constraints for the model. The marketing man- agers think that 20,000 trucks, 20,000 SUVs, 20,000 sedans, 20,000 com- pacts, and 8,000 electric vehicles will be made and sold five years from now. Before starting to build the model in Solver, work out the constraints by hand. You can create a simple table like the one shown in Table 12-4. You will need to figure out the cell reference, the relationship, and value for each constraint. Take your time and do this carefully. These constraints are a criti- cal part of your decision model. وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 425 The Car Production Project 425 30/06/2023 14:29

QUICK TIP Remember that you do not want Solver to compute solutions that include a fraction of a vehicle. TABLE 3-4: Table of base case constraints Variable Number of Trucks Sold Number of SUVs Sold Number of Sedans Sold Number of Compacts Sold Number of Electrics Sold Fleet KPL (per government regulation) 426 12 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 426 Gross Profit Margin (target) Number of Trucks Made less than Truck Capacity Number of SUVs Made less than SUV Capacity Number of Sedans Made less than Sedan Capacity Number of Compacts Made less than Compact Cap Cell Reference <=> Int Constraint Number of Electrics Made less than Electric Capacity Ensure Number of each vehicle made is Integer To start the Solver tool in Excel, first click the Data tab (see Figure 12-5). FIGURE 12-5: Select the Data tab AutoSave Off File Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat From Text/CSV From Web From Picture Recent Sources Get DataFrom Table/Range Existing Connections Get & Transform Data M18 XV fx A 8 Refresh All Queries & Connections Properties Edit Links Queries & Connections Stocks Currencies Data Types Next, select Solver from the Analysis menu (see Figure 12-6). FIGURE 12-6: Locate Solver in the Analysis menu Sort Filter Clear Reapply Advanced Sort & Filter Text to Columns Data Tools What-If Forecast Analysis Sheet Forecast E G Comments Share 目 Group. ?, Solver Data Analysis ru Ungroup Subtotal Outline M N P 121 Analysis D 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 427 If the Solver option isn't visible, you may need to install it first. You can find out how to do this in Chapter 11. Start by setting your objective. This is the key value that you want to maxi- mize, minimize, or achieve. For this project, management wants to maxi- mize its gross margin. Put the cell address for Gross margin (B55) in the Set Objective box (see Figure 12-7). FIGURE 12-7: Enter the cell address for Gross margin Solver Parameters Set Objective: To: Max By Changing Variable Cells: Subject to the Constraints: B55 Min Value Of: 0 Add I-> Click on the Max button. Then, you need to enter the Changing Cell addresses in the box marked By Changing Variable Cells. For this project, the number of vehicles made and sold are going to change based on the constraints we set up. These appear at the top of your worksheet (B4:B8). Under Subject to the Constraints, enter the constraints you identified in Table 12-4. You will enter each of your constraints individually. Start by click- ing the 'Add' button (as shown in Figure 12-8). The Car Production Project 427 30/06/2023 14:29

FIGURE 12-8: The Solver Parameters window Solver Parameters 428 12 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 428 Set Objective: To: Max By Changing Variable Cells: B4:B8 Subject to the Constraints: B55 Min ◇ Value Of: 0 Make Unconstrained Variables Non-Negative Select a Solving Method: Solving Method GRG Nonlinear Add Change Delete Reset All Load/Save Options Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve 1 <- Close A new dialog box will appear (see Figure 12-9 below). For each constraint you will need to enter the cell address, the operation, and the value of the constraint). FIGURE 12-9: The Add Constraint dialog box Add Constraint Cell Reference: t <= OK Add > Constraint: Cancel - X Let's start with the number of trucks (Figure 12-10). Management has set an objective of selling at least 20,000 trucks. To add this as a constraint enter the cell address for the number of trucks (B4) in the Cell Reference box. Because management wants this number to be at least 20,000 select the >= operation. Finally, type "20000" in the Constraint box. 30/06/2023 14:29

FIGURE 12-10: Adding the constraint to sell at least 20,000 trucks Add Constraint Cell Reference: B4 OK 1 >= Constraint: 20000 Add Cancel ↑ CHECK YOUR PROGRESS Compare your constraints to the partial solution shown. Consider each constraint carefully. Are they set up the way you expected? If not, highlight the constraint you would like to change and click the Change button to edit it. Because you have other constraints to enter, click the Add button. A new Add Constraint window will appear. Continue to add the constraints from Table 12-4. When you've entered the final one, click OK instead of Add. This let's Solver know that you've finished, and it will take you back to the main Solver window. FIGURE 12-11: Complete setup for the Base Case Solver Model Solver Parameters Set Objective: To: Max By Changing Variable Cells: $B$4:$B$8 Subject to the Constraints: $B$4 <= $B$30 $B$4 >= 20000 >: $B$4:$B$8 integer = $B$5 <= $B$31 $B$5 >= 20000 $B$50 >= 16 $B$57 >= 0.3 $B$6 <= $B$32 $B$620000 $B$7 <= $B$33 $B$7 >= 20000 $B$55 Min O Value Of: 0 ☑Make Unconstrained Variables Non-Negative Add Change Delete Reset All Load/Save ↑ I-> وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 429 Select a Solving Method: Solving Method GRG Nonlinear Options Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close The Car Production Project 429 30/06/2023 14:29

QUICK TIP Use this setting because the solution is non-linear (the output is not proportional to the inputs). Now you are ready to let Solver work out a solution. Check the Make Unconstrained Variables Non-Negative box. This tells Solver that you don't want a solution with a negative number of vehicles produced or a negative gross margin. Set Solver for GRG Nonlinear mode. Click the Solve button to run Solver. It will quickly examine your worksheet and try to find a solution that meets your constraints. When it does, you will see the Solver Results window (below). Click on the Answer Report when Solver finds a solution, leave Keep Solver Solution checked, and click OK. FIGURE 12-12: Ask for the Answer Report in the Solver Results window Solver Results The Objective Cell values do not converge. Reports Answer Keep Solver Solution Restore Original Values Return to Solver Parameters Dialog Outline Reports OK Cancel Reports Creates the type of report that you specify, and places each report on a separate sheet in the workbook CHECK YOUR PROGRESS Figure 12-13 shows a partial solution for the Base Case Model. Compare your solution to these values. If yours differ significantly, go back through the case and check your work carefully. Most errors are due to inaccurate constraint settings in the Solver Parameters. FIGURE 12-13: Partial solution for the Base Case Model AutoSave Off File Home Insert Page Layout Formulas Data Review Vi Arial 10 A A Paste BIU 3 3 A 2 Undo Clipboard Б F12 XVfx A 1 Car Production Font B 3 四 2 3 Changing cells 4 Number of trucks 20,000 5 Number of SUVs 30,000 6 Number of sedans 20,000 7 Number of compacts 20,000 8 Number of electrics 10,000 9 Constants 430 12 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 430 Save Scenario... 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 When you finish, print the entire spreadsheet and the Answer Report sheet. Save the file using the Save command in the File tab, using the format YourName - CarProject.xlsx (Figure 12-14). FIGURE 12-14: Saving your file Good afternoon AutoSave Off File Home Insert Page Layout Formulas Data Re Home New Arial 10 A A New Paste BIU I 2 Open 3 Undo Clipboard E Font LZ F12 : XV fx Info 1557 A B Business Decision Making S1 S2 S3.indb 431 Save Save As Blank workbook Save as Adobe PDF Search To prepare for the extension case you will create a copy worksheet. To do this: of your base case . Click on the tab at the bottom of your worksheet. Right-click (see Figure 12-15) and Rename your worksheet "Base Case". FIGURE 12-15: Renaming your worksheet 11 Average selling prices: 12 Trucks SAR 225,000.00 13 SUVs SAR 206,250.00 14 Sedans Insert... SAR 150,000.00 15 Compacts Delete 16 Electrics SAR 112,500.00 SAR 131,250.00 17 Average cost of mRename 18 Trucks 19 SUVS 20 Sedans 21 Compacts Move or Copy..... View Code Protect Sheet... SAR 112,500.00 SAR 105,000.00 SAR 123,750.00 22 Electrics 23 Average KPL: SAR 101,250.00 SAR 138,750.00 Tab Color Hide 24 Trucks 25 SUVS 26 Sedans 27 Compacts Data File Accessibility: Good to an Unhide Select All Sheets 0212 14 The Car Production Project 431 30/06/2023 14:29

432 12 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 432 . Right-click the Base Case tab again, but this time select Move or Copy (as shown in Figure 12-16). FIGURE 12-16: Select Move or Copy 11 Average selling prices: 12 Trucks SAR 225,000.00 13 SUVS SAR 206,250.00 14 Sedans Insert... SAR 150,000.00 15 Compacts Delete 16 Electrics SAR 112,500.00 SAR 131,250.00 17 Average cost of m Rename 18 Trucks Move or Copy... SAR 112,500.00 19 SUVS SAR 105,000.00 View Code 20 Sedans SAR 123,750.00 21 Compacts Protect Sheet... SAR 101,250.00 22 Electrics SAR 138,750.00 Tab Color 23 Average KPL: 24 Trucks 25 SUVS 26 Sedans 27 Compacts Base Case Hide Unhide Select All Sheets 0212 14 Accessibility: Good to an Select (move to end). This will put the new copy of your worksheet to the right of your existing sheet. Click on the Create a copy box, then press OK (see Figure 12-17). FIGURE 12-17: Creating a copy of the Base Case worksheet 11 Average selling prices: 12 Trucks 13 SUVS 14 Sedans 15 Compacts Move or Copy Move selected sheets To book: 16 Electrics Chapter 3 data file 1.xlsx 17 Average cost of m Before sheet: 18 Trucks 19 SUVS 20 Sedans 21 22 Compacts Electrics 23 Average KPL: 24 Trucks 25 SUVS 26 Sedans 27 Compacts ase Case (move to end) Create a copy Base Case ibility Good to go. ? OK Cancel A new worksheet named Base Case (2) will appear. This is a copy of your Base Case. Right-click this tab and rename the worksheet "Extension Case" (see Figure 12-18). 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 FIGURE 12-18: Create the new worksheet 17 Average cost of manufacture: SAR 112,500.00 18 Trucks 19 SUVs 20 Sedans 21 Compacts SAR 105,000.00 SAR 123,750.00 SAR 101,250.00 22 Electrics 23 Average KPL: SAR 138,750.00 Business Decision Making S1 S2 S3.indb 433 24 Trucks 25 SUVs 26 Sedans 27 Compacts Base Case Base Case (2) Accessibility: Good to go FIGURE 12-19: Rename the new worksheet 17 Average cost of manufacture: 14 0212 18 Trucks SAR 112,500.00 19 SUVS SAR 105,000.00 20 Sedans SAR 123,750.00 21 Compacts SAR 101,250.00 22 Electrics SAR 138,750.00 23 Average KPL: 24 Trucks 25 SUVS 26 Sedans 27 Compacts 2212 10 12 14 20 Base Case Extension Case Answer Report Accessibility: Good to go Creating the Spreadsheet for the Extension Case Next, you will work on the extension case (be sure that you've selected Extension Case tab at the bottom of the spreadsheet). A small group of the company's senior marketing executives think that compacts and electric cars will be more in demand in five years, and that production capacity should be set at 80,000 elec- tric vehicles and 30,000 for compact cars. There are a fixed number of produc- tion lines, so this change would mean that fewer of the other kinds of vehicles could be made. The revised maximum capacities would be 20,000 for trucks, 50,000 for SUVs, and 20,000 for sedans. The reduction in production capacities for trucks, SUVs, and sedans means that their associated fixed costs would be spread over a smaller number of cars being made. This would increase the expected unit cost of manufacture for these types of vehicles. The expected unit costs of manufacture would be SAR 131,250 for trucks, SAR 120,000 for SUVs, and SAR 138,750 for sedans. The effect would be the opposite for compacts and electric cars. As the number produced increases, the associated fixed costs are spread across a larger number of cars. The expected unit cost of manufacture for electric vehicles would decrease to SAR 108,750, and the unit cost for compacts would be SAR 97,500. The Car Production Project 433 30/06/2023 14:29

CHECK YOUR PROGRESS With these changes, the Constants section should look like Figure 12-20. FIGURE 12-20: Constants section for extension case AutoSave On File Home Insert Page Layout Formulas Data Review X Cut Arial 10 A A Copy Paste BIU Format Painter Clipboard Font 120 434 12 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 434 10 Constants 11 Average selling prices: 12 Trucks B SAR 225,000.00 13 SUVS SAR 206,250.00 14 Sedans SAR 150,000.00 15 Compacts SAR 112,500.00 16 Electrics SAR 131,250.00 17 Average cost of manufacture: 18 Trucks SAR 131,250.00 19 SUVS SAR 120,000.00 20 Sedans SAR 138,750.00 21 Compacts SAR 97,500.00 22 Electrics 23 Average KPL 24 Trucks SAR 108,750.00 10 25 SUVS 12 26 Sedans 14 27 Compacts 20 28 Electrics 36 29 Max production capacity: 30 Trucks 20,000 31 SUVS 50,000 32 Sedans 20,000 33 Compacts 30,000 34 Electrics 80,000 Expected selling prices and KPLs would remain the same. The gross margin per- centage goal of 30% would remain the same. As with the base case, before you start working with Solver you should work out the constraints by hand, using Table 12-5 as a guide. You will need to determine the cell reference, the relationship, and value for each constraint. As with the base case, these values will be used to build your Solver model. TABLE 12-5: Extension case constraints Variable Number of Trucks Sold Number of SUVs Sold Number of Sedans Sold Number of Compacts Sold Number of Electrics Sold Fleet KPL (per government regulation) Gross Profit Margin (target) Trucks Made less than Truck Capacity SUVS Made less than SUV Capacity Sedans Made less than Sedan Capacity Compacts Made less than Compact Cap Electrics Made less than Electric Capacity Ensure Number of each vehicle made is Integer Cell Reference <= > Int Constraint 30/06/2023 14:29

Start Solver in the Extension Case. Because this is a new set of data you will need to enter the constraints again (Solver will not copy the old constraints that you used in your Base Case). Follow the same steps that you did with the Base Case. Enter the constraints and check them carefully. Then, run Solver and ask for the Answer Report when Solver finds a solution that satisfies the constraints. CHECK YOUR PROGRESS Check your Solver constraints against the partial solution shown in Figure 12-21. If your values are different, carefully review your worksheet, cell addresses and constraints. FIGURE 12-21: Partially complete setup for the Extension Case Solver model X Solver Parameters Set Objective: To: Max By Changing Variable Cells: $B$4:$B$8 $B$55 Min Value Of: 1 CHECK YOUR PROGRESS Subject to the Constraints: $B$4 <= $B$30 $B$4 >= 10000 $B$4:$B$8 integer = $B$5 <= $B$31 $B$5 >= 10000 $B$50 >= 16 $B$57 >= 0.3 $B$6 <= $B$32 $B$6 >= 10000 $B$7 <= $B$33 $B$7 >= 20000 Make Unconstrained Variables Non-Negative Select a Solving Method: Solving Method GRG Nonlinear Add Change Delete Reset All Load/Save Options Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help FIGURE 12-22: Partial solution for the Extension Case Model Check that your calculations for the Changing cells match the ones in Figure 12-22. If the values are different, carefully review your work so far and correct any errors. AutoSave off File Home Insert Page Layout Formulas Data Review View Autor From Text/CSV From Picture Queries & Connection From Web Recent Sources Existing Connections Get & Transform Data Properties Refresh All Edit Links Queries & Connections XVfx Get - Data From Table/Range 614 وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 435 1 Car Production 2 3 Changing cells 4 Number of trucks 5 Number of SUVS 6 Number of sedans 7 Number of compacts 8 Number of electrics 20,000 50,000 10,000 20,000 61,112 Solve Close When you are finished, save your spreadsheet and exit Excel. The Car Production Project 435 30/06/2023 14:29

REVIEW QUESTIONS 436 12 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 436 1. Select the correct definition for the base case. a. A model of future performance that is conservative, based on what is most likely to happen according to data already held. b. A model of present performance that is optimistic, based on what is hap- pening according to data already held. c. A model of future performance that is pessimistic, based on what is least likely to happen according to data already held. d. A model of past performance that is realistic, based on what actually happened according to data already held. 2. Which of the following should be included in the income state- ment section? a. Revenue b. Average KPL c. Gross margin d. Max production capacity 3. Which section should come first in your spreadsheets? a. Constants b. Calculations c. Income statement d. Changing cells 4. What is the correct order for the spreadsheets included in Solver? a. Constants, Calculations, Changing Cells, Income Statement b. Changing cells, Constants, Calculations, Income Statement c. Calculations, Income Statement, Constants, Changing Cells d. Changing Cells, Constants, Income Statement, Calculations 30/06/2023 14:29