Can you solve this for mesarahmrkhan! Your a great help

Cases in Healthcare Finance Case Questions CASE 7 QUESTIONS CASCADES MENTAL HEALTH CLINIC Variance Analysis Hint: Because of the large number of variances involved, this case can quickly become unmanageable. The best way to deal with this problem is to focus on the variance structure provided in Exhibit 7.5 of the case. The spreadsheet model for this case uses this structure, which divides the variances into two general categories: revenues and costs. Most of the analysis focuses on costs, and the variance definitions used in the model are such that a negative variance means costs are greater than expected. 1. What is the aggregate profit (total) variance for 2013 for the business as a whole? Interpret your results. 2. What is the profit (total) variance for each of the four product lines? Interpret your results. 3. Decompose the profit (total) variance for the aggregate organization and for each product line into revenue and cost variances. Interpret your results. 4. Decompose the revenue variances into enrollment and rate variances. Interpret your results. 5. Decompose the cost variances into volume and management variances. Interpret your results. 6. Now focus on the volume variances. Break these down into enrollment and utilization variances, both in the aggregate and for the four product lines. Interpret your results. 7. Now focus on the management variances. Note that the case does not contain data related to supplies costs or fixed costs, so the management variances are, in reality, staffing variances. Break down the staffing variances into rate and efficiency variances both for each product line and, within each product line, by inpatient and outpatient services. Interpret your results. 8. Summarize the results of your variance analysis. In this summary, focus on the underlying meaning of the numbers as opposed to the numbers themselves. 9. Finally, what are your recommendations for management action to counter the adverse financial trends experienced in 2013? 10. In your opinion, what are three key learning points from this case? CASE 7 8/13/13 Student Version Copyright 2014 Hea Administration Pres 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 analysisno 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 Commercial PC Medicare SH Commercial SH Medicare 0 0 0 0 Total 0 Expected Premium Data (Per Member Per Month) PC Commercial PC Medicare SH Commercial SH Medicare $0.00 0.00 0.00 0.00 Expected Labor Data per Admission or Session Inpatient # of Hours Hourly Rate Outpatient # of Hours Hourly Rate PC Comm PC Medicare SH Comm SH Medicare 0 0 0 0 $0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 $0.00 0.00 0.00 0.00 Expected Utilization and Total Labor Cost Data: Plan Type Avg # Members (in 000s) Inpatient Admission Cost per Rate Admission Total Costs Referral Rate Outpatien Cost per Session PC Comm PC Medicare Total 0.000 0.000 0.000 0.00 0.00 $0 0 $0 0 $0 0.00 0.00 $0 0 SH Comm SH Medicare Total 0.000 0.000 0.000 0.00 0.00 $0 0 $0 0 0 0.00 0.00 $0 0 Grand total 0.000 $0 Note that there are some rounding differences between the spreadsheet values here Actual Results: Actual Enrollment (Member-Months) PC Commercial PC Medicare SH Commercial SH Medicare 0 0 0 0 Total 0 Actual Premium Data (Per Member Per Month) PC Commercial PC Medicare SH Commercial SH Medicare $0.00 0.00 0.00 0.00 Actual Labor Data per Admission or Session # of Hours Inpatient Hourly Rate Outpatient # of Hours Hourly Rate PC Comm PC Medicare SH Comm SH Medicare 0 0 0 0 $0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 $0.00 0.00 0.00 0.00 Actual Utilization and Total Labor Cost Data: Plan Type Avg # Members (in 000s) Inpatient Admission Cost per Rate Admission Total Costs Referral Rate Outpatien Cost per Session PC Comm PC Medicare Total 0.000 0.000 0.000 0.00 0.00 $0 0 $0 0 $0 0.00 0.00 $0 0 SH Comm SH Medicare Total 0.000 0.000 0.000 0.00 0.00 $0 0 $0 0 $0 0.00 0.00 $0 0 Grand total 0.000 $0 MODEL-GENERATED (OUTPUT) DATA: BUDGET INPUTS: Member-Months: PC Comm PC Medicare SH Comm SH Medicare Premiums: PC Comm PC Medicare SH Comm SH Medicare Revenues: PC Comm PC Medicare SH Comm SH Medicare Admit Rates: Static Enrollment Enrollment Prem & Util Actual - - - - $0.00 0.00 0.00 0.00 $0.00 0.00 0.00 0.00 $0.00 0.00 0.00 0.00 $0.00 0.00 0.00 0.00 $0 0 0 0 $0 0 0 0 $0 0 0 0 $0 0 0 0 PC Comm PC Medicare SH Comm SH Medicare Cost per Admission: PC Comm PC Medicare SH Comm SH Medicare Referral Rates: PC Comm PC Medicare SH Comm SH Medicare Cost per Session: PC Comm PC Medicare SH Comm SH Medicare - - - - $0 0 0 0 $0 0 0 0 $0 0 0 0 $0 0 0 0 - - - - $0 0 0 0 $0 0 0 0 $0 0 0 0 $0 0 0 0 Enrollment Enrollment Prem & Util Actual BUDGETS: Static Total Revenues: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Total Costs (Staffing Costs): PC Commercial Inpatient PC Commercial Outpatient Total PC Medicare Inpatient PC Medicare Outpatient Total SH Commercial Inpatient SH Commercial Outpatient Total SH Medicare Inpatient SH Medicare Outpatient Total Aggregate $ - $ - $ - $ - $ - $ - $ - $ - $ $ $ - $ $ - $ $ - $ - $ - $ - $ - $ - $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Total Profits: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate $ - $ - $ - $ - $ - $ - $ - $ - Margin #DIV/0! #DIV/0! VARIANCE ANALYSIS: Profit (Total) Variance: PC Commercial PC Medicare SH Commercial $ SH Medicare Aggregate Revenue Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Enrollment Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Rate (PMPM) Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Cost Variance: PC Commercial PC Medicare SH Commercial SH Medicare $ Check Figures = Revenue Variance + Cos $ - - #DIV/0! #DIV/0! #DIV/0! - #DIV/0! - #DIV/0! $ - $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! Check Figures = Enrollment variance + Ra $ - $ - #DIV/0! $ $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! $ - #DIV/0! $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! $ - #DIV/0! $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! - Check Figures = Volume Variance + Mana $ $ $ $ - Aggregate Volume Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Enrollment Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Utilization Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Management Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Staffing Variance: PC Commercial Inpatient PC Commercial Outpatient Total PC Medicare Inpatient PC Medicare Outpatient Total SH Commercial Inpatient SH Commercial Outpatient Total SH Medicare Inpatient SH Medicare Outpatient Total Aggregate $ - #DIV/0! $ - $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! Check Figures = Enrollment Variance + U $ - $ - #DIV/0! $ $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! $ - #DIV/0! $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! $ - #DIV/0! $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! $ - #DIV/0! - $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Check Figures: $ $ $ $ $ $ $ $ - $ - #DIV/0! $ $ $ $ $ $ $ $ - Rate Variance: PC Commercial Inpatient PC Commercial Outpatient Total PC Medicare Inpatient PC Medicare Outpatient Total SH Commercial Inpatient SH Commercial Outpatient Total SH Medicare Inpatient SH Medicare Outpatient Total Aggregate Efficiency Variance: PC Commercial Inpatient PC Commercial Outpatient Total PC Medicare Inpatient PC Medicare Outpatient Total SH Commercial Inpatient SH Commercial Outpatient Total SH Medicare Inpatient SH Medicare Outpatient Total Aggregate $ $ - $ - $ $ - $ - $ $ $ $ $ $ $ $ $ $ $ $ Note that some rounding differences may occur in these data. yright 2014 Health ministration Press Outpatient Cost per Total Session Costs Grand Total $0 0 $0 $0 0 $0 $0 0 0 $0 0 0 $0 $0 heet values here and Exhibit 7.1 in the case. Outpatient Cost per Total Session Costs Grand Total $0 0 $0 $0 0 $0 $0 0 $0 $0 0 $0 $0 $0 nue Variance + Cost Variance: lment variance + Rate (PMPM) Variance: me Variance + Management Variance: lment Variance + Utilization Variance: END CASE 7 8/13/13 Student Version Copyright 2014 Hea Administration Pres 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 analysisno 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. Highlighted are the numbers I used to interp INPUT DATA: Static Assumptions: Expected Enrollment (Member-Months) PC Commercial PC Medicare SH Commercial SH Medicare Total 3,365,000 469,000 502,000 215,000 4,551,000 Expected Premium Data (Per Member Per Month) PC Commercial PC Medicare SH Commercial SH Medicare $0.70 0.85 0.75 0.80 Expected Labor Data per Admission or Session Inpatient # of Hours Hourly Rate Outpatient # of Hours Hourly Rate PC Comm PC Medicare SH Comm SH Medicare 53.74 68.43 47.77 56.86 $35.00 35.00 35.00 35.00 Expected Utilization and Total Labor Cost Data: Plan Type PC Comm PC Medicare Total SH Comm SH Medicare Total Grand total 1.04 1.30 1.15 1.14 $100.00 100.00 100.00 100.00 Numbers used to interpret problem 3 Inpatient Admission Cost per Rate Admission Avg # Members (in 000s) 280.417 39.083 3.81 3.96 $1,881 2,395 319.500 Total Costs $2,009,637 370,674 Outpatie Cost per Session Referral Rate 2.00 2.00 $104 130 2.00 2.00 $115 114 $2,380,311 41.833 17.917 59.750 3.89 4.17 $1,672 1,990 379.250 $272,087 148,678 420,765 $2,801,076 Note that there are some rounding differences between the spreadsheet values her Actual Results: Actual Enrollment (Member-Months) PC Commercial PC Medicare SH Commercial SH Medicare 3,073,133 485,000 547,105 257,000 Total 4,362,238 Actual Premium Data (Per Member Per Month) PC Commercial PC Medicare SH Commercial SH Medicare $0.75 0.85 0.80 0.80 Actual Labor Data per Admission or Session Inpatient Outpatient # of Hours PC Comm PC Medicare SH Comm SH Medicare Hourly Rate 47.32 58.66 52.06 84.85 $38.00 38.00 33.00 33.00 # of Hours 0.95 1.15 0.98 2.00 Hourly Rate $109.50 109.50 95.00 95.00 Actual Utilization and Total Labor Cost Data: Plan Type Avg # Members (in 000s) Inpatient Admission Cost per Rate Admission Total Costs Outpatie Cost per Session Referral Rate PC Comm PC Medicare Total 256.094 40.417 296.511 4.33 4.68 $1,798 2,229 $1,993,782 421,615 $2,415,397 3.65 1.86 $104 126 SH Comm SH Medicare Total 45.592 21.417 67.009 5.79 4.56 $1,718 2,800 $453,514 273,448 $726,962 3.35 1.75 $93 190 Grand total 363.520 $3,142,360 MODEL-GENERATED (OUTPUT) DATA: BUDGET INPUTS: Member-Months: PC Comm PC Medicare SH Comm SH Medicare Premiums: PC Comm PC Medicare SH Comm SH Medicare Revenues: PC Comm PC Medicare SH Comm SH Medicare Static Enrollment Enrollment Prem & Util 3,365,000 469,000 502,000 215,000 3,073,133 485,000 547,105 257,000 3,073,133 485,000 547,105 257,000 3,073,133 485,000 547,105 257,000 $0.70 0.85 0.75 0.80 $0.70 0.85 0.75 0.80 $0.75 0.85 0.80 0.80 $0.75 0.85 0.80 0.80 $2,355,500 398,650 376,500 172,000 $2,151,193 412,250 410,329 205,600 $2,304,850 412,250 437,684 205,600 $2,304,850 412,250 437,684 205,600 Actual Admit Rates: PC Comm PC Medicare SH Comm SH Medicare Cost per Admission: PC Comm PC Medicare SH Comm SH Medicare Referral Rates: PC Comm PC Medicare SH Comm SH Medicare Cost per Session: PC Comm PC Medicare SH Comm SH Medicare 3.81 3.96 3.89 4.17 3.81 3.96 3.89 4.17 4.33 4.68 5.79 4.56 4.33 4.68 5.79 4.56 $1,881 2,395 1,672 1,990 $1,881 2,395 1,672 1,990 $1,881 2,395 1,672 1,990 $1,798 2,229 1,718 2,800 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 3.65 1.86 3.35 1.75 3.65 1.86 3.35 1.75 $104 130 115 114 $104 130 115 114 $104 130 115 114 $104 126 93 190 Static Enrollment Enrollment Prem & Util Actual $ 2,355,500 398,650 376,500 172,000 $ 2,151,193 412,250 410,329 205,600 $ 2,304,850 412,250 437,684 205,600 $ 2,304,850 412,250 437,684 205,600 $ 3,302,650 $ 3,179,372 $ 3,360,384 $ 3,360,384 $ 2,009,637 58,327 $ 2,067,964 $ 370,674 10,162 $ 380,836 $ 272,087 9,622 $ 281,709 $ 148,678 4,085 $ 152,763 $ 1,835,329 53,268 $ 1,888,596 $ 383,320 10,508 $ 393,828 $ 296,535 10,486 $ 307,021 $ 177,722 4,883 $ 182,605 $ 2,085,820 97,213 $ 2,183,033 $ 453,014 9,773 $ 462,787 $ 441,371 17,564 $ 458,936 $ 194,343 4,273 $ 198,616 $ 1,993,782 97,213 $ 2,090,996 $ 421,615 9,472 $ 431,087 $ 453,514 14,204 $ 467,719 $ 273,448 7,121 $ 280,569 $ 2,883,271 $ 2,772,050 $ 3,303,372 $ 3,270,371 BUDGETS: Total Revenues: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Total Costs (Staffing Costs): PC Commercial Inpatient PC Commercial Outpatient Total PC Medicare Inpatient PC Medicare Outpatient Total SH Commercial Inpatient SH Commercial Outpatient Total SH Medicare Inpatient SH Medicare Outpatient Total Aggregate Total Profits: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate $ 287,536 17,814 94,791 19,237 $ 262,597 18,422 103,308 22,995 $ $ 419,379 $ 407,322 $ Margin 121,816 $ (50,537) (21,252) 6,984 57,012 $ 12.7% 213,854 (18,837) (30,035) (74,969) 90,013 2.7% VARIANCE ANALYSIS: Profit (Total) Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Revenue Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Enrollment Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Rate (PMPM) Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Cost Variance: PC Commercial PC Medicare SH Commercial Check Figures = Revenue Variance + C $ (73,682) (36,652) (124,826) (94,206) $ (73,682) (36,652) (124,826) (94,206) -26% -206% -132% -490% $ (329,366) -79% $ (329,366) $ (50,650) 13,600 61,184 33,600 -2% 3% 16% 20% $ (50,650) 13,600 61,184 33,600 $ 57,734 2% $ 57,734 $ (204,307) 13,600 33,829 33,600 -9% 3% 9% 20% $ (123,278) -4% $ 153,657 27,355 - 7% 0% 7% 0% $ 181,012 6% $ (23,032) (50,252) (186,010) -1% -13% -66% Check Figures = Volume Variance + Mana $ (23,032) $ (50,252) $ (186,010) SH Medicare Aggregate (127,806) -84% $ (127,806) $ (387,099) -13% $ (387,099) $ (115,070) -6% $ (115,070) Volume Variance: PC Commercial PC Medicare (81,951) -22% (81,951) (177,227) -63% (177,227) (45,853) -30% (45,853) $ (420,101) -15% $ 179,367 (12,992) (25,312) 9% -3% -9% (29,842) -20% $ 111,221 4% $ (294,437) (68,959) (151,915) (16,011) -16% -18% -49% -9% $ (531,322) -19% $ 92,038 31,700 (8,783) (81,953) 76% -63% 41% -1173% $ 33,001 58% SH Commercial SH Medicare Aggregate Enrollment Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Utilization Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Management Variance: PC Commercial PC Medicare SH Commercial SH Medicare Aggregate Staffing Variance: PC Commercial Inpatient PC Commercial Outpatient Total PC Medicare Inpatient PC Medicare Outpatient Total SH Commercial Inpatient SH Commercial Outpatient Total SH Medicare Inpatient SH Medicare Outpatient $ $ $ $ $ $ $ 92,038 92,038 31,399 301 31,700 (12,143) 3,360 (8,783) (79,105) (2,848) 5% 0% 4% 7% 3% 7% -3% 24% -2% -29% -40% $ (420,101) Check Figures: $ 91,749 (23) $ 91,726 $ 31,393 306 $ 31,700 $ (12,151) 3,345 $ (8,806) $ (79,100) (2,848) Total Aggregate $ (81,953) -29% $ (81,948) $ 33,001 1% $ 32,671 $ $ (157,418) (8,436) (165,854) (33,287) (821) (34,108) 27,485 748 28,234 16,573 375 16,948 $ (154,780) $ $ 249,167 8,413 257,580 64,680 1,128 65,807 (39,636) 2,596 (37,040) (95,673) (3,223) (98,896) $ 187,452 Rate Variance: PC Commercial Inpatient PC Commercial Outpatient Total PC Medicare Inpatient PC Medicare Outpatient Total SH Commercial Inpatient SH Commercial Outpatient Total SH Medicare Inpatient SH Medicare Outpatient Total Aggregate Efficiency Variance: PC Commercial Inpatient PC Commercial Outpatient Total PC Medicare Inpatient PC Medicare Outpatient Total SH Commercial Inpatient SH Commercial Outpatient Total SH Medicare Inpatient SH Medicare Outpatient Total Aggregate $ $ $ $ $ $ $ $ $ $ $ $ yright 2014 Health ministration Press sed to interpret each problem blem 3 Outpatient Cost per Session Total Costs Grand Total $58,327 10,162 $2,067,966 380,833 $68,489 $2,448,799 $9,622 4,085 13,707 $281,707 152,766 434,473 $82,196 $2,883,272 sheet values here and Exhibit 7.1 in the case. Outpatient Cost per Session Total Costs Grand Total $97,213 9,472 $106,685 $2,090,996 431,087 $2,522,083 $14,204 7,121 $21,325 $467,719 280,569 $748,288 $128,011 $3,270,371 enue Variance + Cost Variance: me Variance + Management Variance: END