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 $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 282
3 175 255 346 384 445
4 13 36 68 82 181




Trend and Seasonal Pattern

  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

Ft Q1 Q2 Q3 t
20 1 0 0 1
100 0 1 0 2
175 0 0 1 3
13 0 0 0 4
47 1 0 0 5
146 0 1 0 6
255 0 0 1 7
36 0 0 0 8
95 1 0 0 9
175 0 1 0 10
346 0 0 1 11
68 0 0 0 12
92 1 0 0 13
202 0 1 0 14
384 0 0 1 15
82 0 0 0 16
176 1 0 0 17
282 0 1 0 18
445 0 0 1 19
181 0 0 0 20

Regression with Ft,Q1,Q2,Q3

Excel -> Data -> Data Analysis -> Regression

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.819293649
R Square 0.671242084
Adjusted R Square 0.609599974
Standard Error 77.01217436
Observations 20
ANOVA
df SS MS F Significance F
Regression 3 193750 64583.33333 10.8893432 0.00038317
Residual 16 94894 5930.875
Total 19 288644
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 76 34.44089139 2.206679239 0.042294738 2.988571824 149.0114282 2.988571824 149.0114282
Q1 10 48.70677571 0.205310244 0.839919051 -93.25375193 113.2537519 -93.25375193 113.2537519
Q2 105 48.70677571 2.155757561 0.046671815 1.746248066 208.2537519 1.746248066 208.2537519
Q3 245 48.70677571 5.030100975 0.000123165 141.7462481 348.2537519 141.7462481 348.2537519

Ft = 76.00+10.00*Q1+105.00*Q2+245.00*Q3

Regression with Ft,Q1,Q2,Q3 and t

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.980134155
R Square 0.960662962
Adjusted R Square 0.950173085
Standard Error 27.51290606
Observations 20
ANOVA
df SS MS F Significance F
Regression 4 277289.6 69322.4 91.58000423 2.37186E-10
Residual 15 11354.4 756.96
Total 19 288644
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -61.1 17.93622034 -3.406514798 0.003905227 -99.33014869 -22.86985131 -99.33014869 -22.86985131
Q1 44.275 17.70391906 2.5008587 0.024464302 6.53998977 82.01001023 6.53998977 82.01001023
Q2 127.85 17.53610561 7.290672333 2.6496E-06 90.47267566 165.2273243 90.47267566 165.2273243
Q3 256.425 17.43464224 14.70778674 2.56191E-10 219.2639397 293.5860603 219.2639397 293.5860603
t 11.425 1.087543103 10.50533075 2.60233E-08 9.106956748 13.74304325 9.106956748 13.74304325

Ft = -61.1 + 44.28*Q1 + 127.85*Q2 + 256.43*Q3 + 11.43*t

Next Year Ft= -61.1 + 44.28*Q1 + 127.85*Q2 + 256.43*Q3 + 11.43*t Q1 Q2 Q3 t
Quarter 1 forecast = 223.2 1 0 0 21
Quarter 2 forecast = 318.2 0 1 0 22
Quarter 3 forecast = 458.2 0 0 1 23
Quarter 4 forecast = 213.2 0 0 0 24

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 $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...
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