Please create an aggregate production plan on Microsoft Excel, and show what formulas and solver constraints were used.

CASE STUDY Specialty Packaging Corporation, Part B Julie Williams, facility production planning manager at SPC Specialty Packaging Corporation (SPC), left the meeting with the collaborative forecast team with forecasts and From the discussion of this case in Chapter 7, recall that error estimates for the next three years. She then needed SPC processes polystyrene resin into recyclable/disposable to determine how to meet this demand. Because SPC containers for the food industry. Polystyrene is purchased sometimes outsourced warehousing to its supply chain as a commodity in the form of resin pellets. The resin is partners, one decision Julie had to make was whether to unloaded from bulk rail containers or overland trailers into use public or private warehousing. She also had to de- storage silos. Making the food containers is a two-step cide how much warehouse space to lease or build if she process. In the first step, resin is conveyed to an extruder, chose to use private warehousing. which turns pellets into a polystyrene sheet that is wound (continued) (continued) into rolls. The plastic comes in two forms - clear and black. Overtime is paid at 150 percent of regular-time salary. The rolls are then either used immediately to make contain- Workers are limited to 60 overtime hours per quarter. ers or put into storage. In the second step, the rolls are Extruders are fairly expensive, and the addition of loaded onto thermoforming presses, which form the sheet an extruder requires the hiring of six additional people. into container cavities and trim the cavities from the sheet. Each new extruder incurs a fixed cost of $80,000 per These manufacturing steps are shown in Figure 7-11. SPC quarter. Any new personnel hired need to be trained. currently operates for 63 working days each quarter. Each Training cost per person is $3,000. As a result, SPC has work day consists of eight hours of regular time and any decided not to purchase any new extruders over the scheduled overtime. current planning horizon. During any quarter, available Demand Forectruders may be idled if they are not to be used. The only savings here is the salary of associated workers. The collaborative forecasting team used the historical Laying off each worker, however, costs $2,500. If idled demand data provided in Table 7-4 supplemented with extruders are brought online, SPC incurs a training cost stockout data to develop a forecast for quarterly de- of $3,000 per worker. mand for both clear and black plastic containers. The demand forecast between 2010 and 2012 is shown in Thermoforming Presses Table 8-11 The plant currently has 25 thermoforming presses. Each thermoforming press requires one operator and can Extruders produce containers at the rate of 2,000 pounds per hour. The extrusion process is capital intensive, as is the invest- SPC pays each operator $15 per hour including benefits. ment in the facilities required to support it. The plant Overtime is paid at 150 percent of regular-time salary. currently has 14 extruders. Each extruder has a rated Workers are limited to 60 hours of overtime per quarter. processing capacity of 3,000 pounds per hour. A Presses may be idled for the quarter if they are not to be changeover is required whenever the extruder switches used. Laying off a thermoforming operator costs $2,500, between clear and black sheets. SPC estimates that there and training a newly hired operator costs $3,000. is a 5 percent capacity loss due to changeovers. The effective processing capacity of an extruder is thus 2,850 Subcontracting pounds per hour. Each extruder requires six workers. SPC has the option of subcontracting the production of SPC pays each worker $15 per hour including benefits. plastic sheets to one of its supply chain partners; sufficient capacity is always available on the open market. SPC Private warehousing also results in operating spends $60 per 1,000 pounds of plastic sheet produced by costs, both variable and fixed. Private warehousing is a subcontractor. available from a third-party logistics provider who has agreed to charge SPC a variable operating cost Materials Management Practices of $4 per 1,000 pounds of plastic sheet stored per Resin purchased is stored in silos. As there is quarter. To obtain this rate, SPC must sign a lease for easily be purchased at space each quarter even if it is not used for storage. practice has been to purchase resin on a quarterly basis its decision. to match the planned production. SPC must consider several variables in determin- As the extruders produce rolls of plastic sheet, the ing the amount of warehouse space it requires. Usable amount required at the thermoforming presses is passed warehousing space is the fraction of a warehouse that can forward, with the rest driven via shuttle trailer to one of actually be used to store inventory. Considerations are two public warehouses. Transportation is again required made for aisle space, shipping and receiving dock space, to bring the sheets back from the warehouse when they administrative office space, and ceiling height. Storage are needed to feed the thermoforming presses. SPC's density is another consideration. SPC must also take total transportation cost is $2 per 1,000 pounds of plastic into account velocity and times of materials movement sheet. Each quarter, SPC follows a policy of first using because the staffing level required and storage configurasheets in storage for thermoforming and only then using tions are dependent on both. For example, if materials the newly produced sheets. Any sheets left over at the must be retrieved readily, the warehouse layout must end of the quarter are put back into storage. This policy include a greater ratio of aisle and staging space to actual is followed to ensure that sheets do not deteriorate storage space. because of time in storage. The Actions and Decisions Public Warehousing Julie and her group must take two actions. The first, Public warehousing charges customers for both material given a three-year forecast as shown in Table 8-11, is to handling and storage. The SPC plant contracts with local come up with an aggregate production plan. The second warehouses to store material on a per-thousand-pound is to choose from the following three options: basis. Material handling charges are from $4 to $6 per 1,000 pounds unloaded at the warehouse. Storage 1. Continue with the strategy of storing materials charges are from $10 to $12 per 1,000 pounds in storage off-site in public warehousing. at the end of each quarter. The SPC plant negotiates 2. Lease and run a private warehouse to handle off-site annually with local warehouses to establish rates for inventory. each cost element. 3. Use a combination of both public and private warehousing. Private Warehousing In the case of private warehousing, Julie must Operating a private warehouse requires capitalized make a decision regarding the square footage to be investment either to construct a facility or to lease an leased. This decision will apply over the period 2010 to existing facility. Lease rates in any location are deter- 2012. Clearly, this decision must be made in conjunction mined by the economics associated with building costs with the preparation of an aggregate plan over the threein that location and the option value of a lease versus a year period. Ideally, the two decisions should be made long-term capital commitment. Leases are typically in jointly, as each will affect the other. force for three years, but the time span can be shorter What factors do you think influence the actions depending on a given company's negotiating strengths. and decisions? For example, do you think that the price Several viable leasing options exist for the SPC plant, all the subcontractor charges has any relationship to the more favorable than the option of building a new facility. amount of private warehousing space to be leased? Lease rates average $4 per square foot per quarter in Julie also has to decide how to handle any potential each location. On average, one square foot is required error in the demand forecast. How do you recommend per 1,000 pounds in storage. she handle these errors