Question

Your company has the sales for year 1 below. You want to select from one of three models for forecasting: a three-month moving average, a weighted moving average (you believe that the weights should be 0.2, 0.3, and 0.5), and an exponential smoothing average in which you use an alpha of 0.2 and an assumed forecast for January of year one of $35,000. Determine sales forecast for January year 2 and calculate MAD.


Jan Yr 1 34284
Feb 34000
Mar 31017
Apr 33406
May 34518
Jun 35469
Jul 35360
Aug 34894
Sep 34547
Oct 31015
Nov 31167
Dec 32925

A) Three-month moving average:
Sales forecast: $  
MAD:  

B) Weighted moving average:
Sales forecast: $  
MAD:  

C) Exponential moving average:
Sales forecast: $  
MAD:  

Which forecasting method should you use for your company? (enter A, B, C):

Answer 1

Year1 (Months)	Sales	3-month Moving Average	3-month Weighted Moving Average (weights: 0.2, 0.3, 0.5)	Exponential Smoothing (alpha = 0.2)
Jan	34284
Feb	34000			(0.2*34280)+(0.8*34280)= 34280
Mar	31017			(0.2*34000)+(0.8*34280)= 34224
Apr	33406	(34284+34000+31017)/3= 33100	(0.2*34284)+(0.3*34000)+(0.5*31017)= 32562	(0.2*31017)+(0.8*34224)= 33582
May	34518	(34000+31017+33406)/3= 32808	(0.2*34000)+(0.3*31017)+(0.5*33406)= 32808	(0.2*33406)+(0.8*33582)= 33547
Jun	35469	(31017+33406+34518)/3= 32980	(0.2*31017)+(0.3*33406)+(0.5*34518)= 33484	(0.2*34518)+(0.8*33547)= 33742
Jul	35360	(33406+34518+35469)/3= 34464	(0.2*33406)+(0.3*34518)+(0.5*35469)= 34771	(0.2*35469)+(0.8*33742)= 34088
Aug	34894	(34518+35469+35360)/3= 35116	(0.2*34518)+(0.3*35469)+(0.5*35360)= 35225	(0.2*35360)+(0.8*34088)= 34342
Sept	34547	(35469+35360+34894)/3= 35241	(0.2*35469)+(0.3*35360)+(0.5*34894)= 35149	(0.2*34894)+(0.8*34342)= 34453
Oct	31015	(35360+34894+34547)/3= 34934	(0.2*35360)+(0.3*34894)+(0.5*34547)= 30263	(0.2*34547)+(0.8*34453)= 34471
Nov	31167	(34894+34547+31015)/3= 33485	(0.2*34894)+(0.3*34547)+(0.5*31015)= 32851	(0.2*31015)+(0.8*34471)= 33780
Dec	32925	(34547+31015+31167)/3= 32243	(0.2*34547)+(0.3*31015)+(0.5*31167)= 31796	(0.2*31167)+(0.8*33780)= 33257
Year 2 (Month)
Jan		(31015+31167+32925)/3= 31702	(0.2*31015)+(0.3*31167)+(0.5*32925)= 32015	(0.2*32925)+(0.8*33257)= 33191

Calculation of Mean Absolute Deviation (MAD)

Sales	3-month Moving Average	Deviation	Absolute deviation
33406	33100	33406 – 33100 = 306	306
34518	32808	34518 – 32808= 1710	1710
35469	32980	35469 – 32980 = 2489	2489
35360	34464	35360 – 34464 = 896	896
34894	35116	34894 – 35116 = -222	222
34547	35241	34547 – 35241 = -694	694
31015	34934	31015 – 34934 = -3919	3919
31167	33485	31167 – 33485 = -2318	2318
32925	32243	32925 – 32243 = 682	682

Sum of absolute deviation = 306 + 1710 + 2489 + 896 + 222 + 694 + 3919 + 2318 + 682 = 13236

MAD = 13236/9 = 1470.66

Sales	3-month Weighted Moving Average	Deviation	Absolute Deviation
33406	32562	33406 – 32562 = 844	844
34518	32808	34518 – 32808 = 1710	1710
35469	33484	35469 – 33484 = 1985	1985
35360	34771	35360 – 34771 = 589	589
34894	35225	34894 – 35225 = -331	331
34547	35149	34547 – 35149 = -602	602
31015	30263	31015 - 30263 = 752	752
31167	32851	31167 – 32851 = -1684	1684
32925	31796	32925 – 31796 = 1129	1129

Sum of absolute deviation = 844 + 1710 + 1985 + 589 + 331 + 602 + 752 + 1684 + 1129 = 9626

MAD = 9626/9 = 1069.55

Sales	Exponential Smoothing	Deviation	Absolute Deviation
33406	33582	33406 – 33582 = -176	176
34518	33547	34518 – 33547 = 971	971
35469	33742	35469 – 33742 = 1727	1727
35360	34088	35360 – 34088 = 1272	1272
34894	34342	34894 – 34342 = 552	552
34547	34453	34547 – 34453 = 94	94
31015	34471	31015 – 34471 = -3456	3456
31167	33780	31167 – 33780 = -2613	2613
32925	33257	32925 – 33257 = -332	332

Sum of Absolute Deviation = 176 + 971 + 1727 + 1272 + 552 + 94 + 3456 + 2613 + 332 = 11193

MAD = 11193/9 = 1243.66

Therefore,

Requirement A:

3- month Moving Average

Sales forecast = $31702

MAD = 1470.66

Requirement B:

Weighted Moving Average

Sales forecast = $32015

MAD = 1069.55

Requirement C:

Exponential Moving average

Sales forecast = $33191

MAD = 1243.66

Hence, B- weighted moving average has the lowest MAD and is considered to be the best forecast method among the three.