Over the past few years I have written a number of articles on tracking client trust balances, but always focused on QuickBooks desktop, and specifically the windows versions. As more firms move to QuickBooks Online, I have been asked how to replicate the report in QBO. Well, there's good news and there's bad news.
The good news is you can replicate the report, or about 99% of it. The bad news is the 1% you can not duplicate. It's not terrible, the report you generate in QuickBooks Online doesn't have a grand total. You will need have to export the QBO version to excel to get a total, or pull out your calculator or abacus.
Assuming you are recording all your trust transactions correctly, here are the steps to get a report of trust balances:
Step 1 - Go to REPORTS > ACCOUNTANT REPORTS.
Step 2 - Choose the Transaction Detail by Account report, and then CUSTOMIZE.
QBO Trust Account Step 2
Step 3 - Under General set the Transaction date to All Dates
Step 4 - Under Rows/Columns set Group by to Customer
QBO Trust Report Steps 3 and 4
Step 5 - Under Filter set the Distribution Account to be the Trust Liability Account.
Step 6 - Under Status set Cleared to Uncleared
QBO Trust Report Steps 5 and 6
Step 7 - Once you have the report the way you want it, customize the header to the name you want and then Save your customizations.
You may have two questions concerning my instructions. The first is what do you mean by "you are recording all your trust transactions correctly"? This means that you (or your client) are following these two rules for all trust transactions:
1. Every transaction that debits the Trust Bank Account credits the Trust Liability Account and any transaction that credits the Trust Bank Account debits the Trust Liability Account.
2. Every trust transaction includes a customer or a customer:job
If you follow these rules the trust bank account and the trust liability account will always have the same balance and the report will work correctly.
The other question is "why do you set the status to Uncleared?" Over time the report can get long as customers trust accounts go to zero but stay on the list. This filter combined with reconciling the trust liability account help to limit the report.
The "trick" is when the clients trust balance goes to zero you reconcile the trust liability account. You will always be reconciling to $0. You mark as cleared all transactions for any client who's trust balance is now $0. Now when you run the report, those clients will not show and you will only have active trust clients on the report.
Editor's note: Thanks to Liz Scott for helping prepare the graphics for this article.