Determine the amount of net operating income that would result for a hospital whose payer mix and expected volume (100 cases) is as follows:

25 Medicare cases pay $2,500 per case

30 Blue Cross cases pay $2,700 per case

20 commercial cases pay 100% of charges

10 Medicaid cases pay $1,500 per case

10 self-pay cases pay 100% of charges

5 charity cases pay nothing

Average cost per case is expected to be $2,700, and the average charge per case is $2,500.

Your balance sheet / Excel spreadsheet (showing all calculations) should include:

1. Total gross patient revenue
2. Deductions from gross patient revenue
3. Total deductions
4. Net patient revenue
5. Total expenses
6. Excess of revenues over expenses

Please show work in excel!