Question

The following table reports the Consumer Pirce Index for the Los Angeles area on a monthly basis from January 1998 to December 2000 (base year=1982-1984). Eliminating the data for 2000, use Excel to forecast the index for all of 2000 using a three-and -six month average. Which provides a better forecast for 2000 using the data provided?

Salvatore Chapter 6 Appendix Problem 3 (p.261)
Time	CPI	forecast(w=0.3)	(A-F)^2	forecast(w=0.7)	(A-F)^2
Jan-98	161.0	166.63		166.63
Feb-98	161.1	164.94
Mar-98	161.4	163.79
Apr-98	161.8	163.07
May-98	162.3	162.69
Jun-98	162.2	162.57
Jul-98	162.1	162.46
Aug-98	162.6	162.35
Sep-98	162.6	162.43
Oct-98	163.2	162.48
Nov-98	163.4	162.70
Dec-98	163.5	162.91
Jan-99	164.2	163.08
Feb-99	164.6	163.42
Mar-99	165.0	163.77
Apr-99	166.6	164.14
May-99	166.2	164.88
Jun-99	165.4	165.28
Jul-99	165.8	165.31
Aug-99	166.3	165.46
Sep-99	167.2	165.71
Oct-99	167.2	166.16
Nov-99	167.1	166.47
Dec-99	167.3	166.66
Jan-00	167.9	166.85	1.10
Feb-00	169.3	167.17	4.55
Mar-00	170.7	167.81	8.37
Apr-00	170.6	168.67	3.71
May-00	171.1	169.25	3.41
Jun-00	171.0	169.81	1.42
Jul-00	171.7	170.16	2.36
Aug-00	172.2	170.63	2.48
Sep-00	173.3	171.10	4.85
Oct-00	173.8	171.76	4.17
Nov-00	173.5	172.37	1.28
Dec-00	173.5	172.71	0.62

Answer 1

Time	CPI	3-Month Moving Average	6-Month Moving Average	Error for 3-M MA	Absolute Error for 3-M MA	Error for 6-M MA	Absolute Error for 6-M MA
Jan-98	161
Feb-98	161.1
Mar-98	161.4
Apr-98	161.8
May-98	162.3
Jun-98	162.2
Jul-98	162.1
Aug-98	162.6
Sep-98	162.6
Oct-98	163.2
Nov-98	163.4
Dec-98	163.5
Jan-99	164.2
Feb-99	164.6
Mar-99	165
Apr-99	166.6
May-99	166.2
Jun-99	165.4
Jul-99	165.8
Aug-99	166.3
Sep-99	167.2
Oct-99	167.2
Nov-99	167.1
Dec-99	167.3
Jan-00	167.9	167.2	166.8	0.7	0.7	1.1	1.1
Feb-00	169.3	167.4	167.2	1.9	1.9	2.1	2.1
Mar-00	170.7	168.2	167.7	2.5	2.5	3.0	3.0
Apr-00	170.6	169.3	168.3	1.3	1.3	2.3	2.3
May-00	171.1	170.2	168.8	0.9	0.9	2.3	2.3
Jun-00	171	170.8	169.5	0.2	0.2	1.5	1.5
Jul-00	171.7	170.9	170.1	0.8	0.8	1.6	1.6
Aug-00	172.2	171.3	170.7	0.9	0.9	1.5	1.5
Sep-00	173.3	171.6	171.2	1.7	1.7	2.1	2.1
Oct-00	173.8	172.4	171.7	1.4	1.4	2.2	2.2
Nov-00	173.5	173.1	172.2	0.4	0.4	1.3	1.3
Dec-00	173.5	173.5	172.6	0.0	0.0	0.9	0.9
MAD for 3 month moving average = (sum of the absolute errors)/no. of observation
MAD for 3 month moving average = (.7+1.9+2.5+1.3+.9+.2+.8+.9+1.7+1.4+.4+0)/10
MAD for 3 month moving average = 1.27
MAD for 6 month moving average = (sum of the absolute errors)/no. of observation
MAD for 6 month moving average = (1.1+2.1+3.0+2.3+2.3+1.5+1.6+1.5+2.1+2.2+1.3+.9)/10
MAD for 6 month moving average = 2.19

Since MAD is greater for the 6 month moving average, than the 3 month moving average, then it is good to go with the 3 month moving average that represents superior accuracy in the forecast.