Calculate the capitation rate per member per month that will be offered to Miller Hospital with 2% and 3.5% profit margin for shareholders (Please provide total capitation rates per member per month and also for PMPM for each service mentioned above to Miller Hospital) using an excel spreadsheet

Goldberg and Silverstein (GS) is a for-profit mid-level HMO company, providing health plans mostly in metro areas. GS is exploring to extend its health plan in a fast growing community called Newtown. There are three acute care hospitals in Newtown. GS bought Griffith HMO to provide services in Newtown. Griffith HMO is providing health care plans in Newtown area for past 10 years using Miller Hospital, one of the three hospitals in Newtown. You have been assigned to prepare capitation rates to offer a new contract to the Miller Hospital. You have researched the documentation from Griffith HMO, and collected the following information: Griffith HMO has currently 19,000 enrollees. The number of enrollees is expected for next year is 20,000. Here is expected utilization from the previous year data (suppose that hospital only provide the following services): Inpatient days: 300 inpatient days per 1000 enrollees per year. The cost of inpatient days: $1100 per day Outpatient Surgery: 450 surgeries per 10,000 enrollees per year, the cost of per outpatient surgery is $900. Emergency Room Visit: 50 per 1000, enrollees per month, the average cost of each ER visit is $300 Outpatient diagnostic (Other than CTScan, MRI, Echocardiography, and Ultrasound): 300 visits per 1000 enrollees per year, the average cost is $150 per visit Outpatient diagnostic (for CTScan, MRI, Echocardiography, and Ultrasound): 100 visits per 1000 enrollees per year, the average cost is $500 per visit The plan has 10% copay for inpatient services per day, $350 copay for each outpatient surgery, $300 copay for ER visit, and $50 copay for each diagnostic service per visit. You are expected to 15% of copay as bad debt for Miller Hospital. Griffith hospital uses physician services on a contractual basis and does no employ any physician. Therefore, GS will have separate capitation contract for physician services. The coordination of benefit is expected to cover 1.2% of the total cost. You are expecting 11% administrative cost. The CFO of GS suggested you to include some profit margin for the shareholders