Excel's solver and linear programming.

[scowlfaceq]

Linear Programming and the Dunkirk Marine Case

Dunkirk Marine is in the process of developing its aggregate plan for fiberglass hull laminating material for the next 6 months. The same laminating material is used for all models of recreational boats produced and is manufactured or purchased by the linear foot. Forecasted demand requirements for the next 6 months are as follows:

Month Forecasted Demand (linear feet)
1 11,500
2 15,000
3 22,500
4 19,000
5 17,000
6 16,000

First-shift production capacity is 10,000 linear feet per month at a cost of $100 per foot. A night shift with a second set of employees can also be implemented so as to utilize the equipment as much as possible. The second-shift production capacity is 4,000 linear feet per month at a cost of $120 per foot. Cost of production is expected to remain constant through the third month. However, due to governmental regulations, the cost of producing one linear foot of laminate is expected to increase by 10% in month 4 and then remain constant until the end of the planning horizon.

Two other options are available for supplying laminate. First, production can be subcontracted out to a small fiberglass company that Dunkirk Marine has used in the past. The supplier can provide 2,200 linear feet of laminate per month for months 1 through 5 at a cost of $140 per foot. Due to other obligations however, the supplier can provide only 1,000 linear feet of laminate in month 6 at the same unit cost. Second, laminate can be purchased from Dunkirk Marine’s South American subsidiary at a cost of $150 per foot. The subsidiary can supply 1,500 linear feet of laminate per month.

There are currently 100 linear feet of laminate in stock. Inventory carrying costs are $1 per linear foot per month. Management has also decided that, in order to remain competitive in the market, Dunkirk does NOT want to backlog or stockout any of their demand for the next 6 months, even if it provides a more economical production plan.

Your assignment is to formulate a LP spreadsheet model for this problem and solve the model using Excel’s Solver. What is Dunkirk marine's optimal production strategy and total cost?

 

[goldfish]

This is an interesting sounding homework assignment... but I am not going to do it for you (though there may be other people in the forums that might). Do you have any specific questions you need to ask about it? I'd be glad to help with those. Are you stuck on any specific part?

I would start by making 3 columns representing what is produced by first shift, second shift, and the contracter each month. (for now just put in random values). Try to make a cell that determines cost from this, and perhaps another one to determine if enough has been produced to cover the demand for each period.

If this doesn't make sense I'd be glad to clarify, but you should at least give the problem a try before posting it, and if you have tried it, go ahead and post what you have done so far.

~GoldFish