Customer Balance Reports

DavidPB · ‎October 17, 2020

Hi, my name is David Smith and I run a bookkeeping and accounting business in Oregon. I have a question for some pro QBOers out there.

One of my clients (now to be referred to as the "business") receives deposits from his clients. These items are attached to a current liability account named Customer Deposits I created specifically for holding those deposits. Once the client terminates the contract with the "business", the deposits are to be paid back. This is done as an expense against the liability account with the transaction assigned to the client.

Now, this client has a difficult time remembering to pay back deposits and record it correctly. So, one of my trial balance tasks is to consistently audit this liability account to make sure that he doesn't owe a client a deposit, or that he needs to record that he kept the deposit for damage.

In QB Desktop this is a simple task, as I have multiple clients who use deposits that are Desktop users. You simply run a "Customer Balance Summary" report, go into customize and put a filter to only look at the account in question. Although this report defaults to A/R, you can change to any account and QB Desktop with show all debits and credits that are assigned a customer as a total. The benefit is that if a client is attached to a credit for $100, and then a debit for $100, those clear and neither that client, nor the transactions show in the report as the balance is zero. So, I can easily audit which customers have a credit to the liability that haven't been returned, or if there is an error where a return was processed for a client but they never had the initial credit created.

Now for QBO. I can not change the account (or I don't know how) for the customer balance summary. I am left to print a transaction detail by account report and manually cross off equal debits and credits per client. This is a nightmare. My client holds deposits for months in some cases and I have to look at 100's of transactions. I have a couple construction companies that I would like to move to QBO, but this is my one hang up as balancing their customer deposit account would be frustrating.

I would love to see in accountant reports the option to change the account filter on vendor and customer balance reports. The inability to select anything other than AR or AP is just to simple for more complex accounting.

I would love to hear someone tell me why I am an idiot and how QBO can do this. If not, I would love to hear some ideas on a good work around. And if not that, I would love to offer this as a suggestion for future updates. Thank you to anyone who took the time to read this and help.

AlexV · ‎October 17, 2020

Good day, David.

Welcome to QuickBooks Community! Let me show you how to track prepayments in QuickBooks Online.

The Customer Balance Summary report is merely based on A/R, not on any prepayments that set in a liability account. Let's pull up a summary of the liability account instead and customize it by the customer.

You can follow these steps:

Go to the Accounting tab and select Chart of Accounts.
Search the liability account, then select Run report under the Action column.
Select Customize and make sure that the Report period is correct.
From the Filters section, select a Customer.
Select Run report.

Click the Save customization button to save the customized report for future use. Then, you can access it from the Custom Reports tab. You can also check these links for more details:

Post again here if you need more help. Wishing you all the best!

DavidPB · ‎October 18, 2020

This is by far the best workaround I have seen. Here is the issue, if a business has a 100 clients in a period, which for some of my clients that period can be greater than 6 months, I have to go through all the zero balance customers to find the 2 or 3 that we owe. Is there a way to not show customers with a zero balance?

BettyJaneB · ‎October 18, 2020

Glad to hear that the solution shared above was able to help you, @David - Perfect Balance.

Yes, there's a way wherein you can pull up the report without having the customers with zero balance to show up. I'd love to walk you through on how to achieve this.

You can run the report of your livability account by following the steps shared by my peer AlexV above. From there, you'll need to select Unpaid as the status of the A/R transaction from the filter section.

I'll guide you on how to get there:

Click on Customize at the top of the report that you'll pulled up.
Press on Filter.
Put a check-mark on the A/R Paid box. If you want to select the A/P to be included just tick the A/P Paid box.
Choose Unpaid. This is applicable for A/P too.
Hit on Run Report.

To give you more tips about customizing your reports in QuickBoooks Online, please see this link: Learn how to get the most out of your financial reports.

Lastly, I'd like to share this article that contains information about the different reports that are available on your subscription: Learn which reports are available for your version of QuickBooks Online.

Know that my doors are always open to help if there's anything else that I can assist you with. Feel free to visit me here. Have a lovely day!

DavidPB · ‎October 18, 2020

So, I tried selecting A/P by itself, A/R by itself, and then both as unpaid. All 3 selections resulted in a report that had no data to report.

I believe these filters are attached to A/P or A/R. This account is not associated with either. And to be clear, I feel it is inappropriate and misleading to select A/R or A/P for this function. Even if that did work, it would be... wrong?

Again, I am confident that QBO needs to allow the adjustment of customer balance reports to reflect whichever account the user needs. Fixing those accounts to A/P or A/R is too simple of reporting and doesn't supply the need for more complex accounting with clients who hold balances in different accounts other than A/R or A/P.

DavidPB · ‎October 23, 2020

So, I tried selecting A/P by itself, A/R by itself, and then both as unpaid. All 3 selections resulted in a report that had no data to report.

I believe these filters are attached to A/P or A/R. This account is not associated with either. And to be clear, I feel it is inappropriate and misleading to select A/R or A/P for this function. Even if that did work, it would be... wrong?

Again, I am confident that QBO needs to allow the adjustment of customer balance reports to reflect whichever account the user needs. Fixing those accounts to A/P or A/R is too simple of reporting and doesn't supply the need for more complex accounting with clients who hold balances in different accounts other than A/R or A/P.

Topics

Training

Community

Resources

Customer Balance Reports