Explore BrainMass

Linear Progamming / Production Schedule

I'm having a hard time setting up an Excel solver model for the problem below:
Owens-Wheat uses two production lines to produce three types of fiberglass mats. The demand requirements for each type of mat (in tons) for the next four months are as follows:

Month Mat Type
1 2 3
1 200 300 400
2 300 100 300
3 200 400 200
4 300 200 100

If it were dedicated to simply producing a mat of a single type, a single line 1 machine could produce either 20 tons of a type 1 mat or 30 tons of a type 2 mat. Similarly, a single line 2 machine could produce either 25 tons of type 2 mat or 28 tons of type 3 mat. Note that mat type 1 cannot be produced on line2, while mat type 3 cannot be produced on line 1. If costs $ 5,000 per month to operate a single machine on line 1, and $ 5,500 to operate a single machine on line 2. A cost of $ 2,000 is incurred each time a new machine is purchased for either line, while a cost of $ 1,000 is incurred when a machine is retired from service. At the end of each month, Owens would like a minimum inventory of 50 tons of each mat type and the cost of holding one ton on inventory for a month of any mat type is $ 5. At the beginning of month 1, the company has 5 line 1 machines, and 8 line 2 machines. Determine a minimum cost production schedule for each mat type of each production line.