PMPM formula: Category Hospital Inpatient Medical-Surgical Maternity Mental Health Subtotal Hospital Outpatient Surgery X-ray and Lab Emergency Department Subtotal Physician Inpatient surgery Outpatient surgery Office visits Inpatient visits Mental health Subtotal Total Administrative Costs/ Retention Factor (15%) PMPM Requirement Type your sentence here: PMPM = Expected encounters per year X Cost per encounter/12 You MUST use MS Excel formulas for calculating columns 4, 7 & 8. Table 7-8 Hospital and Physician Services Rates and Usage Rates Annual Frequency per Unit Cost 1,000 400 15 50 100 500 150 100 500 5000 250 400 1,000 1,000 400 1,500 300 300 2,000 300 100 150 150 PMPM Copay Frequency Copay Copay per 1,000 Amount PMPM 0 0 0 0 150 0 0 5000 0 400 0 0 0 0 50 50 0 10 0 20 Net PMPM Instructions: This assignment is based on the chapter section titled Setting Prices in Capitated Contracts, pp. 183-188. Read Real-World Scenario, pp. 171-172 for additional context on this assignment. Based on material covered in class lecture and your textbook, complete question 5 on page 190 of your textbook. Question 5: "You represent an integrated delivery system that is in negotiations with a health plan for a capitated rate to cover all hospital and physician services for a defined population. The following utilization data have been given to you, which detail last year's usage rates. You have included in this table your expected costs for selected services. Using the data presented in Table 7- 8, calculate a required breakeven rate for this contract, assuming that you need a 15% retention factor to cover administrative costs." 1. Populate the MS Excel template with data from Table 7-8, p 191. Note: I have added rows for the 15% retention factor for administrative costs and Total PMPM requirement. 2. Calculate the following PMPM and input into the table provided. You MUST use MS Excel formulas for calculations. 1) PMPM column 4 2) Copay PMPM column 7 3) Net PMPM column 8 The 3 PMPM rates must be calculated for each item in: Hospital Inpatient, Hospital Outpatient and Physician category. Note: The Total PMPM Requirement (all the Subtotals added together) is the minimum breakeven rate for this contract. 3. State in a sentence, what your net PMPM requirement must be for this contact between the IDS and the health plan to work for the IDS.