Sales Forecast
Y Shoes Inc. 2007
Objective
Y Shoes Inc sells all kinds of shoes throughout the year to both men and women. In January, 2007, Y Shoes Inc wants to develop a forecasting model to estimate the sale demand (in dollars) for Jan to March 2007.
Data Available
Year 2003 Month 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 Seq 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Amount 1421 1600 1912 1973 1999 1814 1916 2454 1783 1846 1916 2585 1532 1742 1983
Y Shoes Inc has been tracking monthly sales data for the entire company for many years. Latest 4 years, 48 monthly sales records are used for this forecasting exercise due to their relevance.
2003 2003 2003 2003 2003 2003
2003 2003 2003 2003 2003 2004 2004 2004
Analysis
First, all 48 data points are plotted in a scatter plot to eyeball any pattern in the past four years.
Clear seasonality and an upward trend are noticed as below.
Time Series of Amount / Shoe sales
3500 3000 2500 2000 1500 1000 500 0 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 Observation #
StatTools Student Version
For Academic Use Only
Analysis- De-seasonalize data
Original monthly sales data is first de-seasonalized to remove impact of seasonality. (Path: Stat tools – Time Series & Forecasting – Forecast – (12 months) moving average and de-seasonalize).
Months Jan-2003 Feb-2003 Mar-2003 Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 Sep-2003 Oct-2003 Nov-2003 Dec-2003 Jan-2004 Amount SI Deseason
11421.0000 31600.0000 91912.0000 71973.0000 1999.0000 91814.0000 41916.0000 62454.0000 51783.0000 21846.0000 1916.0000 82585.0000 11532.0000
1.58 0.29 0.21 1.81 0.79 0.84 1.02 1.79 1.35 0.31 0.36 1.64 1.58
7213.94 107725.52 438325.01 39709.56 2516.90 109637.63 41058.69 34962.82 38380.41 69909.96 5367.41 50240.29 7284.05
Analysis – Trend identification
De-seasonalized monthly sale data for the past 48 months are plotted in a scatter-plot. And trend line is added to identify the trend in de-seasonalized data.
Chart Title 2500.00 2000.00 1500.00 1000.00 500.00 0.00 0 10 20 30 40 50 60 DS Amt Amount Linear (DS Amt Amount) y = 7.39x + 1869.8 R2 = 0.7712
Analysis – Trend Forecast and Re-seasonalized Forecast
Excel is then used to calculate trend forecast according to the linear equation identified in previous slide. Calculated trend forecast is re-seasonalized as Trend * Seasonal Index.
Mean Square Error and Root of Mean square error is also calculated to measure goodness of fit of this model. MSE = 3321.26. Root of MSE = 57.63.
Analysis – Evaluation of different Models
Alternative forecasting Models are also developed using Stat tool to see whether other models may fit this case better. A comparison is listed next page. The Comparison indicates the trend & seasonality model explained in previous slides fits this situation best as evidenced by lowest MAE, Root of MSE and MAPE.
Analysis – Goodness of fit
Naïve 1 (note 1) Mean Abs Err Root Mean Sq Err Mean Abs Per% Err Naïve 2 (note 2) Moving average 3 months Simple Exponential Holt's Exponential Trend * SI
313.83 445.28 15.58%
95.22 124.16 -599.98%
274.23 368.97 12.93%
243.04 339.62 11.43%
268.37 337.13 13.20%
45.33 57.63 2.18%
Note 1: Forecast of sales in current month equals to sals in previous month, which does not catch seasonality in the data Note 2. Forecast of sales in current month equals to sales in the month one year ago, which does not catch the trend in previous 12 months
Implementation
Using the Trend & Seasonality forecast model developed, Y=Seasonal Index * (7.39 X + 1869.8), total sales demand for Jan – Mar 2007 is estimated to be $5,944.24 MM (1724.96+1984.09+2235.19)
Month Forecasting Data Jun-2006 Jul-2006 Aug-2006 Sep-2006 Oct-2006 Nov-2006 Dec-2006 Jan-2007 Feb-2007 Mar-2007 X Trend forecast Re-seasonalized
42.00 43.00 44.00 45.00 46.00 47.00 48.00 49.00 50.00 51.00
2180.28 2187.67 2195.06 2202.45 2209.84 2217.23 2224.62 2232.01 2239.4 2246.79
2029.46 2148.84 2613.46 2002.56 2126.06 2153.03 2991.69 1724.96 1984.09 2235.19