The forecast demand over the next three years is shown on sheet1 in Cells A2:C14 and on sheet demand. Observe that because of the countercyclical nature of demand for black and clear containers, the aggregate demand is less variable than demand for either type of plastic. In this instance it is reasonable to plan for the aggregate demand because both types of plastic will use the same production capacity.
Sheet1 contains the following data used in the planning exercise:
Extruder data is shown in Cells B17:B23. Each extruder has a capacity (Cell B17) of 2.28 ('000) units per hour (5% is lost to changeovers). There are 14 extruders (Cell B18). Given that each extruder has 6 people, the cost of operating an extruder is $90 (regular time) and $135 (overtime). The cost of laying off each worker is in cell B22 and the cost of hiring per worker is in cell B23.
Thermoforming data is shown in Cells F17:F23. Each press has a capacity (Cell F17) of 2 ('000) units per hour. There are 25 presses(Cell F18). Given that each press has 1 person, the cost of operating press is $15 (regular time) and $22.5 (overtime). The cost of laying off each worker is in cell F22 and the cost of hiring per worker is in cell F23.
Other costs are shown in Cells F4:F8. Material cost of $10 per thousand lb. of containers is incurred for sheet produced by SPC. The lease cost for private warehousing is based on the amount leased and is incurred over the entire three year period. Other warehousing costs are based on the amount of inventory held in a given period.
Copyright 2001, Sunil Chopra