Problem Set #1: Time Series Forecasting

Consider the information and data presented in the e-reserve case study (located on the left-hand side navigation on Blackboard under e-Reserve Items) Specialty Packaging Corporation (pages 207-208 in scanned document). In the case study, Julie is asked to select the appropriate forecasting method for the two types of containers, and use that method to forecast demand for years 6-8. For this assignment, assume that Julie chooses to implement a time series model with level, trend, and seasonal components. Create an Excel spreadsheet that fits a time series model (with level, trend, and seasonal components) for for the black plastic containers. Include calculations necessary to determine forecast margin of uncertainty. Use your models to forecast future demand (for Years 6-8) for the black plastic container, including a margin of uncertainty. Assume that forecast errors are normally distributed. The assessment of your work will include the accuracy as well as the clarity of the spreadsheet (e.g., clear labels, good alignment, easy to understand, etc.).

CASE STUDY Specialty Packaging Corporation Julie Williams had a lot on her mind when she left the form the sheet into containers and trim the containers conference room at Specialty Packaging Corporation from the sheet. The two manufacturing steps are shown (SPC). Her divisional manager had informed her that in Figure 7-11. she would be assigned to a team consisting of SPC's _ Over the past five years, the plastic packaging marketing vice president and staff members from their business has grown steadily. Demand for containers key customers. The goal of this team was to improve made from clear plastic comes from grocery stores, baksupply chain performance, as SPC had been unable to eries, and restaurants. Caterers and grocery stores use meet demand effectively over the previous several the black plastic trays as packaging and serving trays. years. This often left SPC's customers scrambling to Demand for clear plastic containers peaks in the summer meet new client demands. Julie had little contact with months, whereas demand for black plastic containers SPC's customers and wondered how she would add peaks in the fall. Capacity on the extruders is not suffivalue to this process. She was told by her division man- cient to cover demand for sheets during the peak seaager that the team's first task was to establish a collab- sons. As a result, the plant is forced to build inventory of orative forecast using data from both SPC and its each type of sheet in anticipation of future demand. customers. This forecast would serve as the basis for Table 7-4 and Figure 7-12 display historical quarterly improving the firm's performance, as managers could demand for each of the two types of containers (clear use this more accurate forecast for their production and black). The team modified SPC's sales data by planning. Improved forecasts would allow SPC to accounting for lost sales to obtain true demand data. improve delivery performance. Without the customers involved in this team, SPC would never have known this information, as the company did not keep track of lost orders. SPC SPC turns polystyrene resin into recyclable/disposable containers for the food industry. Polystyrene is purForecasting chased as a commodity in the form of resin pellets. The As a first step in the team's decision making, it wants to resin is unloaded from bulk rail containers or overland forecast quarterly demand for each of the two types of trailers into storage silos. Making the food containers containers for years 6 to 8. Based on historical trends, is a two-step process. First, resin is conveyed to an demand is expected to continue to grow until year 8 . extruder, which converts it into a polystyrene sheet that after which it is expected to plateau. Julie must select the is wound into rolls. The plastic comes in two forms - appropriate forecasting method and estimate the likely clear and black. The rolls are either used immediately forecast error. Which method should she choose? Why? to make containers or put into storage. Second, the Using the method selected, forecast demand for years 6 rolls are loaded onto thermoforming presses, which to 8. TABLE 7-4 Quarterly Historical Demand for Clear and Black Plastic Containers FIGURE 7-12 Plot of Quarterly Demand for Clear and Black Plastic Containers