Reconciling over and underpayments on invoices

sean40 · ‎November 15, 2020

Hi.

I have an interesting problem that I've been working around in not-the-best way. (hard to untangle without keeping notes)

I have a set of customers that are all part of a parent company that are invoiced individually.
Let's call them Store 1, Store 2, Store 3.

I need to keep them separate so that I can trace individual deliveries to the stores, etc.

However, I receive ONE weekly payment from the overarching parent organization. Let's call that parent "The LCBO" for arguments sake.

Occasionally there are keying errors when products are received.. either over or under. I generally tend to just record the over- and under-payments and submit a request to correct them, or I modify my invoices if I find that there was an actual over- or under- delivery, based on a signed proof-of-delivery document that I receive back from my logistics company. It is much easier to deal with underpayments, since they still remain open and payable when matching bank transactions later.

The specific problem I have though, is that in any given week, I may be receiving payments for Store 1($100) and Store 2 ($100), but then there is a correction for Store 3 (-$100) because they previously erroneously reported an over-receipt of product by $100 (and I was paid for it). I of course recorded this as an overpayment on the invoice when matching up the banking entry, and QBO treats it as a credit to the account. However, when I see a deposit for 100+100-100 = $100 on the banking side, I cannot select the credit memo to match it against.

I could go ahead and enter a credit memo for Store 1, and things will match up with the banking entry, but there is the problem of transferring the credit from from Store 3 to Store 1. Is there a way to do this using, say, a journal entry to move the balance between accounts? Or is there a way to actually have QBO allow me to choose: payment of $100 for Store 1, payment of $100 for Store 2, and refund of $100 for Store 3 when matching up this $100 bank deposit? Seems only positive $ values show up when matching deposits.

Or is it maybe possible to match up $100 for Store 1, $100 for Store 2, and deal with the -$100 difference at the bottom of the screen with "add resolving transactions" that would consume the credit memo for Store 3? -- something like choosing the payee as Store 3, account as A/R, and amount as -$100?

Thanks for your help!

Rebecca R · ‎November 16, 2020

Hi there sean40,

It sounds like you're doing a fantastic job of accurately recording transactions for your business using QuickBooks Online. QBO's bank feed feature makes matching or adding your transactions a breeze, and can also save you time during your monthly reconciliation. However, there are some stipulations with how things will match up, and I want to go over this with you.

As you mentioned, only transactions with positive values will appear as an option to match when you've selected a deposited amount from the bank feed. Therefore, you'll be unable to match two payments and one refund or credit memo in the same amount to one deposit. Since the transaction in the bank feed is hypothetically $100, QuickBooks expects to match it to a transaction that you've entered in the amount of $100, or several smaller transactions that add up to $100.

I understand how having the ability to match both positive and negative values that equal the amount of the deposit would be a helpful feature, so I encourage you to let our product development team know. You can do this by navigating to the Gear icon within QuickBooks Online, then select Feedback. We're always looking for new ways to improve our products for users like you!

When it comes to entering a journal entry to move the balance between accounts, this may be a good solution. A journal entry with multiple line items to represent the amount of the deposit can be matched within the bank feeds. In terms of deciding which accounts to credit or debit, an accountant would be able to best advise you. If you're already working with an accountant, invite them to work on your books with you by navigating to the My Accountant tab and entering their email address. The Find a pro to help button on the same page is where you'll find our database of QuickBooks-certified accountants. If you don't have one already, this is where you'll find the perfect accountant for your business.

Lastly, I want to note that while matching your transactions or adding them directly from the bank feed is a useful feature, it's not necessary to use. As long as you've entered your transactions correctly into QuickBooks, you don't need to match them to the bank feed unless you want to. The only difference you'll notice is during the reconciliation process. When transactions have been added or matched from the bank feeds they'll automatically be checked off in your reconciliation. Transactions that weren't added or matched from the bank feed will have to be manually checked off once you've compared them to your bank statement. So feel free to select the transactions in your bank feed that are not so straightforward to match, and Exclude them. They'll end up in the Excluded tab of the Banking or Transactions page, and if you decide you want to add or match them once excluded, you simply have to locate them and hit Undo. It's that simple.

I know you'll have this sorted out in no time. All the best!

View solution in original post

Rebecca R · ‎November 16, 2020

Hi there sean40,

It sounds like you're doing a fantastic job of accurately recording transactions for your business using QuickBooks Online. QBO's bank feed feature makes matching or adding your transactions a breeze, and can also save you time during your monthly reconciliation. However, there are some stipulations with how things will match up, and I want to go over this with you.

As you mentioned, only transactions with positive values will appear as an option to match when you've selected a deposited amount from the bank feed. Therefore, you'll be unable to match two payments and one refund or credit memo in the same amount to one deposit. Since the transaction in the bank feed is hypothetically $100, QuickBooks expects to match it to a transaction that you've entered in the amount of $100, or several smaller transactions that add up to $100.

I understand how having the ability to match both positive and negative values that equal the amount of the deposit would be a helpful feature, so I encourage you to let our product development team know. You can do this by navigating to the Gear icon within QuickBooks Online, then select Feedback. We're always looking for new ways to improve our products for users like you!

When it comes to entering a journal entry to move the balance between accounts, this may be a good solution. A journal entry with multiple line items to represent the amount of the deposit can be matched within the bank feeds. In terms of deciding which accounts to credit or debit, an accountant would be able to best advise you. If you're already working with an accountant, invite them to work on your books with you by navigating to the My Accountant tab and entering their email address. The Find a pro to help button on the same page is where you'll find our database of QuickBooks-certified accountants. If you don't have one already, this is where you'll find the perfect accountant for your business.

Lastly, I want to note that while matching your transactions or adding them directly from the bank feed is a useful feature, it's not necessary to use. As long as you've entered your transactions correctly into QuickBooks, you don't need to match them to the bank feed unless you want to. The only difference you'll notice is during the reconciliation process. When transactions have been added or matched from the bank feeds they'll automatically be checked off in your reconciliation. Transactions that weren't added or matched from the bank feed will have to be manually checked off once you've compared them to your bank statement. So feel free to select the transactions in your bank feed that are not so straightforward to match, and Exclude them. They'll end up in the Excluded tab of the Banking or Transactions page, and if you decide you want to add or match them once excluded, you simply have to locate them and hit Undo. It's that simple.

I know you'll have this sorted out in no time. All the best!

sean40 · ‎November 17, 2020

Thanks for the quick response.

I'm considering just adding a dedicated clearing account for this specific purpose to help me speed through all of my bank reconciling, and then return to revisit all of the credits and debits to that account later and farm them off by journal entry to the correct customer under A/R or A/P.

This will help me get through the transaction matching page (there can be dozens of invoices for a single deposit in my case) without having to task-switch to edit an invoice and then being forced to restart the matching because the amounts have changed.

Cheers,

Sean

sean40 · ‎November 17, 2020

I did some further testing, and it seems if I use any account other than A/R to record the resolving lines on the bank matching page, then amounts don't properly tabulate on the customer's detail page. If I use A/R and the customer's name on the resolving line, then an expense gets created in the customer's name. I can then visit the customer's page, pull up that previous payment amount and check-off that expense to resolve the initial overpayment.

As a concrete example:

Store A invoice #101 is $100

Store B invoice #102 is $100

Store C invoice #103 is $200

First week deposit is for $300, which I record as $200 (overpayment) for #101, and $100 for #102. This creates a $100 credit for Store A.

Second week deposit is for $100, which I record as $200 for #103, and add a resolving line for -$100 against A/R in Store A's name. This creates an expense in Store A's name.

Then when I visit Store A's customer page, I can see $0 open transactions, but $100 overdue. If I then click on the auto-generated payment from the "first week" deposit, I can revise the matches that already include invoice #101 and now also check off the box next to the auto-generated expense, and the totals match up.

The customer's page now shows $0 open and $0 overdue correctly.

LauraAB · ‎November 18, 2020

Hey there Sean,

Great job with figuring this out! I can tell you put a lot of time and effort into it, and it's great that you've taken time on top of that to share with the QuickBooks Community the solution you came up with. I'm sure it will benefit someone else in the same or a similar situation.

Feel free to stop by the Community again next time you have a question. Have a great rest of your day!

Topics

Training

Community

Resources

Reconciling over and underpayments on invoices