Using Solver - Business Decision Making - ثاني ثانوي

11 Using Microsoft Excel Solver Decision support systems (DSS) help people make decisions, as you learned in Chapter 10. Chapter 11 teaches you how to use Solver, one of Excel's built-in decision support tools. For some business problems, decision makers want to know the best solution. Usually that means maximizing a variable or minimizing another variable. For example, you might want to maximize net income or minimize total costs. This optimization is subject to constraints, which are rules that must be observed when solving a problem. Solver computes answers to such problems. LEARNING OBJECTIVES Once you have completed this chapter, you should be able to: 1 2 3 4 Demonstrate how to build a Solver spreadsheet Apply your knowledge of Solver to manage constraints, objectives, and variables in real-world examples Interpret output in Solver where multi- ple variables and shared resources are a factor Identify common issues users expe- rience when working with Solver X. Book1 - Excel Tell me what you want to do K Oct 30-Nov 31-Dec وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 387 ww 595 690 Using Microsoft Excel Solver 387 29.75 34.5 85 98.57 4200 30/06/2023 14:28

Lesson 1 Chapter 11 www.ien.edu.sa Using Solver Imagine a company must set a production schedule for its products, each of which has a different profit margin (the selling price minus the costs of labor and materials). At first, you might assume that the company will maximize produc- tion of all products to optimize net income. However, a company typically can- not make and sell an unlimited number of its products. One constraint that affects production is the shared resource problem. For exam- ple, several products in a manufacturer's line might require the same raw mate- rials, which are in limited supply. Similarly, the manufacturer might require the same machines to make several of its products. In addition, a limited pool of skilled workers might be available to make the products. Management policies sometimes impose constraints. For example, management might decide that the company must have a broader product line. As a conse- quence, a certain production quota (a goal set by a business or organization for the quantity of a given product it needs to produce) must be met for several products, regardless of profit margins. Thus, management must find a production schedule that will optimize profit given the constraints. Optimization programs like Solver look at each combination of products, one after the other, ranking each combination by profitability (the amount of money that can be made). Then the program reports the most profitable combination. 388 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 388 DEFINITION Optimization: A continuous process of identifying the best possible outcomes given a particular set of inputs and constraints. 30/06/2023 14:28

1-1 Adding Solver to the Excel Ribbon Before you can use Solver, it must be installed in Excel. To see if it is, start Excel. Click the Data tab in the Ribbon. If you see a group on the right side of the menu named Analysis that contains Solver, Solver has been installed. If Solver is not visible, do the following: . Click the File tab. Click Options. The Excel Options window should appear. In the menu on the left, click Add-Ins to display utilities you can add to Excel. FIGURE 11-1: The Add-ins menu Excel Options General View and manage Microsoft Office Add-ins. Formulas Data Add-ins Proofing Save Name Location Type The Add-ins tab Language Accessibility Advanced Customize Ribbon Quick Access Toolbar Add-ins Trust Center Active Application Add-ins Acrobat PDFMaker Office COM Addin Inactive Application Add-ins Analysis ToolPak Analysis ToolPak-VBA Date (XML) Euro Currency Tools Microsoft Actions Pane 3 Microsoft Data Streamer for Excel Microsoft Power Map for Excel Microsoft Power Pivot for Excel Solver Add-in Document Related Add-ins No Document Related Add-ins Disabled Application Add-ins C\Program Files (x86)\Adobe\Acrobat DC\PD COM Add-in Excel Add-in Action C:\Program Files\Microsoft Office\root\Office Excel Add-in C:\Program Files\Microsoft Office\root\Office C:\Program Files\Common Files\Microsoft Sha C:\Program Files\Microsoft Office\root\Office Excel Add-in XML Expansion Pack C:\Program Files\Microsoft Office\root\Office COM Add-in C:\Program Files\Microsoft Office\root\Office COM Add-in C:\Program Files\Microsoft Office\root\Office COM Add-in C:\Program Files\Microsoft Office\root\Office Excel Add-in The Manage drop-down menu وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 389 Add-in: Publisher: Acrobat PDFMaker Office COM Addin Adobe Inc. Compatibility: No compatibility information available Location: C:\Program Files (x86)\Adobe\Acrobat DC\PDFMaker\Office\x64\PDFMOfficeAddin.dll Description: Acrobat PDFMaker Office COM Addin Manage: Excel Add-ins Go... OK Cancel At the bottom of the screen, set the Manage drop-down box to Excel Add-ins and click the Go button. Using Microsoft Excel Solver 389 30/06/2023 14:28

The Add-ins window appears (Figure 11-2). Click the box for Solver Add-in. Then click the OK button to close the window and return to the Ribbon. FIGURE 11-2: The Add-ins window Add-ins ? Add-ins available: Analysis ToolPak OK Analysis ToolPak - VBA Euro Currency Tools Cancel Solver Add-in Solver Add-in Tool for optimization and equation solving Browse... Automation... Х . If you click the Data tab again, you should see that the Analysis group contains Solver (Figure 11-3). FIGURE 11-3: Solver is now available to use in your Data tab File AutoSave off Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat From Text/CSV From Picture From Wet Recent Sources Get Data- From Table/Range Existing Connections Refresh All- Queries & Connections Properties L Get & handform Data Gaeries & Comedian A2 fx Changing Cells 390 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 390 Comments Share Group-> Q Solver 10 Stocks Currencies #L Sort Filler Advanced Text to Column What- Forecast Analyus Sheet Data Types Sort & Fi Da Took Foreca Ugroup Subtotal Outine Data Analys Б Analy To use Solver, set up a model of the problem. Include the factors that can vary, the constraints on how much they can vary, and the goal—that is, the value you are trying to optimize. Usually, you want to maximize net income or minimize total costs. Solver then computes the best solution. 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 391 1-2 Setting Up a Spreadsheet Skeleton Suppose your company makes two products-basketballs and footballs. Assume that you will sell all the balls you produce. To maximize net income, you want to know how many of each kind of ball to make in the coming year. Making each kind of ball requires a certain number of working hours, and each ball has a different cost of raw materials. Because you have only a limited num- ber of workers and machines, you can devote a maximum of 40,000 hours of time to production, which is a shared resource. You do not want the machines to be idle, however. You should not have more than 1,000 hours of downtime in a year, so machines should be used for at least 39,000 hours. DEFINITION Shared resource: A component part of a product, or something needed to make a product, that is also a part or is used in the production of another product. Marketing executives say that you should not make more than 60,000 basket- balls or fewer than 30,000. They say that you should make at least 20,000 foot- balls but not more than 40,000. The marketing team also says you should produce at least as many basketballs as footballs. What would be the best production plan? You can set up this problem in Solver. The spreadsheet sections are discussed in the pages that follow. Scan the QR code to access the workbook (Chapter 11 data file 1.xlsx) you'll be using for the rest of this lesson. 1. Changing Cells Section The Changing cells section contains the variables Solver is allowed to change while it looks for the solution to the problem. Figure 11-4 shows the skeleton of this spreadsheet section and the values you should enter. The changing cells are for the number of basketballs and footballs to be made and sold. The changing cells are like input cells, except that Solver plays "what if?" with the values, trying to maximize or minimize. In this case, you want Solver to maximize net income. Using Microsoft Excel Solver 391 30/06/2023 14:28

FIGURE 11-4: Changing cells section showing the number of basketballs and footballs sold ☑AutoSave Off File Home Insert Page Layout Formulas Data Review View Aut Arial 11 A A Paste BIU く A T Undo Clipboard Г K19 : XV fx A 1 Chapter 11 Lesson 1 2 Changing cells 3 Number of basketballs sold 4 Number of footballs sold Font B C D 1 1 Alig QUICK TIP You should format cells in the Constants section for currency or for two decimal place numbers. You should enter a number in the changing cells each time you set up a problem, before running Solver. It is customary to enter 1s in the changing cells, as shown in Figure 11-4. Solver will change these values when the pro- gram is run. 2. Constants Section Your spreadsheet should also have a section for values that will not change. Figure 11-5 shows the Constants section values that you should enter. 392 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 392 FIGURE 11-5: Constants section AutoSave Off File Home Insert Page Layout Formulas Data Review View Automate H Arial 10 A A = ab Wr Paste BIU v A 三 Me Undo Clipboard G18 XVfx A 6 Constants 7 Basketball selling price 8 Football selling price 9 Number of hours to make a basketball 10 Number of hours to make a football 11 Cost of labor - 1 machine hour 12 Cost of materials - 1 basketball 13 Cost of materials - 1 football Font 2 B SAR 52.50 SAR 41.25 0.50 0.30 SAR 37.50 SAR 7.50 SAR 4.69 Alignment C D 30/06/2023 14:28

. . Selling price: The selling price for a single basketball and a single football. Number of hours: The time needed to make a basketball and a football. Note, for example, that a ball-making machine can produce two basket- balls in an hour. Cost of labor: A worker makes a ball using a ball-making machine. A worker is paid SAR 37.50 for each hour of work at the machine. . Cost of materials: The costs of raw materials for a basketball and a football. Notice that the profit margins for the two products are not the same. They have different selling prices and different inputs (raw materials and hours to make), and the inputs have different costs per unit. Also note that you can- not tell from the data how many machine hours of the shared resource will be devoted to basketballs and how many will be devoted to footballs. This is because you don't know in advance how many basketballs and footballs will be made. 3. Calculations Section In the Calculations section, you will calculate intermediate results that will be used in the spreadsheet body and as constraints. Before entering formulas, format the cells in the Calculations section for two decimal places. Figure 11-6 shows the skeleton and formulas you should enter. FIGURE 11-6: Cell formulas in the Calculations section QUICK TIP Constants in the Excel cell formulas in Figure 11-6 are ☑AutoSave Off referred to by their cell addresses. Use the cell File Home Insert Page Layout Formulas Data Review View Automate address of a constant rather X than hard-coding a Arial 10 A A ab W number in the Excel Paste BIU A Me Alignment expression. If the number must be changed later, you will have to change it only in the Constants section cell, not in every cell formula in which you used the value. G22 وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 393 Undo Clipboard Font : XV fx A 15 Calculations 16 Total basketball hours used 17 Total football hours used 18 Total machine hours used (BB + FB) =B3*B9 =B4*B10 =B16+B17 B C D Using Microsoft Excel Solver 393 30/06/2023 14:28

QUICK TIP Do not calculate the amounts shown in the changing cells-Solver will compute those numbers. Also, notice that you can use the changing cell addresses in your formulas. When you do, you assume Solver has inserted the optimal values in each changing cell; your expression makes use of those numbers. • Total basketball hours used: The number of machine hours needed to make all basketballs. This is calculated as Total = Number of basketballs × Hours needed for manufacture (B3*B9) and is computed in cell B16. Cell B9 contains the constant for the hours needed to make one basket- ball. Cell B3, a changing cell, contains the number of basketballs made. Currently, this cell shows one ball, but that number will change when Solver works on the problem. . Total football hours used: The number of machine hours needed to make all footballs is calculated the same way. Total machine hours used (basketballs, BB, and footballs, FB): The number of hours needed to make both kinds of balls (cell B18) will be used in the constraints. This value is the sum of the hours calculated for making footballs and basketballs. Figure 11-7 shows the calculated values after Excel evaluates the cell formu- las (with 1s in the changing cells). FIGURE 11-7: Cell values in the Calculations section AutoSave Off File Home Insert Page Layout Formulas Data Review View Automate Arial 10 AA ab w Paste BIU v A EEEEMe Alignment Undo Clipboard Font G22 ✓ fx A 15 Calculations 16 Total basketball hours used 17 Total football hours used 18 Total machine hours used (BB + FB) 19 B 0.50 0.30 0.80 C D QUICK TIP Income statement cells should be formatted as currency and two decimal places. 4. Income Statement Section The target value is calculated in the Income statement section of the spread- sheet. This is the value that Solver has predicted to optimize results. Figure 11-8 shows the skeleton and formulas that you should enter. 394 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 394 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 395 FIGURE 11-8: Cell formulas in the Income statement section ☑AutoSave off File Home Insert Page Layout Formulas Data Review View Automate He Arial 12 A A ab Wra Paste BIU A ▾ Mer Alignment Undo Clipboard Font F32 : X✓ fx A B C D 20 Income statement 21 Basketball revenue (sales) 22 Football revenue (sales) =B3*B7 =B4*B8 23 Total revenue =B21+B22 24 Basketball materials cost =B3*B12 25 Football materials cost 26 Cost of machine labor 27 Total cost of goods sold 28 Net income =B4*B13 =B18*B11 =SUM(B24:B26) =B23-B27 . ☑ Revenue: The values in cells B21 and B22 equal the number of balls sold multiplied by the respective unit selling price. Therefore, the calculation for B21 is Number of basketballs sold × Basketball selling price (B3*B7), while the total for B22 is Number of footballs sold x Football selling price (B4*B8). The number of balls sold is shown in the Changing Cells sec- tion, and the selling prices are constants. . Total revenue: The sum of basketball and football revenue. . Materials cost: The values in cells B24 and B25 equal the number of units made multiplied by unit cost. . Cost of machine labor: The calculated number of machine hours used for producing basketballs and footballs multiplied by the hourly labor rate for machine workers. Total cost of goods sold: The sum of the cost of materials and the cost of labor. Net income: Total revenue minus the total cost of production (B23- B27). Using Microsoft Excel Solver 395 30/06/2023 14:28

396 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 396 Excel evaluates the formulas. Figure 11-9 shows the results, assuming that 1s are in the changing cells. FIGURE 11-9: Cell values in the Income Statement section ☑AutoSave Off File Home Insert Page Layout Formulas Data Review View Automate He Arial 12 A A ab Wra Paste BIU 3 3 A 3 三 Mer Undo Clipboard Font G35 ×✓ fx A B 20 Income statement 21 Basketball revenue (sales) 22 Football revenue (sales) SAR 52.50 SAR 41.25 23 Total revenue SAR 93.75 24 Basketball materials cost SAR 7.50 25 Football materials cost SAR 4.69 26 Cost of machine labor SAR 30.00 27 Total cost of goods sold SAR 42.19 28 Net income SAR 51.56 Alignment C D 5. Constraints Constraints are rules that Solver must observe when computing the optimal answer to a problem. Constraints need to refer to calculated values in the spreadsheet body. There is no section in the main part of the spreadsheet for constraints. You'll add the constraints to the Solver Parameters window (Figure 11-10). Table 11-1 shows the Excel expressions for the constraints of the basketball and football production problem. 30/06/2023 14:28

FIGURE 11-10: Adding constraints in the Solver Parameters window Solver Parameters Set Objective: To: Max By Changing Variable Cells: SBS3:SBS4 Subject to the Constraints: SBS18 <= 40000 SBS18 39000 $B$28 >= 0 SBS360000 SBS3= integer SBS3 SBS4 SBS3 >= 30000 SBS4 <= 40000 SBS4 = integer SBS4 >= 20000 SBS28 Min O 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 Close TABLE 11-1: Solver constraint expressions Description of expression 1. TOTAL MACHINE HOURS >= 39000 2. TOTAL MACHINE HOURS <= 40000 3. MIN BASKETBALLS >= 30000 4. MAX BASKETBALLS <= 60000 5. MIN FOOTBALLS >= 20000 6. MAX FOOTBALLS <= 40000 Excel expression B18 >= 39000 B18 <= 40000 B3 >= 30000 B3 <= 60000 B4 >= 20000 B4 <= 40000 7. AT LEAST AS MANY BASKETBALLS AS FOOTBALLS B3 >= B4 8. NET INCOME MUST BE POSITIVE B28 >= 0 Х QUICK TIP Notice in Figure 11-8 that a cell address in a constraint expression can be a cell address in the Changing cells section, in the ⚫Constants section, in the Calculations section, or in the spreadsheet body. وزارة التعليم Ministry of Education 2024-1446 When using Solver, you might need to set minimum and maximum boundaries for variables. For example, the number of basketballs (MIN and MAX) varies between 30,000 and 60,000. Using Microsoft Excel Solver 397 Business Decision Making S1 S2 S3.indb 397 30/06/2023 14:28

398 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 398 Often a boundary value is zero because you want Solver to find a non-negative result. In this example, you want only answers that yield a positive net income. You tell Solver that the amount in the net income cell must equal or exceed zero so Solver will not find an answer that produces a loss. Machine hours must be shared between the two kinds of balls. The constraints for the shared resource are B18 >= 39000 and B18 <= 40000, where cell B18 shows the total hours used to make both basketballs and footballs. Marketing wants the company to make at least as many basketballs as footballs. The constraint B3 >= B4 expresses this logic. One other constraint arises out of common sense: Solver should not decide to make a partial ball. Solver will let you specify integer values for balls produced as shown in Figure 11-11. FIGURE 11-11: You can add a constraint to make Solver use only integer values Solver Parameters Set Objective: To: Max By Changing Variable Cells: SBS3:SBS4 Subject to the Constraints: SBS18=40000 SBS18 >= 39000 $B$28 >= 0 SBS360000 SBS3 = integer SBS3 >= SBS4 SBS3 = 30000 SBS4 <= 40000 SBS4= integer SBS4=20000 SBS28 Min ◇ Value Of: 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 Close ↑ 30/06/2023 14:28

1-3 Running Solver: Mechanics To set up Solver, you must tell it: the changing cell addresses the cell address of the "target" objective variable that you are trying to optimize the expressions for the constraints. Solver will record its answers in the Variable Cells and in a separate worksheet. 1. Beginning to Set Up Solver . First select the Data tab. In the Analysis group, select Solver. The Solver Parameters window appears, as shown in Figure 11-12. Use the Solver Parameters window to specify the objective cell, the changing cells, and the constraints. FIGURE 11-12: Upper portion of the Solver Parameters window Solver Parameters х Set Objective: To: Max By Changing Variable Cells: B28 Min O Value Of: 0 1 >>> 1 2. Setting the Objective Cell . To set an objective cell, use the following procedure: - Click the Set Objective box and enter B28, which is the cell for Net income. QUICK TIP When you enter the cell address, Solver may insert dollar signs, as if for absolute addressing. Ignore them-do not try to delete them. - Accept the default Max setting, which stands for "maximize". - Leave the Value Of: button unchecked. Do not press Enter when you finish. You'll navigate within this window by clicking in the next input box. 3. Setting the Changing Variables Cells The changing cells are the cells for the quantity of basketballs and footballs, which are in the range B3:B4. Click the By Changing Variable Cells box and type B3:B4, as shown in Figure 11-13. Do not press Enter. وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 399 Using Microsoft Excel Solver 399 30/06/2023 14:28

400 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 400 FIGURE 11-13: Entering data in the By Changing Variable Cells box Solver Parameters Set Objective: To: Max By Changing Variable Cells: B3:B4 B28 Min O Value Of: 0 1 → 4. Entering Constraints . You are now ready to enter the constraint formulas one by one. STEP 1 Click the Add button in the Solver Parameters window. As shown in Figure 11-14, you then see the Add Constraint window. Enter the constraint for minimum bas- ketball production. FIGURE 11-14: Entering data in the Add Constraint window Add Constraint Cell Reference: B3 1 >= Constraint: 30000 OK Add Cancel 1 Х You should do the following when entering constraint expressions: Type the variable's cell address in the Cell Reference input box. Select the operator (<=,=, >=, or Int) in the smaller, middle box. Enter either a raw number or the cell address of a value in the Constraint box. . Click Add to finish entering the constraint. . . STEP 2 If you change your mind about the expression and do not want to enter it, click Cancel. Enter the minimum basketball constraint now. Later, Solver will insert an equals sign in front of the 30000 and a dollar sign in the cell reference. After entering a constraint formula, click the Add button to add the constraint to the Solver model. The Add Constraint window stays open and allows you to enter other constraints. 30/06/2023 14:28

QUICK TIP You should use the Int operator in the Add Constraint window to specify that B3 and B4 must be integer values. Enter the rest of the constraints. See Table 11-1 for the logic. When you're done entering constraints, click the Cancel button to return to the Solver Parameters window. In the Solver Parameters window, you will see that the constraints have been entered into the program. In some cases, not all constraints will appear due to the number of constraints and the size of the box, although that is not a problem here. The base case constraints are shown in Figure 11-15. FIGURE 11-15: Constraints entered in the Solver Parameters window Solver Parameters وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 401 Set Objective: To: Max By Changing Variable Cells: $B$3:$B$4 Subject to the Constraints: $B$18 <= 40000 $B$18 >= 39000 $B$28 >= 0 $B$360000 $B$3 = integer $B$3 >= $B$4 $B$330000 $B$4 <= 40000 $B$4 = integer $B$4 >= 20000 $B$28 Min O Value Of: 0 Make Unconstrained Variables Non-Negative Select a Solving Method: Solving Method GRG Nonlinear < Add Change Delete Reset All Load/Save <--1 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 5. Selecting a Solving Method Figure 11-15 shows the Select a Solving Method field. For this problem, choose GRG Nonlinear. The Make Unconstrained Variables Non-Negative box should be checked, as shown in Figure 11-15. 6. Computing Solver's Answer Using Microsoft Excel Solver 401 30/06/2023 14:28

402 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 402 STEP 1 To have Solver calculate answers, click Solve in the lower-right corner of the Solver Parameters window. Solver does its work in the background-you do not see the internal calculations, but may notice a slight delay on your computer. Next you will see a Solver Results window, as shown in Figure 11-16. FIGURE 11-16: Solver Results window Solver Results Solver found a solution. All Constraints and optimality conditions are satisfied. Keep Solver Solution ◇ Restore Original Values Reports Answer Return to Solver Parameters Dialog Outline Reports OK Cancel Save Scenario... Solver found a solution. All Constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution. In the Solver Results window, Solver reports that it has found a solution and that the constraints and objectives were met. This is a very important mes- sage you should always check for it. Impossible constraints Sometimes, your constraints might not allow Solver to find an answer. For example, imagine you had a constraint that said, "Net income must be at least a billion dollars." That amount cannot be reached, given so few basketballs and footballs and the prices. Solver would report that no answer is possible. It might also report that it found an answer, but only by ignoring some constraints. In either case, something is wrong with your model, and you would need to rework it. There are two ways to see your answers. One way is to click OK in the Solver Results window, which lets you see the new changing cell values. A more х 30/06/2023 14:28

formal and complete way is to click Answer in the Reports box and then click OK, which inserts detailed results into a new sheet in your Excel book. The new sheet is called an Answer Report. All Answer Reports are numbered sequentially as you run Solver. STEP 2 To see the Answer Report, click its tab, as shown in Figure 11-17. In this case, the tab is named Answer Report 1. FIGURE 11-17: Answer Report sheet tab 32 $B$3 33 $B$4 Number of basketballs sold Number of footballs sold 34 $B$4 Number of footballs sold 35 $B$3=Integer 36 $B$4=Integer 37 Answer Report 1 Sheet 1 Acrassibility: Investigate 56000 $B$3>=30000 40000 $B$4<=40000 40000 $B$4>=20000 Not Binding Binding Not Binding 26000 0 20000 The top portion of the report is shown in Figure 11-18. FIGURE 11-18: Top portion of the Answer Report وزارة التعليم Ministry of Education 2024-1446 AutoSave Off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat X Arial 10 AA Wrap Text General Paste BIU 3 Merge & Center % 9 LA Alignment E Number Undo Clipboard Font E L44 X fx A 1 Microsoft Excel 16.0 Answer Report 2 Worksheet: 3 Report Created: D E F 4 Result: Solver found a solution. All Constraints and optimality conditions are satisfied. 5 Solver Engine 6 Engine: GRG Nonlinear 7 Solution Time: 0.047 Seconds. 8 Iterations: 3 Subproblems: 0 9 Solver Options 10 Max Time 100 sec, Iterations 100, Precision 0.000001 11 12 13 Convergence 0.0001, Population Size 100, Random Seed 0, Derivatives Central Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 0%, Assume NonNegative 14 Objective Cell (Max) 15 Cell Name 16 $B$28 Net income Original Value SAR 51.56 Final Value SAR 2,482,400.00 17 18 19 Variable Cells 22222 20 Cell Name Original Value Final Value Integer 21 $B$3 $B$4 Number of basketballs sold Number of footballs sold 1 56000 Integer 1 40000 Integer 23 Business Decision Making S1 S2 S3.indb 403 H 123 Using Microsoft Excel Solver 403 30/06/2023 14:28

Figure 11-19 shows the remainder of the Answer Report. FIGURE 11-19: Remainder of the Answer Report 121 AutoSave Off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat X Arial 10 AA Wrap Text General Paste BIU Y A lili Merge & Center %9 Undo Clipboard 5 Font E Alignment ES Number L44 A : X✓ fx B C D 14 Objective Cell (Max) 15 Cell Name 16 $B$28 Net income Original Value SAR 51.56 E Final Value G H SAR 2,482,400.00 17 18 19 Variable Cells Integer 56000 Integer 40000 Integer 20 Cell Name Original Value Final Value 21 $B$3 22 $B$4 Number of basketballs sold Number of footballs sold 1 1 23 24 25 Constraints 26 27 $B$18 Cell Name Cell Value 28 29 $B$18 $B$28 Total machine hours used (BB+ FB) Total machine hours used (BB + FB) Net income Formula 40000.00 $B$18<=40000 40000.00 $B$18>=39000 SAR 2,482,400.00 $B$28>=0 Status Binding Slack Not Binding 1000.00 Not Binding SAR 2,482,400.00 30 $B$3 Number of basketballs sold 31 $B$3 Number of basketballs sold 56000 $B$3>=$B$4 56000 $B$3<=60000 Not Binding 16000 Not Binding 4000 32 $B$3 Number of basketballs sold 33 $B$4 Number of footballs sold 34 $B$4 Number of footballs sold 56000 $B$3>=30000 40000 $B$4<=40000 40000 $B$4>=20000 Not Binding Binding Not Binding 26000 0 20000 35 $B$3=Integer 36 $B$4=Integer 37 404 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 404 At the beginning of this example, the changing cells had a value of 1, and the income was SAR 51.56 (the original value, with only one football and one bas- ketball sold). The optimal solution values (the final value, with 56,000 basket- balls and 40,00 footballs sold) are also shown: SAR 2,482,400 for net income (the target) and 56,000 basketballs and 40,000 footballs for the changing cells. The report also shows details for the constraints: the constraint expression and the value of the variable in the optimal solution. Binding means the final answer caused Solver to bump up against the constraint. For example, the maximum number of machine hours was 40,000, which is the value Solver used to find the answer. Not binding means the reverse. For exam- ple, the maximum limit of 60,000 basketballs did not constrain Solver. The procedures you use to change or delete a constraint are discussed later in this chapter. 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 405 Save the Excel file using the Save option in the File tab. Then use the Save As option in the File tab to create a new file named Sports2.xlsx, which you will use in the next section of this chapter. 1-4 Multiple Variables in Excel Solver Next, you'll modify the sporting goods spreadsheet because management wants to know what net income would be if certain constraints were changed. In other words, management wants to do "what if?" analysis with certain constraints in the base case. The results of this second case are called the extension case. Here are the changes that are made to the original base case conditions: . Assume maximum production constraints will be removed. STEP 1 Similarly, the basketball-to-football production comparison (B3 >= B4) will be removed. There still will be minimum production constraints-assume that at least 30,000 basketballs and 30,000 footballs will be produced. The machine-hours shared resource imposes the same limits as in the base case. Begin by putting 1s in the changing cells. In the Solver Parameters window, click to highlight constraints that are no longer needed: the net income >= 0 constraint, the maximum football and basketball constraints, and the basketball-to-football comparison constraint. Delete the con- straints by clicking the Delete button. The minimum football constraint must be modified, not deleted. Select that con- straint and then click Change to open the Add Constraint window. Edit the con- straint so that 30,000 is the lower boundary. Using Microsoft Excel Solver 405 30/06/2023 14:28

406 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 406 When you are finished with the constraints, your Solver Parameters window should look like the one shown in Figure 11-20. FIGURE 11-20: Solver Parameters window for the extension case Solver Parameters х Set Objective: To: Max By Changing Variable Cells: $B$3:$B$4 Subject to the Constraints: $B$18 <=40000 $B$18 >= 39000 $B$3 = integer $B$3 >= 30000 $B$4 = integer $B$4 >= 30000 $B$28 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 ↑ Close ← The constraints now apply only to the minimum production levels, the machine- hours shared resource, and the whole-number output. STEP 2 Set the Solver method to GRG Nonlinear in the Solver Parameters window. When Solver has ran, the values in the Answer Report should match those in Figure 11-21. 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 FIGURE 11-21: Answer Report for the extension case AutoSave Off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat From Text/CSV From Picture Queries & Connections 21 A From Web Recent Sources Properties Get Data From Table/Range Existing Connections Refresh All- Edit Links Get & Transform Data Queries & Connections Stocks Currencies Data Types >> NA Z↓ Sort F Sort K38 : X✓ fx A B C E F G H 14 Objective Cell (Max) 15 Cell Name 16 $B$28 Net income Original Value SAR 51.56 Final Value SAR 2,896,658.23 17 18 19 Variable Cells 20 Cell Name Original Value Final Value Integer 21 $B$3 22 $B$4 Number of basketballs sold Number of footballs sold 1 30000 Integer 1 83333 Integer 23 24 25 Constraints 26 27 $B$18 Cell Name Cell Value 28 $B$18 29 $B$3 Total machine hours used (BB + FB) Total machine hours used (BB + FB) Number of basketballs sold 30 $B$4 Number of footballs sold Formula 39999.90 $B$18<=40000 39999.90 $B$18>=39000 30000 $B$3>=30000 83333 $B$4>=30000 Status Slack Not Binding Not Binding 0.1 999.90 Binding Not Binding 0 53333 31 $B$3=Integer 32 $B$4-Integer 33 Business Decision Making S1 S2 S3.indb 407 The answer for the extension case differs from the base case answer. Which pro- duction schedule should management use: the one that has maximum produc- tion limits or the one that has no such limits? This question is posed to get you to think about the purpose of using a DSS program. Solver's very different answers to the base case and the extension case are shown in Table 11-2. TABLE 11-2: Solver's answers for the two cases Basketballs Footballs Base case Extension case 56,000 40,000 30,000 83,333 Can you use this output alone to decide how many of each kind of ball to pro- duce? No, you cannot. You must also refer to the case objective, which is to optimize net income. Table 11-3 shows the answers with net income data added. Using Microsoft Excel Solver 407 30/06/2023 14:28

TABLE 11-3: Solver's answers for the two cases-with objective data Basketballs Footballs Net income Base case Extension case 56,000 30,000 40,000 83,333 SAR 2,482,400 SAR 2,896,658 When viewed this way, the extension case's production schedule looks better because it gives a higher target net income. . Save the Sports2.xlsx file and then close it. Select Close from the File tab. REVIEW QUESTIONS 1. What is the purpose of optimization? a. To continually ensure that a system is working as well as possible to max- imize profits b. To ensure that most employees have work to do most of the time c. To calculate how many breaks employees should be given during a working day d. To design a marketing strategy for an existing product 2. Which of the following is an example of a shared resource? a. Stationery such as notebooks and pens b. The desktop computer each employee is using c. The material that is used to make both bike tires and car tires in the same factory d. The cell phones used by the sales team 408 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 408 30/06/2023 14:28