In: Accounting
Part 2: Cameron Company is interested in establishing the relationship between utility costs and machine hours. Data has been collected and a regression analysis prepared using Excel. The monthly data and the regression output follow:
Month | MACHINE HOURS | ELECTRICITY COSTS |
JAN | 3250 | 22080 |
FEB | 3770 | 25200 |
MAR | 2470 | 16200 |
APR | 4030 | 27600 |
MAY | 4940 | 33900 |
JUN | 4290 | 26400 |
JUL | 5330 | 29700 |
AUG | 4550 | 27300 |
SEP | 2600 | 18600 |
OCT | 4810 | 31200 |
NOV | 6110 | 37200 |
DEC | 5460 | 33300 |
Required:
a. Using Excel, perform a regression analysis on the above data and
generate a summary output.
b. What is the equation for utility costs using the regression analysis?
c. Prepare an estimate of utility costs for a month when 3,000 machine hours are worked.
Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you. | ||||||||
SUMMARY OUTPUT | ||||||||
Regression Statistics | ||||||||
Multiple R | 0.96538 | |||||||
R Square | 0.93197 | |||||||
Adjusted R Square | 0.92516 | |||||||
Standard Error | 1,710.21088 | |||||||
Observations | 12.00 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 1.00 | 400,652,987.60 | 400,652,987.60 | 136.983752100 | 0.000000369 | |||
Residual | 10.00 | 29,248,212.40 | 2,924,821.24 | |||||
Total | 11.00 | 429,901,200.00 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
Intercept | 4,472.2547160 | 2,019.3896880 | 2.2146566 | 0.0511556 | -27.2258867 | 8,971.7353190 | -27.2258867 | 8,971.7353190 |
Machine Hours (x) | 5.3286755 | 0.4552865 | 11.7040058 | 0.0000004 | 4.3142340 | 6.3431170 | 4.3142340 | 6.3431170 |
(b) | The regression equation is y = 4472.25 + 5.3287x | |||||||
(c) | When x = 3000, y = 4472.25 + 5.3287(3000) = 20458.35 |