Question

The quarterly sales data (number of book sold) for Christian book over the past three years in California follow: (You can use Excel to compute the equation)

Quarter	Year 1	Year 2	Year 3
1 2 3 4	1230 1020 2534 2600	1470 990 2800 2590	1520 1020 2850 2700

1. Use the following dummy variables to develop an estimated regression equation to account for any seasonal effects in the data: Quarter1=1 if the sales data point is in Quarter 1, otherwise Quarter 1=0; Quarter 2=1 if the sales data point is in Quarter 2, otherwise, Quarter 2=0; Quarter 3=1 if the sales data point is in Quarter 3, otherwise Quarter 3=0.

2. Compute the quarterly forecasts for next year.

3. Let t=1 to refer to the observation in quarter 1 of year 1; t=2 to refer to the observation in quarter 2 of year 1;,,,, and t=12 to refer to the observation in quarter 4 of year 3. Using the dummy variables defined in part (2) and t, develop an estimated regression equation to account for seasonable effects and any linear trend in the time series. Based upon the seasonal effects in the data and linear trend, compute the quarterly forecasts for next year.

Answer 1

	Period(t)	Actual Data(y)	Y*t	t2
	1	1230	1230	1
	2	1020	2040	4
	3	2534	7602	9
	4	2600	10400	16
	5	1470	7350	25
	6	990	5940	36
	7	2800	19600	49
	8	2590	20720	64
	9	1520	13680	81
	10	1020	10200	100
	11	2850	31350	121
	12	2700	32400	144
Total	78	23324	162512	650

t-bar = sum(t)/n = 78/12 = 6.5

y-bar = sum(y)/n = 23324/12= 1943.67(Rounding to 2 decimal places)

Regression equation is y = a + bt where a = intercept and b = slope

b = (sum (y*t) – n* t-bar *y-bar)/(sum(t²) – n*t-bar²)

= (162512–12*6.5*1943.67)/(650 – 12*6.5*6.5) = (162512-151606.26)/(650 – 507) = 10905.74/143= 76.26391608 = 76.26 (Rounding to 2 decimal places)

a = y-bar – b*x-bar = 1943.67 – 76.26*6.5 = 1447.98

So, regression equation becomes y = 1447.98 + 76.26t

Plotting t = 1,2….20 we get de-seasonalised values at yd_t

So the table becomes

	Period(t)	Actual Data(y)	Yt	t^2	Deseasonalized data(ydt)	Seasonality factor
	1	1230	1230	1	1524.24	0.806959534
	2	1020	2040	4	1600.5	0.637300843
	3	2534	7602	9	1676.76	1.511247883
	4	2600	10400	16	1753.02	1.483154784
	5	1470	7350	25	1829.28	0.803594857
	6	990	5940	36	1905.54	0.519537769
	7	2800	19600	49	1981.8	1.412856999
	8	2590	20720	64	2058.06	1.258466711
	9	1520	13680	81	2134.32	0.712170621
	10	1020	10200	100	2210.58	0.461417366
	11	2850	31350	121	2286.84	1.246261216
	12	2700	32400	144	2363.1	1.142566967
Total	78	23324	162512	650

1. Seasonality = Actual data /deseasonalized data

Example Seasonality for period 1 = 1230/1524.24=0.806959534

Using periodicity = 4 we can say seasonal factor of Quarter 1 would be average of seasonal factors of period 1,5,and 9 = Average(0.806959534, 0.803594857, 0.712170621)

= 0.774241671

Likewise seasonality factors for all quarters are calculated

Quarter	Seasonal factor
1	0.774241671
2	0.539418659
3	1.390122033
4	1.294729488

2. Quarterly forecast for next year

Quarter t = (1447.98 + 76.26t)* seasonality for quarter 1 [ Next year Quarter 1 would be period 13 so value of t = 13 and seasonality for quarter 1 would be applied here due to periodicity]

So,

Next year-Quarter 1 = (1447.98 + 76.26*1)* 0.774241671= 1180.130125

Next year-Quarter 2 = (1447.98 + 76.26*2)* 0.539418659= 863.3395637

Next year-Quarter 3 = (1447.98 + 76.26*3)* 1.390122033= 2330.901

Next year-Quarter 4 = (1447.98 + 76.26*4)* 1.294729488= 2269.687