In: Statistics and Probability
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
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 |