Forecasting: Time series and trend analysis

sri999
ForecastingClassroomexample.xlsx
Home>Business & Finance homework help>Operations Management homework help>Forecasting: Time series and trend analysis

Sheet1

Naïve Method #1 Ycap(t+1) = Y(t)
Time Sales Ycap Y-Ycap Abs(Y-Ycap) (Y-Ycap)^2 APE
1 10 ---
2 14 10 4 4.00 16.00 28.57
3 12 14 -2 2.00 4.00 16.67
4 11 12 -1 1.00 1.00 9.09
5 15 11 4 4.00 16.00 26.67
6 13 15 -2 2.00 4.00 15.38
7 16 13 3 3.00 9.00 18.75
13 16 2.67 8.33 19.19
Forecast MAD MSE MAPE
Naïve Method #2 Ycap(T) = Ybar
Time Sales Ycap Y-Ycap Abs(Y-Ycap) (Y-Ycap)^2 APE
1 10 13 -3 3.00 9.00 30.00
2 14 13 1 1.00 1.00 7.14
3 12 13 -1 1.00 1.00 8.33
4 11 13 -2 2.00 4.00 18.18
5 15 13 2 2.00 4.00 13.33
6 13 13 0 0.00 0.00 0.00
7 16 13 3 3.00 9.00 18.75
13 13 1.71 4.00 13.68
Mean Forecast MAD MSE MAPE
3 Period Maving Average Method (MAM) Ycap (4) = Avg (3 prior pds)
Time Sales Ycap Y-Ycap Abs(Y-Ycap) (Y-Ycap)^2 APE
1 10
2 14
3 12
4 11 12 -1 1.00 1.00 9.09
5 15 12.3333333333 2.6666666667 2.67 7.11 17.78
6 13 12.6666666667 0.3333333333 0.33 0.11 2.56
7 16 13 3 3.00 9.00 18.75
13 14.6666666667 1.75 4.31 12.05
Mean Forecast MAD MSE MAPE
Exponential Smoothing (ESM) Alpha = 0.30
Time Sales Ycap Y-Ycap Abs(Y-Ycap) (Y-Ycap)^2 APE
1 10 ---
2 14 10 4 4.00 16.00 28.57
3 12 11.2 0.8 0.80 0.64 6.67
4 11 11.44 -0.44 0.44 0.19 4.00
5 15 11.308 3.692 3.69 13.63 24.61
6 13 12.4156 0.5844 0.58 0.34 4.50
7 16 12.59092 3.40908 3.41 11.62 21.31
13 13.613644 2.15 7.07 14.94
Forecast MAD MSE MAPE
Trend Analysis using Regression
Time Sales Ycap Error Abs (Err) Err^2 MAPE SUMMARY OUTPUT
1 10 10.9642857143 -0.9642857143 0.96 0.93 9.64
2 14 11.6428571429 2.3571428571 2.36 5.56 16.84 Regression Statistics
3 12 12.3214285714 -0.3214285714 0.32 0.10 2.68 Multiple R 0.6785714286
4 11 13 -2 2.00 4.00 18.18 R Square 0.4604591837
5 15 13.6785714286 1.3214285714 1.32 1.75 8.81 Adjusted R Square 0.3525510204
6 13 14.3571428571 -1.3571428571 1.36 1.84 10.44 Standard Error 1.7382256963
7 16 15.0357142857 0.9642857143 0.96 0.93 6.03 Observations 7
15.712 1.33 2.16 10.37
Forecast MAD MSE MAPE ANOVA
Point Estimate df SS MS F Significance F
Confidence Interval Estimate Regression 1 12.8928571429 12.8928571429 4.2671394799 0.093750254
Est Error = "Talpha * Std Error" Residual 5 15.1071428571 3.0214285714
Doc 95% 2.4469118511 E = 4.25 Total 6 28
We are 95% confident that sales for period 8
will be 15.71 - 4.25 and 15.71 +4.25 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 10.2857142857 1.4690688429 7.0015195919 0.0009158322 6.5093526027 14.0620759687 6.5093526027 14.0620759687
Time 0.6785714286 0.3284937796 2.0657055647 0.093750254 -0.1658487145 1.5229915716 -0.1658487145 1.5229915716
Ycap = 10.28 + 0.679 * Time
RESIDUAL OUTPUT
Observation Predicted Sales Residuals
1 10.9642857143 -0.9642857143
2 11.6428571429 2.3571428571
3 12.3214285714 -0.3214285714
4 13 -2
5 13.6785714286 1.3214285714
6 14.3571428571 -1.3571428571
7 15.0357142857 0.9642857143