12 PivotTables with CMS Data: Medicare Inpatient Hospitals
Download the data
Answer the following questions using the file you download from Medicare Inpatient Hospitals – by Provider and Service Information on hospital discharges from Original Medicare (fee-for-service) Part A (Hospital Insurance) beneficiaries by Inpatient Prospective Payment System (IPPS) hospitals; aggregated by provider and service.
Let’s start with the context because having a general understanding of what we will find in the data will allow us to process it faster.
Warm-up QUESTIONS:
- What does the file name reveal? What do you think of the naming convention?
- What kinds of data do you expect to find in a file?
- Next, open the data file. Is there a summary of info sheet?
- Is there a Data Dictionary? If it’s not included in the data file, where can you locate it?
- Do the field names make sense? What can you do if there is no data dictionary to be found?
- How much data is in the workbook: how many fields, records, cells with contents are there?
- What types of questions can you answer with the data?
- What functions do you know in Excel that will help you process data?
Explore the data with pivottables
Click anywhere in the data in the Excel file, then click INSERT > PivotTable. Insert in New Worksheet.
The PivotTable field selector will show the field names on the right side of the interface. Consult the Data Dictionary also downloadable from the website to understand what the field names mean.
Now, build some summary data to find out how states, providers, and other relevant items compare!
You can create PivotTables to calculate the following:
- Which state has the highest number of total discharges?
- Which state has the lowest number of total discharges?
- How many total discharges are there in Texas?
- Which state has the highest overall average charge (Avg_Submtd_Cvrd_Chrg) of all providers’ services covered by Medicare?
- Which state has the lowest?
- How does Texas (or the state of your choice) compares to the highest or lowest values? Is this reasonable compared to your expectations?
- Which state has the highest number of total payments?
- Which state has the lowest number of total payments?
- How many total payments are there in Texas?
- Can you think of ways to incorporate the Medicare payments into this?
