Resolving Bank Reconciliation Discrepancies in Sage 100 ERP

Resolving Bank Reconciliation Discrepancies in Sage 100 ERP

Bank reconciliation issues can be challenging, especially when you encounter discrepancies between the Bank Reconciliation module and the Bank Reconciliation Report in Sage 100 ERP. This article aims to unravel common problems associated with such discrepancies and provide actionable insights for resolution.

Understanding the Issue

In some cases, users notice a discrepancy between the Bank Reconciliation module and the Bank Reconciliation Report. A sample scenario could like the user has a $0.00 out-of-balance amount within the module, yet the Bank Reconciliation Report shows an non-zero out-of-balance amount. Another important observation is that while the adjusted bank balance is correct and matches the General Ledger (GL) balance, there is still a persistent variance.

Key Points to Consider

  1. Correct Adjusted Bank Balance: Ensure that the bank balance you have adjusted within the module matches the balance in your General Ledger.

  2. Out-of-Balance Amount: Differences indicated in the report versus the module suggest potential discrepancies in reconciliation entries that need scrutiny.

Investigative Steps

Reviewing Journal Entries

When identifying reconciliation discrepancies, it's critical to carefully review Journal Entries (JE) within defined periods:

  • Compare Monthly Entries: Verify if all Journal Entries for the months involved (e.g., October and November) have been accurately posted and included in reconciliations. For instance, ensure that all entries you see with October dates were considered when you completed October’s reconciliation.

  • Corresponding Entries Check: Match November's GL postings to entries listed in the bank reconciliation. This will help you identify any missed or unmatched transactions that might contribute to the discrepancy.

Troubleshooting Steps

To address the ongoing issues, it is advisable to start with the following procedures:

  1. Documentation Review: Reference any troubleshooting documents to understand potential missteps in previous reconciliations. These documents often contain procedural guidelines that highlight common errors and how to rectify them.

  2. Reconciliation Report Analyze: Run a detailed Bank Reconciliation Report to capture all transactions within the reconciliation period, focusing on unmatched or erroneously posted entries.

  3. Cross Verification: Check that each entry both in Sage 100 ERP and physical bank statements or online banking reports align accurately. Variances often stem from posting errors or unapplied entries.

General Ledger doesn't balance to the Bank Reconciliation module

If the General Ledger doesn’t match the Bank Reconciliation Report, follow these steps to find and fix the issue.

Check Beginning Balances in Both Modules

  1. Go to Bank Reconciliation, then Reports.
  2. Run a Bank Recap Report. Set the Bank Code to Equal to, and choose your bank code.
  3. Find the ending balance from the previous month on this report.
  4. Go to General Ledger, Main, Account Maintenance, and select the account for the bank code.
  5. On the History Tab, find the ending balance for the same prior month.
  6. Compare both balances. If they don’t match, the issue started last month.
    • Check to see if a transaction posted after reconciliation in one module but not the other.

Check Data in General Ledger

  1. In General Ledger, Main, Account Maintenance, select the account for the bank code.
  2. Compare the ending balance on the History Tab with the Transaction Tab.
  3. If they don’t match, refer to How to recalculate G/L account balances and dates .

Check Data in Bank Reconciliation

Even if your bank statement matches, verify the following:

  1. Run Bank Recap Report, set Bank Code to Equal to, and choose your bank code.
  2. The beginning balance must match the ending balance from the last reconciled and purged bank statement.
  3. If not, refer to How the system calculates the beginning balance on the Bank Recap Report .
  4. The ending balance must match the Current Balance in Bank Code Maintenance.
  5. If not, refer to Ending Balance on the Bank Reconciliation Recap Report doesn't match the Current Balance .

If Reconciliation Was in Balance but Now Isn’t

Use caution with repair tools. Always back up first. Contact a Sage partner or support if needed.

  • For versions 4.40.0.2 and higher: Go to Bank Reconciliation, Utilities, Recalculate Outstanding Balances.
  • For versions 4.20 – 4.40.0.1: Rebuild sort files for Bank Reconciliation.
  • See: How to rebuild sort files.
  • If still out of balance, refer to How to resolve an Out of Balance in Bank Reconciliation .

Confirm All Entries and Dates Match

  1. Make sure all transactions appear in both modules.
  2. Posting Dates in the General Ledger must match Document Dates in Bank Reconciliation.
  3. Any mismatch is a reconciling item.

Use these reports to check:

  • Bank Recap Report
  • General Ledger Detail Report

Review transactions before, during, and after the month.


How to resolve an Out of Balance in Bank Reconciliation

How to resolve an Out of Balance in Bank Reconciliation

Steps, tips, and tricks for reconciling bank accounts or resolving an Out of Balance in Bank Reconciliation in Sage 100.

Bank Recap Report

  1. Lists checks, deposits, and adjustments in the order they happened.
  2. You can print it anytime before deleting the Bank Reconciliation file.
  3. This report shows all transactions for a chosen date range.
  4. You can print:
    • One or more transaction types.
    • Only cleared, only outstanding, or all transactions.
  5. For each bank code, it prints:
    • Transaction date, type, source, check number, and check type.
    • Reference, comment/payee name, cleared status, amount, and bank balance.
  6. The report prints the number of transactions and total amount for each selected transaction type.

Bank Reconciliation Report (Register)

  1. Lists all transactions for a chosen date range.
  2. The report provides a summary of reconciliation.
  3. The register acts as an audit record of bank activity.
  4. It includes transactions from:
    • General Ledger, Accounts Payable, Accounts Receivable.
    • Payroll, Purchase Order updates, and manual entries using Check, Deposit & Adjustment Entry.
  5. You normally print the Bank Reconciliation report after using the Reconcile Bank.
  6. The reconciliation summary:
    • Total all outstanding checks, deposits, and adjustments.
    • Shows calculated book balance up to the ending transaction date.

Bank Reconciliation Process Tips

Match these amounts with the bank statement summary:

  • Beginning balance on statement = beginning balance on Bank Recap Report.
  • Ending balance on statement = ending balance entered in Reconcile Bank.
  • Cleared items on statement = cleared items on Bank Recap Report.

You can create a template such as the example below. Enter the bank information and use it to check amounts in Sage 100.

Example bank reconciliation template


Notes
Bank Recap Report example
  1. Open Bank Reconciliation, go to Reports, then select Bank Recap Report.
  2. Enter the bank code only. Leave other fields blank.
  3. Preview the report and compare its beginning balance to the bank statement’s beginning balance.
  4. If the balances don’t match, follow these steps to fix it:
  5. Find the difference between:
    • Ending balance on last purged bank statement.
    • Beginning balance on Bank Recap Report. (For example: If you purged May, June’s bank statement beginning balance needs to match the Bank Recap Report.)
    • Tip: Always purge cleared transactions monthly after printing a balanced Bank Reconciliation Report.
  6. Go to Bank Reconciliation, then Main, and open Bank Code Maintenance.
  7. Choose the correct bank code from the lookup list.
  8. Write down the Current Balance before making changes.
  9. Adjust the Current Balance using the difference from step 5:
    • If the Bank Recap Report is too low: Increase the Current Balance.
    • If the Bank Recap Report is too high: Decrease the Current Balance.
    • Enter the new amount and click Accept.
  10. Run the Bank Recap Report again and confirm the beginning balance matches the bank statement.

Notes

Make sure the ending balance on the bank statement matches the Ending Balance entered in the Reconcile Bank.

Reconcile Bank ending balance example


Notes
  1. When the out of balance amount is zero, start reconciling against the General Ledger.
  2. This process uses the Bank Recap Report and General Ledger Detail report for comparison.
  3. Print the Bank Recap Report through the last day of the month you're reconciling.
  4. Print the Bank Reconciliation Report through the same date.
  5. Answer Yes when asked, “Do you want to purge cleared documents?”

CAUTION: Retain these copies as you can't regenerate these reports.

Bank reconciliation caution example

Reconcile Bank Matches But Bank Reconciliation Report Shows Out-of-Balance

The Reconcile Bank screen and report don’t match in Sage 100. One shows balanced while the other isn’t, or their out-of-balance amounts differ.

Resolution

Version 4.10 - 4.40.0.2 and Sage MAS 200 - SQL Server Edition

  1. Open Library Master, Utilities, Rebuild Sort Files.
  2. Select Company Code and select Bank Reconciliation from Module List.
  3. Click Proceed.

Version 4.40.0.2 and higher

  1. Open Bank Reconciliation, Utilities, Recalculate Outstanding Entries Totals.
  2. Click Proceed.

Reconcile Bank and Bank Register do not match after rebuilding the Bank Reconciliation Files

Cause

A blank record exists in GL_Bank.M4T. Delete the blank record, and rebuild the Bank Reconciliation sort files.

Resolution

WarningCAUTION: Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions. If necessary, seek the assistance of a qualified Sage business partner, network administrator, or Sage customer support analyst.


  1. Open Library Master, Utilities, Data File Display and Maintenance.
  2. In the File Name field, browse to ..\MAS90\MAS_xxx\GLxxx\GL_Bank.M4T (xxx = company code).
  3. Click Key Scan and select the Blank record.
  4. Click Delete, then click Yes to confirm.
  5. Close Data File Display and Maintenance.
  6. Open Library Master, Utilities, Rebuild Sort Files.
  7. Select Company Code and in the Module list, select Bank Reconciliation.
  8. Click Proceed.

 

Technical Assistance

If the issue remains unresolved after following the above procedures, please contact our support team, and we can connect you with a Sage 100 consultant who can further scope and assist toward a resolution.


    • Related Articles

    • Bank feed issue System.TypeInitializationException

      Issue When you try to access Bank Feed or perform a task that is related to Bank Feed you get a pop up similar to the one below. System.TypeInitializationException: The type initializer for 'OLB_Center.WPF.Model.FIBluePrintParser' threw an exception. ...

    • TLS 1.2 Compliance for Sage 100 and Sage 300

      Overview Sage has announced the following: "Effective October 1, 2023, Sage servers will stop accepting communication from Sage 100 and Sage 300 product versions using Transport Layer Security (TLS) 1.0 and 1.1." This will mean that older versions of ...

    • Sage Business Insight Fails to Load Views

      Problem In certain instances , after a migration of Sage 100, Business Insight may not load the appropriate views. Solution Manual To resolve this issue, the "ViewsPath" key needs to be created for each user and pointed to the following location: ...

    • How to change Sage CRM user Password

      How to change Sage CRM user Password Follow the steps below to update the password: Click on Profile Menu and select Prefrences. Click on Change Password. A Pop-Up window will appear: Enter your current password. Enter the new password . Click on ...

    • Sage 100 appears to launch but cannot see the login window

      Problem: When launching Sage the user experiences some or all of the following (not all-inclusive of symptoms) The application appears to be launching, but Sage would not show up Odd display of the Sage application Reason: If the user is using Sage ...