1 of 3
Trust Account Report
2 of 3
Trust Balances By Client
3 of 3
Trust Activity Report
In my last article I wrote about how to record Client Funds Transactions in QuickBooks. This time, we will look at various reports that can be easily produced in QuickBooks to answer the question – Who’s money are we holding?
Insurance companies, Real Estate companies, Decorators, Law Firms and many other types of businesses get money from clients in advance of doing work and need to carefully track these funds. For some firms, like lawyers, improper tracking can lead to severe penalties even if the money is all there. But for any firm, problems in this area can hurt your client reputation and waste time as you search for the issue. Whether you refer to these funds as deposits, retainer, IOLTA or trust funds the tracking process and recommended reports are the same. For convenience I will use the term Client Funds Account in this article.
Whether you are using another program for billing and linking to QuickBooks or doing your billing in QuickBooks, you may want to track your Client Funds accounts in QuickBooks. Tracking balances in QuickBooks will allow you to reconcile with your other program and prepare reports if your licensing association or the state comes to audit. Since you will likely be doing your bank statement reconciliation in QuickBooks, it is especially important to have reports that insure you can account for every dollar by client.
When tracking Client Funds in QuickBooks there are several reports you can setup and use to help make sure everything is in balance and to identify whose money you are holding. Before you start I suggest creating a Custom Report Group to hold the reports you will memorize. This is done by going to Reports >Memorized Reports >Memorized Reports List. Then click on the drop down next to Memorized Report and choose New Group. Give the Group a Name, like Trust Reports, and click OK.
The first report I create is simply to match the Client Trust Bank Account with the Client Trust Liability Account. (Slide 1) If you have only one of each, this report is fairly simple but, it is still useful as a quick way to check that you are in balance.
Start by going to Reports >Custom Reports >Summary. On the Display tab, change the Dates to All and Display rows by to Account List. On the Filters tab, choose to filter for Account and choose Multiple Accounts. Then select all Client Trust Bank and Liability Accounts. I recommend going to the Header/Footer tab and giving the report a meaningful name. If the report shows $0, you are ready to move forward. If not, then you need to determine which client’s account is out of balance. Memorize the report in the group you created. You can also add the report to the toolbar by clicking on View - Add “xxxx” to Toolbar, where xxxx is the report name.
The next report will show how much any given client has in trust. (Slide 2) I recommend running this from the Trust Liability Account as the balances will then show as Positive amount when a client has money in the Trust Account, and Negative amount if they are “overdrawn”. This makes the problem clients with negative balances easier to spot.
Start by going to Reports >Custom Reports >Summary. On the Display tab, change the Dates to All and Display rows by to Customer. Click on Advanced and set Display Rows by to Non-Zero. On the Filters tab, choose to filter for Account and choose the Trust Liability Account. If you have more than one Trust account you can choose Multiple Accounts. In this case I recommend setting Display Columns by, on the Display tab, to Account list so that each account is in a separate column. Go to the Header/Footer tab and giving the report a meaningful name, like Client Trust Balances. Remember to Memorize the report in the group you created and add the report to the toolbar.
If, when you ran your Client Funds Balance Sheet, you found that the results were not zero you should run the “Client Trust Balances” report filtered for the Client Trust Liability Account and then the Client Trust Bank Account. This will help you identify source of the discrepancies as the amounts in both should be equal for each client with the exception of the sign being reduced (Negative/Positive).
For the final report go to Reports >Custom Reports >Transaction Detail. On the Display tab, change the Dates to All and change Total by to Customer. On the Filters tab, choose to filter for Account and choose the Trust Liability Account. If you have more than one Trust account you can choose Multiple Accounts or you can setup a separate report for each account. You should also Filter for Cleared = No. Go to the Header/Footer tab and give the report a meaningful name, like Trust Activity (Slide 3). Once the report is open you can remove excess columns like Class, Clr and Split to make the report less cluttered. Remember to Memorize the report in the group you created and add the report to the toolbar.
You are probably asking two question (1) Why did we filter for Cleared = No and (2) How do we remove clients that have a zero balance? The answer to these questions is the same. By using the bank reconciliation process we can remove all clients that have a zero balance from our report. Go to Banking >Reconcile and choose the Client Trust Liability Account. Set the date to the current date and set the Ending Balance to ZERO. You will always reconcile to zero. When the reconcile window opens use the Columns to Display to include Payee on both the Charges and Payments side. Then sort each side by Payee. Mark as cleared all transactions for each client that has a zero balance. When you are done, choose Reconcile Now. When you return to your Trust Activity report, the clients with a zero dollar balance will be removed from the report since all of their transactions are cleared.
If you have clients that pay in advance and you put the funds into your operating account but offset them to a liability account like Deposits or Suspense, you can use the same Client Trust Balances and Trust Activity reports to track the money. Just choose the Deposits or Suspense account instead of the Client Trust Liability account in your reports.
We hope these articles help you and your clients to efficiently manage your Client Trust Activity.