Question

Consider the following time series.

Quarter	Year 1	Year 2	Year 3
1	71	68	62
2	49	41	51
3	58	60	53
4	83	85	72

b. Use the following dummy variables to develop an estimated regression equation to account for seasonal effects in the data: Qtr1 = 1 if Quarter 1, 0 otherwise; Qtr2 = 1 if Quarter 2, 0 otherwise; Qtr3 = 1 if Quarter 3, 0 otherwise. Enter negative values as negative numbers.

Value = + Qtr1 + Qtr2 + Qtr3

c. Compute the quarterly forecasts for next year.

Quarter 1 forecast
Quarter 2 forecast
Quarter 3 forecast
Quarter 4 forecast

Answer 1

(b) Here we are taking dummy variables in the following way

Qtr1 = 1 if Quarter 1, 0 otherwise; Qtr2 = 1 if Quarter 2, 0 otherwise; Qtr3 = 1 if Quarter 3, 0 otherwise.

Now i am using the regression formula of excel.

First go to data analysis tool. Then put this the demand column in dependent variable. THen , we will create three column like given below and put in independent variable tab.

Quarter 1	Quarter 2	Quarter 3	Demand
1	0	0	71
0	1	0	49
0	0	1	58
0	0	0	83
1	0	0	68
0	1	0	41
0	0	1	60
0	0	0	85
1	0	0	62
0	1	0	51
0	0	1	53
0	0	0	72

Regression output is

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.943226
R Square	0.889676
Adjusted R Square	0.848304
Standard Error	5.267827
Observations	12
ANOVA
	df	SS	MS	F
Regression	3	1790.25	596.75	21.5045
Residual	8	222	27.75
Total	11	2012.25
	Coefficients	Standard Error	t Stat	P-value
Intercept	80	3.041381	26.30384	4.69E-09
Quarter 1	-13	4.301163	-3.02244	0.016498
Quarter 2	-33	4.301163	-7.67234	5.89E-05
Quarter 3	-23	4.301163	-5.34739	0.000688

so here the regression equation is

Demand = 80 - 13 * Qtr1 - 33 * Qtr 2 - 23 * Qtr 3

c. Compute the quarterly forecasts for next year.

Quarter 1 forecast	= 80 - 13	67
Quarter 2 forecast	= 80 - 33	47
Quarter 3 forecast	= 80 - 23 = 57	57
Quarter 4 forecast = 80

Quart 1 forecast = 67

Quart 2 forecast = 47

Quart 3 forecast = 57

Quart 4 forecast = 80