Time Series Example

Using QM

Naïve Method # 1
Time Sales Demand(y) Forecast Error |Error| Error^2 |Pct Error|
1 20 Past period 1 20
2 24 Past period 2 24 20 4 4 16 16.67%
3 27 Past period 3 27 24 3 3 9 11.11%
4 29 Past period 4 29 27 2 2 4 6.90%
5 23 Past period 5 23 29 -6 6 36 26.09%
6 31 Past period 6 31 23 8 8 64 25.81%
7 16 Past period 7 16 31 -15 15 225 93.75%
8 20 Past period 8 20 16 4 4 16 20%
9 23 Past period 9 23 20 3 3 9 13.04%
10 27 Past period 10 27 23 4 4 16 14.82%
11 29 Past period 11 29 27 2 2 4 6.90%
12 28 Past period 12 28 29 -1 1 1 3.57%
13 32 Past period 13 32 28 4 4 16 12.50%
14 30 Past period 14 30 32 -2 2 4 6.67%
15 28 Past period 15 28 30 -2 2 4 7.14%
TOTALS 387 8 60 424 264.95%
AVERAGE 25.8 0.571 4.286 30.286 18.93%
Next period forecast 28 (Bias) (MAD) (MSE) (MAPE)
Std err 5.944
naïve Metod # 2 Not easy to do using QM Please use Excel
Demand(y) Forecast Error |Error| Error^2 |Pct Error|
Past period 1 20
Past period 2 24
Past period 3 27
Past period 4 29
Past period 5 23 25 -2 2 4 8.70%
Past period 6 31 25.75 5.25 5.25 27.563 16.94%
Past period 7 16 27.5 -11.5 11.5 132.25 71.88%
Past period 8 20 24.75 -4.75 4.75 22.563 23.75%
Past period 9 23 22.5 0.5 0.5 0.25 2.17%
Past period 10 27 22.5 4.5 4.5 20.25 16.67%
Past period 11 29 21.5 7.5 7.5 56.25 25.86%
Past period 12 28 24.75 3.25 3.25 10.563 11.61%
Past period 13 32 26.75 5.25 5.25 27.563 16.41%
Past period 14 30 29 1 1 1 3.33%
Past period 15 28 29.75 -1.75 1.75 3.063 6.25%
TOTALS 387 7.25 47.25 305.313 203.56%
AVERAGE 25.8 0.659 4.295 27.756 18.51%
Next period forecast 29.5 (Bias) (MAD) (MSE) (MAPE)
Std err 5.824
Demand(y) Forecast Error |Error| Error^2 |Pct Error|
Past period 1 20
Past period 2 24 20 4 4 16 16.67%
Past period 3 27 22.4 4.6 4.6 21.16 17.04%
Past period 4 29 25.16 3.84 3.84 14.746 13.24%
Past period 5 23 27.464 -4.464 4.464 19.927 19.41%
Past period 6 31 24.786 6.214 6.214 38.619 20.05%
Past period 7 16 28.514 -12.514 12.514 156.606 78.21%
Past period 8 20 21.006 -1.006 1.006 1.011 5.03%
Past period 9 23 20.402 2.598 2.598 6.748 11.29%
Past period 10 27 21.961 5.039 5.039 25.392 18.66%
Past period 11 29 24.984 4.016 4.016 16.125 13.85%
Past period 12 28 27.394 0.606 0.606 0.368 2.17%
Past period 13 32 27.758 4.243 4.243 17.999 13.26%
Past period 14 30 30.303 -0.303 0.303 0.092 1.01%
Past period 15 28 30.121 -2.121 2.121 4.499 7.58%
TOTALS 387 14.747 55.564 339.293 237.46%
AVERAGE 25.8 1.053 3.969 24.235 16.96%
Next period forecast 28.848 (Bias) (MAD) (MSE) (MAPE)
Std err 5.317

Using Excel

Naïve Method # 1
Time Sales Y Ycap Err Abs(Err) Err^2 APE
1 24
2 25 24 1 1 1 4
3 28 25 3 3 9 10.7142857143
4 24 28 -4 4 16 16.6666666667
5 26 24 2 2 4 7.6923076923
6 21 26 -5 5 25 23.8095238095
7 23 21 2 2 4 8.6956521739
8 30 23 7 7 49 23.3333333333
9 24 30 -6 6 36 25
10 26 24 2 2 4 7.6923076923
11 24 26 -2 2 4 8.3333333333
12 29 24 5 5 25 17.2413793103
29 3.5454545455 16.0909090909 13.9253445205
Forecast MAD MSE MAPE
Naïve Method # 2 Ycap = Average Sales
Time Sales Y Ycap Err Abs(Err) Err^2 APE
1 24 25.0833333333 -1.0833333333 1.0833333333 1.1736111111 4.5138888889
2 25 25.0833333333 -0.0833333333 0.0833333333 0.0069444444 0.3333333333
3 27 25.0833333333 1.9166666667 1.9166666667 3.6736111111 7.0987654321
4 24 25.0833333333 -1.0833333333 1.0833333333 1.1736111111 4.5138888889
5 26 25.0833333333 0.9166666667 0.9166666667 0.8402777778 3.5256410256
6 21 25.0833333333 -4.0833333333 4.0833333333 16.6736111111 19.4444444444
7 23 25.0833333333 -2.0833333333 2.0833333333 4.3402777778 9.0579710145
8 28 25.0833333333 2.9166666667 2.9166666667 8.5069444444 10.4166666667
9 24 25.0833333333 -1.0833333333 1.0833333333 1.1736111111 4.5138888889
10 26 25.0833333333 0.9166666667 0.9166666667 0.8402777778 3.5256410256
11 24 25.0833333333 -1.0833333333 1.0833333333 1.1736111111 4.5138888889
12 29 25.0833333333 3.9166666667 3.9166666667 15.3402777778 13.5057471264
Average 25.0833333333 25.0833333333 1.7638888889 4.5763888889 7.080313802
Forecast MAD MSE MAPE
Moving Average Method Ycap(5) = Average (Y1,Y2,Y3,Y4)
Time Sales Y Ycap Err Abs(Err) Err^2 APE
1 24
2 25
3 27
4 24
5 26 25 1 1 1 3.8461538462
6 21 25.5 -4.5 4.5 20.25 21.4285714286
7 23 24.5 -1.5 1.5 2.25 6.5217391304
8 28 23.5 4.5 4.5 20.25 16.0714285714
9 24 24.5 -0.5 0.5 0.25 2.0833333333
10 26 24 2 2 4 7.6923076923
11 24 25.25 -1.25 1.25 1.5625 5.2083333333
12 29 25.5 3.5 3.5 12.25 12.0689655172
Average 25.0833333333 25.75 2.34375 7.7265625 9.3651041066
Forecast MAD MSE MAPE
Exponential Smoothing Method alpha = 0.7
Time Sales Y Ycap Err Abs(Err) Err^2 APE
1 24
2 25 24 1 1 1 4
3 27 24.7 2.3 2.3 5.29 8.5185185185
4 24 26.31 -2.31 2.31 5.3361 9.625
5 26 24.693 1.307 1.307 1.708249 5.0269230769
6 21 25.6079 -4.6079 4.6079 21.23274241 21.9423809524
7 23 22.38237 0.61763 0.61763 0.3814668169 2.6853478261
8 28 22.814711 5.185289 5.185289 26.8872220135 18.5188892857
9 24 26.4444133 -2.4444133 2.4444133 5.9751563812 10.1850554167
10 26 24.73332399 1.26667601 1.26667601 1.6044681143 4.8718308077
11 24 25.619997197 -1.619997197 1.619997197 2.6243909183 6.7499883208
12 29 24.4859991591 4.5140008409 4.5140008409 20.3762035916 15.565520141
27.6457997477 2.4702642134 8.4014544769 9.7899503951
Forecast MAD MSE MAPE
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.449743533
R Square 0.2022692455
Adjusted R Square 0.1409053413 Ycap =22.1 + 0.46*X Period # 16
Standard Error 4.2791341427 Forecast = 29.46
Observations 15
ANOVA
df SS MS F Significance F
Regression 1 60.3571428571 60.3571428571 3.2962251695 0.0925660468
Residual 13 238.0428571429 18.310989011
Total 14 298.4
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 22.0857142857 2.3251024746 9.4988132896 0.0000003256 17.0626357765 27.108792795 17.0626357765 27.108792795
Time 0.4642857143 0.2557271775 1.8155509273 0.0925660468 -0.0881792646 1.0167506932 -0.0881792646 1.0167506932
RESIDUAL OUTPUT
Sales Y Ycap Error
Actual Y Predicted Sales Y Residuals Abs(ERR) Err^2 APE
20 22.55 -2.55 2.55 6.5025 12.75
24 23.0142857143 0.9857142857 0.9857142857 0.9716326531 4.1071428571
27 23.4785714286 3.5214285714 3.5214285714 12.4004591837 13.0423280423
29 23.9428571429 5.0571428571 5.0571428571 25.5746938776 17.4384236453
23 24.4071428571 -1.4071428571 1.4071428571 1.9800510204 6.1180124224
31 24.8714285714 6.1285714286 6.1285714286 37.5593877551 19.7695852535
16 25.3357142857 -9.3357142857 9.3357142857 87.1555612245 58.3482142857
20 25.8 -5.8 5.8 33.64 29
23 26.2642857143 -3.2642857143 3.2642857143 10.6555612245 14.1925465839
27 26.7285714286 0.2714285714 0.2714285714 0.0736734694 1.0052910053
29 27.1928571429 1.8071428571 1.8071428571 3.2657653061 6.2315270936
28 27.6571428571 0.3428571429 0.3428571429 0.1175510204 1.2244897959
32 28.1214285714 3.8785714286 3.8785714286 15.0433163265 12.1205357143
30 28.5857142857 1.4142857143 1.4142857143 2.0002040816 4.7142857143
28 29.05 -1.05 1.05 1.1025 3.75
3.120952381 15.8695238095 13.5874921609
MAD MSE MAPE

Sheet3



You Need a Professional Writer To Work On Your Paper?