Sage 100 bank reconciliation software options compared for month end

Sage 100 bank reconciliation options at a glance

Most Sage 100 shops are distributors, manufacturers and service companies with a few operating accounts, a payroll account and a lockbox or merchant account. Hundreds of lines a month per account is normal. The question is rarely whether Sage 100 can reconcile. It is how many of those lines a person still has to clear by hand.

These are not either or choices. Most controllers end up with the module plus one or two of the others, because the module is what keeps the bank code balance, the check register and the outstanding checks list correct.

The Sage 100 Bank Reconciliation module

The module tracks every bank code (up to 36 per company), receives checks and deposits from Accounts Payable, Accounts Receivable and Payroll, and stores what cleared on each statement date. The monthly routine looks like this:

1. Post every open register in General Ledger, Accounts Payable, Accounts Receivable and Payroll, so nothing is waiting to reach the bank code.
2. Confirm the bank code balance in Bank Code Maintenance agrees with the cash account in General Ledger.
3. Enter fees, interest and other bank side items through Check, Deposit and Adjustment Entry.
4. Open Bank Reconciliation, Main, Reconcile Bank, enter the statement date and ending balance, and mark what cleared.
5. Print the reconciliation and outstanding items reports for the month file.

Step four is where the hours go. Checks can be cleared quickly by number. Deposits are harder: AR cash receipts are entered by customer, and the bank shows a single deposit for the whole batch, so the person reconciling has to work out which deposit entries make up which statement line. Wires, ACH debits from vendors and merchant fees often do not exist in Sage 100 at all until someone enters them.

Auto Check Reconciliation

From Sage 100 2019, Bank Reconciliation includes Auto Check Reconciliation. You download a cleared checks file from your bank, set up the file layout once in the setting wizard, run it from the Main menu, and review the log for rejections and warnings before the checks are marked cleared.

It is the right tool for an account that writes a lot of paper checks, such as an AP operating account with 200 vendor checks a month. It does nothing for deposits, ACH, wires or fees, and the result depends on how clean the bank's file is. Some banks only offer a positive pay style export or a PDF, and then the setup is more work than it saves.

Bank feeds in Sage 100

Bank feeds connect Sage 100 to the bank through a guided setup in Bank Code Maintenance and bring transactions in daily. One to one items match as they arrive, which keeps a busy account current during the month instead of only at month end.

The limits are the ones every feed has. A batched deposit does not match a single receipt. A processor payout net of fees does not equal the gross receipts. A feed that drops for a week leaves a gap nobody notices until the reconciliation is off. Feeds also need a supported bank connection, which is not available for every account, and payroll or trust accounts at smaller banks are frequently left out.

Outside matching software next to Sage 100

The fourth option does the matching outside the company file. You export the cash account from the General Ledger detail report to Excel with the date, reference, description, debit and credit columns, download the statement from the bank, and upload both. The software pairs the lines and gives back a cleared list, the items Sage 100 is missing, the Sage 100 entries the bank never paid and a report for the auditors. Then you mark the cleared items in Reconcile Bank from that list.

This is what automated bank reconciliation means in practice for a desktop ERP: nothing is installed in Sage 100, no add-on touches the company file, and the module stays the system of record. BankReconciler runs six matching passes, including grouped deposits where several receipts add up exactly to one bank line, and near matches where a check was keyed with two digits swapped. Statements can be PDF, CSV, XLSX, OFX, QFX, QBO, MT940 or CAMT.053. If the bank only gives you PDFs and you also want the transactions in a workbook for your own analysis, you can convert the PDF bank statement to Excel as a separate step, though the matching itself reads the PDF directly.

The same approach works for Sage 50 Accounting, which reconciles through a different window. That workflow is on Sage 50 bank reconciliation software.

Which Sage 100 bank reconciliation option fits your accounts

A simple test: count last month's lines you cleared by hand that were not checks. If it is under 30, Sage 100 alone is fine. If it is in the hundreds, or the reconciliation was off and took a day to find, outside matching pays for itself in the first close. For companies that are several months behind, start from the last balanced month and work forward, as described on catch up bank reconciliation. For the batched deposit problem on its own, see match bank deposits to invoices.

Questions about Sage 100 bank reconciliation

How do I do a bank reconciliation in Sage 100?

Make sure the General Ledger transaction register is empty and every module has posted, then go to Bank Reconciliation, Main, Reconcile Bank. Pick the bank code, enter the statement date and ending balance, and mark each check, deposit and adjustment that cleared. Fees and interest go in first through Check, Deposit and Adjustment Entry so the difference can reach zero.

Does Sage 100 have automatic bank reconciliation?

For checks, yes. Since Sage 100 2019, Auto Check Reconciliation reads a cleared checks file from your bank and marks those checks cleared. Deposits, ACH debits, card fees and wires are not in that file, so they are still cleared by hand, through bank feeds, or from a matched list produced by outside software.

Why is my Sage 100 bank reconciliation out of balance?

The usual causes are an unposted register in General Ledger or a subledger, a bank code balance that no longer agrees with the cash account, a deposit recorded as several receipts that the bank shows as one line, a missing fee, or a check voided after it was cleared. Matching the statement against a General Ledger export shows which line it is.

Do I need the Sage 100 Bank Reconciliation module?

If you write checks from Sage 100, it is worth having, because it tracks cleared and outstanding checks per bank code and feeds the check register. Outside matching software does not replace it. It tells you which lines cleared and which are missing, so the module work takes minutes instead of an afternoon.