CASE 7Student Version8/13/13CASCADES MENTAL HEALTH CLINICVariance AnalysisThis case illustrates the use of variance analysis on a mental health provider's four productlines. There is a large amount of input data required by the model. Also, because themodel'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 madeto the existing MODEL-GENERATED DATA section. However, all values in the studentversion INPUT DATA section have been replaced with zeros. Thus, students must determineWhen 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 onesuggested by Exhibit 7.5 in the case.INPUT DATA:Static Assumptions:Expected Enrollment (Member-Months)PC Commercial3,365,000PC Medicare469,000SH Commercial502,000SH Medicare215,000Total4,551,000Expected Premium Data (Per Member Per Month)PC Commercial$0.70 PC Medicare0.85 SH Commercial0.75 SH Medicare0.80 Expected Labor Data per Admission or SessionInpatient Outpatient # of Hours Hourly Rate # of Hours Hourly RateCopyright 2Administrathe appropriate input values and enter them into the model. These cells are colored red.
PC Comm53.74$35.00 1.04$100.00 PC Medicare68.4335.00 1.30100.00 SH Comm47.7735.00 1.15100.00 SH Medicare56.8635.00 1.14100.00 Expected Utilization and Total Labor Cost Data:Inpatient Avg # Members Admission Cost per Total Referral CosPlan Type (in 000s) Rate Admission Costs Rate SesPC Comm280.4173.81$1,881$2,009,6372.00$104PC Medicare39.0833.962,395370,6742.00130Total 319.500$2,380,311SH Comm41.8333.89$1,672$272,0872.00$115SH Medicare17.9174.171,990148,6782.00114Total 59.750420,765Grand total379.250$2,801,076Actual Results:Actual Enrollment (Member-Months)PC Commercial3,073,133PC Medicare485,000SH Commercial547,105SH Medicare257,000Total4,362,238Actual Premium Data (Per Member Per Month)PC Commercial$0.75 PC Medicare0.85 SH Commercial0.80 SH Medicare0.80 Actual Labor Data per Admission or SessionInpatient Outpatient # of Hours Hourly Rate # of Hours Hourly RateNote that there are some rounding differences between the spreadsheet va
PC Comm47.32$38.00 0.95$109.50 PC Medicare58.6638.00 1.15109.50SH Comm52.0633.00 0.9895.00SH Medicare84.8533.00 2.0095.00Actual Utilization and Total Labor Cost Data:Inpatient Avg # Members Admission