Speadsheet Modeling & Decision Analysis 5e: Chapter 3 Solutions

Only available on StudyMode
  • Download(s) : 165
  • Published : May 15, 2013
Open Document
Text Preview
Chapter 3
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
tracking img