Question

i have sales data for the period jan to june 2016

how do i calculate the implied seasonal index for each month

Actual Seasonally adjusted index

jan 150 176 ?

feb 140 175 ?

mar 130 173 ?

apr 120 185 ?

may 110 183 ?

june 190 158 ?

the trend equation based on the last 6 months seasonally adjusted data is ?

seasonally adjusted sale Y = 150 + 10k so than do a forecast of actual month sale for next june 2017

time value (x) for next june is 10 and use the seasonal index from last june 2016

Answer 1

For ease of understanding I have named the given months from Jan15-Jun16. Also, we are considering that there are only 6 months in a year. We will be calculating the seasonality of each month (ie Jan – Jun) and use it to predict Sales for next 6 months (Jan17-Jun17)

Steps:

1. First we calculate 4 months moving Average.
2. Then we calculate Centred Average of these 4 months moving Average.
3. Then % Average ie Sales/Centred Average
4. Then we table the % Average for each month (1-6) for the 2 years (2015-2016) and calculate mean for each month. (As Shown in Table 2)
5. 600%/The total of these 6 months mean will be the x Adjustment Factor
6. Seasonality Index for each month will be X Adj Factor * Month’s Mean ie Seasonality Index for Jan = 103.15%*1.01 = 1.04
7. Then we calculate ∑X, ∑Y, ∑XY, ∑X2 and using these values we calculate the regression equation.
8. Regression Equation will be: Y = 127.82 + 4.67*X
9. Using this equation and Seasonality Index , we calculate value for Predicted Sales for next 6 months. Eg: Jan17 Sales = (127.82 + 4.67*13)*1.04

Table 1

Month	Month	X	Y	4 Q MA	Centred Average	% Average	XY	X2	SEASONAL INDEX
Jan-15	1	1	150				150	1	1.04
Feb-15	2	2	140	135.00			280	4	0.99
Mar-15	3	3	130	125.00	130.00	100.00%	390	9	0.99
Apr-15	4	4	120	137.50	131.25	91.43%	480	16	0.99
May-15	5	5	110	149.00	143.25	76.79%	550	25	0.77
Jun-15	6	6	190	162.75	155.88	121.89%	1140	36	1.23
Jan-16	1	7	176	178.50	170.63	103.15%	1232	49	1.04
Feb-16	2	8	175	177.25	177.88	98.38%	1400	64	0.99
Mar-16	3	9	173	179.00	178.13	97.12%	1557	81	0.99
Apr-16	4	10	185	174.75	176.88	104.59%	1850	100	0.99
May-16	5	11	183				2013	121	0.77
Jun-16	6	12	158				1896	144	1.23

Table 2

Month	2015	2016	Mean	X ADJ Factor	SEASONAL INDEX	Cumulative Indx	2017 Sales (Predicted)		Regression Equation: Y = a + bX
1		103.15%	103.15%	1.01	1.04	13	194.12		n	12
2		98.38%	98.38%	1.01	0.99	14	189.66		b	4.57
3	100.00%	97.12%	98.56%	1.01	0.99	15	194.53		a	127.82
4	91.43%	104.59%	98.01%	1.01	0.99	16	197.95
5	76.79%		76.79%	1.01	0.77	17	158.61		∑ XY	12,938
6	121.89%		121.89%	1.01	1.23	18	257.37		∑ X2	650
		Total	596.79%						∑ X	78
									∑ Y	1,890

Final Result

Month	SEASONAL INDEX	2017 Sales (Predicted)
Jan	1.04	194.12
Feb	0.99	189.66
Mar	0.99	194.53
Apr	0.99	197.95
May	0.77	158.61
Jun	1.23	257.37

Regression Equation:

Y = 127.82 + 4.67*X