What is Moving Average?

 What is Moving Average?

It is technique use to calculate the overall trend with the help of historical data. To calculate the moving average simply calculates the average of set of data with time period. There are 4 type of Moving Average that is:

·         Simple Moving Average

·         Cumulative Moving Average

·         Weighted Moving Average

·         Exponential Moving Average

Example: Find out the simple moving average of following year sales data of XYZ Company by using 3 moving years

Year	Sales (Rs.)
1	5259
2	6453
3	9845
4	4865
5	3212
6	9856
7	4578
8	5965

Solution:

Year	Sales (Rs.)	Moving Average (for 3 years)	Deviation	Absolute Deviation	Squared deviation	Absolute Percentage Error
1	5259
2	6453
3	9845	(5259+6453+9845)/3 =7185.67	2659.33	2659.33	7072054	0.27012
4	4865	(6453+9845+4865)/3 =7054.33	-2189.33	2189.33	4793180	0.450017
5	3212	(9845+4865+3212)/3 =5974	-2762.00	2762.00	7628644	0.8599
6	9856	(4865+3212+9856)/3 =5977.67	3878.33	3878.33	15041469	0.3935
7	4578	(3212+9856+4578)/3 =5882	-1304.00	1304.00	1700416	0.284841
8	5965	(9856+4578+5965)/3 =6799.67	-834.67	834.67	696668	0.139927
	Total			13627.67	36932432	2.398305

 

Deviation = Actual sales-Forecast (Moving average)

Absolute deviation =Ignore minus sign

Squared deviation = Absolute deviation ^2

 MAD = Ʃ ǀ actual –forecast ǀ / n

=13627.67/6

=2271.28

MSE = Ʃ (forecast errors) ² / n

=36932432/6

=6155405.33

MAPE = 100* Ʃ ǀ actual _i –forecast _i ǀ/actual _i / n

=2.398/6

=0.3996

Example: Find out the next year forecasted sales by using weighted moving average with the help of given information

Year	Sales
2012	49
2013	45
2014	32
2015	25
2016	28

 The weights are 0.6, 0.2 and 0.2

Solution:

	A	B	C	D
1	Year	Sales	Weighted Moving Average	Formula
2	2012	49
3	2013	45
4	2014	32
5	2015	25	38	0.6*B4+0.2*B3+0.2*B2
6	2016	28	30.4	0.6*B5+0.2*B4+0.2*B3
7	2017	30	28.2	0.6*B6+0.2*B5+0.2*B4
8	2018	21	28.6	0.6*B7+0.2*B6+0.2*B5
9	2019		24.2	0.6*B8+0.2*B7+0.2*B6

 Highest Weight is given to recent sales price and less weight is given to last year to calculate average.

Example: Find out the MAD and MSE of given information:

Year	Demand
2015	40
2016	35
2017	38
2018	42
2019	55

Weights are 0.5 and 0.3.

Solution:

	A	B	C	D	E	F
1	Year	Demand	Weighted Moving Average	Formula	Deviation	Squared deviation
2	2015	40
3	2016	35
4	2017	38	14.75	(0.5*B3+0.3*B2)/2	23.25	540.56
5	2018	42	14.75	(0.5*B4+0.3*B3)/2	27.25	742.56
6	2019	55	16.2	(0.5*B5+0.3*B4)/2	38.8	1505.44
7	2020	59	20.05	(0.5*B6+0.3*B5)/2	38.95	1517.10
8	2021	37	23	(0.5*B7+0.3*B6)/2	14	196
	Total				142.25	4501.67

 

MAD = Ʃ ǀ actual –forecast ǀ / n

=142.25/5

=28.45

MSE = Ʃ (forecast errors) ² / n

= 4501.67/5

=900.34

Example: Find out the next month sales with the help information:

·         Using Exponential Moving Average

·         Sales of 6 months are given as

·         Alpha is 0.2

Month	Sales	EMA
Jan	88	38
Feb	98	?
March	94	?
April	75	?
May	86	?
June	80	?

 

Solution:

Month	Sales	Exponential Moving Average
Jan	88	68
Feb	98	72
March	94	77.2
April	75	80.56
May	86	79.45
June	80	80.76

 

S_t=α*(Y_t-1) + (1-α)*S_t-1

Where,

S_t= EMA

α= alpha*

(Y_t-1) = Sales of previous period

S_t-1 = EMA previous period

S _Feb = 0.2*88 + (1-0.2)*68

= 17.6+0.8*68

=17.6+54.4

Feb=72

=0.2*98+ (1-0.2)*72

=19.6 + 0.8*72

= 19.6 + 57.6

March=77.2

=0.2*94+ (1-0.2)*77.2

= 18.8 + 0.8*77.2

= 18.8 + 61.76

April=80.56

=0.2*75+ (1-0.2)*80.56

=15 + 64.45

May=79.45

=0.2*86 + (1-0.2)*79.45

=17.2 + 63.56

June= 80.76