Practicing with Solver - Business Decision Making - ثاني ثانوي

Lesson 2 Chapter 11 www.ien.edu.sa Practicing with Solver This lesson includes a short problem that lets you practice what you have learned about Excel Solver. Assume that you run a clothing manufacturing company. You have two prod- ucts: T-shirts with a buttoned pocket and button-down shirts. You must decide how many T-shirts and button-down shirts to make. Assume that you'll sell every item you make. Use the file Chapter 11 data file 2.xlxs, which you can access by scanning the QR code. 1. Changing Cells Section Your Changing cells section should look like those in Figure 11-22. FIGURE 11-22: Clothing manufacturing changing cells وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 409 AutoSave Off File Home Insert Page Layout Formulas Data Review View Aut Arial 11 A A Paste BIU. 0 A Undo Clipboard Г Font 110 : × ✓ fx A 1 Chapter 11 Lesson 2 2 Changing cells 3 Number of T-shirts 4 Number of button-down shirts Г B C 1 1 Alic Using Microsoft Excel Solver 409 30/06/2023 14:28

410 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 410 2. Constants Section Your spreadsheet should contain the constants shown in Figure 11-23. FIGURE 11-23: Clothing manufacturing constants AutoSave Off H Search File Home Insert Page Layout Formulas Data Review View Automate Help Acroba X Arial 10 A A ab Wrap Text Paste BIU 3 3 A v 三 Merge & Center Undo Clipboard Б Font Alignment G22 X fx A B C D 6 Constants 7 Selling price: T-shirt 8 Selling price: button-down shirt 9 Variable cost to make: T-shirt 10 Variable cost to make: button-down shirt 11 Cotton usage (kg): T-shirt SAR 30.00 SAR 135.00 SAR 9.38 12 Cotton usage (kg): button-down shirt 13 Total cotton available (kg) 14 Buttons per T-shirt 15 Buttons per button-down shirt 16 Total buttons available 17 SAR 52.50 0.68 1.13 13,000,000 3.00 12.00 110,000,000 Selling price: You sell T-shirts for SAR 30 and button-down shirts for SAR 135. Variable cost to make: It costs SAR 9.38 to make a T-shirt and SAR 52.50 to make a button-down shirt. These variable costs are for machine operator labor, cloth, buttons, and so on. Cotton usage: Each T-shirt uses 0.68 kilograms of cotton fabric. Each button-down shirt uses 1.13 kilograms of cotton fabric. Total cotton available: You have 13 million kilograms of cotton on hand to make all of the T-shirts and button-down shirts. Buttons: Each T-shirt has three buttons. By contrast, each button-down shirt has one button on each collar tip, eight buttons down the front, and one button on each cuff, for a total of 12 buttons. Total buttons available: You have 110 million buttons on hand to be used in making the shirts. 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 411 3. Calculations Section Calculations and related business constraints are discussed next. Your spread- sheet should contain the calculations shown in Figure 11-24. FIGURE 11-24: Clothing manufacturing calculations AutoSave off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acroba Arial 10 A A Wrap Text Paste BIU V 4A Undo Clipboard 12 Font 12 > lili Merge & Center Alignment F31 XVfx A 18 Calculations 19 Cotton used: T-shirts 20 Cotton used: button-down shirts 21 Cotton used: total 22 Buttons used: T-shirts 23 Buttons used: button-down shirts 24 Buttons used: total 25 Ratio of button-downs to T-shirts 26 B 0.68 1.13 1.81 3 12 15 1.0 C D . . Cotton used/buttons used: You have a limited amount of cotton and buttons. The usage of each resource must be calculated and then used in constraints. Ratio of button-down shirts to T-shirts: You think you must make at least 2 million T-shirts and at least 2 million button-down shirts. You want to operate as a balanced shirt maker, so the ratio of button-down shirts to T-shirts should be no greater than 4:1. Thus, if you produced 9 million button-down shirts and 2 million T-shirts, the ratio would be too high (4:5:1). Using Microsoft Excel Solver 411 30/06/2023 14:28

4. Income Statement Section Your spreadsheet should have the Income statement section shown in Figure 11-25. FIGURE 11-25: Line items in the clothing manufacturing Income statement AutoSave off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acroba Paste X Arial BIU v 10 AA Undo Clipboard E Font F39 fx A 27 Income statement 28 T-shirt revenue 29 Button-down shirt revenue 30 Total revenue 31 Variable costs: T-shirts 32 Variable costs: button-downs 33 Total costs 34 Income 35 > LZ === ab Wrap Text H Merge & Center Alignment B C D SAR 30.00 SAR 135.00 SAR 165.00 SAR 9.38 SAR 52.50 SAR 61.88 SAR 103.12 The meaning of the line items is similar to the sports example in Lesson 1. Refer to these examples for explanations. Solver's objective is to optimize net income. Use the table shown in Table 11-4 to hand-write the constraints before enter- ing them into Solver. TABLE 11-4: Logic of clothing manufacturing constraints Expression Net income to revenue Ratio of button-downs to T-shirts Minimum T-shirts Minimum button-downs Usage of buttons Fill in the Excel expression || 412 11 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 412 Before running Solver, set the method to GRG Nonlinear in the Solver Parameters window. Remember, the model can only calculate values based on the inputs and constraints you have provided. The spreadsheet can't interpret the results or make recommendations based on them. 30/06/2023 14:28

www.ien.edu.sa REVIEW QUESTIONS You asked Solver to change the number of T-shirts and button-down shirts (with constraints) to maximize the company's income. In this instance, Solver is suggesting that you should produce four times as many button-downs as T-shirts. When you are finished with the program, select Save from the File tab to save the file, and then select Close from the File tab to close the file. Scan the QR code to access a lesson that will help you to overcome problems with Solver. The lesson will also refresh your memory on basic file-handling procedures. 1. Which of the following would NOT be included in the Constants section of the Solver spreadsheet? a. Selling price b. Variable cost to make an item c. Number of items d. Material usage 2. If you are using Solver to optimize net income, which of the fol- lowing should be included in the Income statement section? a. Revenue of items being sold b. Total revenue c. Variable costs d. All of the above وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 413 Using Microsoft Excel Solver 413 30/06/2023 14:28