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 only five years, revenue has increased from $315,000 in the first year of operation to $1,075,000 in the most recent year. The following data show the quarterly sales revenue in thousands of dollars. Quarter Year 1 TE Year 2 Year 3 Year 4 Year 5 24 40 80 92 163 144 154 197 292 172 245 329 439 181 a. Which of the following is the correct time series plot?
Please help I am very confused on how to solve this

Answer 1

Solve in Excel, input data in the following manner:

Year	Quarter	Period	Q1	Q2	Q3	Q4	Sales
1	1	1	1	0	0	0	21
1	2	2	0	1	0	0	97
1	3	3	0	0	1	0	172
1	4	4	0	0	0	0	22
2	1	5	1	0	0	0	10
2	2	6	0	1	0	0	111
2	3	7	0	0	1	0	215
2	4	8	0	0	0	0	23
3	1	9	1	0	0	0	60
3	2	10	0	1	0	0	154
3	3	11	0	0	1	0	329
3	4	12	0	0	0	0	48
4	1	13	1	0	0	0	92
4	2	14	0	1	0	0	197
4	3	15	0	0	1	0	389
4	4	16	0	0	0	0	83
5	1	17	1	0	0	0	163
5	2	18	0	1	0	0	292
5	3	19	0	0	1	0	439
5	4	20	0	0	0	0	181

Sales 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Time Period

a) Horizontal trend

b) Using Excel, go to Data, select Data Analyis, choose Regression. Put Q1, Q2, Q3 and Q4 in X input range and Sales in Y input range.

	Coefficients	Standard Error	t Stat	P-value
Intercept	71.4	36.93891834	1.932921	0.072351
Q1	-2.2	52.2395193	-0.04211	0.966964
Q2	98.8	52.2395193	1.891288	0.078057
Q3	237.4	52.2395193	4.544452	0.000387
Q4	0	0	65535	#NUM!

Revenue = 71 - 2*Q1 + 99*Q2 + 237*Q3

Using above equation:

Q1 Forecast = 71 - 2*1 + 99*0 + 237*0 = 69

Q2 Forecast = 71 - 2*0 + 99*1 + 237*0 = 170

Q3 Forecast = 71 - 2*0 + 99*0 + 237*1 = 308

Q4 Forecast = 71 - 2*0 + 99*0 + 237*0 = 71

c) Using Excel, go to Data, select Data Analyis, choose Regression. Put Q1, Q2, Q3, Q4 and Period in X input range and Sales in Y input range.

	Coefficients	Standard Error	t Stat	P-value
Intercept	-73.2	21.0610256	-3.47561	0.003711
Period	12.05	1.277012252	9.436088	1.91E-07
Q1	33.95	20.78825335	1.633134	0.124721
Q2	122.9	20.59120385	5.968568	3.44E-05
Q3	249.45	20.47206377	12.1849	7.68E-09
Q4	0	0	65535	#NUM!

Revenue = -73.2 + 12.05*Period + 33.95*Q1 + 122.9*Q2 + 294.45*Q3

Using above equation:

Q1 Forecast = -73.2 + 12.05*21 + 33.95*1 + 122.9*0 + 294.45*0 = 213.8

Q2 Forecast = -73.2 + 12.05*22 + 33.95*0 + 122.9*1 + 294.45*0 = 314.8

Q3 Forecast = -73.2 + 12.05*23 + 33.95*0 + 122.9*0 + 294.45*1 = 453.4

Q4 Forecast = -73.2 + 12.05*24 + 33.95*0 + 122.9*0 + 294.45*0 = 216