# Forecasting With Smoothing Techniques

Forecasting with Simple Moving Average method If we choose a moving average of order k, MA(k) forecasting is calculated as:

t+1 = [Yt + Yt-1 + …+ Yt -k+1]/k where Y values are actual values and Y with hat are forecasted values. For example, for a 2-period moving average t+1 = [Yt + Yt-1]/2. The forecast for the third period will be the average of the actual values for the first and second period. The forecast for the fourth period will be the average of the second and third period actual values. We can start making forecast only from the period k +1. So, this method requires larger data set if k is larger. The larger is k the larger is the smoothing (ironing out fluctuations) and the larger is the weight given to past values. The smaller the number k, the larger the weight given to recent values and the smaller is the smoothing. With frequent turning points in the data, larger values of k will do too much smoothing and miss out most of the turning points.

Example 1: Data given for 15 periods to forecast the 16th period

t Y
1 275
2 291
3 307
4 281
5 295
6 268
7 252
8 279
9 264
10 288
11 302
12 287
13 290
14 311
15 277
16
The plot using Excel (insert-Chart) shows a fairly stationary data with almost horizontal trendline.

We run MA(3) and MA(5) using Excel and compare their forecasted errors.

t Y t(3) e(3) t(5) e(5) |e3|

|e5| e2(3) e2(5)
1 275
2 291
3 307
4 281 291 -10 10 3.6 100.0
5 295 293 2 2 0.7 4.0
6 268 294.3 -26.3 289.8 -21.8 26.3 21.8 9.8 7.4 693.4 475.2
7 252 281.3 -29.3 288.4 -36.4 29.3 36.4 11.6 12.9 860.4 1325.0
8 279 271.7 7.3 280.6 -1.6 7.3 1.6 2.6 0.6 53.8 2.6
9 264 266.3 -2.3 275 -11 2.3 11 0.9 4.1 5.4 121
10 288 265 23 271.6 16.4 23.0 16.4 8 6.2 529.0 269.0
11 302 277 25 270.2 31.8 25.0 31.8 8.3 11.5 625.0 1011.2
12 287 284.7 2.3 277 10 2.3 10 0.8 3.5 5.4 100
13 290 292.3 -2.3 284 6 2.3 6 0.8 2.1 5.4 36
14 311 293 18 286.2 24.8 18.0 24.8 5.8 8.5 324.0 615.0
15 277 296 -19 295.6 -18.6 19.0 18.6 6.9 6.3 361.0 346
16 NA 292.7 NA 293.4 NA 13.9 17.8 5 6.3 297.3 430.1
17.24 20.75
RMSE(3) RMSE(5)

You can also use Excel Moving Average function. For example, for MA(3) click on Data, then Data Analysis and select Moving Average and then specify the input range (Y column), interval 3, check label, and chart and the output range (for output range select from the fourth period for MA-3).

Double Moving Average The above data were stationary (constant mean and variance over time) where MA forecast is OK. But it is not good with trending data as shown with weekly data. A better method is the double moving average method (DMA)- MA on MA. Note that we report MA(3) from time 3 instead of time 4 (because it is MA not MA forecast for the next period) for this calculation. That is, the values are moved up one cell compared to the forecast method discussed above.

t Y M=MA(3) DMA(3) a=2M-M’ b=M-M’ DMA(F)=a+b e(MA3) e(DMA) MA-3(F)
1 654
2 658
3 665 659
4 672 665 13 659
5 673 670 665 675 5 8 665
6 671 672 669 675 3 681 1 -10 670
7 693 679 674 684 5 678 21 15 672
8 694 686 679 693 7 690 15 4 679
9 701 696 687 705 9 700 15 1 686
10 703 699 694 705 6 714 7 -11 696
11 702 702 699 705 3 710 3 -8 699
12 710 705 702 708 3 708 8 2 702
13 712 708 705 711 3 711 7 1 705
14 711 711 708 714 3 714 3 -3 708
15 728 717 712 722 5 717 17 11 711
16 727 717

The errors in MA(3) are all positive, showing consistent underestimation, a frequent issue when the data has an upward trend as indicated below.

The scatter plot of Ŷt and Yt clearly shows the systematic underestimation. The overall error is very large (MSE is 133).

Such biases can be corrected by DMA. The DMA(3) estimates are below the MA(3) estimates just like the MA(3) are below Yt. This systematic bias can be corrected by using the difference between MA(3) and DMA(3) to find the “intercept a” and “slope b” using the following formulas: (Denoting MA-3 by M and DMA-3 by M’)

at = Mt + (Mt – M’t) = 2Mt – M’t

bt = (Mt – M’t). In DMA(3) case, k = 3. So, bt = Mt – M’t. If k = 4, bt =2/3(Mt-M’t).

The forecast with DMA is at + bt p, where p is the number of periods ahead in the forecast. For one period ahead, the forecast is simply at + bt. The resulting forecast is reported in the seventh column (or second last) above. Calculation shows that the MSE is reduced from 133 (for MA-3 forecast) to only 63.7. Moreover, the errors are positive and negative showing lack of systematic bias. The forecast for period 16 is 727 (the MA-3 forecast was 717).

Exploring Data Patterns with Autocorrelation Analysis Autocorrelation is a measure of linear relation of Yt with its past (or lagged) values. Trend and seasonality patterns can also be discerned using autocorrelation analysis in addition to graphical analysis as discussed above. The formula for autocorrelation of k lags denoted as rk is:

rk = k = 0,1,2,…, where rk = autocorrelation coefficient for a lag of k periods = the average or mean of the Y values Yt = observed value at time t Yt-k = observed value at time t-k n= number of observations in the series

Example: Suppose we have data for twelve months (sales) and want to calculate r1. Using Excel, I calculated as shown below

t Yt Yt-1 Yt-

Yt-1-

( 2

(Yt-r)(Yt-1-)

1 123 -19 361
2 130 123 -12 -19 144 228
3 125 130 -17 -12 289 204
4 138 125 -4 -17 16 68
5 145 138 3 -4 9 -12
6 142 145 0 3 0 0
7 141 142 -1 0 1 0
8 146 141 4 -1 16 -4
9 147 146 5 4 25 20
10 157 147 15 5 225 75
11 150 157 8 15 64 120
12 160 150 18 8 324 144
Total 1704 0 1474 843
Ybar =

1704/12 = 142 r1 = 843/1474 = 0.572
Similarly, we can calculate r2 or autocorrelation for lag 2 = 0.463 which is lower than that for lag 1. Generally, as time lag increases, the autocorrelation declines. Instead of going through all the above calculations it would be nice if you could use a shortcut Excel formula. There is no in-built Excel formula for autocorrelation, but you can use the following command for n =12 and k = 1 (for different n you have the change the number in the formula accordingly).

r1 = . The Excel formula is (assuming first column for t, second column for Y and first row for label in the spreadsheet):

=(SUMPRODUCT(B2:B12-AVERAGE(B2:B13), B3:B13-AVERAGE(B2:B13))/COUNT(B2:B13))/VAR.P(B2:B13)

r2 =

=(SUMPRODUCT(B2:B11-AVERAGE(B2:B13), B4:B13-AVERAGE(B2:B13))/COUNT(B2:B13))/VAR.P(B2:B13)

r3 =(SUMPRODUCT(B2:B10-AVERAGE(B2:B13), B5:B13-AVERAGE(B2:B13))/COUNT(B2:B13))/VAR.P(B2:B13)

r4 =(SUMPRODUCT(B2:B9-AVERAGE(B2:B13), B6:B13-AVERAGE(B2:B13))/COUNT(B2:B13))/VAR.P(B2:B13)

r5 =(SUMPRODUCT(B2:B8-AVERAGE(B2:B13), B7:B13-AVERAGE(B2:B13))/COUNT(B2:B13))/VAR.P(B2:B13)

and so on. We get the values :

k rk
1 0.572
2 0.463
3 0.111
4 0.016
5 -0.033
A plot of rk against k is known as a correlogram using Insert, recommended charts-more charts-X-Y plot)

Autocorrelation coefficients for different time lags can be used to answer the following: (i) Are the data random? (White noise) (ii) Does data contain a trend (nonstationary)? (iii) Are the data stationary? (iv) Are the data seasonal?

If the series is random, the autocorrelations for any time lag are close to zero. If there is trend, the autocorrelations for the first several time lags are significantly different from zero but gradually drop toward zero. The first autocorrelation is very large, close to 1. The second lag autocorrelation will also be large. We see such a feature in the above example, as the time plot also shows:

If the time series has seasonal pattern, the autocorrelations will be pronounced at intervals equal to seasonal lag or its multiples: seasonal lag 4 for quarterly data and 12 for monthly data. There are statistical tests for the significance of the autocorrelation coefficients, but we will skip them in this course.

## Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
\$26
The price is based on these factors:
Number of pages
Urgency
Basic features
• Free title page and bibliography
• Unlimited revisions
• Plagiarism-free guarantee
• Money-back guarantee
On-demand options
• Writer’s samples
• Part-by-part delivery
• Overnight delivery
• Copies of used sources
Paper format
• 275 words per page
• 12 pt Arial/Times New Roman
• Double line spacing
• Any citation style (APA, MLA, Chicago/Turabian, Harvard)

## Benefits of our college essay writing service

• ### 80+ disciplines

Buy an essay in any subject you find difficult—we’ll have a specialist in it ready

• ### Custom formatting

APA, MLA, Chicago—we can use any formatting style you need.

• ### Plagiarism check

Get a paper that’s fully original and checked for plagiarism

### What the numbers say?

• 527
writers active
• 9.5 out of 10
current average quality score
• 98.40%
of orders delivered on time
error: