Skip to content
logo Knowledgebase

Bank reconciliation shows a discrepancy or an unresolved amount

Created on  | Last modified on 

Summary

How to solve discrepancies or unresolved amounts in the bank reconciliation journal. In the bank reconciliation journal, I have an unresolved amount What is a discrepancy? What is unresolved? Bank Reconciliation balance does not equal GL bank balance Bank reconciliation now shows outstanding items from the past

Resolution

CAUTION: Sage Customer Support does not assist with issues related to third-party products or enhancements, hardware, report customizations, state or federal tax-related questions, or specific accounting questions. Please get in touch with your Sage business partner, network administrator, or accountant for assistance. Please refer to our Scope of Support document for details.


Notes:

  • The purpose of bank reconciliation is to test whether your account records match your bank statement
  • If you have a discrepancy and/or an unresolved amount, then you have to spend the appropriate amount of time investigating the source
  • Sage Support does not provide bank reconciliation analysis services nor a quick fix as it is not possible for Sage to know what entries should exist in your company books or what should be posted to your bank statement
  • This article is used to help in your investigation

Option 1: Double-check the following

  1. You have entered the correct:
    • Statement Opening Balance
    • Statement Ending Balance
    • Statement Start Date
    • Statement End Date
  2. All the transactions in your bank statement were recorded in Sage 50
  3. All the transactions are dated correctly
  4. You have cleared the transactions that appear in your bank statement
  5. Your bank account in Sage 50 has the proper book balance
  6. If applicable, you have added bank fees in the Expense tab
  7. If applicable, you have added gains (interest, exchange, errors) in the Income tab
  8. The Outstanding amount matches the total of all the unchecked items in the Reconciliation window
  9. If they don't, then reset the reconciliation. Follow How do I reset the bank reconciliation?

Option 2: I have a Discrepancy (Bank reconciliation will not post)

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

  1. To find out the value of the opening outstandings, please refer to the previous period's leftover outstandings amount at the end of your reconciliation
  2. If you are starting your reconciliation for the first time, then the opening outstandings is equal the total prior outstandings that you have added prior to the start date
  3. To find out the value of the opening book balance:
    • Until the discrepancy is fixed, the bank reconciliation cannot move into the next period
    • Do the following comparison to test if there is data corruption in your bank reconciliation only:
      • Is opening book balance equal to (opening outstandings + statement opening balance)?
      • If these two amounts are not equal, then the difference is the discrepancy amount. Proceed to Step IV
      • If the two amounts are equal, but you still have a discrepancy amount, then the bank reconciliation table contains some bad data. You can reset the bank reconciliation in order to solve this issue - How do I reset the bank reconciliation?
  4. Look for the Balance on the very first line on the top. This is your opening balance
    • Note: A discrepancy only deals with transactions preceding the start date of your current reconciliation period including leftover outstandings from the previous period
  5. Select OK
  6. Select the account
  7. Set the Start and Finish dates to be identical to your bank reconciliation dates respectively
  8. Select Reports, Financials, General Ledger (or Transactions by Account).

Discrepancy is negative: This means the opening book balance is less than the sum of: opening outstandings + statement opening balance

  1. So there are three possibilities:
    • A. The opening book balance is too low
    • B. The opening outstandings is too high
    • C. The statement opening balance is too high
  2. Go to Step 4

Discrepancy is positive: This means the opening book balance is more than the sum of: opening outstandings + statement opening balance

  1. So there are three possibilities:
    • A. The opening book balance is too high
    • B. The opening outstandings is too low
    • C. The statement opening balance is too low
  2. Go to Step 5

Option 3: I have a discrepancy. Under the Worksheet, the Opening Book Balance does not match the Trial Balance or Balance Sheet.

  1. Run Rebuild in the Support Utilities. Please contact Support for assistance in rebuilding.
  2. Check the Opening Book Balance under Worksheet again. Make sure it matches the Trial Balance or Balance Sheet
  3. Check the discrepancy again to see it is correct.

Option 4: I have an unresolved

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

The calculation of the "unresolved amount” is the statement end balance plus the ending outstanding balance minus the ending book balance

  • The ending outstanding is the outstanding amount on the bottom of your bank reconciliation window after checking everything
  • To find out the value of the ending book balance:
    1. Select Reports, Financials, then General Ledger (or Transactions by Account).
    2. Set the Start and Finish dates to be identical to your bank reconciliation dates respectively
    3. Select the account
    4. Select OK.
    5. Look for the Balance on the last line at the bottom. This is your ending book balance
      • Note: An Unresolved deals with transactions in this period's transactions or leftover outstandings in this period
    6. Do the following comparison to test if there is data corruption in your bank reconciliation only:
      • Is statement ending balance + ending outstanding equal to ending book balance?
        • If these two amounts are not equal, then the difference is the unresolved amount. Proceed to step IV.
        • If the two amounts are equal, but you still have a discrepancy amount, then the bank reconciliation table contains some bad data and you need to reset the bank reconciliation - How do I reset the bank reconciliation?
    7. If you have an unresolved, you can move into the next period upon accepting an automated general journal entry
      • This option is not recommended by Sage as you are 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

Unresolved is negative: This means the ending book balance is more than the sum of the ending outstandings + statement ending balance

  1. So there are three possibilities:
    • A. The ending book balance is high
    • B. The ending outstandings is too low
    • C. The statement ending balance is too low
  2. Go to Step 6

Unresolved is positive: This means the ending book balance is less than the sum of the ending outstandings + statement ending balance

  1. So there are three possibilities:
    • A. The ending book balance is low
    • B. The ending outstandings is too high
    • C. The statement ending balance is too high
  2. Go to Step 7

Option 5: Discrepancy is negative

  1. Below are possible reasons for each:
    • A. The opening book balance is too low:
      • There are missing debit entries(s) preceding the start date
      • There are too many credit entries(s) preceding the start date
      • The statement start date is wrong on the bank reconciliation window
    • B. The opening outstandings is too high:
      • There are too many outstanding debit cheques (customer payment cheques or general journal revenue cheques)
        • Most likely due to incorrectly posted dates for cheques or duplicate entries
      • Not enough outstandings 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 start date is wrong on the bank reconciliation window
    • C. The statement opening balance is too high:
      • Although very rare, your bank statement has errors from your bank:
        • The bank gave you March 3rd to March 30th instead of March 1st to March 30th
        • Bank human mistakes such as duplicate entries(s)
        • Deposit(s) 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 not matching the preceding period's bank statement end balance
      • The statement start date is wrong on the bank reconciliation window
  2. You must find out which of the three possibilities is correct and which are not and process of elimination is the best approach:
    • Usually, the statement opening balance is correct
    • Then you are left with either the opening book balance is too low or the opening outstandings is too high
  3. Make the appropriate adjustments or entries once you have found the problem
    • The entry/adjustment must precede the current bank reconciliation's period
    • You may need to reset your bank reconciliation after posting the adjustments/entries if the discrepancy is still the same
  4. If you have voided a receipt or payment but did not clear the credit note where you should have, then clear the credit note dated the same voided receipt date
  5. Stop here unless you also have an unresolved amount in which case go to Step III

Option 6: Discrepancy is positive

  1. Below are possible reasons for each:
    • A. The opening book balance is too high:
      • There are too many debit entries(s) preceding the start date
      • There are missing credit entries(s) preceding the start date
      • The statement start date is wrong on the bank reconciliation window
    • B. The opening outstandings is too low:
      • There are not enough outstanding debit cheques (customer payment cheques or general journal revenue cheques)
        • Most likely due to incorrectly posted dates for cheques or duplicate entries
      • Too many outstandings 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 credit cheques or not enough prior debit cheques. If an Adjustment was made for the opening book balance, this may need to be removed from prior outstanding transactions as well, as the adjustment will be included in the discrepancy amount
      • The statement start date is wrong on the bank reconciliation window
    • C. The statement opening balance is too low:
      • Although very rare, your bank statement has errors from your bank:
        • The bank gave you March 3rd to March 30th instead of March 1st to March 30th
        • Bank human mistakes such as duplicate entries(s)
        • Charge(s) 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 not matching the preceding period's bank statement end balance
      • The statement start date is wrong on the bank reconciliation window
  2. You must find out which of the three possibilities is correct and which are not. Process by elimination is the best approach:
    1. Usually the statement opening balance is correct
    2. Then you are left with either the opening book balance is too high or the opening outstandings is too low
  3. Make the appropriate adjustments or entries once you have found the problem
    • The entry/adjustment must precede the current bank reconciliation's period
    • You may need to reset your bank reconciliation after posting the adjustments/entries if the discrepancy is still the same
  4. If you have voided a receipt or payment but did not clear the credit note where you should have, then clear the credit note dated the same voided receipt date
  5. Stop here unless you have an unresolved amount in which case go to Step 3

Option 7: Unresolved is negative

  1. Below are possible reasons for each:
    • A. The ending book balance is high:
      • There are too many debit entries(s) in this period
      • There are missing credit entries(s) in this period
      • An adjusted transaction occurred where the deposit entry is checked but the matching withdrawal entry is not
        • e.g., the Reversed versus Adjustment entries when Do not show corrections is unchecked
      • A credit transaction has been reversed (voided) from a previous period but its reverse debit entry is now showing on this period
        • A replacing credit entry has to be entered to replace the voided credit transaction to offset the difference
        • If you feel this replacement credit entry should not exist, then this means your books is missing this credit entry from an earlier date than the previous period. You will have to dig further into the past
      • Bank fees have not been recorded for this period
      • The statement end date is wrong on the bank reconciliation window
    • B. The ending outstandings is too low:
      • There are missing outstanding debit cheques (customer payment cheques or general journal revenue cheques)
        • 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 outstandings 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 credit cheques or not enough prior debit cheques
      • The statement end date is wrong on the bank reconciliation window
    • C. The statement ending balance is too low:
      • Although very rare, your bank statement has errors from your bank:
        • The bank gave you March 1st to April 3rd instead of March 1st to March 30th
        • Bank human mistakes such as duplicate entries(s)
        • Charge(s) 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
  2. You must find out which of the three possibilities is correct and which are not. Process by elimination is the best approach:
    • Usually the statement ending balance is correct
    • Then you are left with either the ending book balance is too high or the ending outstandings is too low
  3. Make the appropriate adjustments or entries once you have found the problem
    • The entry/adjustment must be in the bank reconciliation's current period
    • You may need to reset your bank reconciliation after posting the adjustments/entries if the unresolved is still the same
  4. If you have voided a receipt or payment but did not clear the credit note where you should have, then clear the credit note dated the same voided receipt date
  5. Stop here

Option 8: Unresolved is positive

  1. Below are possible reasons for each:
    • A. The ending book balance is low:
      • There are too many credit entries(s) in this period
      • There are missing debit entries(s) in this period
      • An adjusted transaction occurred where the withdrawal entry is checked but the matching deposit entry is not
        • e.g., the Reversed versus Adjustment entries when Do not show corrections is unchecked
      • A debit transaction has been reversed (voided) from a previous period but its reverse credit entry is now showing on this period
        • A replacing debit entry has to be entered to replace the voided debit transaction to offset the difference
        • If you feel this replacement debit entry should not exist, then this means your books is missing this debit entry from an earlier date than the previous period. You will have to dig further in the past
      • Bank gains (interests, etc.) have not been recorded
      • The statement end date is wrong on the bank reconciliation window
    • B. The ending outstandings is too high:
      • There too many outstanding debit cheques (customer payment cheques or general journal revenue cheques)
        • 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 outstandings 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
    • C. The statement ending balance is too high:
      • Although very rare, your bank statement has errors from your bank:
        • The bank gave you March 1st to April 3rd instead of March 1st to March 30th
        • Bank human mistakes such as duplicate entries(s)
        • Deposit(s) 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
  2. You must find out which of the three possibilities is correct and which are not. Process by elimination is the best approach:
    • Usually the statement ending balance is correct
    • Then you are left with either the ending book balance is too high or the ending outstandings is too low
  3. Make the appropriate adjustments or entries once you have found the problem
    • The entry/adjustment must be in the bank reconciliation's current period
    • You may need to reset your bank reconciliation after posting the adjustments/entries if the unresolved is still the same
  4. If you have voided a receipt or payment but did not clear the credit note where you should have, then clear the credit note dated the same voided receipt date

DocLink: How do I reset the bank reconciliation?
DocLink: How to reverse bank reconciliations

Chat with support