Question

Forecast the advertising revenue for each quarter in 2011 using seasonal dummy variables and a best subsets regression.​ (Let the first dummy variable be equal to 1 for Quarter 2 and so​ on, following the order of the seasonal categories in the given​ table.

2008

1	540
2	516
3	488
4	500

2009

quarter	Rev. in Millions
1	433
2	407
3	402
4	460

2010

quarters	rev. in millions
1	347
2	297
3	292
4	332

A) The revenue forecast for the first quarter in 2011 is?

The revenue forecast for the second quarter in 2011 is?

The revenue forecast for the third quarter in 2011 is?

The revenue forecast for the fourth quarter is?

B.) quarter 4 has an average revenue that is ___ above that for quarter !

C.) Calculate the MAD for the forecast.

Answer 1

Please See the Values Carefully......

The data can be presented in terms of Quarter and Year variables as:

Quarter1	Quarter2	Quarter3	Quarter4	Year	Revenue ($ millions)
1	0	0	0	1	540
0	1	0	0	1	516
0	0	1	0	1	488
0	0	0	1	1	500
1	0	0	0	2	433
0	1	0	0	2	407
0	0	1	0	2	402
0	0	0	1	2	460
1	0	0	0	3	347
0	1	0	0	3	297
0	0	1	0	3	292
0	0	0	1	3	332

Carrying out regression in Excel (go to Data tab -> Data Analysis -> Regression, choose Revenue as Y-column, and Quarter/Year columns as X-columns), we get the following output:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.986938585
R Square	0.97404777
Adjusted R Square	0.816360782
Standard Error	17.37540681
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	5	79318.33333	15863.66667	65.68158517	3.71424E-05
Residual	7	2113.333333	301.9047619
Total	12	81431.66667
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	634	15.86150376	39.97098948	1.59828E-09	596.4935035	671.5064965
Quarter1	0	0	65535		0	0
Quarter2	-33.33333333	14.18696025	-2.349575437		-66.88016361	0.213496941
Quarter3	-46	14.18696025	-3.242414102	0.014205528	-79.54683027	-12.45316973
Quarter4	-9.333333333	14.18696025	-0.657881122	0.531645555	-42.88016361	24.21349694
Year	-97	6.143133992	-15.78998605	9.90104E-07	-111.5262036	-82.47379638

Hence, the regression model obtained is:

Revenue = 634 - 33.33 * Quarter2 - 46 * Quarter3 - 9.33 * Quarter4 - 97 * Year

Now, 2011 is Year 4. Hence,

the revenue forecast for the first quarter in 2011 is = 634 - 33.33 * 0 - 46 * 0 - 9.33 * 0 - 97 * 4 = 246 mil $

the revenue forecast for the second quarter in 2011 is =  634 - 33.33 * 1 - 46 * 0 - 9.33 * 0 - 97 * 4 = 212.67 mil $

the revenue forecast for the third quarter in 2011 is = 634 - 33.33 * 0 - 46 * 1 - 9.33 * 0 - 97 * 4 = 200 mil $

the revenue forecast for the fourth quarter in 2011 is = 634 - 33.33 * 0- 46 * 0 - 9.33 * 1 - 97 *4 = 236.67 mi; $

Part b) Avg revenue for Quarter 1 = 391.5 mil $

Avg revenue for Quarter 4 = 382.17

Hence,

quarter four has an average revenue that is 391.5 - 382.17 = 9.33 mil $ above that for quarter 1

Part c) Using the above regression model, the data along with Predicted values and absolute deviations of predictions from the actual revenues are shown below:

Quarter1	Quarter2	Quarter3	Quarter4	Year	Revenue ($ millions)	Prediction ($ mil)	Abs Deviation ($ mil)
1	0	0	0	1	540	537.00	3.00
0	1	0	0	1	516	503.67	12.33
0	0	1	0	1	488	491.00	3.00
0	0	0	1	1	500	527.67	27.67
1	0	0	0	2	433	440.00	7.00
0	1	0	0	2	407	406.67	0.33
0	0	1	0	2	402	394.00	8.00
0	0	0	1	2	460	430.67	29.33
1	0	0	0	3	347	343.00	4.00
0	1	0	0	3	297	309.67	12.67
0	0	1	0	3	292	297.00	5.00
0	0	0	1	3	332	333.67	1.67

Hence, MAD = Mean Absolute Deviation = 9.5 mil $