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

Solving Optimization Word Problems

Purpose: 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 MAX

Since 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,200

2.

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 University

Mathematics Department

instructor: Donna M. Wacha

Optimization Word Problems

page 2 of 9

NOTE: 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 "zero" 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 "$" signs. (It is not absolutely necessary to use the "$" 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 may

b.

c. d.

Monmouth University

Mathematics Department

instructor: Donna M. Wacha

Optimization Word Problems

page 3 of 9

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.) To enter the 5 constraints for the given problem, use this information:

cell reference symbol constraint "action"

D2 E2 F2 G2 H2

0 0 300 48,000,000 43,200

ADD ADD ADD ADD OK

When 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 = 60

Monmouth University

Mathematics Department

instructor: Donna M. Wacha

Optimization Word Problems

page 4 of 9

Example #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 "A" is: 10x + 10y + 10z 90 · The constraint for ore "B" is: 10y + 10z 50 · The constraint for ore "C" is: 10x + 10z 60

2.

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 University

Mathematics Department

instructor: Donna M. Wacha

Optimization Word Problems

page 5 of 9

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: a. Click on the "zero" 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 "$" signs. (It is not absolutely necessary to use the "$" 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 University

Mathematics Department

instructor: Donna M. Wacha

Optimization Word Problems

page 6 of 9

To enter the 6 constraints for the given problem, use this information:

cell reference symbol constraint "action"

E2 F2 G2 H2 I2 J2

0 0 0 90 50 60

ADD ADD ADD ADD ADD OK

When 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 = 2

Monmouth University

Mathematics Department

instructor: Donna M. Wacha

Optimization Word Problems

page 7 of 9

Example #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 MIN

product #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 300

2.

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 University

Mathematics Department

instructor: Donna M. Wacha

Optimization Word Problems

page 8 of 9

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. 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 "zero" 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 "$" signs. (It is not absolutely necessary to use the "$" 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 the

b.

c. d.

Monmouth University

Mathematics Department

instructor: Donna M. Wacha

Optimization Word Problems

page 9 of 9

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 "action"

E2 F2 G2 H2 I2 J2

0 0 0 200 500 300

ADD ADD ADD ADD ADD OK

When 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 = 0

Monmouth University

Mathematics Department

instructor: Donna M. Wacha

#### Information

##### Microsoft Word - E07g-OptimizationWPs

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