You have just been hired as a consultant to a local hospital and have decided to use your new MBA skills to make a recommendation to management on what should be the cost driver(s) for estimating the monthly overhead cost for the operating rooms in the hospital.
There are multiple operating rooms being used.
| Month |
Operating Room hours
used during month |
Operating Room Setup
Hours used during Month |
Number of VIP
patients during month |
Total Overhead costs for the operating rooms
during month |
| January |
700 |
225 |
5 |
$ 775,000 |
| February |
625 |
220 |
2 |
648,000 |
| March |
645 |
185 |
2 |
610,000 |
| April |
325 |
150 |
0 |
495,000 |
| May |
300 |
100 |
1 |
427,000 |
| June |
425 |
120 |
2 |
525,000 |
| July |
450 |
150 |
3 |
540,000 |
| August |
525 |
215 |
6 |
615,000 |
| September |
600 |
240 |
4 |
635,000 |
| October |
685 |
325 |
2 |
732,000 |
Required:
1. Prepare 3 scattergraphs and 3 simple regression analyses***** to estimate the overhead costs using each of the cost drivers above. Make sure you show(insert) the equation of the line and R-squared on each of your scattergraphs. The output for your simple regression table should be on a separate sheet and you should type out the equation of the line below the table output.
I have prepared the operating room hours scattergraph and regression for you, therefore, you actually only have to do 2. You may want to redo this scattergraph and regression analysis just to make sure you understand the process.
If you have done your scattergraph and simple regression correctly the equation of the line and the R-squared should match the respective output.
To make a scattergraph with data from nonadjacent columns highlight the X data (i.e. independent variable-cost driver) first and then hold down the control (ctrl) key and highlight the Y data (i.e. the dependent variable-cost).
2. Prepare a multiple regression table using all three of the activities as the independent variable. Using the output from the multiple regression, give the equation of the line to estimate factory cost in terms of the 3 cost drivers below the table output.
Also, make sure you identify which cost driver relates to X1, X2, and X3--I have done the first one for you.(excel reads left to right) Identify the 2 other X variables(I have identified the X1 variable for you, which will be the first column highlighted):
X1 Operating room hours
X2
X3
3. Assuming the following level of cost-driver volume for the month, what is the estimated cost using simple regression for each of the cost drivers and using multiple regression. You need to use cell references and the output from the regression tables to estimate the costs.
4. Using the simple and multiple regression analyses above, what would you recommend to estimate the cost of the overhead? Why?