Question

The manager of the main laboratory facility at Elmhurst HealthElmhurst Health Center is interested in being able to predict the overhead costs each month for the lab. The manager believes that total overhead varies with the number of lab tests performed but that some costs remain the same each month regardless of the number of lab tests performed. The lab manager collected the following data for the first seven months of the year.

Requirements

1.	Determine the​ lab's cost equation​ (use the output from the Excel​ regression).
2.	Determine the​ R-square (use the output from the Excel​ regression).
3.	Predict the total laboratory overhead for the month if 3,100 tests are performed

Data Table

	Number of Lab Tests	Total Laboratory
Month	Performed	Overhead Costs
January. . . . . . . . .	3,000	$21,900
February. . . . . . . .	2,850	$20,600
March. . . . . . . . . . .	3,400	$28,900
April. . . . . . . . . . . .	3,700	$31,000
May. . . . . . . . . . . .	3,900	$28,000
June. . . . . . . . . . . .	1,900	$20,200
July. . . . . . . . . . . .	2,050	$14,000

The laboratory manager performed a regression analysis to predict total laboratory overhead costs. The output generated by Excel is as​ follows:

SUMMARY OUTPUT
Regression Statistics
Multiple R		0.878214
R Square		0.77126
Adjusted R Square		0.725512
Standard Error		3156.338706
Observations		7
ANOVA
	df	SS	MS	F	Significance F
Regression	1	167956201.3	167956201.3	16.858885	0.009301
Residual	5	49812370.15	9962474.03
Total	6	217768571.4
		Standard			Lower	Upper
	Coefficients	Error	t Stat	P-value	95%	95%
Intercept	3187.94	5092.169	0.626	0.559	-9901.898	16277.778
X Variable 1	6.84	1.666	4.106	0.009	2.558	11.123

Answer 1

Part 1)

Using the excel regression provided in the question, we can determine the cost equation as below:

Cost Equation = $6.84X + $3,187.94

____

Notes:

1) $6.84 indicates variable cost per unit. (Refer to X Variable 1 under the head "Coefficients" in the Excel Regression Table). X indicates the number of tests peformed.

2) $3,187.94 indicates fixed cost. (Refer to Intercept under the head "Coefficients" in the Excel Regression Table).

______

Part 2)

The value of R square is 0.77126. (Refer to Summary Output>Regression Statistics>R Square) in Excel Regression Table)

______

Part 3)

The total laboratory overhead for the month if 3,100 tests are performed is calculated with the use of cost equation prepared in Part 1) as below:

Total Laboratory Overhead if 3100 Tests Peformed = 6.84*3,100 + 3187.94 = $24,391.94