The J. Mehta Company's production manager is planning a series of one-month production periods for stainless steel sinks. The forecasted demand for the next four months is as follows:
Month Demand for Stainless Steel Sinks
1 120
2 160
3 240
4 100
The Mehta firm can normally produce 100 stainless steel sinks in a month. This is done during regular production hours at a cost of $100 per sink. If demand in any one month cannot be satisfied by regular production, the production manager has three other choices:
(1) he can produce up to 50 more sinks per month in overtime but at a cost of $130 per sink;
(2) he can purchase a limited number of sinks from a friendly competitor for resale (the maximum number of outside purchases over the four-month period is 450 sinks, at a cost of $150 each);
(3) Or, he can fill the demand from on-hand/available inventory. The inventory carrying cost is $10 per sink per month.
Back orders are NOT permitted (e.g. order taken in period 3 to satisfy demand in later period 2 is not permitted). Inventory on hand at the beginning of month 1 is 40 sinks.
a. Formulate algebraically the Linear Programming (LP) model for the above "production scheduling" problem.
b. Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel solver (Provide a printout of the corresponding "Excel Spreadsheet" and the "Answer Report").