Problem 3-43 Name:
Fill in the correct data in columns C, E, and G. Then run a
regression in Excel. The word “wrong” will appear when
incorrect data is input except for the formula because
formats may vary. Excel may have different answers than
the solution manual due to the precision of Excel.
MHs Total Cost
a. High activity 2,700 13,160$
Low activity (1,400) (9,000)
Difference 1,300 4,160$
Variable rate = 3.20$ per MH
Fixed cost at low activity 4,520$
Total R&M cost
b.
1,400 9,000$
1,900 10,719$
2,000 10,900$
2,500 13,000$
2,200 11,578$
2,700 13,160$
1,700 9,525$
2,300 11,670$
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.98667158
R Square 0.97352081
Adjusted R Square
0.96910762
Standard Error 260.600438
Observations 8
ANOVA
Residual 6 407475.5291
Total 7 15388521.5
Solution
Machine Hours
Repair and
Maintenance Cost
$4,520 + $3.20 MH
Coefficients Standard Error
Intercept 4020.1064 491.6727065
X Variable 1 3.43623645 0.231359381
R&M Cost Budgeting Formula
Y = a + bX
Y = $4,020.11 + $3.436 MH
c.
Part (b) computations provide the better answer. The least squares regression
approach takes into consideration all of the available data and employs a
mathemateical algorithm to minimize the variance around the fitted regression
line.
MS F
Significance F
14981046 220.593065 5.8604E-06
67912.5882
t Stat P-value Lower 95% Upper 95%
Lower 95.0%
Upper 95.0%
8.17638716 0.00018024 2817.02575 5223.18706 2817.02575 5223.18706
14.8523757 5.8604E-06 2.87012003 4.00235288 2.87012003 4.00235288