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, X1 through X4. A local business has proposed that Phoenix provide health services to its employees and their families at the following set rates per procedure: X1 $100 X2 200 X3 60 X4 300 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. Number of Procedures Month Total Cost X1 X2 X3 X4 $17,250 30 155 19 18,750 38 30 135 20,250 50 20 105 38 14,250 20 25 90 25 15,000 68 15 120 20 20,250 90 19 158 14 19,125 20 30 143 16,125 16 30 132 20 9 19,500 60 21 93 35 10 16,500 20 22 75 35 11 17,100 20 18 113 33 12 19,875 72 15 150 30 Required a. Use multiple regression analysis to determine the unit cost of each procedure. How much variation in monthly cost is explained by your cost-estimating equation? Round each answer to two decimal places. Y = $ 0 x + 3 0 x X1 + $ 0 x X2 + $ ( x X3 + $ 0 x X4 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? 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 X1 X2 X3 X4 Proposed rate |$ 0 X Estimated cost 0 x Profit (loss) 0 * $ 0 * $ 0 x 5 0 x c. Evaluate the rates proposed by the local business. 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? OPhoenix should accept the proposal. Phoenix should reject the proposal.v