Question

South Shore Construction builds permanent docks and seawalls along the southern shore of long island, new york. Although the firm has been in business for only five years, revenue has increased from $320,000 in the first year of operation to $1,116,000 in the most recent year. The following data show the quarterly sales revenue in thousands of dollars:

Quarter	Year 1	Year 2	Year 3	Year 4	Year 5
1	23	59	76	92	184
2	103	158	156	202	290
3	178	267	327	384	453
4	16	48	49	82	189

a. Use Excel Solver to find the coefficients of a multiple regression model with dummy variables as follows to develop an 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. Round your answers to two decimal places.

F_t = _ + _Qtr1 + _Qtr2 + _Qtr3

b. Let Period = 1 to refer to the observation in Quarter 1 of year 1; Period = 2 to refer to the observation in Quarter 2 of year 1; . . . and Period = 20 to refer to the observation in Quarter 4 of year 5. Using the dummy variables defined in part (b) and Period, develop an equation to account for seasonal effects and any linear trend in the time series using Excel Solver. Round your answers to two decimal places. If your answer is negative value enter minus sign.

F_t = _ + _Qtr1 + _Qtr2 + _Qtr3 + _Period

Based upon the seasonal effects in the data and linear trend, compute estimates of quarterly sales for year 6. Round your answers to one decimal place.

Quarter 1 forecast =

Quarter 2 forecast =

Quarter 3 forecast =

Quarter 4 forecast =

Answer 1

(first part) the estimated model is given as F_t = 76.8 + 10*Qtr1 + 105*Qtr2 + 245*Qtr3

Quarter 1 forecast =76.8 + 10*1 + 105*0 + 245*0 =86.8

Quarter 2 forecast =76.8 + 10*0 + 105*1 + 245*0=181.8

Quarter 3 forecast =76.8 + 10*0 + 105*0 + 245*1=321.8

Quarter 4 forecast =76.8 + 10*0 + 105*0+ 245*0=76.8

following information has been generated using ms-excel

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.816746
R Square	0.667075
Adjusted R Square	0.604651
Standard Error	77.74043
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	3	193750	64583.33	10.68628	0.000423
Residual	16	96697.2	6043.575
Total	19	290447.2
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	76.8	34.76658	2.209018	0.042103	3.098146	150.5019
Q1	10	49.16737	0.203387	0.841397	-94.2302	114.2302
Q2	105	49.16737	2.135563	0.048521	0.769838	209.2302
Q3	245	49.16737	4.98298	0.000135	140.7698	349.2302

(second part)  he estimated model is given as F_t = -59.7+44.125*Qtr1+127.75*Qtr2+256.375*Qtr3 +11.375*Period

Quarter 1 forecast =-59.7+44.125*1+127.75*0+256.375*0+11.375*21=223.3

Quarter 2 forecast =-59.7+44.125*0+127.75*1+256.375*0+11.375*22=318.3

Quarter 3 forecast =-59.7+44.125*0+127.75*0+256.375*1+11.375*23=458.3

Quarter 4 forecast =-59.7+44.125*0+127.75*0+256.375*0+11.375*24=213.3

following information has been generated using ms-excel

		SUMMARY OUTPUT
		Regression Statistics
	Multiple R	0.975801
	R Square	0.952187
	Adjusted R Square	0.939437
	Standard Error	30.42718
	Observations	20
	ANOVA
		df	SS	MS	F	Significance F
	Regression	4	276560	69140	74.68028	1.02E-09
	Residual	15	13887.2	925.8133
	Total	19	290447.2
		Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
	Intercept	-59.7	19.8361	-3.00966	0.008798	-101.98	-17.4204
	Q1	44.125	19.57919	2.253669	0.039608	2.39295	85.85705
	Q2	127.75	19.3936	6.587225	8.62E-06	86.41352	169.0865
	Q3	256.375	19.28139	13.2965	1.05E-09	215.2777	297.4723
	t	11.375	1.20274	9.457572	1.04E-07	8.811421	13.93858