case7-stephanie ramirez.xls - CASE 7 Student Version Copyright 2014 Hea Administration Pres CASCADES MENTAL HEALTH CLINIC Variance Analysis This case

case7-stephanie ramirez.xls - CASE 7 Student Version...

This preview shows page 1 - 4 out of 15 pages.

CASE 7 Student Version 8/13/13 CASCADES MENTAL HEALTH CLINIC Variance Analysis This case illustrates the use of variance analysis on a mental health provider's four product lines. There is a large amount of input data required by the model. Also, because the model's output cannot be easily summarized, there is no KEY OUTPUT section. The model consists of a complete base case analysis—no changes need to be made to the existing MODEL-GENERATED DATA section. However, all values in the student version INPUT DATA section have been replaced with zeros. Thus, students must determine When this is done, any error cells will be corrected and the base case solution will appear. Furthermore, students will have to create any graphical output needed in the case. Note that all variances have been defined in the model so that negative values are bad. Also, note that there are a variety of variance definitions that can be applied, as well as a variety of approaches to variance analysis. This model uses only one approach; the one suggested by Exhibit 7.5 in the case. INPUT DATA: Static Assumptions: Expected Enrollment (Member-Months) PC Commercial 3,365,000 PC Medicare 469,000 SH Commercial 502,000 SH Medicare 215,000 Total 4,551,000 Expected Premium Data (Per Member Per Month) PC Commercial $0.70 PC Medicare 0.85 SH Commercial 0.75 SH Medicare 0.80 Expected Labor Data per Admission or Session Inpatient Outpatient # of Hours Hourly Rate # of Hours Hourly Rate Copyright 2 Administra the appropriate input values and enter them into the model. These cells are colored red .
PC Comm 53.74 $35.00 1.04 $100.00 PC Medicare 68.43 35.00 1.30 100.00 SH Comm 47.77 35.00 1.15 100.00 SH Medicare 56.86 35.00 1.14 100.00 Expected Utilization and Total Labor Cost Data: Inpatient Avg # Members Admission Cost per Total Referral Cos Plan Type (in 000s) Rate Admission Costs Rate Ses PC Comm 280.417 3.81 $1,881 $2,009,637 2.00 $104 PC Medicare 39.083 3.96 2,395 370,674 2.00 130 Total 319.500 $2,380,311 SH Comm 41.833 3.89 $1,672 $272,087 2.00 $115 SH Medicare 17.917 4.17 1,990 148,678 2.00 114 Total 59.750 420,765 Grand total 379.250 $2,801,076 Actual Results: Actual Enrollment (Member-Months) PC Commercial 3,073,133 PC Medicare 485,000 SH Commercial 547,105 SH Medicare 257,000 Total 4,362,238 Actual Premium Data (Per Member Per Month) PC Commercial $0.75 PC Medicare 0.85 SH Commercial 0.80 SH Medicare 0.80 Actual Labor Data per Admission or Session Inpatient Outpatient # of Hours Hourly Rate # of Hours Hourly Rate Note that there are some rounding differences between the spreadsheet va
PC Comm 47.32 $38.00 0.95 $109.50 PC Medicare 58.66 38.00 1.15 109.50 SH Comm 52.06 33.00 0.98 95.00 SH Medicare 84.85 33.00 2.00 95.00 Actual Utilization and Total Labor Cost Data: Inpatient Avg # Members Admission

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture