Spreadsheet Modeling Assignment



Problem Set #2

Linear Programming & Sensitivity Analysis

QMETH 501—Professor Hillier

due Monday, March 1

1. A Production Planning Problem. The Omega Manufacturing Company has discontinued the production of a certain unprofitable product line. This act created considerable excess production capacity. Management is considering devoting this excess capacity to one or more of three products, products 1, 2, and 3. The machine-hours required for each unit of the respective products, along with the available capacity of the machines, are summarized in the following table.

| |Machine-Hours Required per Unit |Available Time |

| |Product 1 |Product 2 |Product 3 |(machine hours per week) |

|Milling Machine |4 |6 |9 |2400 |

|Lathe |8 |6 |0 |1800 |

|Grinder |2 |0 |5 |1000 |

The unit profit would be $80, $40, and $50, respectively, on products 1, 2, and 3. The objective is to determine how many of each product the firm should produce to maximize profit.

a. Formulate the algebraic model for this problem—be sure to define all decision variables, write the objective function as a function of the decision variables, and write all constraints (including nonnegativity) as functions of the decision variables. (Type the algebraic model in your spreadsheet.)

b. On the same worksheet, build a linear programming spreadsheet model, and solve it using Solver.

2. Stanwood Development. The Stanwood Development Group is considering taking part in one or more different construction projects—A, B, and C—that are about to be launched. Each project requires a significant investment over the next few years and then would be sold upon completion. The projected cash flows (in millions of dollars) associated with each project are shown in the table below.

|Year |Project A |Project B |Project C |

|1 |–6 |–12 |–15 |

|2 |–9 |–13 |–10 |

|3 |20 |–6 |–10 |

|4 |0 |–6 |–8 |

|5 |0 |48 |–4 |

|6 |0 |0 |60 |

Stanwood has $15 million cash on hand now and expects to receive $10 million from other projects in each of the following years (1 through 6) that would also be available for investments. (Assume the $10 million cash in year i is available for projects in that year, e.g., a total of $25 million is available for projects in year 1). Assume that money not spent in a given year is available for spending in future years.

By acting now, the company may participate in each project either fully, fractionally (with other development partners), or not at all. If Stanwood participates at less than 100%, then all the cash flows associated with that project are reduced proportionally. Company policy requires ending each year with a cash balance of at least $2 million.

Build a linear programming spreadsheet model to determine which projects Stanwood should participate in, and at what level, so as to end up with as much cash as possible. Solve the model using Solver.

3. A Multiechelon Distribution System. A multiechelon distribution system consists of three factories, three regional warehouses, and four wholesale distribution points. Production costs are $6 per unit at factory 1, $6.10 per unit at factory 2, and $5.95 per unit at factory 3. Costs of shipping from the factories to the regional warehouses are given in Table 1, and costs of shipping from the warehouses to the wholesale distribution points are given in Table 2. Factory 1 can produce up to 4800 units per month, factory 2 up to 3500 units per month, and factory 3 up to 6500 units per month. Each of the four distribution points require at least 3000, 4800, 4200, and 2300 units per month, respectively. You must decide how much to produce and ship to each regional warehouse from each factory, and how much to ship from each regional warehouse to each distribution point to minimize total monthly costs. Do only parts b, c, and d from the top of the previous page (no algebraic formulation necessary).

| |Warehouse 1 |Warehouse 2 |Warehouse 3 |

|Factory 1 |$0.92 |$1.48 |$0.62 |

|Factory 2 |$1.46 |$0.96 |$1.12 |

|Factory 3 |$0.80 |$1.12 |$1.02 |

Table 1. Shipping Cost per Unit from Factories to Warehouses

| |Dist. Pt. 1 |Dist. Pt. 2 |Dist. Pt. 3 |Dist. Pt. 4 |

|Warehouse 1 |$0.72 |$0.94 |$0.56 |$0.60 |

|Warehouse 2 |$0.55 |$0.42 |$0.82 |$0.57 |

|Warehouse 3 |$0.83 |$0.48 |$0.32 |$0.68 |

Table 2. Shipping Cost per Unit from Warehouses to Distribution Points

4. A Production Planning Problem Revisited (Sensitivity Analysis).

Generate a Sensitivity Report for the production problem considered in #1. Use only your original spreadsheet and the “Sensitivity Report” to answer parts a-c as completely as is possible without re-solving. (Answer the questions directly on the sensitivity report spreadsheet). Then use Solver Table to answer part d. Assume that each question is independent (i.e., the changes made to the problem in one question do not apply in the other questions).

a. Suppose that the profit per unit of product 2 changes to $50. Will the solution change? What can be said about the new total profit? What if the profit per unit of product 2 changes to $30 instead? Will the solution change? What can be said about the new total profit? What if the profit per unit for both product 2 and product 3 increase by $3 each. Will the solution change? What can be said about the new total profit? Explain your answers.

b. Suppose the company can lease another Lathe at a cost of $200 per week. The extra Lathe could be operated 40 hours per week. Would this be worthwhile? Explain your answer.

c. Suppose a milling machine breaks down beyond repair, so that 40 fewer machine hours per week are available on the milling machines. How much will this cost the company? Will the optimal solution change? What would happen if, in addition, a lathe broke down, so that 40 fewer hours per week are available on the lathes as well? How much will this cost the company in total? Will the optimal solution change? Explain your answers.

d. Suppose they have options on the grinders and would like to see how the solution changes for different available hours for the grinders. Use Solver Table to create a table (directly on the original spreadsheet) that shows how the production quantities for the three products and the optimal profit change as the available grinder hours change from 0 to 2000 (in increments of 200).

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download