Best Practice: Account Clearing Method for NetSuite Electronic Payment Method Reconciliation

This article is relevant if you desire a usable and scalable practice for reconciling credit cards and other electronic payment charges in the NetSuite business system.

Background

In our NetSuite Systems Integration practice, we build automations to move information generated outside of NetSuite into the platform. A common example is using a third-party eCommerce system. In my article, Shopify to NetSuite Integration: Your Way!, we automatically create each order and payment transaction properly in NetSuite.

For a recent implementation, the client activated three different payment gateway types:

  1. Shopify Payments: leveraging Authorize.net behind the scenes, Shopify classifies their payments for general credit cards acceptance.
  2. PayPal: shoppers can elect to pay with PayPal in their eCommerce check out process.
  3. Amazon: more recent to the payments game, Amazon offers a similar payment solution relative to PayPal.

Other payment providers are offered, such as Apple Pay, Google Pay and a number of other more exotic types (crypto-currencies, in my mind, being the most interesting innovation: see my article, Step by Step: Accounting for Bitcoin as Foreign Currency).

Naturally, while much of the attention is about making sure you can transact, the next major concern for accountants is the method to reconcile those transactions.

How-to Reconcile NetSuite Electronic Transactions Using the Account Clearing Method

To manage high transaction volume records in NetSuite demands a study of the pattern for which data is flowing. In our client’s model, each payment gateway will gather collected transactions and periodically make a deposit of collected funds in the bank account of choice. Many payment providers will add fees to the transaction stream for which they will reduce the amount they ultimately deposit.

NetSuite offers an “Undeposited Funds” current asset general ledger account to be able to hold funds between the time you accept a customer payment and when you deposit it to the bank account. In my mind, this “Undeposited Funds” feature should only be used in a low-transaction volume environment. In looking at the nature of the account and the tools NetSuite provides, it appears well suited for an operation where physical checks arrive in the mail, you accept payments from the customer (generally called cash-receipts operations), and then you batch the checks to make a single deposit at the bank account.

We will avoid this method and instead use a practice that scales better.

Use a Bank Clearing Account for Each Payment Method

The key to the success of this better practice is to route each payment method to a specific phantom bank account for clearing. The idea is that we will use the clearing bank account to act as a holding bin to “clear” as the money is moving to the ultimate real bank account. If done as prescribed, your entire bank reconciliation practice streamlines. The key observable pattern is as follows:

  1. Bank Cash Receipts (debits): money flows to the bank either via cash payment or customer deposit records as they will be properly referenced against the customer for which they were derived.
  2. Bank Transfers (credits): as the payment provider deposits funds into your operating bank account, you initiate a bank transfer to move the funds from clearing to the actual bank account.
  3. Fees: since we will be leveraging NetSuite’s account reconciliation tool, we can let these fees come in as journal entries (the tool is limited to initiating the creation of journal entries, but my preference is to have these fees come in as check transactions against the payment provider vendor so that you have better visibility and management).

Setup a Payment Method with GL Bank Account Routing

In our client’s example, we created three different payment methods: Shopify Payments, Shopify PayPal, and Shopify Amazon each with a separate route to a user-defined phantom bank clearing account. As we build out automated electronic integrations, we create a cross-reference between the payment method on the eCommerce site and the defined payment method in NetSuite. This allows the accountants control over the GL routing of transactions for this practice.

Bank Transfers Records Drive Actual Bank Deposits

The key now is to understand how each payment provider makes a bank deposit to your operating account. This varies by each payment provider. Typically, the deposit happens daily, every few days, weekly or even monthly. The idea is to create a Bank Transfer transaction that fits exactly the date and the amount of the deposit. If you do this, then when you reconcile your traditional bank account, your bank will report a deposit that matches what you have on your general ledger — this then simplifies the traditional back reconciliation.

Use NetSuite’s Account Reconciliation Tool Against Credit Card Transactions

NetSuite offers a second generation account reconciliation tool. The tool was designed primarily for reconciling bank and credit card statement using the downloads offered in that industry. In our case, since we are using a bank clearing account, we want to use the tool for the payment gateway reconciliation. The primary challenge is that the payment gateway providers generally do not supply a file that is in a format easily consumed by NetSuite. We solve this. We give our clients free-of-charge a tool that converts the files supplied by each payment gateway into a format that can be imported by NetSuite’s account reconciliation tool.

As is typical with account reconciliations, the starting and ending balances are what is needed to drive the solution. Each payment gateway provider has its own nuance for supplying the statement balance at a particular date. You will need the dates and amounts so that you have an anchor point to compare transactions. Often, the way this information is provided will shape your account reconciliation frequency and practice.

Also, for those that use the Cybersource gateway, we created a custom reconciliation tool as an alternative to NetSuite’s. This tool can be easily adapted to other high volume situations where you would like hands-free downloading of payment gateway data and refined transaction matching and exception rules.

Clearing Account Reasonableness

The key control for the clearing account method is to think of it as ideally as being zero balance. If the practice is running as intended, the account should always be zero. Yet, because days may pass between the time you accepted the payment and it is ultimately deposited, then the balance should represent “funds-in-transit”. With regular account reconciliation of the clearing account, you can trust that you are under control. The frequency you reconcile the account is up to you but certainly no less frequent than your accounting period close.

Get Expert NetSuite Accounting and Integration Leadership

We have led a number of clients to learn how to adopt this payment gateway reconciliation practice. The great thing about the NetSuite platform is that we are never really stuck. We just need to be inventive yet hold to sound accounting principles and related procedures. If you are looking for strong leadership with your NetSuite system and would like to get your electronic payment reconciliations under control, let’s have a conversation.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube

| Category: NetSuite | Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *