Managing cash collections is important in any business, especially in post-acute where many of your payers are government entities with strict rules & regulations.

When you can’t collect cash, your accounts receivable (AR) balance grows over time and you generate bad debt. Every month you should be adjusting a reserve for your bad debt. When debt gets too old, it becomes 100% reserved, which means it has been fully expensed on your P&L, but it still adds to your AR balance.

Where this can be a problem is when it comes to your DSO. DSO or Days Sales Outstanding is your AR divided by your average daily revenue. This metric helps you understand how long it takes for you to collect the money owed to you.

What should be happening, is that you should be aging & tracking AR balances and try to (1) collect cash or (2) write it off or (3) do nothing which hopefully you are not currently doing.

(1) Collect Cash on old AR balances

Bad Debt is one of the only areas of your P&L where you can get a credit! You do this by collecting cash for old AR balances that have already been 100% reserved. You should be monitoring lists of patients & payers where you are owed money. Think of it as a “to do” collections list for your BOM.

When you are having a tough month financially, getting a credit in your bad debt expense that month can go a long way.

(2) Write Off AR balances

This is a solid option as well, if the debt cannot be collected for whatever reason, don’t let it sit in your AR balance. This increases your DSO unnecessarily. Many operators are incentivized on this metric. For some unmanaged AR balances, some operators can dramatically improve their DSO instantly just by doing write-offs.

PCC Query to help your business

Below is an aging query that you can use to help you calculate Bad Debt, Aging Balances & DSO. The benefit of this view is that you can drill into an operations, payers and patients. The query has some notes so that you can customize it. You can run this against your pcc relayed database and analyze the results in an excel pivot table.

--put in the last day of the most recent closed month
DECLARE @date DATE = '20170930', @facilityID INT = 1


SELECT F.[FacilityName]
    ,LTRIM(RTRIM(FP.[LastName])) + ', ' + LTRIM(RTRIM(FP.[FirstName])) As [PatientFullName]
    ,P.[PayerType]
    ,B.[payer_consolidation_account]  As [GL Code] --AR GL account
    --you can easily customize your aging buckets below by altering the case statement
    ,CASE 
    --this captures future payments <= 30
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 30 THEN '0 to 30'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 60 THEN '31 to 60'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 90 THEN '61 to 90'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 120 THEN '91 to 120'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 150 THEN '121 to 150'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 180 THEN '151 to 180'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 210 THEN '181 to 210'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 240 THEN '211 to 240'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 270 THEN '241 to 270'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 300 THEN '271 to 300'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 330 THEN '301 to 330'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 360 THEN '331 to 360'
    --all other aging bucket
    ELSE '361+' END As [Age]
    ,SUM([Amount]) As [Amount]
FROM [dbo].[view_ods_ar_aging_detail] A WITH (NOLOCK) LEFT OUTER JOIN
[dbo].[ar_payer_consolidation_accounts_view] B WITH (NOLOCK) ON A.[TransactionID] = B.[transaction_id] LEFT OUTER JOIN
[dbo].[view_ods_payer] P WITH (NOLOCK) ON P.[PayerID] = A.[PayerID] LEFT OUTER JOIN
[dbo].[view_ods_facility_patient] FP WITH (NOLOCK)  ON FP.[PatientID] = A.[PatientID] AND FP.[FacilityID] = A.[FacilityID] LEFT OUTER JOIN
[dbo].[view_ods_facility] F WITH (NOLOCK)  ON F.[FacilityID] = A.[FacilityID]
WHERE A.[FacilityID] = @facilityID AND A.[TransactionDate] <= @date
--requires a <= on the date because you need all history to create a full againg
--requires this because we are summing the transaction amounts
GROUP BY F.[FacilityName]
    ,LTRIM(RTRIM(FP.[LastName])) + ', ' + LTRIM(RTRIM(FP.[FirstName]))
    ,P.[PayerType]
    ,B.[payer_consolidation_account]  
    ,CASE 
    --this captures future payments <= 30
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 30 THEN '0 to 30'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 60 THEN '31 to 60'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 90 THEN '61 to 90'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 120 THEN '91 to 120'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 150 THEN '121 to 150'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 180 THEN '151 to 180'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 210 THEN '181 to 210'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 240 THEN '211 to 240'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 270 THEN '241 to 270'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 300 THEN '271 to 300'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 330 THEN '301 to 330'
    WHEN DATEDIFF(day, A.[EffectiveDate], @date) <= 360 THEN '331 to 360'
    --all other aging bucket
    ELSE '361+' END
    -- > 0 is debit balanced Accounts
    -- < 0 are credit balance accounts
    --exclude 0 balance accounts, these balances are paid in full or written off already
    HAVING SUM(A.[Amount]) <> 0

Below is an example when you paste the results into Excel & Pivot by Age & Payer Type.