Speciality packaging corporation case study solution

Can you help me understand this Excel question?

Specialty Packaging Corporation, Part A

Julie Williams had a lot on her mind when she left the conference room at Specialty Packaging Corporation (SPC). Her divisional manager had informed her that she would be assigned to a team consisting of SPC’s marketing vice president and several staff members from their key customers. The goal of this team was to improve supply chain performance, as SPC had been unable to meet all the demand of their customers over the past several years. This often left SPC’s customers scrambling to meet new client demands. Julie had little contact with SPC’s customers and wondered how she would add value to this process. She was told by her division manager that the team’s first task was to establish a collaborative forecast using data from both SPC and their customers. This forecast would serve as the basis for improving their performance as they could use this more accurate forecast for their production planning. With this in place, SPC would have a key tool to improve delivery performance.

SPC

SPC turns polystyrene resin into recyclable/disposable containers for the food industry. Polystyrene is purchased as a commodity in the form of resin pellets. The resin is unloaded from bulk rail containers or overland trailers into storage silos. Making the food containers is a two-step process. First, resin is conveyed to an extruder, which converts it into polystyrene sheet wound into rolls. The plastic comes in two forms: clear and black. The rolls are either used immediately to make containers or are put into storage. Second, the rolls are loaded onto thermo-forming presses, which form the sheet into containers and trim the containers from the sheet.

Over the past five years, the plastic packaging business has grown steadily. Demand for containers made from clear plastic comes from grocery stores, bakeries, and restaurants. Demand for black plastic trays comes from caterers and grocery stores, who use them as packaging and serving trays. Demand for clear plastic containers peaks in the summer months, whereas demand for black plastic containers peaks in the fall. Capacity on the extruders is not sufficient to cover demand for sheets during the peak seasons. As a result, the plant is forced to build inventory of each type of sheet in anticipation of future demand. Table 1 display historical quarterly demand for each of the two types (clear and black) of containers. This demand data was modified from SPC’s sales data by the team to take into account the lost sales when SPC was out of stock. Without the customers involved in this team, SPC would never have known this information as they did not keep track of lost orders.