Modeling & Solving LP Problems In A Spreadsheet

1.In general, it does not matter what is placed in a variable (changing) cell. Ultimately, Solver will determine the optimal values for these cells. If the model builder places formulas in changing cells, Solver will replace the formulas with numeric constants representing the optimal values of the decision variables. An exception to this general principle is found in Chapter 8 where, when solving nonlinear programming problems, the values placed in the changing cells represent the initial starting point for the optimizer.

2.Communication - once the user understands the first formula in a range of copied cells, he or she should understand all the formulas in the range. Reliability - assuming the first formula is entered correctly, all the copied formulas should be correct also. Auditability - once the user understands the first formula in a range of copied cells, he or she should understand (and audit) all the formulas in the range Maintainability - if a change needs to be made, it can be made in one formula and then copied as necessary.

3.TV ads = 10, Magazine ads =25, Maximum profit = $775,000 See file: Prb3_3.xls

4. Ore 1 = 28, Ore 2 = 8, Minimum cost = $3,480

See file: Prb3_4.xls

5.Beef = 50%, Pork = 50%, Minimum cost per pound = $0.75

See file: Prb3_5.xls

6.Razors = 240, Zoomers = 420, Maximum profit = $33,600

See file: Prb3_6.xls

7.Executive desks = 100, Senator desks = 500, Maximum profit = $59,300 See file: Prb3_7.xls

8.Acres planted in watermelons = 60, Acres planted in cantaloupes = 40, Maximum profit = $26,740 See file: Prb3_8.xls

9. Doors = 20, Windows = 40 , Maximum profit = $26,000

See file: Prb3_9.xls

10.Desktops = 46.15, Laptops = 69.23, Maximum profit = $90,000 (alternate optimal solutions exist) See file: Prb3_10.xls

11.TV = 20, Managize = 2 , Minimal cost = $3.5 million

See file: Prb3_11.xls

12.a.X1 = Number of country tables to produce

X2 = Number of contemporary tables to produce

MAX 350 X1 + 450 X2

ST 1.5 X1 + 2 X2 ( 1,000

3 X1 + 4.5 X2 ( 2,000

2.5 X1 + 1.5 X2 ( 1,500

X1/ ( X1 +X2) ( 0.20 (implement as X1 ( 0.2* ( X1 +X2) ) X2/ ( X1 +X2) ( 0.30(implement as X2 ( 0.3* ( X1 +X2) ) Xi ( 0

Many students attempt to implement the ratio constraints in their original form; resulting in a division by zero error at the null solution and a message from Solver that the model is not linear. The algebraic equivalence of the alternate form of these constraints (given parenthetically above) should be noted.

b.See file: Prb3_12.xls

c.X1 = 405.80, X2 = 173.91, Maximum revenue = $220,290

13. a.X1,j = Number of dehumidifiers made in Atlanta in month j X2,j = Number of dehumidifiers made in Phoenix in month j Bj = Beginning inventory in month j

MIN 400 (X11 + X12 + X13 ) +360 (X21 + X22 + X23 ) + 30 ( B1 + B2 + B3 )

ST B1 + X11 + X21 – 300 ( 0

B2 + X12 + X22 – 400 ( 0

B3 + X13 + X23 – 500 ( 0

Xij ( 300

Xij ( 0

Where

B1 = 0

B2 = B1 + X11 + X21 – 300

B3 = B2 + X12 + X22 – 400

b.See file: Prb3_13.xls

c.X11 = 0, X12 = 100, X13 = 200, X21 = 300, X22 = 300, X23 = 300, Maximum revenue = $444,000

14.a.X1 = pounds of Whole product to produce

X2 = pounds of Cluster product to produce

X3 = pounds of Crunch product to produce

X4 = pounds of Roasted product to produce

MAX 1.85 X1 + 1.4 X2 + 1.04 X3 + 1.40 X4

ST 1 X1 + 1 X2 + 1 X3 + 1 X4 < 3600

2 X1 + 1.5 X2 + 1 X3 + 1.75 X4 < 3600

1 X1 + 0.7 X2 + 0.2 X3 + 0.00 X4 < 3600

2.5 X1 + 1.6 X2 + 1.25 X3 + 1 X4 < 3600

0.6 X1 + 0.4 X2 + 0.2 X3 + 1 X4 < 1100

0.4 X1 + 0.6 X2 + 0.8 X3 + 0 X4 < 800

1,000 < X1