Summary
Resolution
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, you need to investigate the source
- Sage Support doesn’t provide bank reconciliation analysis services or a quick fix as it isn’t possible to know what entries must exist in your company books or what must be posted to your bank statement
- This article is used to aid in your bank reconciliation investigation
Check the following before you begin:
- You’ve entered the correct:
- Statement Opening Balance
- Statement Ending Balance
- Statement Start Date
- Statement End Date
- All the transactions in your bank statement are recorded in Sage 50.
- All the transactions are dated correctly.
- You’ve cleared the transactions that appear in your bank statement.
- Your bank account in Sage 50 has the proper book balance.
- If applicable, you have added bank fees in the Expense tab.
- If applicable, you have added gains (interest, exchange, errors) in the Income tab.
- The Outstanding amount matches the total of all the unchecked items in the Reconciliation window.
- If they don't, then reset the reconciliation by following How do I reset the bank reconciliation?
Discrepancy = Opening book balance - (Opening outstanding + Statement Opening balance)
- To find out the value of the opening outstanding, refer to the previous period's leftover outstanding amount at the end of your reconciliation.
- If you’re starting your reconciliation for the first time, then the opening outstanding is equal to the total prior outstanding that you’ve added before the start date.
- To find out the value of the opening book balance:
- Until the discrepancy is fixed, the bank reconciliation can’t move into the next period
- Do the following comparison to test if there’s data corruption in your bank reconciliation only:
- Is the opening book balance equal to (opening outstanding + statement opening balance)?
- If these two amounts aren’t equal, then the difference is the discrepancy amount. Proceed to Step 5
- 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 by following How do I reset the bank reconciliation?
- Look for the balance on the 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 outstanding from the previous period
- Select OK.
- Select the Account.
- Set the Start and Finish dates to be identical to your bank reconciliation dates.
- Select Reports, Financials, General Ledger (or Transactions by Account).
Discrepancy is negative: This means the opening book balance is less than the sum of the opening outstanding + statement opening balance.
- So there are three possibilities:
- The opening book balance is too low
- The opening outstanding is too high
- The statement opening balance is too high
- Go to Option 4.
Discrepancy is positive: This means the opening book balance is more than the sum of opening outstandings + statement opening balance.
- So there are three possibilities:
- The opening book balance is too high
- The opening outstanding is too low
- The statement opening balance is too low
- Go to Option 5.
- Run Rebuild in the Support Utilities. Contact Support for assistance in rebuilding
- Check the Opening Book Balance under Worksheet again. Make sure it matches the Trial Balance or Balance Sheet
- Check the discrepancy again to see if it’s correct
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:
- Select Reports, Financials, General Ledger (or Transactions by Account).
- Set the Start and Finish dates to be identical to your bank reconciliation dates.
- Select the account.
- Click OK.
- Look for the Balance on the last line at the bottom. This is your ending book balance.
- Note: An Unresolved amount deals with transactions in this period's transactions or leftover outstanding in this period.
- 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 by following How do I reset the bank reconciliation?
- Is the statement ending balance + ending outstanding equal to the ending book balance?
- 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
Unresolved is negative: This means the ending book balance is more than the sum of the ending outstanding + statement ending balance.
- So there are three possibilities:
- The ending book balance is high
- The ending outstanding is too low
- The statement ending balance is too low
- Go to Option 6.
Unresolved is positive: This means the ending book balance is less than the sum of the ending outstanding + statement ending balance.
- So there are three possibilities:
- The ending book balance is low
- The ending outstanding is too high
- The statement ending balance is too high
- Go to Option 7.
- Below are the possible reasons for each:
- 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
- The opening 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
- Not enough 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 start date is wrong on the bank reconciliation window
- There are too many outstanding debit cheques (customer payment cheques or general journal revenue cheques)
- 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(s)
- 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
- Although rare, your bank statement has errors from your bank:
- The opening book balance is too low:
- You must find out which of the three possibilities is correct and which isn’t and the process of elimination is the best approach.
- Usually, the statement opening balance is correct
- Then you’re left with either the opening book balance is too low or the opening outstanding is too high
- Make the appropriate adjustments or entries once you have found the problem.
- The entry/adjustment must precede the current bank reconciliation period
- You may need to reset your bank reconciliation after posting the adjustments/entries if the discrepancy is still the same
- If you’ve voided a receipt or payment but didn’t clear the credit note where you should have, then clear the credit note dated the same voided receipt date.
- Stop here unless you also have an unresolved amount, in which case go to Option 2.
- Below are the possible reasons for each:
- 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
- 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, 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
- There aren’t enough outstanding debit cheques (customer payment cheques or general journal revenue cheques)
- 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(s)
- 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
- Although rare, your bank statement has errors from your bank:
- The opening book balance is too high:
- To find out which of the three possibilities is correct and which isn’t, process by elimination is the best approach
- Usually, the statement opening balance is correct
- Then you’re left with either the opening book balance is too high or the opening outstanding is too low
- Make the appropriate adjustments or entries once you have found the problem
- The entry/adjustment must precede the current bank reconciliation period
- You may need to reset your bank reconciliation after posting the adjustments/entries if the discrepancy is still the same
- If you’ve voided a receipt or payment but didn’t clear the credit note where you should have, then clear the credit note dated the same voided receipt date
- Stop here unless you have an unresolved amount, in which case go to Option 3
- Below are the possible reasons for each:
-
- 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 (voided) 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, 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
- There are missing outstanding debit cheques (customer payment cheques or general journal revenue cheques)
- 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(s)
- 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
- Although rare, your bank statement has errors from your bank:
- The ending book balance is high:
- To find out which of the three possibilities is correct and which isn’t, process by elimination is the best approach
- Usually, the statement ending balance is correct
- Then you’re left with either the ending book balance is too high or the ending outstanding is too low
- 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 amount is still the same
- If you’ve voided a receipt or payment but didn’t clear the credit note where you should have, then clear the credit note dated the same voided receipt date
- Stop here
- Below are the possible reasons for each:
- 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 (voided) 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
- There are too many outstanding debit cheques (customer payment cheques or general journal revenue cheques)
- 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
- Although rare, your bank statement has errors from your bank:
- The ending book balance is low:
- To find out which of the three possibilities is correct and which isn’t, process by elimination is the best approach.
- Usually, the statement ending balance is correct
- Then you’re left with either the ending book balance is too high or the ending outstanding is too low
- 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 amount is still the same
- If you’ve voided a receipt or payment but didn’t clear the credit note where you should have, then clear the credit note dated the same voided receipt date.
DocLink: How to reverse bank reconciliations