Accounts Receivable and Net Realizable Value

A hospital provided goods/services for the month totaling $25,000,000 to various third-party payors. Build the table below (including column headers) in Microsoft Excel and complete the yellow highlighted cells to calculate the net A/R by payor for the month. The calculation for Medicaid and Other Government are provided as an example of the proper format and calculation. Use the formulas within Excel to compute the answers. Format numbers using $ (zero decimals) and % (zero decimals).

Payor Payor Mix 35% 15% 40% 8% 2% 100% Medicare Medicaid Commercial Other Govt Charity Total Contractual Gross Charges Adjustment 30% 20% $ 3,750,000 $ (3,000,000) $ 60% 30% $ 2,000,000 $ (1,400,000) $ 0% $ 25,000,000 Net A/R to Gross (%) Collection Rate Net A/R 750,000 600,000