Fluent Essays

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 MAD(3) MAD(5) MAPE(3) MAPE(5) MSE(3) MSE(5) 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 Ŷ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. Systematic underestimation by MA(3) Forecast Y 654 658 665 672 673 671 693 694 701 703 702 710 712 711 728 MA-3-F 659 665 670 672 679 686 696 699.33333333333337 702 705 708 711 717 Double Moving Average Forecast Y 654 658 665 672 673 671 693 694 701 703 702 710 712 711 728 MA(3) #N/A #N/A 659 665 670 672 679 686 696 699.33333333333337 702 705 708 711 717 a+bp(p=1) 680.66666666666674 678 689.66666666666674 700 714 710.44444444444446 707.7777777777776 710.7777777777776 714 717 727 correlogram rk 1 2 3 4 5 0.57191316146540028 0.46268656716417916 0.11058344640434194 1.560379918588874E-2 -3.3242876526458617E-2 Yt 1 2 3 4 5 6 7 8 9 10 11 12 123 130 125 138 145 142 141 146 147 157 150 160 Time Y values Time Series Plot of Y-values Y 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 275 291 307 281 295 268 252 279 264 288 302 287 290 311 277 time Y-values Time series plot of Y,Yhat(3) and Trend Y 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 275 291 307 281 295 268 252 279 264 288 302 287 290 311 277 Yhat(3) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 291 293 294.33333333333331 281.33333333333331 271.66666666666669 266.33333333333331 265 277 284.66666666666669 292.33333333333331 293 296 292.66666666666669 Upward Trend in Y Y 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 654 658 665 672 673 671 693 694 701 703 702 710 712 711 728 2 Eco 309-Assignment 1 due by midnight on September 19, 2021(Chapters 1 and 4) Total points 125. Submit through D2L in Word/Excel format, single or multiple files. Must show your work, answer all parts of the question, and write necessary explanations to earn full points. You have to use Excel to solve this numerical problem (project). 1. The following data covers 20 period (could be years or quarters) for company XYZ’s sales in $millions. Period Sales(Y) 1 10 2 11 3 14 4 14 5 12 6 15 7 18 8 16 9 16 10 17 11 16 12 20 13 24 14 22 15 20 16 24 17 26 18 28 19 28 20 26 (i) Plot the data using Excel (insert, Chart, scatter, lines…) showing the trend line in the plot. Comment on the visible features in the plot. Does it have stationarity and/or Seasonality? Why or why not? (ii) Calculate the autocorrelation coefficients r1, r2, r3, r4, r5, r6, r7 and r8 for the above series and plot the “Correlogram” using Excel. What do the autocorrelation coefficients tell you about the time series. (iii) Create a table of first differences of the series (Yt-Yt-1). Plot the first differenced series and comment on its stationarity. (iv) Run 2-periods and 3-periods moving average forecasts for the above sales data and compare the forecasts using MAD, MAPE, MSE, RMSE. Also calculate the Mean Percentage Error (MPE) for MA(2) and MA(3) forecasts and discuss the systematic bias (if any) illustrating with the plot of actual vs forecasted values for both MA(2) and MA(3) forecasts. Why did such a systematic bias occur? (v) Correct this bias by performing a Double Moving Average method with four periods and plot the errors of DMA(4). Calculate its MAD, MAPE, MSE, RMSE, and MPE and compare with simple MA(4) forecast. Make a forecast for period 21 (one period beyond the sample) using DMA(4).