QuickBooks – Reconciling Credit Card Deposits

One of the most painful and time consuming tasks in QuickBooks is the process of reconciling the credit card deposits made by your merchant service provider to your bank account. They seldom match because the merchant service provider deducts fees and lumps your clients’ payments in non-chronological order. There is a shortcut to this reconciliation process. It has some drawbacks, but it could save you a lot of time.

Let’s first look at the root cause of the problem. Where do the discrepancies come from?

  • Depending on how you are setup with your merchant service provider, they may deduct fees from each deposit that they make or they may charge you the fees only once a month. The latter option is of course much easier to reconcile and you can often call you merchant service provider and ask them to switch to the monthly deduction approach. If they don’t give you the option, don’t despair. There is an alternative. See below.
  • The Amex charges are often deposited separately from the other credit cards.
  • Let’s say that you have 3 clients (A, B and C) paying you on Monday and one client (D) on Tuesday. Typically, it would take two business days for the deposits to hit your bank account. So, in theory, you should see the deposit from clients A, B and C on Wednesday and client D on Thursday. Unfortunately, that would be too simple. Very often, for reasons only known to the merchant service provider, the Wednesday deposit might include only client A and C and Thursday might get the deposit from client B and D.
  • Because of the lag issue described above, the statements from the merchant service provider can be confusing. Payments received on the last 3 or 4 days of the month will likely show up on the statement of the following month. So, on each statement, you may have extra deposits at the beginning of the month that are payments from the previous month, and you might be missing deposits at the end of the month because they will show up on the next statement.

Net-net, it can be outright frustrating to reconcile all of this. If you have few transactions per month, you can put your Sherlock Holmes hat on and reverse engineer everything to the cent. Experienced bookkeepers can be pretty fast at it. However, if you have a lot of transactions, it can become extremely complex and time consuming. If you are paying a bookkeeper to do this for you, it can add up to your bookkeeping cost pretty rapidly.

There is an alternative solution to this and it can save you a significant amount of time/cost, but it is not as rigorous, and before you embrace the approach that I’ll describe below, you may want to ask your CPA if he/she is OK with it.

Here’s how it works.

  • Create a new account in QuickBooks that you will use as a virtual account to help the reconciliation. Call it for instance “Credit Card Deposits in Transit” and classify it as “other current asset”. In accounting terms, this is a “suspense” account; a utility account that should be zeroed out by the end of the month (if you close monthly) or end of the year. In other words, when you look at a formal balance sheet, this account should not show up, but within the month or year, its balance can swing up and down. You should clearly label it so that your CPA understands what this is for.
  • Let’s say that you received $1,000 in payments from Client A, B and C on Monday. In QuickBooks, you would typically use the function “Make Deposit” to move the funds from the “Undeposited Funds” account to your checking account. In this approach, go ahead and record that deposit, but instead of sending it to your checking account, send it to the suspense account. The net effect is that it adds funds to this suspense account. $1,000 in our example.
  • In parallel, every time that you see on your bank statement (not the merchant statement) a deposit coming from your merchant service provider into your checking account, record it as transfer from the suspense account to your checking account. It has the next effect of deducting funds from the suspense account. Let’s say in this example that the deposit was for $700 only because it included only 2 of the 3 clients’ payments and the fees were deducted.
  • Now, your Undeposited Funds account is empty (which is correct), your checking account has $700 more in cash in it (which is correct), and your suspense account has $300 in it. Weird, I know, because this suspense account should be at zero, but the rationale is that over time this account will hover close to zero once the deposits catch up. This is your buffer account. In our example, you are likely to see a deposit in your checking account for something like $290 the next day (3rd client’s payment minus fees). You make the $290 transfer from the suspense account to the checking account. The suspense account now has a balance of $10. Those are the fees charged by the merchant service provider that we haven’t yet recorded. Leave them there for now.
  • At the end of the month, when the statement from the merchant service provider arrives, you will figure the total amount of fees that had been deducted from the deposits made during that month and you make a journal entry to transfer that amount from the suspense account to the account where you record bank charges (expense account). You will still be left with a balance in the suspense account. Those are the payments that were made by your clients at the end of the month and that haven’t yet been deposited to your checking account, hence the suggested name of the account: “Credit Card Deposits in Transit”.
  • At this point, you have several options to true things up, but the easiest is to do nothing and let your CPA make an adjustment at the end of the year when he/she prepares your taxes. Again, make sure to label this suspense account very clearly, so that your CPA can figure out what to do with it. Most likely, he/she will do a journal entry on 12/31 to zero out this account and reverse it on 1/1 of the following year to let you continue using it.

The benefit of this “suspense” account technique is that it is much faster and it still gives you some reasonable control. If you see this account spike up, that means that you are not recording things correctly or the merchant service provider is not making the deposits. If you see it go negative, that means that you didn’t record the payments from your clients correctly. The downside is that it is not as precise as the brute force approach. You are trusting that the merchant service provider and the banks are sending the correct information to you and all you are doing is making sure to record it correctly in QuickBooks. Double check with your CPA to see if he/she approves of this approach. If time and money are not an issue, always go for the brute force approach. It’s the most accurate technique, but it can be painful.

Comments

  • 7-06-11

    WOW just what I was looking for. Came here by searching
    for merchant services accept credit cards

  • Habibur says:
    7-06-11

    This is one of great posts that clearly elaborates the pros and cons of
    online payment service. Frankly enough, I have been very skeptic in making
    online payments as I was never sure of the features and benefits.
    Furthermore, I never knew of Bill Me Later or Google Wallet.So, thank you for
    the insight.

  • Gloria says:
    7-06-11

    You have just saved me from pulling any more of my hair out trying to make my cc deposits match what the bank says! Thanks for the post. Now, if I can just get AmEx to reconcile, I’ll be good to go!

Post a comment

*