Question

Month	Revenue ($000)
1	700
2	740
3	710
4	695
5	760
6	810
7	750
8	820
9	730
10	710
11	720
12	760
13	800
14	820
15	770
16	740
17	690
18	680
19	710
20	740

Monthly revenue for the past 20 months of all the products sold in the Health and Beauty Department of Green Corporation is contained in the Excel data file. Use that data to answer the following.

1. Obtain a Moving Average forecast of the data using 3 periods to get the moving average – i.e., MA3 forecast. Plot the results on one chart with the plots showing both the actual line and the MA3 line. Show the screenshot of the plot (copy as picture and paste) below. What is the MAD of this forecast? What is the MAPE?

2. Obtain the Exponential Smoothing (ES) Forecast using a = 0.25. Plot the results on one chart with the plots showing both the actual line and the ES forecast line. Show the screenshot of the plot (copy as picture and paste) below. What is the MAD of this forecast? What is the MAPE?

Answer 1

a)

		Moving Average
1	700
2	740
3	710
4	695	716.67
5	760	715.00
6	810	721.67
7	750	755.00
8	820	773.33
9	730	793.33
10	710	766.67
11	720	753.33
12	760	720.00
13	800	730.00
14	820	760.00
15	770	793.33
16	740	796.67
17	690	776.67
18	680	733.33
19	710	703.33
20	740	693.33

period	demand	forcast	forecast error=demand value-forecast value	absolute forecast error	squared forcast error	Abs %error
t	Dt	Ft	et=Dt-Ft	| et |	(et)²	| et/Dt |
1	700
2	740
3	710
4	695	716.667	-21.67	21.67	469.44	3.12%
5	760	715.000	45.00	45.00	2025.00	5.92%
6	810	721.667	88.33	88.33	7802.78	10.91%
7	750	755.000	-5.00	5.00	25.00	0.67%
8	820	773.333	46.67	46.67	2177.78	5.69%
9	730	793.333	-63.33	63.33	4011.11	8.68%
10	710	766.667	-56.67	56.67	3211.11	7.98%
11	720	753.333	-33.33	33.33	1111.11	4.63%
12	760	720.000	40.00	40.00	1600.00	5.26%
13	800	730.000	70.00	70.00	4900.00	8.75%
14	820	760.000	60.00	60.00	3600.00	7.32%
15	770	793.333	-23.33	23.33	544.44	3.03%
16	740	796.667	-56.67	56.67	3211.11	7.66%
17	690	776.667	-86.67	86.67	7511.11	12.56%
18	680	733.333	-53.33	53.33	2844.44	7.84%
19	710	703.333	6.67	6.67	44.44	0.94%
20	740	693.333	46.67	46.67	2177.78	6.31%

	et=Dt-Ft	| et |	(et)²	| et/Dt |
total sum=	3.33	803.33	47266.67	107.26%
n=	17	17	17	17
average=	0.20	47.25	2780.39	6.31%

      
MAD=   Σ |et|/n =    47.25
      

      
MAPE=   Σ | et/Dt |/n =    6.31%

b)

		Exponential forecast
1	700	700
2	740	700
3	710	708
4	695	708.4
5	760	705.72
6	810	716.576
7	750	735.2608
8	820	738.2086
9	730	754.5669
10	710	749.6535
11	720	741.7228
12	760	737.3783
13	800	741.9026
14	820	753.5221
15	770	766.8177
16	740	767.4541
17	690	761.9633
18	680	747.5706
19	710	734.0565
20	740	729.2452

period	demand	forcast	forecast error=demand value-forecast value	absolute forecast error	squared forcast error	Abs %error
t	Dt	Ft	et=Dt-Ft	| et |	(et)²	| et/Dt |
1	700
2	740
3	710	708.000	2.00	2.00	4.00	0.28%
4	695	708.400	-13.40	13.40	179.56	1.93%
5	760	705.720	54.28	54.28	2946.32	7.14%
6	810	716.576	93.42	93.42	8728.04	11.53%
7	750	735.261	14.74	14.74	217.24	1.97%
8	820	738.209	81.79	81.79	6689.83	9.97%
9	730	754.567	-24.57	24.57	603.53	3.37%
10	710	749.654	-39.65	39.65	1572.40	5.59%
11	720	741.723	-21.72	21.72	471.88	3.02%
12	760	737.378	22.62	22.62	511.74	2.98%
13	800	741.903	58.10	58.10	3375.31	7.26%
14	820	753.522	66.48	66.48	4419.31	8.11%
15	770	766.818	3.18	3.18	10.13	0.41%
16	740	767.454	-27.45	27.45	753.73	3.71%
17	690	761.963	-71.96	71.96	5178.72	10.43%
18	680	747.571	-67.57	67.57	4565.79	9.94%
19	710	734.057	-24.06	24.06	578.72	3.39%
20	740	729.245	10.75	10.75	115.67	1.45%

	forecast error=demand value-forecast value	absolute forecast error	squared forcast error	Abs %error
	et=Dt-Ft	| et |	(et)²	| et/Dt |
total sum=	116.98	697.76	40921.92	92.47%
n=	18	18	18	18
average=	6.50	38.76	2273.44	5.14%

      
MAD=   Σ |et|/n =    38.76
      

      
MAPE=   Σ | et/Dt |/n =    5.14%

Thanks in advance!

revert back for doubt

Please upvote