Streamline your credit card gateway reconciliations with Advanced Bank Rec from Nolan. http://bit.ly/2fGbvxh

Problem:

It’s difficult to confirm that what was submitted to the gateway was actually received in the bank account. It's challenging to reconcile the bank account due to credit card settlement timing issues, data integration issues, and returns.

If the volumes are small then reconciling these receipts is manageable but when volumes increase, a separate reconcile needs to be created to manage the settlements. This separate reconcile functions exactly like a bank reconcile except that it is for credit cards cash receipts or PayPal cash receipts.

Historically:

Traditionally when posting credit card receipts and PayPal cash, GL accountants would post the accounting transaction directly to the cash account within the chart of accounts.

The Challenge:

The challenge for reconciling the credit cards within the cash GL account is that the credit cards are usually showing in detail in the cash GL account. The statements received from the bank are lumped by AMEX and then by Visa, Discovery and MasterCard by settlement batch. In other words, there is detail in the general ledger and lump sums on the bank statement.

Issues

  1. It can be confusing to deal with the lump settlements from the bank that also contain credits and corrections
  2. American Express sometimes settles net of the processing fees
  3. It is hard to know for sure that what was submitted versus what actually settled
  4. Can’t easily match using the transaction ID at the transaction level.
  5. There can also be some timing differences in the settlements which make the settlement batches inconsistent.

The Solution:

The best way to handle this situation is to slightly modify your receivables accounting practices. Instead of posting credit card and PayPal receipts to the cash or bank general ledger, post the debit to the undeposited funds gateway GL account and the credit to sales (as normal). Enter the credit card transaction ID when posting the payments as this will also show in the downloaded csv merchant statement.

Example: A sale for a $50 baseball glove using MasterCard

DEBIT: Undeposited Funds Gateway $50

CREDIT: Sales $50

Transaction ID 234579. Enter this in the check number field, reference, or memo field on the GL transaction as this will be used as your matching criteria.

The credit card statement is then downloaded as a csv and reconciled against the undeposited funds- gateway GL account. This reconcile is treated the same as a bank reconcile except that it is for credit cards. Use the transaction ID as your matching criteria. You will do your normal bank reconciliation later.

What you need:

  1. The ability to download you credit card and PayPal merchant accounts into a csv file format
  2. Be able to enter a credit card or PayPal Transaction ID number onto the transaction in the general ledger when you post your payment.
  3. Create two undeposited funds gateway accounts and one PayPal receivable account. The new GL accounts are as follows: 
  4. MasterCard, Visa, and Discovery receivable
  5. American Express receivable
  6. PayPal receivable
  7. Change your credit card set-up in your accounting system so that they post to the new undeposited funds accounts.
  8. A reconcile product, feature, or bank reconciliation within your accounting application that supports this set-up. Nolan Business Solutions has Advanced Bank Reconciliation for both NetSuite and Microsoft Dynamics GP.

Let’s do an example with the MasterCard, Visa, Discovery (MVD) undeposited funds account

On November 28, 2014, Black Friday, you process 3 MasterCard sales transactions. The transactions are for $400, $75, and $50. These will debit the MVD undeposited funds GL account for $400, $75, and $50 and credit your sales account. On your bank account statement for November 28 you have a lump sum posted into your bank account for a total of $525.

The first thing is to reconcile the MVD undeposited funds account. You will then know if you received all the money from MasterCard . You will also be able to identify any discrepancies between what you thought MasterCard should pay you and what was actually paid. This allows you to have confidence that what was sent to your gateway was received from MasterCard. Also if there are discrepancies it can be dealt with at the MVD undeposited funds GL account level before dealing with it at the bank account level.

The advantage of a two-step reconciliation process for receipts from credit cards or PayPal are as follows:

  • Accuracy in cash
  • Knowing with confidence that what was expected from the credit cards or PayPal was settled correctly
  • Able to use the transaction ID for more accurate matching
  • Being able to perform a daily reconcile on credit cards and PayPal receivable
  • Faster bank reconciliation
  • Ability to quickly close the month end

Talk to us at Nolan Business Solutions about our Advanced Bank Reconciliation products for Microsoft Dynamics GP and NetSuite. We can show you how this solution would work for you.