Speadsheet Modeling & Decision Analysis 5e: Chapter 3 Solutions

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