BOX COMPANY INCORPORATED
Box Company Incorporated (BOXCO) is a small firm specializing in the manufacture of corrugated cartons.Sales are mostly regional within a six-state surrounding area.BOXCO makes 15 different standard-sized generic type boxes for a variety of applications.Altogether, they serve around 600 customers with order size ranging from 100 boxes to blanket orders for as much as 50,000 boxes.
Historically, the Operations Manager has prepared production schedules and forecasted demand from experience.Unfortunately, due to an increasing number of customers and changes in customer purchasing policies, the accuracy of his forecasting has been declining.BOXCO regularly experiences heavy demand near the Christmas season when their customers stock up for holiday promotionals.This seasonality in demand contributes to the difficulty in making a reliable forecast.
The Operations Manager is in need of a forecasting method that will take into account both customer growth and seasonality.You have been called in to help improve the forecasts.The following demand data are available:
Month 2005 2006 2007
January122501526015750
February 88501220021220
March177751426018240
April133301788018225
May142501456016225
June180001821018250
July179002015027550
August201002222027850
September 245602615020780
October282002835029975
November 182252035022275
December 181252237527850
Using Excel:
1.Prepare a spreadsheet using the above data and determine the least squares regression for the trend.
2.Determine the seasonal variation around the trend line, and develop seasonal indices.
3.Forecast monthly demand for 2008 using both trend and seasonal indices.Submit hardcopy showing the raw data, trend, ratio-to-trend, seasonal factors, and the 2008 forecast.
4.Prepare and submit a single full-size graph containing:
? The 2005-2007 raw data
? The trend line extending from 2005 through 2008 as a neat line without symbols
? The 2008 monthly forecast
? An X scale of January/year# only (e.g. January ’05)
? Any suitable titles and legends of your choice
用excel的哪个功能怎么求the least squares regression，seasonal，variation和indices呢？用linest求出来squares regression是一个数值阿，还是应该出来一个公式呢？
|
