Question

Generate one step ahead forecast using simple exponential smoothing(SES), using alfa= 0.80.

Calculate RMSE, MAE, and MAPE.

Please explain how to do this in excel.

Notes:

In the case of SES:

a. uses the actual value of day one (18,085.45) as initial of value (starting value) of the forecast for 9/9/16 and calculate forecasts for day 2 to day 20.

b. In process of calculating RMSE, MAE, and MAPE (after you generate forecast for day 2 to day 20) ignore the first row (day 9/9/16).

day	DJIA
9/9/2016	18,085.45
9/12/2016	18,325.07
9/13/2016	18,066.75
9/14/2016	18,034.77
9/15/2016	18,212.48
9/16/2016	18,123.80
9/19/2016	18,120.17
9/20/2016	18,129.96
9/21/2016	18,293.70
9/22/2016	18,392.46
9/23/2016	18,261.45
9/26/2016	18,094.83
9/27/2016	18,228.30
9/28/2016	18,339.24
9/29/2016	18,143.45
9/30/2016	18,308.15
10/3/2016	18,253.85
10/4/2016	18,168.45
10/5/2016	18,281.03
10/6/2016	18,268.50

Answer 1

Ans. a)

First enter the data into Excel worksheet as,

Period	DJIA (Actual)
t	At
1	18,085.45
2	18,325.07
3	18,066.75
4	18,034.77
5	18,212.48
6	18,123.80
7	18,120.17
8	18,129.96
9	18,293.70
10	18,392.46
11	18,261.45
12	18,094.83
13	18,228.30
14	18,339.24
15	18,143.45
16	18,308.15
17	18,253.85
18	18,168.45
19	18,281.03
20	18,268.50

Procedure:

1. If this is the first time you have used an Excel add-in, click the File tab, otherwise skip to step 6.
2. Click Options from the list on the left.
3. Select Add-ins in the Excel Options box.
4. In the Add-in list box, select Analysis Toolbox-VBA from the Inactive Application Add-ins list.
5. Click OK.
6. Click the Data tab.
7. In the Analysis section, click the Data Analysis command button.
8. Click Exponential Smoothing and then click OK.
9. Click in the Input Range box and select the range of A_t. (To forecast one time period beyond the end of the baseline data, we are including one extra row in the Input Range.)
10. Click In the Damping factor text box, and type 0.2. (The damping factor is equal to 1-)
11. Click the Labels checkbox.
12. Click in the Output range text box and give the output range.
13. Click the Chart Output check box and then click OK.

Then the output is,

Period	DJIA (Actual)	Forecast
t	At	Ft
1	18,085.45	#N/A
2	18,325.07	18,325.07
3	18,066.75	18118.414
4	18,034.77	18051.4988
5	18,212.48	18180.28376
6	18,123.80	18135.09675
7	18,120.17	18123.15535
8	18,129.96	18128.59907
9	18,293.70	18260.67981
10	18,392.46	18366.10396
11	18,261.45	18282.38079
12	18,094.83	18132.34016
13	18,228.30	18209.10803
14	18,339.24	18313.21361
15	18,143.45	18177.40272
16	18,308.15	18282.00054
17	18,253.85	18259.48011
18	18,168.45	18186.65602
19	18,281.03	18262.1552
20	18,268.50	18267.23104

The values in column Ft denotes the forecast values.

Ans. b)

We know that,

RMSE =

MAE =

MAPE =

Now,

t	At	Ft	Et=(At-Ft)	|Et|=|At-Ft|	(At-Ft)^2	|(At-Ft)/At|
1	18,325.07	18,325.07	0.00	0	0.00	0
2	18,066.75	18118.414	-51.66	51.664	2,669.17	0.002859618
3	18,034.77	18051.4988	-16.73	16.7288	279.85	0.000927586
4	18,212.48	18180.2838	32.20	32.19624	1,036.60	0.001767812
5	18,123.80	18135.0968	-11.30	11.29675	127.62	0.00062331
6	18,120.17	18123.1554	-2.99	2.98535	8.91	0.000164753
7	18,129.96	18128.5991	1.36	1.36093	1.85	7.50652E-05
8	18,293.70	18260.6798	33.02	33.02019	1,090.33	0.001805003
9	18,392.46	18366.104	26.36	26.35604	694.64	0.001432981
10	18,261.45	18282.3808	-20.93	20.93079	438.10	0.001146174
11	18,094.83	18132.3402	-37.51	37.51016	1,407.01	0.002072977
12	18,228.30	18209.108	19.19	19.19197	368.33	0.001052867
13	18,339.24	18313.2136	26.03	26.02639	677.37	0.001419164
14	18,143.45	18177.4027	-33.95	33.95272	1,152.79	0.001871349
15	18,308.15	18282.0005	26.15	26.14946	683.79	0.001428296
16	18,253.85	18259.4801	-5.63	5.630109	31.70	0.000308434
17	18,168.45	18186.656	-18.21	18.20602	331.46	0.001002068
18	18,281.03	18262.1552	18.87	18.8748	356.26	0.00103248
19	18,268.50	18267.231	1.27	1.268959	1.61	6.94616E-05
Total	3,46,046.41	3,46,060.87	-14.46	383.35	11,357.40	0.02

Here n = 19 (By ignoring first row).

By using the above formulae,

RMSE = 24.4491

MAE = 20.1763

MAPE = 0.1053