by DMS Staff Contributor Lisa Scrofano
Lisa Scrofano, Accounting Manager
My helpful hint comes from a client experience where they were using QuickBooks efficiencies, specifically bank feeds and merchant payments, to pull in their accounting data only to find out there were unmatched transactions left in the register. As a reminder, people and computers are not perfect, which is a reason DMS stresses using best practices to ensure that data comes in correctly. One of those best practices, reconciling the balance sheet, identified this client’s issue. Payment links were paying invoices in full and bank feeds were recording the correct deposit amount, but payments still sat in the undeposited funds account. These should have been relieved when the bank feed deposits were accepted. Why did this happen and how can you make sure this is not happening to you?
The following illustrates how to catch the error and how to fix the error without having to undo previously reconciled accounts.
The client had been using outside point-of-sale (POS) systems such as Shopify and PayPal to accept payments on outstanding invoices using payment links. The customer paid, the client used “record merchant deposits” and the invoices were marked paid. The client then used the bank feeds to pull in the rest of their accounting data (including deposits) and on the surface, everything matched and reconciled. Bank amounts were correct and invoices that were paid were marked as such.
We found the error when we were performing our month close services, which includes fully reconciling the balance sheet, and we noticed that there were a few payments sitting in undeposited funds and they were almost a month old. Upon questioning, the client informed us that those were paid invoices and those payments had already been deposited and accepted into their bank account, which was also reconciled. The problem lies in the fact that almost every point-of-sale system charges a service fee for accepting payments. Some of the bigger POS systems like Shopify and PayPal take those merchant fees off the top before sending the deposit to your bank. This creates an extra step while accepting merchant deposits. You have to remember to record the fee when you deposit your payment to the bank. The bank did not recognize the payments without the fee (they did not match) and therefore did not relieve the amounts sitting in undeposited funds.
To fix this, we opened up the payment portal and located the payment fees for each deposit that needed to be accepted. Using the amount sitting in undeposited funds minus the payment fee found in the portal we knew what the amount deposited to the bank should total. Taking that amount and using the “edit”-“find” feature in QB to quickly locate the deposit that was pulled in with the bank feed, we were able to start correcting the issue. We hit the payment button in the deposit window and clicked on the applicable payment, we added a line to record the merchant fees as a negative amount. These two lines now totaled the original amount pulled in through the bank feed. Once we confirmed that the math was correct we deleted the line from the bank feed and hit “save and close”. This cleared out undeposited funds, kept the payment link intact, and kept the client’s reconciled bank account intact as well.
Best practices would also call for reviewing set up to make sure that the merchant fees are being recorded as a negative offset to each incoming deposit; to avoid the issue altogether. Doing so will create a match with the bank feeds. This review, along with continued monthly review and reconciliation of balance sheet accounts, are critical checkpoints to ensure that your data is imported properly and accurately.