Question

Standard Pump recently won a $14 million contract with the U.S. Navy to supply 2000 custom-designed submersible pumps over the next four months. The contract calls for the delivery of 200 pumps at the end of May, 600 pumps at the end of June, 600 pumps at the end of July, and 600 pumps at the end of August. Standard’s production capacity is 500 pumps in May, 400 pumps in June, 800 pumps in July, and 500 pumps in August. Following relationship holds between beginning and ending inventory of each month. (Beginning Inventory) + (Current Production) - (This Month's Demand) = (Ending Inventory) Management would like to develop a production schedule that will keep monthly ending inventories low (ideally 0) while at the same time minimizing the fluctuations in production level (ideally level production strategy with same number of pumps produced each month). Assuming the production fluctuation and inventory goals are of equal importance, develop and solve an optimization model to determine the best production schedule for next 4 months. List clearly: 1) How many pumps should be produced in May, June, July and August 2) Value of optimization function. What this number tells you?

i need to know how to do this in excel.

Answer 1

(1) Production units :

Month	May	June	July	August
Op. Bal.	0	200	0	100
Production	400	400	700	500
Sales	200	600	600	600
Cl. Bal.	200	0	100	0

(2) Optimize function :

In the month of May & June we have total capacity of 500 & 400 pumps respectively against the demand of 200 & 600 pumps for the same months respectively. Therefore combined capacity for both months is 900 units against the demand of 800 units. Therefore we will produce 400 pumps in May & June respectively so that at the end of June Inventory will remain at the least i.e zero.

Again total capacity for the month of July & August is 1300 units against the demand of 1200 units. Therefore we will produce 700 pumps in July. Because of this cl. balance of pumps will remain at 100 in july. In August demand is 600 units & capacity is 500 units. However we have 100 units of pumps which were manufctured in July, so that we will have total 600 units to supply in August & our inventory at the end of August will remain @zero.

In excel put this formula :

Production = Sales + Cl. Balance - Op. Balance