A healthcare manager is interested in determining the number of…

A healthcare manager is interested in determining the number of ACOs with 25,000 or more assigned beneficiaries.

1. Navigate to https://data.cms.gov/. Select the ACO category and locate the Medicare Shared Savings Program Accountable Care Organizations Performance Year 1 Results data set. Click “Export” and download the file into MS Excel format.
2. Insert a new column entitled “Beneficiary Grouping” to the right of the “Total Assigned Beneficiaries Column”. In the first cell of this new column, input the following formula: =F(F2<25000,”< 25000″,”25000+”) This will assign a value of “<25000” for all ACOs with fewer than 25,000 beneficiaries and “25000+” to ACOs with 25,000 or more assigned beneficiaries. Copy the formula down to all rows. Note that if the “Total Assigned Beneficiaries” column were in column C instead of F, the formula would read =IF(C1<25000,…).
3. Construct a pivot table that compares the total counts of ACOs with fewer than 25,000 beneficiaries and ACOs with 25,000 or more assigned beneficiaries. Include a filter for the beneficiaries’ state of residence. What is the total number of ACOs with 25,000 or more assigned beneficiaries?
4. Suppose the manager is interested in the total expenditure per assigned beneficiary for ACOs with fewer than 25,000 beneficiaries compared to those with 25,000 or more beneficiaries. Modify the pivot table in part (b) to display the sum of assigned beneficiaries and the sum of total expenditures for ACOs with fewer than 25,000 beneficiaries versus those with 25,000 or more beneficiaries.
5. Calculate the average expenditure per beneficiary for each beneficiary grouping by dividing the sum of expenditures by the sum of assigned beneficiaries.