Question

In: Statistics and Probability

South Shore Construction builds permanent docks and seawalls along the southern shore of long island, new...

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,188,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 38 83 97 202
2 103 137 163 207 308
3 178 246 334 389 471
4 16 27 56 87 207
  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.

    Ft = ____________ + ____________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.

    Ft = ___________ + ___________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 =

Solutions

Expert Solution

Answer:

b)

Value Qtr1 Qtr2 Qtr3
23 1 0 0
103 0 1 0
178 0 0 1
16 0 0 0
38 1 0 0
137 0 1 0
246 0 0 1
27 0 0 0
83 1 0 0
163 0 1 0
334 0 0 1
56 0 0 0
97 1 0 0
207 0 1 0
389 0 0 1
87 0 0 0
202 1 0 0
308 0 1 0
471 0 0 1
207 0 0 0
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.783434
R Square 0.613768
Adjusted R Square 0.54135
Standard Error 87.29361
Observations 20
ANOVA
df SS MS F Significance F
Regression 3 193750 64583.33 8.475308 0.001339
Residual 16 121922.8 7620.175
Total 19 315672.8
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%
Intercept 78.6 39.03889 2.013377 0.061213 -4.15875 161.3588
Qtr1 10 55.20933 0.181129 0.858541 -107.039 127.0385
Qtr2 105 55.20933 1.901852 0.075351 -12.0385 222.0385
Qtr3 245 55.20933 4.437656 0.000414 127.9615 362.0385

Estimated regression equation:

ŷ = 78.6 + (10)Qtr1 + (105)Qtr2 + (245)Qtr3

-------------------------

c)

Revenue Period Qtr1 Qtr2 Qtr3
23 1 1 0 0
103 2 0 1 0
178 3 0 0 1
16 4 0 0 0
38 5 1 0 0
137 6 0 1 0
246 7 0 0 1
27 8 0 0 0
83 9 1 0 0
163 10 0 1 0
334 11 0 0 1
56 12 0 0 0
97 13 1 0 0
207 14 0 1 0
389 15 0 0 1
87 16 0 0 0
202 17 1 0 0
308 18 0 1 0
471 19 0 0 1
207 20 0 0 0
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9759404
R Square 0.9524596
Adjusted R Square 0.9397822
Standard Error 31.630365
Observations 20
ANOVA
df SS MS F Significance F
Regression 4 300665.6 75166.4 75.13034 9.745E-10
Residual 15 15007.2 1000.48
Total 19 315672.8
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%
Intercept -76.5 20.62048 -3.7099 0.002096 -120.4515 -32.5485
Period 12.925 1.2503 10.33752 3.22E-08 10.260049 15.58995
Qtr1 48.775 20.35341 2.396404 0.030035 5.3927361 92.15726
Qtr2 130.85 20.16048 6.49042 1.02E-05 87.878952 173.821
Qtr3 257.925 20.04383 12.86805 1.65E-09 215.20258 300.6474

Estimated regression equation:

ŷ = -76.5 + (48.78)Qtr1 + (130.85)Qtr2 + (257.93)Qtr3 + (12.93)Period

--

Quarter 1 forecast: x1 = 1, x2 = 0, x3 = 0, t = 21

ŷ = -76.5 + (48.78)*1 + (130.85)*0 + (257.93)*0 + (12.93)*21 = 243.7

Quarter 2 forecast: x1 = 0, x2 = 1, x3 = 0, t = 22

ŷ = -76.5 + (48.78)*0 + (130.85)*1 + (257.93)*0 + (12.93)*22 = 338.7

Quarter 3 forecast: x1 = 0, x2 = 0, x3 = 1, t = 23

ŷ = -76.5 + (48.78)*0 + (130.85)*0 + (257.93)*1 + (12.93)*23 = 478.7

Quarter 4 forecast: x1 = 0, x2 = 0, x3 = 0, t = 24

ŷ = -76.5 + (48.78)*0 + (130.85)*0 + (257.93)*0 + (12.93)*24 = 233.7

NOTE:: I HOPE THIS ANSWER IS HELPFULL TO YOU........***PLEASE SUPPORT ME WITH YOUR RATING....

**PLEASE GIVE ME "LIKE"........PLEASE SUPPORT ME ..........THANK YOU

orchestra answered 2 years ago
Next > < Previous

Related Solutions

South Shore Construction builds permanent docks and seawalls along the southern shore of long island, new...
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 $308,000 in the first year of operation to $1,144,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 20 47 75 92 191 2 100 146 155 202...
South Shore Construction builds permanent docks and seawalls along the southern shore of long island, new...
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 $308,000 in the first year of operation to $1,084,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 20 47 95 92 176 2 100 146 175 202...
South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New...
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 Year 2 Year 3 Year 4 Year 5 1 24 40 80 92 163 2 97 144 154 197 292...
South Shore Construction builds permanent docks and seawalls along the southern shore of long island, new...
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...
South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New...
South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New York. The following data show quarterly sales revenues (in $’000s) for the past 5 years. Quarter Year 1 Year 2 Year 3 Year 4 Year 5 1 20 37 75 92 176 2 100 136 155 202 282 3 175 245 326 384 445 4 13 26 48 82 181 Question 4 Now make adjustments for trend and seasonality. Quantify the trend in the...
Section 03: Inventory South City is a construction company which builds houses, offices and hotels. Materials...
Section 03: Inventory South City is a construction company which builds houses, offices and hotels. Materials (cement, bricks, etc…) are purchased from a large number of suppliers. As a member of the audit team, you are required to attend the inventory count of South City. On the day of the inventory count, you attended the warehouse premises of South City and observed the following activities:  Pre-numbered count sheets were being issued to the client's staff carrying out the count....
Go Party Ltd (GPL) is a successful New Zealand catering company, operating in South Island. It...
Go Party Ltd (GPL) is a successful New Zealand catering company, operating in South Island. It has a balance date of 30 June. During the preparation of the 30 June 2020 financial statements, the following two issues have come into the light. The details of these issues are as follows: (a) After a wedding party held by a customer in January 2020, 60 people became seriously ill, possibly as a result of food poisoning from food served by GPL. Legal...
Go Party Ltd (GPL) is a successful New Zealand catering company, operating in South Island. It...
Go Party Ltd (GPL) is a successful New Zealand catering company, operating in South Island. It has a balance date of 30 June. During the preparation of the 30 June 2020 financial statements, the following two issues have come into the light. The details of these issues are as follows: (a)   After a wedding party held by a customer in January 2020, 60 people became seriously ill, possibly as a result of food poisoning from food served by GPL. Legal...
The port of South Louisiana, located along 54 miles of the Mississippi River between New Orleans...
The port of South Louisiana, located along 54 miles of the Mississippi River between New Orleans and Baton Rouge, is the largest bulk cargo port in the world. The U.S. Army Corps of Engineers reports that the port handles a mean of 4.5 million tons of cargo per week.† Assume that the number of tons of cargo handled per week is normally distributed with a standard deviation of 0.89 million tons. (a) What is the probability that the port handles...
The port of South Louisiana, located along 54 miles of the Mississippi River between New Orleans...
The port of South Louisiana, located along 54 miles of the Mississippi River between New Orleans and Baton Rouge, is the largest bulk cargo port in the world. The U.S. Army Corps of Engineers reports that the port handles a mean of 4.5 million tons of cargo per week (USA Today, September 25, 2012). Assume that the number of tons of cargo handled per week is normally distributed with a standard deviation of .82 million tons. a. What is the...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT