Question

Problem 15-28 (Algorithmic)

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 $400,000 in the first year of operation to $1,092,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	43	46	75	99	178
2	123	145	155	209	284
3	198	254	326	391	447
4	36	35	48	89	183

1. 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
   
2. 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

(a)

Go to Data --> Data Analysis --> Regression and enter the following details:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.83380
R Square	0.69522
Adjusted R Square	0.63808
Standard Error	72.85997
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	3	193750	64583.3333	12.1659	0.0002
Residual	16	84937.2	5308.575
Total	19	278687.2
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	78.2	32.584	2.400	0.029	9.125	147.275
Qtr1	10	46.081	0.217	0.831	-87.687	107.687
Qtr2	105	46.081	2.279	0.037	7.313	202.687
Qtr3	245	46.081	5.317	0.000	147.313	342.687

So,

the regression equation will be as follows:

F_t = 78.20 + 10.00 Qtr1 + 105.00 Qtr2 + 245.00 Qtr3

(b)

Go to Data --> Data Analysis --> Regression and enter the following details:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.976
R Square	0.952
Adjusted R Square	0.939
Standard Error	29.850
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	4	265321.6	66330.4	74.44155145	1.04034E-09
Residual	15	13365.6	891.04
Total	19	278687.2
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-48.70	19.46	-2.50	0.02	-90.18	-7.22
Period	10.58	1.18	8.96	0.00	8.06	13.09
Qtr1	41.73	19.21	2.17	0.05	0.78	82.67
Qtr2	126.15	19.03	6.63	0.00	85.60	166.70
Qtr3	255.58	18.92	13.51	0.00	215.26	295.89

So,

the regression equation will be as follows:

F_t = -48.70 + 41.73 Qtr1 + 126.15 Qtr2 + 255.58 Qtr3 + 10.58 Period

Next year forecasts:

Year	Quarter	Period	Qtr1	Qtr2	Qtr3	Ft
6	1	21	1	0	0	215.1
	2	22	0	1	0	310.1
	3	23	0	0	1	450.1
	4	24	0	0	0	205.1