Question

Greenville Cabinets received a contract to produce speaker cabinets for a major speaker manufacturer. The contract calls for the production of 3300 bookshelf speakers and 4100 floor speakers over the next two months, with the following delivery schedule:

Model	Month 1	Month 2
Bookshelf	2100	1200
Floor	1500	2600

Greenville estimates that the production time for each bookshelf model is 0.7 hour and the production time for each floor model is 1 hour. The raw material costs are $10 for each bookshelf model and $12 for each floor model. Labor costs are $22 per hour using regular production time and $33 using overtime. Greenville has up to 2400 hours of regular production time available each month and up to 1000 additional hours of overtime available each month. If production for either cabinet exceeds demand in month 1, the cabinets can be stored at a cost of $5 per cabinet. For each product, determine the number of units that should be manufactured each month on regular time and on overtime to minimize total production and storage costs.

Build an LP model for this problem (insert a textbox in an Excel worksheet and type in the LP model by defining decision variables, clearly stating the objective function and the constraints). Then, enter your model into the same Excel worksheet and solve it using Excel solver. Save your Excel file under your full name and then upload your file.

Answer 1

Algebraic formulation:

BjR = Regular production (units) of Bookshelf in month j for j=1,2
BjO = Overtime production (units) of Bookshelf in month j for j=1,2
BjE = Ending inventory of bookshelves in month j for j=1,2

FjR = Regular production (units) of Floorin month j for j=1,2
FjO = Overtime production (units) of Floorin month j for j=1,2
FjE = Ending inventory of floors in month j for j=1,2

Min Z = 10*(2100+1200) + 12*(1500+2600) + 0.7*22*(B1R + B2R) + 1*22*(F1R + F2R) + 0.7*33*(B1O + B2O) + 1*33*(F1O + F2O) + 5*(B1E + B2E + F1E + F2E)

Subject to,

0.7*B1R + 1*F1R <= 2400
0.7*B2R + 1*F2R <= 2400

0.7*B1O + 1*F1O <= 1000
0.7*B2O + 1*F2O <= 1000

0 + B1R + B1O - B1E = 2100
B1E + B2R + B2O - B2E = 1200

0 + F1R + F1O - F1E = 1500
F1E + F2R + F2O - F2E = 1200

BjR, BjO, BjE, FjR, FjO, FjE >= 0 for j=1,2

Excel model:

Solver inputs:

Solution:

Month	Model	Demand	Regular production	Overtime production	Ending Inventory	Total output	Prod + B.I - E.I.	Regular hrs.	Overtime hrs.
1	Bookshelf	2,100	2,100	0	0	2,100	2,100	1,470	0
2	Bookshelf	1,200	1,200	0	0	1,200	1,200	840	0
1	Floor	1,500	930	610	40	1,540	1,500	930	610
2	Floor	2,600	1,560	1,000	0	2,560	2,600	1,560	1,000
COSTS
Month	Model	Material	Regular production	Overtime production	Carrying cost
1	Bookshelf	$10	$15.40	$23.10	$5.00
2	Bookshelf	$10	$15.40	$23.10	$5.00
1	Floor	$12	$22.00	$33.00	$5.00
2	Floor	$12	$22.00	$33.00	$5.00
	Regular hrs.	Overtime hrs.	Regular hrs limit	Overtime hrs limit
Month-1	2,400	610	2,400	1,000
Month-2	2400	1000	2,400	1,000
Total cost		$241,130