Automating the Reconciliation of your credit card Merchant Accounts, Stripe and PayPal type accounts in NetSuite

Accounting departments using NetSuite are looking for better ways to reconcile their credit card merchant accounts, and PayPal type accounts. Current manual practices can slow down the month end close and are very labor intensive. It’s difficult to confirm that what was submitted to the gateway was actually received in the bank account.

Brick-and-mortar stores now have web stores and almost every business now accepts payment by credit card. If the volumes are small then reconciling these receipts is manageable but when volumes increase, a separate reconcile needs to be created within NetSuite to manage the settlements. This separate reconcile functions exactly like a bank reconcile except that it is for credit cards cash receipts or PayPal type cash receipts.

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

The Challenge

The challenge for reconciling the credit cards within the cash or bank GL account is that the credit cards are usually showing in detail in the bank 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.

This causes several 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 definitively 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). Create this new account in NetSuite using the type “Bank”. 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 memo field on the transaction in NetSuite 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 your 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 an Undeposited Funds Gateway account and more if you need a separate reconcile for Amex or PayPal.
4. The Nolan Advanced Bank Reconciliation to support this set-up.

Let’s do an example with MasterCard.

On November 28, 2014, Black Friday, you process 3 MasterCard sales transactions. The transactions are for $400, $75, and $50. These will debit the Undeposited Funds Gateway 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 Undeposited Funds Gateway 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. If there are discrepancies, they can be dealt with at the 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, Stripe 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.

View our Technical How To Video on Reconciling Credit Card Gateways in NetSuite. Talk to us at Nolan Business Solutions about our Advanced Bank Reconciliation products for NetSuite. We can show you how this solution would work for you.

By Nolan Business Solutions, an International Microsoft Dynamics GP ISV and NetSuite Provider.