DB Distributor - Credit Card Transactions and Your DB Distributor Reconciliation

DemandBridge Best Practices is designed to keep you informed and provide you with better and more efficient procedures.

Credit Card Transactions and Your DB Distributor Reconciliation

Accepting credit cards can be a great way to improve your cash flow and offer conveniences to your customers. However, just turning on credit card processing without the proper procedures in place might cause you accounting hassles. In this issue of DemandBridge Best Practices, we will provide you with some suggestions on how to make your accounts receivable and bank reconciliation processes easier. These recommendations are for those who use the Credit Card Plus module within DB Distributor or with the DB Distributor eCommerce Toolkits.

As you probably know, your bank reconciliation data files are updated with transactions from three sources.

All deposit entries are created from Cash Receipts Processing.
All check entries are created from Check Processing and Manual Check Processing.
All other entries are created as Adjustments during the bank reconciliation process.

In an effort to eliminate unnecessary cash receipts data entries, invoices that are automatically settled during credit card processing are updated to your Accounts Receivable transactions files (AR6) with a "zero" balance.

Our first recommendation is that you setup a separate Accounts Receivable Account for your Merchant Bank. Even though the invoice will update with a zero balance, an actual dollar amount will update to your General Ledger for the amount due from your Merchant Bank. By setting up a separate AR account, you will be able to isolate these amounts. When running your AR Open Invoice Report (Illustration A) or your AR Trial Balance (Illustration B), you will have the option to print all invoices, only credit card invoices, or only non-credit card invoices. By leaving the box unchecked, you will get a report that will list your actual open invoices with a total that will tie to the balance in your Trade Accounts Receivable account.

At some future date, usually two or three days after you settle a credit card payment, your Merchant Bank will transfer funds into your bank account for all the credit cards transactions that they have cleared. This will generally show up as a lump sum for the day. You basically have two choices.

Option A - You can enter a cash receipt for the total that was deposited each day. This cash receipt should be posted directly to the Merchant Bank Accounts Receivable account that you created for credit card transactions.

Option B - You can wait until you get the statement from your Merchant bank and enter a single cash receipt entry, or access your Merchant bank account on-line for settlement detail and make periodic entries. These cash receipts entries will be summary entries that will account for the following:

The total deposited into your account for the month.
The credit card fees charged that you will need to expense

Dr Cr

Cash - Credit Card Account xxx

Receivable from Merchant Bank xxx

Credit Card Transactions Charges yyy

Cash - Credit Card Account yyy

If you chose Option B, then our next recommendation is that you maintain a separate bank account for credit card deposits. By utilizing a separate bank account, your bank reconciliation process will be much easier. First, your bank statement for your main operating account will only include details on the cleared checks that you have written and the actual cash deposits that you have made. The process will not be encumbered by the daily credit card deposits for which you did not enter individual cash receipts entries. Comparing your credit card statements to a separate bank account will make the reconciliation of this account easier as well.

Reconciling Settlements to Credit Card Invoice Transactions

By running your AR Open Invoice Report for "Credit Card Only" transactions, you will be able to compare the transactions against your Credit Card Statement for the month. If your Merchant Bank does not currently settle in "Gross," you should request that they do so. This means that they will deposit the full amount of the original invoice and show any of their charges separately. This will enable you to more easily compare the transactions.

You will use the option to print your AR Open Invoice Report and/or your AR Trial Balance for only credit card transactions to get a detailing of all the credit card transactions for the month.

Many Merchant Banks will provide you with the option to export their statement detail to a CSV file or to Excel. If so, you may also wish to export all your credit card invoices from your Open Invoice File (AR6) to Excel. Using standing formulas in Excel, you will be able to compare these two files automatically and view the exceptions.

For example, you can use the "View Files" feature and export the relevant data fields from your AR6 file to Excel. We suggest that you include the Invoice Number, Invoice Date, Terms, and Invoice Total.

You will need to restrict the export to only those transactions where the Terms Code was a credit card terms code. When setting up credit card processing, you were probably advised by our implementation staff to use a terms code of CC. If you have multiple credit card terms codes, we recommend that you set them up as C1, C2, C3, etc. Using the "Equal to" or "Begins with" search options, you will be able to restrict the export to just the Credit Card invoices.

Then copy and paste the columns from your Credit Card Report that contain, at a minimum, the Invoice Number and the Payment Total into your Excel spreadsheet (See column F and G below).

Add a Column "E" with a title of Missing.

Assuming that Column "F" has the invoice numbers from your Credit Card statement and that there are 500 transactions, you would enter this formula in the E2 cell.

=ISNA(MATCH(A2,$F$2:$F$500,FALSE))

A2 is the first cell of the Invoice Number Column from your AR6 export.

$F$2:$F$500 means that you want to search for the invoice number in Column "F" from row 2 all the way down to row 500.

Drag this formula down the sheet for all rows of Column A

TRUE indicates that it was true that there was no match. You could then sort your report by column E in descending order and you will see at the very top of the report, all of those transactions where there is not a corresponding invoice settled on your Credit Card statement.

You could use this same logic by adding a "Missing" column "H" and applying the formula in reverse to search for any invoices that are on your credit card statement that are not in your AR6 file.

Please note that you need to run your receivable reports and/or export the invoice data prior to performing month-end processing for the period. As you probably already know, all invoices with a zero balance move from the Open Invoice File (AR6) to the Invoice History File (AR8) at the time of period-end update.

We hope that this information helps you become more efficient in reconciling your bank statement and your credit card transactions. If you need assistance regarding any of these procedures or the use of the View Files feature within DB Distributor software, please contact DemandBridge support, support@demandbridge.com.