The J. Mehta Company’s production manager is planning for a series of 1-month production periods for stainless steel sinks. The demand for the next 4 months is as follows:

DEMAND FOR

MONTH STAINLESS STEEL SINKS
1 ……………………………… 130
2 ……………………………… 160
3 ……………………………… 290
4 ……………………………… 150

The Mehta firm can normally produce 80 stainless steel sinks in a month. This is done during regular production hours at a cost of $100 per sink. If demand in any 1 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 $150 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 4-month period is 400 sinks, at a cost of $200 each); (3) He can fill the demand from his on-hand inventory. The inventory carrying cost is $20 per sink per month. Back orders are not permitted. Inventory on hand at the beginning of month 1 is 40 sinks.

1. Formulate algebraically the Linear Programming (LP) model for the above “production scheduling” problem. Define the decision variables, objective function, and constraints.

2. 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”).