Embedded Files
Big Question: Which state had the biggest discrepancy between charges submitted and payments made in Medicare patients in the year 2014?
Data Source: Retrieved from a publicly available data set on Google's BigQuery.
Tools & Methods: Google Sheets, Pivot table
After ensuring cleanliness of the data, I created a pivot table using the SUM function to generate three columns which would display the sum of estimated charges, estimated Medicare payouts, and number of units billed as organized by state. I then created a custom column to display the difference between the payout and the estimated charges by state using the following formula:
=SUM(average_total_payments) -SUM(average_estimated_submitted_charges)
The differences for all states were negative numbers, indicating the submitted charges were higher than the total payout. When organized by this number alone, the state with the highest discrepancy was California, with a discrepancy of $6.16 million dollars.
Figure 1. Chart displaying the discrepancy between charges billed by providers and the amount paid out by Medicare.
However, California also had the highest population in 2014, so this discrepancy may be the highest by virtue of having provided services for a higher number of people. While the current data set does not provide information about the number of people served, it does provide a "number of services billed by a provider." In my experience as a healthcare provider, this likely refers the number of units billed to Medicare.
I created a new column to display the average amount of money charged per unit, organized by state using the following formula:
=average_estimated_submitted_charges/outpatient_services
Figure 2. Chart displaying the amount in dollars that was charged per service by providers
When ordered by cost per unit in descending order, the state with the highest charge per unit was Wyoming at approximately $11.15 per unit billed. California comes in at #14 with approximately $3.56 per unit billed.
I then became curious about which state had the highest amount paid out by medicare. I completed this by creating another custom column with the following formula:
=average_total_payment/outpatient_services
Figure 3. Chart displaying the amount in dollars paid out by Medicare per service.
The results of this indicate that again, Wyoming received the most money from Medicare per unit at $2.39. In this line of questioning, California comes in at #15 with $0.50 per unit paid out.
Summary of Results:
California had the largest discrepancy between the charges filed by providers and the payments made by Medicare.
Wyoming both charged the most and received the most per unit from Medicare.
Figure 4. Google Sheets workbook containing the original open data and the pivot table used for this analysis.
Page updated
Google Sites
Report abuse