Question

Medicare publishes reimbursement data every year by providers for charges related to inpatient hospitalizations. Centers for Medicare and Medicaid services publishes such files for every year, albeit after some delay. In this homework, we will use spreadsheet techniques to analyze Medicare reimbursement data. The attached Excel spreadsheet file only shows the above data for IL, IN, IA, and WI (see the first worksheet/tab in the attached Excel file; name of the tab Provider_DRG_Summary).

Draw a chart that shows the average reimbursement by provider name in the state of IL for DRG code "219 - CARDIAC VALVE & OTH MAJ CARDIOTHORACIC PROC W/O CARD CATH W MCC". (To complete this task, only consider the "Average Medicare Payments" column (column L) in the Excel spreadsheet.) Also, show this data as a table.

What are the minimum, maximum, average, and standard deviation of reimbursements for this data (Average Medicare reimbursements for DRG code 219 by provider in the state of IL)?

As a manager at your hospital, how would you use the standard deviation to determine whether the reimbursements your hospital is receiving for code 219 are not too high or too low?

Add a new column "Payment Ratio" to the Excel spreadsheet worksheet "Provider_DRG_Summary." Compute the payment ratio for each row as follows: Average Medicare Payments/Average Covered Charges. In other words, the payment ratio in cell M7 will be L7/J7.

Show the payment ratio as a percentage.

For DRG code: "682 - RENAL FAILURE W MCC," what is the average payment ratio by state? Which state has the highest payment ratio and which state has the least? What is the standard deviation for payment ratio among the states?

Answer 1

DRG CODE - 219

Total Discharges	16990
Average Covered Discharges	191,696.43
Average Total Payments	40,362.57
Average Medicare Payments	35,338.94

The average amount of reimbursement of DRG Code 219 is 35,338 for a total of 16990 patient.

.DRG CODE -682

*It stands for Renal Failure W MCC:

The percentage of episodes is 5,6%

Average number of the chronic condition is 5.8

Average Medicare Episode Payment is $ 15,160

The new york city has the least payment ratio while comparing with California, Alaska, and Nevada