Multiple Regression Analysis for a Special Decision $($Requires Computer Spreadsheet$)$

For billing purposes, assume Phoenix Family Medical Clinic classifies its services into one of four major procedures, X$1$ through X$4.$ Assume that a local business has proposed that Phoenix provide health services to its employees and their families at the following set rates per procedure:

X$1$ $$100$

X$2200$

X$360$

X$4300$

Because these rates are significantly below the current rates charged for these services, management has asked for detailed cost information on each procedure. The following information is available for the most recent $12$ months and is also provided in this link.

Number of Procedures

Month Total Cost X$1$ X$2$ X$3$ X$4$

$1$ $$17,250302515519$

$218,750383013523$

$320,250502010538$

$414,25020259025$

$515,000681512020$

$620,250901915814$

$719,125203014328$

$816,125163013220$

$919,50060219335$

$1016,50020227535$

$1117,100201811333$

$1219,875721515030$

Required

Note: Round your answers to two decimal places.

a$.$ Use multiple regression analysis to determine the unit cost of each procedure.

Y $=$ Answer

$4275\mathrm{.}29$

$+$ Answer

$74\mathrm{.}97$

X$1+$ Answer

$235\mathrm{.}79$

X$2+$ Answer

$51\mathrm{.}63$

X$3+$ Answer

$274\mathrm{.}98$

X$4$

How much variation in monthly cost is explained by your cost$-$estimating equation? Hint: First load Analysis ToolPak in Excel. Hint: Under the Data tab, click on Data analysis, Regression, and select the cells for the Y Range and X Range. Hint: Use the R$-$squared value and not the adjusted R$-$squared value.

The cost$-$estimating equation explains Answer

$\%$ of the variation in total manufacturing costs.

b$.$ Evaluate the rates proposed by the local business. Assuming Phoenix has excess capacity and no employees of the local business currently patronize the clinic, what are your recommendations regarding the proposal?

Note: Use negative signs with answers only to indicate a loss. Otherwise, do not use negative signs with your answers.

Comparison of Proposed Rates and Estimated Variable Costs

Procedure X$1$ X$2$ X$3$ X$4$

Proposed rate Answer

Answer

Answer

Answer

Estimated cost Answer

Answer

Answer

Answer

Profit $($loss$)$ Answer

Answer

Answer

Answer

The desirability of the proposal depends on the Answer

used by the employees of the local business.

If the mix contained a significant portion of Answer

$,$ the proposal might not be desirable.

c$.$ Assuming Phoenix is operating at capacity and would have to turn current customers away if it agrees to provide health services to the local business, what are your recommendations regarding the proposal?

Phoenix should accept the proposal.

Phoenix should reject the proposal.