Question

In: Operations Management

Problem 15-28 (Algorithmic) South Shore Construction builds permanent docks and seawalls along the southern shore of...

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

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

keosha answered 6 months 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 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...
South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island. Although...
South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island. Although the firm has been in business 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: (Answer 1-3 1.Below is a simple linear regression analysis for this forecasting problem. Is this a valid model to use to forecast quarterly revenue for...
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....
Problem 12-18 (Algorithmic) A building contractor is preparing a bid on a new construction project. Two...
Problem 12-18 (Algorithmic) A building contractor is preparing a bid on a new construction project. Two other contractors will be submitting bids for the same project. Based on past bidding practices, bids from the other contractors can be described by the following probability distributions: Contractor Probability Distribution of Bid A Uniform probability distribution between $530,000 and $730,000 B Normal probability distribution with a mean bid of $630,000 and a standard deviation of $43,000 If required, round your answers to three...
Problem 12-15 (Algorithmic) Strassel Investors buys real estate, develops it, and resells it for a profit....
Problem 12-15 (Algorithmic) Strassel Investors buys real estate, develops it, and resells it for a profit. A new property is available, and Bud Strassel, the president and owner of Strassel Investors, believes if he purchases and develops this property it can then be sold for $160000. The current property owner has asked for bids and stated that the property will be sold for the highest bid in excess of $100000. Two competitors will be submitting bids for the property. Strassel...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT