Question

The manager of the main laboratory facility at Urban 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.

After running a regression analysis on the first seven months of the​ year, the manager of the main laboratory facility at Urban Health Center collects seven additional months of data. The number of tests performed and the total monthly overhead costs for the lab​ follow:

	Number of Lab Tests	Total Laboratory
Month	Performed	Overhead
August	3,350	$23,500
September	3,700	$27,550
October	3,650	$24,500
November	3,450	$26,400
December	4,200	$28,500
January	2,500	$22,800
February	3,800	$25,350

Use Excel to do the​ following:

1.	Run a regression analysis using data for August through February.
2.	Determine the​ lab's cost equation​ (use the output from the regression analysis you performed using​ Excel).
3.	Determine the​ R-square using the Excel output you obtain. What does the​ lab's R-square​ indicate?
4.	Predict the​ lab's total overhead costs for the month if 3,400 tests are preformed.

Requirements 1 and 2. Use Excel to run a regression analysis using data for August through February and determine the​ lab's cost equation​ (use the output from the regression analysis you perform using​ Excel). ​(Enter all dollar amounts to two decimal​ places.)

y = $

x + $

Answer 1

using excel>data>data analysis>regression

we have

Ans 1 )

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.79588
R Square	0.633425
Adjusted R Square	0.56011
Standard Error	1389.213
Observations	7
ANOVA
	df	SS	MS	F	Significance F
Regression	1	16674011	16674011	8.639778	0.032281
Residual	5	9649560	1929912
Total	6	26323571
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	14368.13	3828.227	3.753208	0.01325	4527.363	24208.9	4527.363	24208.9
number of lab test	3.165236	1.076849	2.93935	0.032281	0.397108	5.933365	0.397108	5.933365

Ans 2 ) the​ lab's cost equation is y = 14368.13+3.17 * number of lab test

Ans 3 ) the​ R-square 0.6334 it indicates that 63.34 % variation in Total Laboratory overhead explained by number of lab test

Ans 4 )the​ lab's total overhead costs for the month if 3,400 tests are preformed is 14368.13+3.17 *3400 = 25146.13