#### Read Microsoft Word - E07g-OptimizationWPs text version

`Solving Optimization Word ProblemsPurpose: Use Microsoft Excel's SOLVER to solve optimization word problems. Example #1: A contractor builds two types of homes. The Carolina model requires one lot, \$160,000 in capital, and 160 worker-days of labor. The Savannah model requires one lot, \$240,000 in capital, and 160 worker-days of labor. The contractor owns 300 lots, has \$48,000,000 available in capital, and has 43,200 available worker-days of labor. The profit on the Carolina model is \$40,000. The profit on the Savannah model is \$50,000. Find how many of each type of home should be built to maximize profit. What is the maximum possible profit? 1. The first thing that needs to be done with any word problem is to translate the given information into appropriate mathematical sentences (equations or inequalities). a. A note-taking chart may be useful in sorting the information: lots capital worker-days profit b. c. x = Carolina 1 160,000 160 40,000 y = Savannah 1 240,000 160 50,000 limits 300 48,000,000 43,200 MAXSince the profit is to be maximized, the objective function for this word problem is: F = 40,000x + 50,000y Besides x  0 and y  0, there are 3 other constraints: lots, capital worker-days. · The constraint for the number of lots is: x + y  300 · The constraint for the amount of capital is: 160,000x + 240,000y  48,000,000 · The constraint for the number of worker-days is: 160x + 160y  43,2002.Now that we have the algebraic objective function and the companion constraints, we need to create the SOLVER chart in an Excel worksheet. Referring to the image below:a. b. c. d. e. f. g. h.In cell A2, type: In cell B2, type: In cell C2, type: In cell D2, type: In cell E2, type: In cell F2, type: In cell F2, type: In cell F2, type:0 0 =40000*A2+50000*B2 =A2 =B2 =A2+B2 =160000*A2+240000*B2 =160*A2+160*B2(to initialize variable x) (to initialize variable y) (objective function.) (formula for the 1st constraint) (formula for the 2nd constraint) (formula for the 3rd constraint) (formula for the 4th constraint) (formula for the 5th constraint)Monmouth UniversityMathematics Departmentinstructor: Donna M. WachaOptimization Word Problemspage 2 of 9NOTE: Because the values of x and y are each zero at the moment, the remaining columns will also display zeroes right now. Once you execute the SOLVER tool, Excel will change the contents of these cells to display the optimal solution for the given problem. OPTIONAL: To remind yourself what kind of optimization problem is being completed as well as the relationship symbols used and the limits for the constraints, type this information onto the Excel worksheet (as shown in the image above in cells D3, E3, F3, G3, H3, C4, D4, E4, F4 G4, and H4). 3. This next step consists of several clicks: a. Click on the &quot;zero&quot; that represents the objective function (OBJ) . . . that would be cell C2 in our example (as shown in the image below). b. Click the DATA tab on the top of the Excel screen. c. Click SOLVER to access this tool.4.You are now ready to implement SOLVER when the Solver Parameters box appears. Referring to the image at the right: a. Since we clicked cell C2 before calling up SOLVER, \$C\$2 appears in the Set Target Cell box using &quot;\$&quot; signs. (It is not absolutely necessary to use the &quot;\$&quot; signs. If the computer does not display the proper cell, change it so that it does by just typing C2 into the Set Target Cell box. ) Since we are attempting to solve a maximum optimization problem, make sure that the EQUAL TO radio dot indicates the MAX option. (MAX is usually the default for SOLVER. However, if a correction is necessary, make the change needed by just clicking the correct radio dot.) Click in the white box beneath BY CHANGING CELLS and then use the computer mouse to highlight cells A2 to B2 on your Excel worksheet. Click in the white box beneath SUBJECT TO THE CONSTRAINTS and then the ADD button to the right of the white box. An ADD CONSTRAINT dialog box will appear (as shown at the right): You must now enter each of the constraints ­ one at a time ­ for the given problem. Click the ADD button to enter each new constraint until the final one. Then, you mayb.c. d.Monmouth UniversityMathematics Departmentinstructor: Donna M. WachaOptimization Word Problemspage 3 of 9click on the OK button to enter the last constraint. (The OK button will return you to the Solver Parameters box. If you forget and click OK before you are finished, just click the ADD button of the Solver Parameters box and continue entering constraints.) To enter the 5 constraints for the given problem, use this information:cell reference symbol constraint &quot;action&quot;D2 E2 F2 G2 H2    0 0 300 48,000,000 43,200ADD ADD ADD ADD OKWhen you click OK and return to the Solver Parameters box, all of the information entered should be the same as that displayed in the image on the bottom of the previous page. e. To complete the SOLVER process, click the SOLVE button at the top right of the Solver Parameters box (as shown in the image at the right). Then click the OK button when the Solver Results box appears.The solution will now appear on the Excel worksheet. The proposed values of the variables and the optimal maximum value will be displayed under their respective column headings. In our example: the value of x appears in cell A2, the value of y appears in cell B2 and the proposed MAX value appears in cell C2 (as shown below):Therefore, for our sample problem, the maximum value for F = 40,000x + 50,000y will be: MAX = \$11,400,000 when x = 210, y = 60Monmouth UniversityMathematics Departmentinstructor: Donna M. WachaOptimization Word Problemspage 4 of 9Example #2: The Monmouth Mining Company owns three mines (M1, M2, M3). Three ores (A, B, C) are mined at these locations. For each ore, the number of tons per week available from each mine and the number of tons per week required to fill orders are given in this table: ores: A B C daily cost M1 10 0 10 \$6000 mines: M2 10 10 0 \$8000 M3 10 10 10 \$12,000 required tons per week 90 50 60 ­Find the minimum cost and the number of days the company should operate each mine so that orders are filled at this minimum cost. 1. The first thing that needs to be done with any word problem is to translate the given information into appropriate mathematical sentences (equations or inequalities). The data table that was provided with the word problem can be used to create the objective function and the companion constraints. a. b. Since the daily cost is to be minimized, the objective function for this word problem is: G = 6000x + 8000y + 12,000z Besides x  0, y  0 and z  0, there are 3 other constraints: · The constraint for ore &quot;A&quot; is: 10x + 10y + 10z  90 · The constraint for ore &quot;B&quot; is: 10y + 10z  50 · The constraint for ore &quot;C&quot; is: 10x + 10z  602.Now that we have the algebraic objective function and the companion constraints, we need to create the SOLVER chart in an Excel worksheet. Referring to the image below:a. b. c. d. e. f. g. h. i. j.In cell A2, type: 0 In cell B2, type: 0 In cell C2, type: 0 In cell D2, type: =6000*A2+8000*B2+12000*C2 In cell E2, type: =A2 In cell F2, type: =B2 In cell G2, type: =C2 In cell H2, type: =10*A2+10*B2+10*C2 In cell I2, type: =10*B2+10*C2 In cell J2, type: =10*A2+10*C2(to initialize variable x) (to initialize variable y) (to initialize variable z) (objective function.) (formula for the 1st constraint) (formula for the 2nd constraint) (formula for the 3rd constraint) (formula for the 4th constraint) (formula for the 5th constraint) (formula for the 6th constraint)NOTE: Because the values of x, y and z are each zero at the moment, the remaining columns will also display zeroes right now. Once you execute the SOLVER tool, Excel will change the contents of these cells to display the optimal solution for the given problem.Monmouth UniversityMathematics Departmentinstructor: Donna M. WachaOptimization Word Problemspage 5 of 9OPTIONAL: To remind yourself what kind of optimization problem is being completed as well as the relationship symbols used and the limits for the constraints, type this information onto the Excel worksheet (as shown in the image above in cells E3, F3, G3, H3, I3, J3, D4, E4, F4 G4, H4, I4 and J4). 3. This next step consists of several clicks: a. Click on the &quot;zero&quot; that represents the objective function (OBJ) . . . that would be cell D2 in our example (as shown in the image below). b. Click the DATA tab on the top of the Excel screen. c. Click SOLVER to access this tool.4.You are now ready to implement SOLVER when the Solver Parameters box appears. Referring to the image at the right: a. Since we clicked cell D2 before calling up SOLVER, \$D\$2 appears in the Set Target Cell box using &quot;\$&quot; signs. (It is not absolutely necessary to use the &quot;\$&quot; signs. If the computer does not display the proper cell, change it so that it does by just typing D2 into the Set Target Cell box. ) Since we are attempting to solve a minimum optimization problem, make sure that the EQUAL TO radio dot indicates the MIN option. (MAX is usually the default for SOLVER. However, if a correction is necessary, make the change needed by just clicking the correct radio dot.) Click in the white box beneath BY CHANGING CELLS and then use the computer mouse to highlight cells A2 to C2 on your Excel worksheet. Click in the white box beneath SUBJECT TO THE CONSTRAINTS and then the ADD button to the right of the white box. An ADD CONSTRAINT dialog box will appear (as shown at the right): You must now enter each of the constraints ­ one at a time ­ for the given problem. Click the ADD button to enter each new constraint until the final one. Then, you may click on the OK button to enter the last constraint. (The OK button will return you to the Solver Parameters box. If you forget and click OK before you are finished, just click the ADD button of the Solver Parameters box and continue entering constraints.)b.c. d.Monmouth UniversityMathematics Departmentinstructor: Donna M. WachaOptimization Word Problemspage 6 of 9To enter the 6 constraints for the given problem, use this information:cell reference symbol constraint &quot;action&quot;E2 F2 G2 H2 I2 J2     0 0 0 90 50 60ADD ADD ADD ADD ADD OKWhen you click OK and return to the Solver Parameters box, all of the information entered should be the same as that displayed in the image on the bottom of the previous page. e. To complete the SOLVER process, click the SOLVE button at the top right of the Solver Parameters box (as shown in the image at the right). Then click the OK button when the Solver Results box appears.The solution will now appear on the Excel worksheet. The proposed values of the variables and the optimal minimum value will be displayed under their respective column headings. In our example: the value of x appears in cell A2, the value of y appears in cell B2 and the proposed MIN value appears in cell C2 (as shown below):Therefore, for our sample problem, the minimum value for G = 6000x + 8000y + 12,000z will be: MIN = 72,000 when x = 4, y = 3, z = 2Monmouth UniversityMathematics Departmentinstructor: Donna M. WachaOptimization Word Problemspage 7 of 9Example #3: A company makes three products (P1, P2, P3) at three different factories (F1, F2, F3): · At factory F1, the company can make 10 units of each product per day. · At factory F2, it can make 20 units of P2 and 20 units of P3 per day. · At factory F3, it can make 20 units of P1, 20 units of P2, and 10 units of P3 per day. The company has orders for 200 units of P1, 500 units of P2, and 300 units of P3. The daily costs are \$200 at factory F1, \$300 at factory F2, and \$500 at factory F3. Find the minimum cost and the number of days each factory should operate so that the company can fill its orders at the minimum cost. 1. The first thing that needs to be done with any word problem is to translate the given information into appropriate mathematical sentences (equations or inequalities). a. A note-taking chart may be useful in sorting the information: x = F1 10 10 10 \$200 y = F2 0 20 20 \$300 z = F3 20 20 10 \$500 orders: 200 500 300 MINproduct #1 product #2 product #3 daily costs b. c.Since the daily costs are to be minimized, the objective function for this word problem is: F = 200x + 300y + 500z Besides x  0, y  0 and z  0, there are 3 other constraints: · The constraint for product #1 is: 10x + 20z  200 · The constraint for product #2 is: 10x + 20y + 20z  500 · The constraint for product #3 is: 10x + 20y + 10z  3002.Now that we have the algebraic objective function and the companion constraints, we need to create the SOLVER chart in an Excel worksheet. Referring to the image below:a. b. c. d. e. f. g. h. i. j.In cell A2, type: 0 In cell B2, type: 0 In cell C2, type: 0 In cell D2, type: =200*A2+300*B2+500*C2 In cell E2, type: =A2 In cell F2, type: =B2 In cell G2, type: =C2 In cell H2, type: =10*A2+20*C2 In cell I2, type: =10*A2+20*B2+20*C2 In cell J2, type: =10*A2+20*B2+10*C2(to initialize variable x) (to initialize variable y) (to initialize variable z) (objective function) (formula for the 1st constraint) (formula for the 2nd constraint) (formula for the 3rd constraint) (formula for the 4th constraint) (formula for the 5th constraint) (formula for the 6th constraint)Monmouth UniversityMathematics Departmentinstructor: Donna M. WachaOptimization Word Problemspage 8 of 9NOTE: Because the values of x. y and z are each zero at the moment, the remaining columns will also display zeroes right now. Once you execute the SOLVER tool, Excel will change the contents of these cells to display the optimal solution for the given problem. OPTIONAL: To remind yourself what kind of optimization problem is being completed as well as the relationship symbols used and the limits for the constraints, type this information onto the Excel worksheet (as shown in the image above in cells E3, F3, G3, H3, I3, J3, D4, E4, F4 G4, H4, I4 and J4). 3. This next step consists of several clicks: d. Click on the &quot;zero&quot; that represents the objective function (OBJ) . . . that would be cell C2 in our example (as shown in the image below). e. Click the DATA tab on the top of the Excel screen. f. Click SOLVER to access this tool.4.You are now ready to implement SOLVER when the Solver Parameters box appears. Referring to the image at the right: a. Since we clicked cell D2 before calling up SOLVER, \$D\$2 appears in the Set Target Cell box using &quot;\$&quot; signs. (It is not absolutely necessary to use the &quot;\$&quot; signs. If the computer does not display the proper cell, change it so that it does by just typing C2 into the Set Target Cell box. ) Since we are attempting to solve a minimum optimization problem, make sure that the EQUAL TO radio dot indicates the MIN option. (MAX is usually the default for SOLVER. However, if a correction is necessary, make the change needed by just clicking the correct radio dot.) Click in the white box beneath BY CHANGING CELLS and then use the computer mouse to highlight cells A2 to C2 on your Excel worksheet. Click in the white box beneath SUBJECT TO THE CONSTRAINTS and then the ADD button to the right of the white box. An ADD CONSTRAINT dialog box will appear (as shown at the right): You must now enter each of the constraints ­ one at a time ­ for the given problem. Click the ADD button to enter each new constraint until the final one. Then, you may click on the OK button to enter the last constraint. (The OK button will return you to theb.c. d.Monmouth UniversityMathematics Departmentinstructor: Donna M. WachaOptimization Word Problemspage 9 of 9Solver Parameters box. If you forget and click OK before you are finished, just click the ADD button of the Solver Parameters box and continue entering constraints.) To enter the 5 constraints for the given problem, use this information:cell reference symbol constraint &quot;action&quot;E2 F2 G2 H2 I2 J2     0 0 0 200 500 300ADD ADD ADD ADD ADD OKWhen you click OK and return to the Solver Parameters box, all of the information entered should be the same as that displayed in the image on the bottom of the previous page. e. To complete the SOLVER process, click the SOLVE button at the top right of the Solver Parameters box (as shown in the image at the right). Then click the OK button when the Solver Results box appears.The solution will now appear on the Excel worksheet. The proposed values of the variables and the optimal maximum value will be displayed under their respective column headings. In our example: the value of x appears in cell A2, the value of y appears in cell B2 and the proposed MIN value appears in cell D2 (as shown below):Therefore, for our sample problem, the minimum value for G = 200x + 300y + 500z will be: MIN = \$8500 when x = 20, y = 15, z = 0Monmouth UniversityMathematics Departmentinstructor: Donna M. Wacha`

9 pages

#### Report File (DMCA)

Our content is added by our users. We aim to remove reported files within 1 working day. Please use this link to notify us:

Report this file as copyright or inappropriate

233697

Notice: fwrite(): send of 204 bytes failed with errno=104 Connection reset by peer in /home/readbag.com/web/sphinxapi.php on line 531