Question

Problem 1 The table below presents data for tire sales in the Costco Branch at South Dakota Avenue, Washington D.C. from the months of July through December of the Year 2017. Automobile Tire Sales 550 576 590 602 622 664 689 905 860 869 886 920 Y ear 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 Month Janua Februar March April June Jul August September October November December a. Compute a two-month moving average. What is the forecast for January 2019? b. Compute a three-month moving average. What is the forecast for January 2019? Compute a weighted 2-month moving average with weights of 0.4 and 0.7 to forecast sales for January 2019. (The weight of 0.6 is for the most recent month) Calculate the MAD for the 3 models. Based on the MAD, which of the 3 models gives a better forecast Compute the MSE for the 3 models. Based on the MSE, which of the 3 models gives a better forecast? c. d. e.

Answer 1

t	sales	2 MA	3 MA	Weighted 2		abs error 2 MA	3 MA abs error	Weighted MA
1	550
2	576
3	590	563		565.6		27		24.4
4	602	583	572	584.4		19	30	17.6
5	622	596	589.3333	597.2		26	32.66666667	24.8
6	664	612	604.6667	614		52	59.33333333	50
7	689	643	629.3333	647.2		46	59.66666667	41.8
8	905	676.5	658.3333	679		228.5	246.6666667	226
9	860	797	752.6667	818.6		63	107.3333333	41.4
10	869	882.5	818	878		13.5	51	9
11	886	864.5	878	865.4		21.5	8	20.6
12	920	877.5	871.6667	879.2		42.5	48.33333333	40.8
	Forecast	903	891.6667	906.4
				MAD		53.9	71.44444444	49.64

Formulas

t	sales	2 MA	3 MA	Weighted 2		abs error 2 MA	3 MA abs error	Weighted MA
1	550
=1+A2	576
=1+A3	590	=AVERAGE(B2:B3)		=0.6*B3+0.4*B2		=ABS(B4-C4)		=ABS(B4-E4)
=1+A4	602	=AVERAGE(B3:B4)	=AVERAGE(B2:B4)	=0.6*B4+0.4*B3		=ABS(B5-C5)	=ABS(B5-D5)	=ABS(B5-E5)
=1+A5	622	=AVERAGE(B4:B5)	=AVERAGE(B3:B5)	=0.6*B5+0.4*B4		=ABS(B6-C6)	=ABS(B6-D6)	=ABS(B6-E6)
=1+A6	664	=AVERAGE(B5:B6)	=AVERAGE(B4:B6)	=0.6*B6+0.4*B5		=ABS(B7-C7)	=ABS(B7-D7)	=ABS(B7-E7)
=1+A7	689	=AVERAGE(B6:B7)	=AVERAGE(B5:B7)	=0.6*B7+0.4*B6		=ABS(B8-C8)	=ABS(B8-D8)	=ABS(B8-E8)
=1+A8	905	=AVERAGE(B7:B8)	=AVERAGE(B6:B8)	=0.6*B8+0.4*B7		=ABS(B9-C9)	=ABS(B9-D9)	=ABS(B9-E9)
=1+A9	860	=AVERAGE(B8:B9)	=AVERAGE(B7:B9)	=0.6*B9+0.4*B8		=ABS(B10-C10)	=ABS(B10-D10)	=ABS(B10-E10)
=1+A10	869	=AVERAGE(B9:B10)	=AVERAGE(B8:B10)	=0.6*B10+0.4*B9		=ABS(B11-C11)	=ABS(B11-D11)	=ABS(B11-E11)
=1+A11	886	=AVERAGE(B10:B11)	=AVERAGE(B9:B11)	=0.6*B11+0.4*B10		=ABS(B12-C12)	=ABS(B12-D12)	=ABS(B12-E12)
=1+A12	920	=AVERAGE(B11:B12)	=AVERAGE(B10:B12)	=0.6*B12+0.4*B11		=ABS(B13-C13)	=ABS(B13-D13)	=ABS(B13-E13)
		=AVERAGE(B12:B13)	=AVERAGE(B11:B13)	=0.6*B13+0.4*B12
						=AVERAGE(G4:G13)	=AVERAGE(H4:H13)	=AVERAGE(I4:I13)

a)

b)
891.67
c)
906.4

d)

	MAD
2 MA	53.9
3 MA	71.44444
weighted	49.64

Weighted 2-month gave best forecast based on MAD

e)

	error^2
	2 MA	3 MA	weighted
	729		595.36
	361	900	309.76
	676	1067.111	615.04
	2704	3520.444	2500
	2116	3560.111	1747.24
	52212.25	60844.44	51076
	3969	11520.44	1713.96
	182.25	2601	81
	462.25	64	424.36
	1806.25	2336.111	1664.64
MSE	6521.8	9601.519	6072.736

	MSE
2 MA	6521.8
3 MA	9601.519
weighted	6072.736

Weighted 2-month gave best forecast based on MSE