Question

Solve and show work using excel. Problem 1. The pigskin Company produces footballs. Pigskin must decide how many footballs to produce each month. It has decided to use a six-month planning horizon. The forecasted demands for the next six months are 10,000, 15,000, 30,000, 35,000, 25,000, and 10,000. Pigskin wants to meet these demands on time, knowing that it currently has 5,000 footballs in inventory and it can use a given month’s production to help meet the demand for that month. During each month there is enough production capacity to produce up to 30,000 footballs, and there is enough storage capacity to store up to 10,000 footballs at the end of the month. The forecasted production costs per football for the next six months are $12.50, $12.55, $12.70, $12.80, $12.85, and $12.95, respectively. The holding cost per football held in inventory at the end of any month is figured at 5% of the production cost for that month. (This cost includes the cost of storage and also the cost of money tied up in inventory.) The selling price for footballs is not considered relevant to the production decision because it is assumed that all demand will be met exactly when it occurs---at whatever the selling price is. Therefore, Pigskin wants to determine the production schedule that minimizes the total production and holding costs.

Answer 1

SOLUTION:

Assume:

1. All productions are occurs only at the beginning of the month
2. All DD are occurs after production - all units satisfy the DD in that particular month
3. Storage & holding costs are only based on ending inventory in the given month

Following image shows the spreedsheet model :

MULTIPERIOD PRODUCTION MODEL RANGE NAMES USED INITIAL INVENTORY HOLDING_COST UNITS PRODUCED B4 B5 B12: G1 INPUT DATA INITIAL INVENTORY HOLDING COSTS AS % OF PROD. COST 5000 5% PRODUCTION CAPACITY ON-HAND AFTER-PRODUCTION|B16:GI DEMAND B14: G14 MONTH PRODUCTION COST PER UNIT 3 4 B18:G18 $12.50 $12.55 $12.70 $12.80 $12.85 $12.95 ENDINGINVENTORY STORAGE_CAPACITY B20:G20 B22: G2 H28 PRODUCTION PLAN MONTH UNITS PRODUCED OTAL COST 3 4 15.000 15.000 30.000 30,000 25.000 10.000 PRODUCTION CAPACITY 30.000 30.000 30.000 30.000 30.000 30.000 ON HAND AFTER PRODUCTION 20.000 25.000 40.000 40.000 30.000 15.000 DEMAND 10.000 15.000 30.000 35.000 25.000 10.000 ENDING INVENTORY 10,000 10,000 10,000 5,000 5,000 5,000 STORAGE CAPACITY 10.000 10.000 10.000 10.000 10.000 10.000 SUMMARY OF COSTS MONTHS PRODUCTION COST HOLDING COSTS TOTALS 3 4 6 TOTALS $187,500$188,250$%381,000$384,000$321,250$129,500 $1,591, $6.250 $6,275 $6,350 $3,200 $3,213 $3,238 $28,52 $194,750$194,525$387,350$387,200$324,463$132,738$1,620,02

Following image the solution of Solvier :

MULTIPERIOD PRODUCTION MODEL INPUT DATA INITIAL INVENTORY HOLDING COST AS % OF PROD COST 50,000 5% MONTH PRODUCTION COST PER UNIT 2 4 $12.50 $12.55 $12.70 $12.80 $12.85 $12.95 PRODUCTION PLAN MONTH UNITS PRODUCED 2 4 5.000 20.000 30.000 30.000 25.000 10.000 PRODUCTION CAPACITY 30,000 30,000 30,000 30,000 30,000 30,000 ON HAND AFTER PRODUCTION 10,000 20,000 35,000 35,000 25,000 10,000 DEMAND 10.000 15.000 30.000 35.000 25.000 10.000 ENDING INVENTORY 0 5.000 5.000 STORAGE CAPACITY 10.000 10.000 10.000 10.000 10.000 10.000 SUMMARY OF COSTS MONTHS PRODUCTION COSTS HOLDING COSTS TOTALS 4 6 TOTALS $62,500$251,000$381,000$384,000$321,250$129,500 $1,529,250 $0 $3,138 $3,175 $0 $0 $6,313 $62,500$254,138$384,175$384,000$321,250$129,500$1,534,56

The model's recommendations can be implement by Pigskin for the first month only. For the next months Pigskin has to go for new forcasts.