Receivables Report

[kapricorn]

We have added a receivables report for a client and have been getting more requests for it. We are looking for comments / suggestions. We would like to add the report to version 6.6 if all agree.

http://www.kapricorn.com/ftp/RcvReport.jpg
http://www.kapricorn.com/ftp/RcvScreen.jpg

[drtech]

looks like a good idea, but I can't really see the report...the image is too low res

[kapricorn]

I posted new links, try the images again.

http://www.kapricorn.com/ftp/RcvReport.jpg
http://www.kapricorn.com/ftp/RcvScreen.jpg

[jordansparks]

Yes, thank you.

[kapricorn]

We saw somewhere that a receivable breakdown by provider was requested so we added it to the report.
Here is a sample screen shot and report.

Please let us know if anything else is needed. We will commit early next week if no objections.

http://www.kapricorn.com/ftp/RCVReport.pdf
http://www.kapricorn.com/ftp/rcvScreen2.jpg

[drtech]

looks good, I like it, except for one thing...are you figuring in your expected ins payments so you can see what your A/R is from just patients vs ins companies and then total?--Like the current Aging Report.

unrelated questions to the addition, but is that your real data? $900,000 AR? You need to get your A/R under control!
We do about $9,500/day (1,400,000/year) and today as I look our A/R is $125,000 including expected ins payments(only $47,000 from pts). "Non-Confrontational" case presentation and "Non-Confrontational" financial arrangements are the key. It makes a big difference for us.

[kapricorn]

The receivables report is basically giving a total true receivables as of the current day it is run for. It does not take into account any future dates. It bases the amount owed on the patient balance, whether it is expected by the patient or the insurance. The account aging report, like you said, would then break down the receivables to give you expected insurance vs expected patient monies.
This report allows you to go back in time to a specific date and see what your total receivables were at that time. You cannot do this with the aging report, nor can you see the total receivables effect for the month, using the aging report. The patient payments and insurance payments shown on the receivables report are actual monies collected and posted.

As far as the figures go, this is just a sample database and not true numbers. However, if you collect all the money in the practice and then run the receivable report for say a hygienist, you will get very similar results. Running this report broken down by provider will only make sense if you are separating production and collection by provider.

[atd]

I would find this report very useful for our practice also because for our monthly AR report we need to be able to have a cutoff date. Each time we run the current AR report (even if the aging date has not been updated), the amounts are different, which makes our CFO unhappy. However, we do not do cash accounting, therefore we count our writeoffs based on procedure date, not payment date. Could this version of the AR report have the option to show writeoffs by procedure date, like the P&I report does? I'm currently running a custom query for my monthly AR - breaking down the ins est column to include the writeoff (by procedure date) amount. I've added that as a feature request for the current Aging report, but it only has 6 votes right now.

[jordansparks]

Those pesky writeoffs will muck up the A/R report.

[kapricorn]

ATD, are you posting the writes offs at the time of service? I'm unclear how this would give you a true A/R. If you are waiting till the payment comes in then posting the writeoff your A/R would not be static. Every time you run the report you would get different results as long as payment continue to be posted with write offs from previous time frame proceedures.
Help clear this up and we will try to incorporate your needs. You are welcome to call us direct, just ask for me.

[atd]

As a non-profit we do accrual accounting, not cash accounting. So yes, we are posting the writeoff by procedure date, not payment date. It actually gives us a very accurate AR, because we know how much we expect to receive (net), not the gross fees.

[kapricorn]

I understand that if it is not an estimate, and if the writeoff is posted at the time of service it would definately be a more accurate look athe the A/R. I just question what the CFO would say about the report if it changes as new payments get posted and the write off are updated or added.

I will look further to see if we can add the option.

[kapricorn]

Ok, it is done. Added to version 6.6. If you need it sooner let me know.

[fdscadmin]

Oh boy, I love a good A/R report discussion. I have been writing reports for OD for quite a while, but I have been using iReports and querying the DB direct. A good A/R report should be broken down by payer. This is so you can see who your problem payers are. Additionally if you are on an accrual basis you have to be very concerned about changing reports. Here are some tips for accrual...

1. Each month should be "closed" and no new procedures/claims should be made (Then you can run the report)
----This means all claims should be created and "put on hold" if you have secondary/tertiary...
2. Currently the fee for each procedure should count as production for both primary and secondary...I know this will slightly inflate your A/R, but it is the only way to have it balance.
3. The adjustment and payment dates should be queried on the date entered and not the procdate. This is the only way to capture all your adjustments and payments during your monthly reporting "window".
4. The basic formula is (Beginning A/R + New Production + Adjustments(+/-) + Payments(-) = new A/R).
5. If you are using Quickbooks (Yes, I hate it too) you can post these numbers and they should balance with your OD report at the end of the year. If they don't you have a problem.

If you have any questions call me. The concept is simple, but the implementation isn't.

david
559-593-5291

[jordansparks]

I'm going to start a new thread on A/R and accrual issues. It will be in the advanced topics forum.

[atd]

I was hoping someone could explain the logic/query behind the Receivables Breakdown Report you created in Open Dental. We have multiple clinics on one database – 2 new clinics and 1 that was converted. I found the report worked great when we had just 1 clinic using Open Dental (no conversion). Once we converted a clinic and merged the databases I could not get the aging balance on the report to match the standard receivables report – for the second clinic. I think this is because we had so many “hidden” providers and also because of how the conversion balances were brought over. I tried “unhiding” all the providers for that clinic and rerunning the report, but I could still not get it to match. It also wasn’t feasible to be unhiding 10 years worth of hidden providers each month when I needed to run the report, even if it had worked. To deal with the hidden providers I created my own Aging Report (similar to the standard aging receivables report) as a query that uses the clinic the guarantor was assigned to, rather than the primary provider. I also added a column for writeoffs by procedure date (we do accrual account). We’ve since opened another clinic – the new problem is that patients are often seen at 2 clinics (they are in proximity). Since the family aging balance goes to whichever clinic the guarantor is assigned to, it doesn’t take into account the fact that part (or all) of those balances may be for procedures done at a different clinic. I thought the breakdown report would work for this clinic since we started it on Open Dental and didn’t do a conversion. But when I run your Receivables Breakdown report I get a very different number. I’m assuming that you aren’t using the family balances that are stored in the guarantor, but are instead looking at all past procedures, payments, writeoffs, adjustments etc. to calculate the running balance each day. Is that correct?

I’m hoping I can get help with these problems:
1) How to accurately divide aging receivables between two clinics when patients are seen at both clinics.
2) How to get my Aging Receivables query and the Receivables Breakdown report to match.
3) How to get the Receivables Breakdown report to work for our converted data with hidden providers.

The only thing that comes to mind for me to fix the hidden provider issue is to be able to assign a provider to a clinic and then have the option to run reports by clinic. For the other issues I’m stumped.

Any thoughts?

[jordansparks]

I don't have direct answers for your questions because I was not involved in any way with the A/R report that you refer to. However, I have some general comments.
* On http://www.opendental.com/manual/accrual.html , I just added "Group by provider instead of by patient".
* There is now a clinic field for all financial tables, so that is how it could be broken down by clinic in the future.
* To include hidden providers, most of our financial reports now include an "all providers" option which does no provider filtering at all. When we write an A/R report, we would include that detail.

I see us incrementally moving in that direction, and I see the Accrual Accounting page as being our roadmap.