I need help with an excel problem in my healthcare finance class. I am completely stuck and don't know what to do! Attached is the problem. Please help!!!
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT, 5ed
Chapter 17 -- Capitation, Rate Setting, and Risk Sharing
Families First is a managed care plan that has been asked to submit a premium bid to ABC Company, a large
manufacturer in its service area. The premium bid includes the primary care for all of the ABC employees.
ABC has provided information about the age and gender of its employees, and Families First has identified
average primary care utilization rates from its own databases. This information is shown below:
ABC Company Average Primary Care
Number of Number of Visits per Year
Age Band Males Females Males Females
20-29 285 325 2.10 3.18
30-39 96 100 2.60 3.52
40-49 53 57 3.28 3.93
50-59 36 36 4.14 4.43
60+ 7 5 4.98 5.04
Each primary care physician can handle about 3,000 patient visits per year, for which he or she is paid
$180,000. What primary care rate (PMPM) will Families First propose to ABC Company?
We need to find the number of primary care visits on average, each year, from the employees of ABC company. Since we know the primary care rate per physician, all we need to determine is the number of physicians necessary.
For each age band and gender, ...
We find the number of primary care visits from the employees of ABC company and determine the number of physicians necessary.