E6-33A Performing a regression analysis to predict overhead for a hospital laboratory (Learning Objective 5)

The manager of the main laboratory facility at CenterHealth Center (from E6-30A) collects seven additional months of data after obtaining the regression results in E6-32A. The number of tests performed and the total monthly overhead costs for the lab follow:

Month Number of Lab Tests Performed Total Laboratory Overhead Costs August......................................... 3,150 $22,950 September................................... 3,100 $21,750 October....................................... 3,850 $25,460 November................................... 4,160 $29,800 December.................................... 3,920 $28,200 January........................................ 2,700 $19,750 February...................................... 3,450 $23,400 Use Excel to do the following:

Requirements 1. Run a regression analysis.

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 performed.