Question

Daily high temperatures in degrees Fahrenheit in the city of Lubbock for the last week havebeen as follows: 93, 94, 93, 95, 96, 88, 90. Using Excel’s Data Analysis add-in,
a. Forecast the high temperature today, using a 3-day moving average.
b. Forecast the high temperature today, using a 2-day moving average.

c. Forecast the high temperature today, using weighted moving average with weights 0.5, 0.3 and 0.2 for the most recent, second most recent, and third most recent periods, respectively. d. Calculate the mean absolute deviation (MAD) based on each of the techniques above. Which forecasting method provides a lower MAD?

Answer 1

a) Using excel add-in : Data tab > data analysis > moving average

Inset data range > interval is 3 > set output range

We get :

Day	Temperature	MA
1	93
2	94
3	93	93.33333
4	95	94
5	96	94.66667
6	88	93
7	90	91.33333

b)  

Using excel add-in : Data tab > data analysis > moving average

Inset data range > interval is 2 > set output range

We get :

Day	Temperature	MA
1	93
2	94	93.5
3	93	93.5
4	95	94
5	96	95.5
6	88	92
7	90	89

c) Here we have weights 0.5, 0.3 and 0.2 for the most recent, second most recent, and third most recent periods, respectively.

Most recent period is day 7, second most is day 6 and third most is day 5 . So weighted moving average is given by

WMA = 0.5 * 90 + 0.3 * 88 + 0.2 * 96 = 90.6

d) 3-Day moving average :

Error = Actual value - Forecasted value

Day	Temperature	MA	Error	Absolute Error
1	93
2	94
3	93	93.33333	=93-93.3333=-0.3333	0.3333
4	95	94	95-94 = 1	1
5	96	94.66667	1.333333	1.33333
6	88	93	-5	5
7	90	91.33333	-1.33333	1.33333
			Total	9

----------------------------------------------------------------------------------------------------------------------

2 -Day moving average :

Error = Actual value - Forecasted value

Day	Temperature	MA	Error	|Error|
1	93
2	94	93.5	0.5	0.5
3	93	93.5	-0.5	0.5
4	95	94	1	1
5	96	95.5	0.5	0.5
6	88	92	-4	4
7	90	89	1	1
				7.5

  

= 1.25

2 - day moving average has lower MAD.