Skip to content
logo Knowledgebase

Bank reconciliation shows a discrepancy or an unresolved amount

Created on  | Last modified on 

Summary

Steps to help you investigate the source of bank reconciliation discrepancy or unresolved amount in Sage 50 Accounting.

Resolution

 NOTE: Sage Support doesn’t provide bank reconciliation analysis services as it isn’t possible to know what entries must exist in your company books or bank statement. Use these steps to identify the cause of the issue. 

▼Check the bank reconciliation setup
  1. Enter the correct:
    • Statement Opening Balance
    • Statement Ending Balance
    • Statement Start Date
    • Statement End Date
  2. Record all the transactions on your bank statement in Sage 50.
  3. Make sure all transactions dates are correct.
  4. Clear all the transactions that appear in your bank statement.
  5. Make sure your bank account in Sage 50 has the proper book balance.
  6. If applicable, add bank fees in the Expense tab.
  7. If applicable, add gains (interest, exchange, errors) in the Income tab.
  8. Make sure the Outstanding amount matches the total of all the unchecked items in the Reconciliation window. If they don't, reset the reconciliation.
▼There's a discrepancy

Discrepancy = Opening book balance - (Opening outstanding + Statement Opening balance)

  1. To find out the value of the opening outstanding, check the previous period's outstanding amount at the end of your reconciliation.
  2. If you’re starting your reconciliation for the first time, the opening outstanding is equal to the total prior outstanding that you’ve added.
  3. To find out the value of the opening book balance:
    • Go to Reports, Financials, General Ledger (or Transactions by Account)
    • Select OK
    • Select the Account
    • Set the Start and Finish dates to be identical to your bank reconciliation dates
    • Look for the balance on the first line on the top. This is your opening balance
  4. Do the following comparison to test if there’s data corruption in your bank reconciliation only:
    • Verify that the opening book balance is equal to (opening outstanding + statement opening balance)
    • If these two amounts aren’t equal, the difference is the discrepancy amount
    • If the two amounts are equal, but you still have a discrepancy amount, then the bank reconciliation table contains some bad data. Reset the bank reconciliation 

The discrepancy is negative

A negative discrepancy means the opening book balance is less than the sum of the opening outstanding + statement opening balance.

There are three possible causes for a negative discrepancy:

The opening book balance is too low
  • There are missing debit entries preceding the start date
  • There are too many credit entries preceding the start date
  • The statement start date is wrong on the bank reconciliation window
The opening outstanding is too high
  • There are too many outstanding debit cheques (customer payment or general journal revenue cheques)
    • This is most likely due to incorrectly posted dates for cheques or duplicate entries
  • Not enough outstanding credit cheques (payment, payroll, or general journal payment cheques)
  • If this is your first bank reconciliation for this account, you may have added too many prior debit cheques or not enough prior credit cheques
  • The statement start date is wrong on the bank reconciliation window
The statement opening balance is too high
  • Although rare, your bank statement has errors from your bank
    • The bank gave you March 3 to March 30 instead of March 1 to March 30
    • Bank human mistakes such as duplicate entries
    • Deposits occurred by mistake from another company to your company's bank account
  • A mistake occurred while entering the amount in the bank reconciliation screen compared to your bank statement
  • The preceding period's Statement End Balance is wrong and doesn’t match the prior period's bank statement end balance
  • The Statement Start Date is wrong on the bank reconciliation window
  • Use the process of elimination to find out which of the three possibilities is correct and which isn’t
  • Make the appropriate adjustments or entries once you have found the problem
    • The adjustment must precede the current bank reconciliation period
    • You may need to reset your bank reconciliation after posting the adjustments if the discrepancy is still the same
  • If you’ve voided a receipt or payment but didn’t clear the credit note when you should have, clear the credit note dated the same voided receipt date
  • Stop here unless you also have an unresolved amount

The discrepancy is positive

A positive discrepancy means the opening book balance is more than the sum of opening outstanding + statement opening balance.

There are three possible causes for a positive discrepancy:

The opening book balance is too high
  • There are too many debit entries preceding the start date
  • There are missing credit entries preceding the start date
  • The statement start date is wrong on the bank reconciliation window
The opening outstanding is too low
  • There aren’t enough outstanding debit cheques (customer payment cheques or general journal revenue cheques)
    • This is most likely due to incorrectly posted dates for cheques or duplicate entries
  • Too many outstanding credit cheques (payment, payroll, or general journal payment cheques)
  • If this is your first bank reconciliation for this account, you may have added too many prior credit cheques or not enough prior debit cheques
  • If an adjustment was made for the opening book balance, remove this from prior outstanding transactions
  • The statement start date is wrong on the bank reconciliation window
The statement opening balance is too low
  • Although rare, your bank statement has errors from your bank:
    • The bank gave you March 3 to March 30 instead of March 1 to March 30
    • Bank human mistakes such as duplicate entries
    • Charges occurred by mistake from another company to your company's bank account
  • A mistake occurred while entering the amount in the bank reconciliation screen compared to your bank statement
  • The preceding period's Statement End Balance is wrong and doesn’t match the prior period's bank statement end balance
  • The statement start date is wrong on the bank reconciliation window
  • Use the process of elimination to find out which of the three possibilities is correct and which isn’t
  • Make the appropriate adjustments or entries once you have found the problem
    • The adjustments must precede the current bank reconciliation period
    • You may need to reset your bank reconciliation after posting the adjustments if the discrepancy is still the same
  • If you’ve voided a receipt or payment but didn’t clear the credit note when you should have, clear the credit note dated the same voided receipt date
  • Stop here unless you have an unresolved amount

The Opening Book Balance doesn’t match the Trial Balance or Balance Sheet

  1. Run the Database Utilities.
  2. Check the Opening Book Balance under Worksheet. Make sure it matches the Trial Balance or Balance Sheet.
  3. Check the discrepancy again to see if it’s correct.
▼There's an unresolved amount

Unresolved = (statement end balance + ending outstanding) - ending book balance

The ending outstanding is the outstanding amount on the bottom of your bank reconciliation window after checking everything.

  1. Go to Reports, Financials, General Ledger (or Transactions by Account).
  2. Set the Start and Finish dates to be identical to your bank reconciliation dates.
  3. Select the account.
  4. Click OK.
  5. Look for the Balance on the last line at the bottom. This is your ending book balance.
    • An Unresolved amount deals with transactions in this period's transactions or leftover outstanding in this period
  6. Do the following comparison to test if there’s data corruption in your bank reconciliation only:
    • Is the statement ending balance + ending outstanding equal to the ending book balance?
    • If these two amounts aren’t equal, then the difference is the unresolved amount
    • If the two amounts are equal, but you still have a discrepancy amount, the bank reconciliation table contains some bad data and you need to reset the bank reconciliation
  7. If you have an unresolved amount, you can move into the next period upon accepting an automated general journal entry.
    • This option isn’t recommended by Sage as you’re most likely delaying the same issue in the future
    • This also defeats the purpose of using the bank reconciliation
    • Stop here if you decide to post and accept the automated general journal entry

The unresolved amount is negative

This means the ending book balance is more than the sum of the ending outstanding + statement ending balance.

There are three possible causes for a negative unresolved amount:

The ending book balance is high
  • There are too many debit entries in this period
  • There are missing credit entries in this period
  • An adjusted transaction occurred where the deposit entry was checked but the matching withdrawal entry wasn’t
    • For example, the Reversed versus Adjustment entries when Do not show corrections is unchecked
  • A credit transaction has been reversed from a previous period but its reverse debit entry is now shown on this period
  • A replacement credit entry has to be entered to replace the voided credit transaction to offset the difference
  • If you feel this replacement credit entry shouldn’t exist, your books are missing this credit entry from an earlier date than the previous period. You’ll have to dig further into the past
  • Bank fees haven’t been recorded for this period
  • The statement end date is wrong on the bank reconciliation window
The ending outstanding is too low
  • There are missing outstanding debit cheques (customer payment cheques or general journal revenue cheques)
    • This is most likely due to incorrectly posted dates for cheques or duplicate entries
  • Some outstanding cheques have an amount mistake (too large or too low)
  • Too many outstanding credit cheques (payment, payroll, or general journal payment cheques)
  • If this is your first bank reconciliation for this account, you may have added too many prior credit cheques or not enough prior debit cheques
  • The statement end date is wrong on the bank reconciliation window
The statement ending balance is too low
  • Although rare, your bank statement has errors from your bank:
    • The bank gave you March 1 to April 3 instead of March 1 to March 30
    • Bank human mistakes such as duplicate entries
    • Charges occurred by mistake from another company to your company's bank account
  • A mistake occurred while entering the ending balance amount in the bank reconciliation screen compared to your bank statement
  • The statement end date is wrong on the bank reconciliation window
  • Use the process of elimination to find out which of the three possibilities is correct and which isn’t
  • Make the appropriate adjustments or entries once you have found the problem.
    • The adjustment must be in the bank reconciliation's current period
    • You may need to reset your bank reconciliation after posting the adjustments if the unresolved amount is still the same
  • If you’ve voided a receipt or payment but didn’t clear the credit note when you should have, clear the credit note dated the same voided receipt date
  • Stop here

The unresolved amount is positive

This means the ending book balance is less than the sum of the ending outstanding + statement ending balance.

There are three possible causes for a positive unresolved amount:

The ending book balance is low
  • There are too many credit entries in this period
  • There are missing debit entries in this period
  • An adjusted transaction occurred where the withdrawal entry is checked but the matching deposit entry isn’t
    • For example, the Reversed versus Adjustment entries when Do not show corrections is unchecked
  • A debit transaction has been reversed from a previous period but its reverse credit entry is now shown in this period
    • A replacement debit entry has to be entered to replace the voided debit transaction to offset the difference
    • If you feel this replacement debit entry shouldn’t exist, your books are missing this debit entry from an earlier date than the previous period. You’ll have to dig further into the past
  • Bank gains (interests, etc.) haven’t been recorded
  • The statement end date is wrong on the bank reconciliation window
The ending outstanding is too high
  • There are too many outstanding debit cheques (customer payment cheques or general journal revenue cheques)
    • This is most likely due to incorrectly posted dates for cheques or duplicate entries
  • Some outstanding cheques have an amount mistake (too large or too low)
  • Missing outstanding credit cheques (payment, payroll, or general journal payment cheques)
  • If this is your first bank reconciliation for this account, then you may have added too many prior debit cheques or not enough prior credit cheques
  • The statement end date is wrong on the bank reconciliation window
The statement ending balance is too high
  • Although rare, your bank statement has errors from your bank:
    • The bank gave you March 1 to April 3 instead of March 1 to March 30
    • Bank human mistakes such as duplicate entries
    • Deposits occurred by mistake from another company to your company's bank account
  • A mistake occurred while entering the ending balance amount in the bank reconciliation screen compared to your bank statement
  • The statement end date is wrong on the bank reconciliation window
  • Use the process of elimination to find out which of the three possibilities is correct and which isn’t
  • Make the appropriate adjustments or entries once you have found the problem.
    • The entry must be in the bank reconciliation's current period
    • You may need to reset your bank reconciliation after posting the adjustments if the unresolved amount is still the same
  • If you’ve voided a receipt or payment but didn’t clear the credit note when you should have, clear the credit note dated the same voided receipt date.

Need more help?

Chat now



 

Related Solutions

How to reverse bank reconciliations