Total variance Revenue variance Enrollment variance Rate variance Cost variance Volume variance Enrollment variance Utilization variance
Management variance Fixed cost variance Staffing variance
Rate variance Efficiency variance Supplies variance Price variance
Usage variance
= Actual profit — Static profit = Actual revenue — Static revenue = Flexible (enrollment) revenue — Static revenue = Actual revenue — Flexible (enrollment) revenue = Static costs — ACtual costs = Static costs — Flexible (enrollment/utilization) costs = Static costs — Flexible (enrollment) costs = Flexible (enrollment) costs — Flexible (enrollment/ utilization) costs = Flexible (enrollment/utilization) costs Actual costs = Actual fixed costs — Flexible fixed costs = Actual staffing costs — Flexible (enrollment/utilization) staffing costs = (Static hourly labor rate — Actual hourly labor rate) x Actual number of hours per episode x Actual utilization rate x Actual enrollment = (Expected number of hours per episode — Actual number of hours per episode) x Expected hourly labor rate x Actual utilization rate x Actual enrollment = Flexible (enrollment/utilization) supplies costs —Actual supplies costs = (Static price — Actual price) x Actual units = (Flexible units — Actual units) x Static price
Note: Not all of the above equations are necessarily useful to all product lines.
CASE7 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 the appropriate input values and enter them into the model. These cells are colored red. 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 Commercial3,365,000 PC Medicare469,000 SH Commercial502,000 SH Medicare215,000 Total4,551,000 Expected 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 Session Inpatient Outpatient # of Hours Hourly Rate # of Hours Hourly Rate PC Comm53.74$35.001.04$100.00 PC Medicare68.4335.001.30100.00 SH Comm47.7735.001.15100.00 SH Medicare56.8635.001.14100.00 Expected Utilization and Total Labor Cost Data: Inpatient Outpatient Avg # Members Admission Cost per Total Referral Cost per Total Grand Plan Type (in 000s) Rate Admission Costs Rate Session Costs Total PC Comm280.4173.81$1,881$2,009,6372.00$104$58,327$2,067,964 PC Medicare39.0833.962,395370,6742.0013010,162380,836 Total319.500$2,380,311$68,488$2,448,799 SH Comm41.8333.89$1,672$272,0872.00$115$9,622$281,709 SH Medicare17.9174.171,990148,6782.001144,085152,763 Total59.750420,76513,707434,472 Grand total379.250$2,801,076$82,195$2,883,271 Actual Results: Actual Enrollment (Member-Months) PC Commercial3,073,133 PC Medicare485,000 SH Commercial547,105 SH Medicare257,000 Total4,362,238 Actual 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 Session Inpatient Outpatient # of Hours Hourly Rate # of Hours Hourly Rate PC Comm47.32$38.000.95$109.50 PC Medicare58.6638.001.15109.50 SH Comm52.0633.000.9895.00 SH Medicare84.8533.002.0095.00 Actual Utilization and Total Labor Cost Data: Inpatient Outpatient Avg # Members Admission Cost per Total Referral Cost per Total Grand Plan Type (in 000s) Rate Admission Costs Rate Session Costs Total PC Comm256.0944.33$1,798$1,993,7823.65$104$97,213$2,090,996 PC Medicare40.4174.682,229421,6151.861269,472431,087 Total296.511$2,415,397$106,685$2,522,083 SH Comm45.5925.79$1,718$453,5143.35$93$14,204$467,719 SH Medicare21.4174.562,800273,4481.751907,121280,569 Total67.009$726,962$21,325$748,288 Grand total363.520$3,142,360$128,011$3,270,371 MODEL-GENERATED (OUTPUT) DATA: BUDGET INPUTS: Enrollment StaticEnrollmentPrem & UtilActual Member-Months: PC Comm3,365,0003,073,1333,073,1333,073,133 PC Medicare469,000485,000485,000485,000 SH Comm502,000547,105547,105547,105 SH Medicare215,000257,000257,000257,000 Premiums: PC Comm$0.70$0.70$0.75$0.75 PC Medicare0.850.850.850.85 SH Comm0.750.750.800.80 SH Medicare0.800.800.800.80 Revenues: PC Comm$2,355,500$2,151,193$2,304,850$2,304,850 PC Medicare398,650412,250412,250412,250 SH Comm376,500410,329437,684437,684 SH Medicare172,000205,600205,600205,600 Admit Rates: PC Comm3.813.814.334.33 PC Medicare3.963.964.684.68 SH Comm3.893.895.795.79 SH Medicare4.174.174.564.56 Cost per Admission: PC Comm$1,881$1,881$1,881$1,798 PC Medicare2,3952,3952,3952,229 SH Comm1,6721,6721,6721,718 SH Medicare1,9901,9901,9902,800 Referral Rates: PC Comm2.002.003.653.65 PC Medicare2.002.001.861.86 SH Comm2.002.003.353.35 SH Medicare2.002.001.751.75 Cost per Session: PC Comm$104$104$104$104 PC Medicare130130130126 SH Comm11511511593 SH Medicare114114114190 BUDGETS: Enrollment StaticEnrollmentPrem & UtilActual Total Revenues: PC Commercial$ 2,355,500$ 2,151,193$ 2,304,850$ 2,304,850 PC Medicare398,650412,250412,250412,250 SH Commercial376,500410,329437,684437,684 SH Medicare172,000205,600205,600205,600 Aggregate$ 3,302,650$ 3,179,372$ 3,360,384$ 3,360,384 Total Costs (Staffing Costs): PC Commercial Inpatient$ 2,009,637$ 1,835,329$ 2,085,820$ 1,993,782 PC Commercial Outpatient58,32753,26897,21397,213 Total$ 2,067,964$ 1,888,596$ 2,183,033$ 2,090,996 PC Medicare Inpatient$ 370,674$ 383,320$ 453,014$ 421,615 PC Medicare Outpatient10,16210,5089,7739,472 Total$ 380,836$ 393,828$ 462,787$ 431,087 SH Commercial Inpatient$ 272,087$ 296,535$ 441,371$ 453,514 SH Commercial Outpatient9,62210,48617,56414,204 Total$ 281,709$ 307,021$ 458,936$ 467,719 SH Medicare Inpatient$ 148,678$ 177,722$ 194,343$ 273,448 SH Medicare Outpatient4,0854,8834,2737,121 Total$ 152,763$ 182,605$ 198,616$ 280,569 Aggregate$ 2,883,271$ 2,772,050$ 3,303,372$ 3,270,371 Total Profits: PC Commercial$ 287,536$ 262,597$ 121,816$ 213,854 PC Medicare17,81418,422(50,537)(18,837) SH Commercial94,791103,308(21,252)(30,035) SH Medicare19,23722,9956,984(74,969) Aggregate$ 419,379$ 407,322$ 57,012$ 90,013 Margin12.7%2.7% VARIANCE ANALYSIS: Profit (Total) Variance:Check Figures = Revenue Variance + Cost Variance: PC Commercial$ (73,682)-26%$ (73,682) PC Medicare(36,652)-206%(36,652) SH Commercial(124,826)-132%(124,826) SH Medicare(94,206)-490%(94,206) Aggregate$ (329,366)-79%$ (329,366) Revenue Variance:Check Figures = Enrollment variance + Rate (PMPM) Variance: PC Commercial$ (50,650)-2%$ (50,650) PC Medicare13,6003%13,600 SH Commercial61,18416%61,184 SH Medicare33,60020%33,600 Aggregate$ 57,7342%$ 57,734 Enrollment Variance: PC Commercial$ (204,307)-9% PC Medicare13,6003% SH Commercial33,8299% SH Medicare33,60020% Aggregate$ (123,278)-4% Rate (PMPM) Variance: PC Commercial$ 153,6577% PC Medicare-0% SH Commercial27,3557% SH Medicare-0% Aggregate$ 181,0126% Cost Variance:Check Figures = Volume Variance + Management Variance: PC Commercial$ (23,032)-1%$ (23,032) PC Medicare(50,252)-13%$ (50,252) SH Commercial(186,010)-66%$ (186,010) SH Medicare(127,806)-84%$ (127,806) Aggregate$ (387,099)-13%$ (387,099) Volume Variance:Check Figures = Enrollment Variance + Utilization Variance: PC Commercial$ (115,070)-6%$ (115,070) PC Medicare(81,951)-22%(81,951) SH Commercial(177