Excel's solver and linear programming.

scowlfaceq

New Member
Joined
Jul 19, 2006
Messages
1
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,792
Messages
5,655,332
Members
418,190
Latest member
Timex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top