Question

A small business has recorded sales over the last 20 years.  The data is provided in accompanying workbook, tab “Small Business”. The business would like to use this data to provide a sales forecast for the future, specifically, the next two years. Build a spreadsheet that shows all the forecasts on a side-by-side basis so they can be compared for effectiveness using an error measure.

1. Create a chart showing the data in time.  Discuss any patterns in the dat
2. Create moving average forecasts for sales with 2, 4, and 6 period moving averages over the 20-year period.
3. Create exponential smoothing forecasts for sales using the values of alpha equal to 0.2, 0.5 and 0.8 over the 20-year period.
4. Create a linear trend forecast for sales over the 20-year perio
5. In addition to the trend (TIME) variable (i., 1, 2, 3, 4,….), from part d, add a new predictor variable, “TIMESQ”, that is the square of the TIME variable (i.e., 1, 4, 9, 16,…).  Use this predictor along with the TIME variable to create “quadratic trend” forecasts for sales over the 20-year period.  It is common to use the lower order variable (TIME) alongside the higher order variable (TIMESQ) when building a model such as this.   
6. Of the 8 models/forecasts created above, which is the best?  How was “the best” forecast determined?  (Hint: There are several methods provided in Chapter 8 of the text, but we focused on one in the class.  Remember it is easier to interpret error metrics in the same units as the data itsel)  
7. Make forecasts for the next two years using all 8 models above.  Comment on any challenges faced in this process.
8. Summarize the findings of this forecasting exercise, what are the recommendations to the management of the small business?

Small Business Sales
Year	Sales
1	279
2	288
3	334
4	379
5	408
6	412
7	416
8	435
9	428
10	435
11	462
12	440
13	474
14	476
15	497
16	460
17	481
18	462
19	436
20	451

Answer 1

   Chart showing the data in time.
The chart shows that the sales increases with time i.e., data shows an increasing trend in the long run.

   Moving average forecasts for sales with 2, 4, and 6 period moving averages over the 20-year period.
Small Business Sales

Year Sales   2-Year MA   4-Year MA   6-Year MA
1   279           
       283.5        
2   288           
       311   320     
3   334           
       356.5   352.25   350
4   379           
       393.5   383.25   372.833333
5   408           
       410   403.75   397.333333
6   412           
       414   417.75   413
7   416           
       425.5   422.75   422.333333
8   435           
       431.5   428.5   431.333333
9   428           
       431.5   440   436
10   435           
       448.5   441.25   445.666667
11   462           
       451   452.75   452.5
12   440           
       457   463   464
13   474           
       475   471.75   468.166667
14   476           
       486.5   476.75   471.333333
15   497           
       478.5   478.5   475
16   460           
       470.5   475   468.666667
17   481           
       471.5   459.75   464.5
18   462           
       449   457.5     
19   436           
       443.5        
20   451           

   Exponential smoothing forecasts for sales using the values of alpha equal to 0.2, 0.5 and 0.8 over the 20-year period.
       exponential smoothing forecasts for sales
Year   Sales   alpha=0.2   alpha=0.5   alpha=0.8
1   279   #N/A   #N/A   #N/A
2   288   279   279   279
3   334   286.2   283.5   280.8
4   379   324.44   308.75   291.44
5   408   368.088   343.875   308.952
6   412   400.0176   375.9375   328.7616
7   416   409.60352   393.96875   345.40928
8   435   414.720704   404.984375   359.527424
9   428   430.9441408   419.9921875   374.6219392
10   435   428.5888282   423.9960938   385.2975514
11   462   433.7177656   429.4980469   395.2380411
12   440   456.3435531   445.7490234   408.5904329
13   474   443.2687106   442.8745117   414.8723463
14   476   467.8537421   458.4372559   426.697877
15   497   474.3707484   467.2186279   436.5583016
16   460   492.4741497   482.109314   448.6466413
17   481   466.4948299   471.054657   450.917313
18   462   478.098966   476.0273285   456.9338504
19   436   465.2197932   469.0136642   457.9470803
20   451   441.8439586   452.5068321   453.5576643

   A linear trend forecast for sales over the 20-year period.
On running simple linear regression we get the following result.
    Coefficients
Intercept   335.4526316
X Variable 1   8.304511278

Thus, y (sale) = 335.4526 + x (year) * 8.30451
   e “quadratic trend” forecasts for sales over the 20-year period
For this we will fit a second degree parabolic trend to the given data,
Yt =a + bx +cx2
Normal equations for estimating a,b and c are :
∑y_t=na+b∑x+c∑x^2
∑xy_t=a ∑x+b ∑x^2+c ∑x^3
∑x^2 y_t=a ∑x^2+b∑x^3+c∑x^4
y   TIME    x = 2t -21   x^2    X^3   X^4   X*Y   X^2*Y
279   1   -9.5   90.25   -857.375   8145.063   -2650.5   25179.75
288   2   -8.5   72.25   -614.125   5220.063   -2448   20808
334   3   -7.5   56.25   -421.875   3164.063   -2505   18787.5
379   4   -6.5   42.25   -274.625   1785.063   -2463.5   16012.75
408   5   -5.5   30.25   -166.375   915.0625   -2244   12342
412   6   -4.5   20.25   -91.125   410.0625   -1854   8343
416   7   -3.5   12.25   -42.875   150.0625   -1456   5096
435   8   -2.5   6.25   -15.625   39.0625   -1087.5   2718.75
428   9   -1.5   2.25   -3.375   5.0625   -642   963
435   10   -0.5   0.25   -0.125   0.0625   -217.5   108.75
462   11   0.5   0.25   0.125   0.0625   231   115.5
440   12   1.5   2.25   3.375   5.0625   660   990
474   13   2.5   6.25   15.625   39.0625   1185   2962.5
476   14   3.5   12.25   42.875   150.0625   1666   5831
497   15   4.5   20.25   91.125   410.0625   2236.5   10064.25
460   16   5.5   30.25   166.375   915.0625   2530   13915
481   17   6.5   42.25   274.625   1785.063   3126.5   20322.25
462   18   7.5   56.25   421.875   3164.063   3465   25987.5
436   19   8.5   72.25   614.125   5220.063   3706   31501
451   20   9.5   90.25   857.375   8145.063   4284.5   40702.75
                        
8453      0   665   0   39667.25   5522.5   262751.3

Putting Values from table and solving we will get,
Y=457.33+ 8.305*x + x2 *(-1.043)
   How was “the best” forecast determined?
For the best forecast, you have to find a residual sum of squares for each of the models. Model having the least residual sum of the square is the best-fitted model.
   Make forecasts for the next two years using all 8 models above.
Moving Average models cannot be used for forecasting future trends.
While by putting the value of x in the other models you can get the forecasted value of y.
   findings of this forecasting exercise, what are the recommendations to the management of the small business.
I will recommend Quadratic model for forecasting because it will have the least residual sum of square.