Forecasting: Time series and trend analysis

5-14

		Solutions to Forecasting Problems
Problem 5-14 through 16
		Three period Weighted Average
		0.25	0.25	0.5
			Error
Period	Sales Yt	Forecast Ft	Abs(Yt - Ft)	(Yt-Ft)^2	APE
1	4
2	6
3	4
4	5	4.500	0.500	0.250	10
5	10	5.000	5.000	25.000	50
6	8	7.250	0.750	0.563	9.38
7	7	7.750	0.750	0.563	10.71
8	9	8.000	1.000	1.000	11.11
9	12	8.250	3.750	14.063	31.25
10	14	10.000	4.000	16.000	28.57
11	15	12.250	2.750	7.563	18.33
		14.000	2.313	8.125	21.169
		Forecast	MAD	MSSE	MAPE
		Two period Simple				Four period Simple
		Moving Average				Moving Average
			Error				Error
Period	Sales Yt	Forecast Ft	Abs(Yt - Ft)	(Yt-Ft)^2	APE	Forecast Ft	Abs(Yt - Ft)	(Yt-Ft)^2	APE
1	4
2	6
3	4	5	1.000	1.000	25
4	5	5	0.000	0.000	0
5	10	4.5	5.500	30.250	55	4.750	5.250	27.563	52.5
6	8	7.5	0.500	0.250	6.25	6.250	1.750	3.063	21.88
7	7	9	2.000	4.000	28.57	6.750	0.250	0.063	3.57
8	9	7.5	1.500	2.250	16.67	7.500	1.500	2.250	16.67
9	12	8	4.000	16.000	33.33	8.500	3.500	12.250	29.17
10	14	10.5	3.500	12.250	25	9.000	5.000	25.000	35.71
11	15	13	2.000	4.000	13.33	10.500	4.500	20.250	30
		13.667	2.222	7.778	22.572	12.500	3.107	12.920	21.056
		Forecast	MAD	MSSE	MAPE	Forecast	MAD	MSSE	MAPE
SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9069144874
R Square	0.8224938875
Adjusted R Square	0.8027709861
Standard Error	1.7126976772
Observations	11
ANOVA
	df	SS	MS	F	Significance F
Regression	1	122.3272727273	122.3272727273	41.7024793388	0.0001170771
Residual	9	26.4	2.9333333333
Total	10	148.7272727273
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	2.2181818182	1.1075498484	2.0027828286	0.0762100924	-0.2872699994	4.7236336357
Period	1.0545454545	0.1632993162	6.4577456855	0.0001170771	0.6851367375	1.4239541716
			Error
Period	Sales Yt	Forecast Ft	Abs(Yt - Ft)	(Yt-Ft)^2	APE
1	4	3.27	0.73	0.5329	18.25
2	6	4.33	1.67	2.7889	27.83
3	4	5.38	1.38	1.9044	34.5
4	5	6.44	1.44	2.0736	28.8
5	10	7.49	2.51	6.3001	25.1
6	8	8.55	0.55	0.3025	6.88
7	7	9.6	2.6	6.76	37.14
8	9	10.65	1.65	2.7225	18.33
9	12	11.71	0.29	0.0841	2.42
10	14	12.76	1.24	1.5376	8.86
11	15	13.82	1.18	1.3924	7.87
			1.3854545455	2.3999090909	19.6345454545
		Forecast	MAD	MSSE	MAPE
Trend line Forecast has the best MAD, MSSE, and MAPE

4-19

Problem 4-19
Num. of Oservations		12		Regression Analysis
				between tourists and ridership
X	Y	X^2	X.Y		Error		Yt - Ft
Tourists	Ridership			Forecast Ft	Abs(Yt - Ft)	(Yt-Ft)^2
7	15	49	105	16.211	1.211	1.467	-1.211
2	10	4	20	8.246	1.754	3.076	1.754
6	13	36	78	14.618	1.618	2.619	-1.618
4	15	16	60	11.432	3.568	12.729	3.568
14	25	196	350	27.362	2.362	5.581	-2.362
15	27	225	405	28.955	1.955	3.824	-1.955
16	24	256	384	30.548	6.548	42.882	-6.548
12	20	144	240	24.176	4.176	17.442	-4.176
14	27	196	378	27.362	0.362	0.131	-0.362
20	44	400	880	36.921	7.079	50.119	7.079
15	34	225	510	28.955	5.045	25.448	5.045
7	17	49	119	16.211	0.789	0.622	0.789
11	22.5833333333	1796	3529		3.039	13.828	-0.000
Average	Average				MAD	MSSE
	Slope b =	1.5930
	Intecpt a=	5.0601
	Equation is Estimated Y = 5.0601 + 1.593 X
	When X = 10 then Forecast for Y =			20.9903100775
	When X = 0 then Forecast is Y = 5.0601
You can use Chart Wizzard to get the equation
and then just build Forecast, Error and Error squared
columns to find MAD and MSSE.
You can also use Regression within Data Analyis
Tool Pack to find the best equation.

4-19

5-25

Problem 5 - 25
		Exponential Smoothing Method
		Alpha =	0.2
			Error			Tracking
Period	Sales Yt	Forecast Ft	Abs(Yt - Ft)	(Yt-Ft)^2	RSFE	Signal
1	17
2	21	17.000	4.000	16.000	4.000	1.541
3	19	17.800	1.200	1.440	5.200	2.003
4	23	18.040	4.960	24.602	10.160	3.913
5	18	19.032	1.032	1.065	9.128	3.516
6	16	18.826	2.826	7.984	6.302	2.427
7	20	18.260	1.740	3.026	8.042	3.097
8	18	18.608	0.608	0.370	7.434	2.863
9	22	18.487	3.513	12.343	10.947	4.216
10	20	19.189	0.811	0.657	11.757	4.528
11	15	19.351	4.351	18.935	7.406	2.852
12	22	18.481	3.519	12.382	10.925	4.208
		19.185	2.596	8.982
		Forecast	MAD	MSSE
	There is a trend in RSFE and tracking signal which indicates weakness of the model.
	It is more than 4 MAD.