Hi Kristine

Below is information directly from the Sage 50 Canada Knowledgebase. You can find this information also from Help Sage Support Knowledgebase directly in Sage 50 Canada and it will take you to the Knowledgebase. The article number is 10237 and you can use that as your search term. Hope this help you solve your issue.

• In the bank reconciliation journal, I have a discrepancy 
• In the bank reconciliation journal, I have an unresolved amount.
• What is a discrepancy?
• What is unresolved?

Note:

• The purpose of bank reconciliation is to quickly test whether your account records match your bank.
• As a result, 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 exist in your company books.
• The following steps are to help guide you in your investigation.

Step I: 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. You have cleared the transactions that appear in your bank statement.
4. Your bank account in Sage 50 has the proper book balance.
5. If applicable, you have added bank fees in the Expense tab.
6. If applicable, you have added gains (interest, exchange, errors) in the Income tab.
7. The Outstanding amount matches the total of all the unchecked items in the Reconciliation window.
8. If they don't, then reset the reconciliation. Follow Article ID 10073 How do I reset the bank reconciliation, in Related Resources.

Step II: I have a discrepancy

Discrepancy = opening book balance - (opening outstandings + statement opening balance)

Note: The calculation of the "discrepancy amount” is the opening book balance minus opening outstandings minus the statement opening balance.

• To find out the value of the opening outstandings, please refer to the previous period's left over outstandings amount at the end of your reconciliation.

Note: 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.

• To find out the value of the opening book balance:

1. 1. Select Reports, Financials, 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 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 left over outstandings from the previous period.

   6. 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. See Related Resources. Stop here.
   7. Until the discrepancy is fixed, the bank reconciliation cannot move into the next period.

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 IV.

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 V.

Step III: 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 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 on the bottom. This is your ending book balance.

     Note: An Unresolved deals with transactions in this period's transactions or left over 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. You can reset the bank reconciliation in order to solve this issue. See Related Resources. Stop here.
  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 VI.

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 VII.

Step IV: 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. Process by 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.

Step V: 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.

Step VI: 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 in 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.

Step VII: 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.

How do I reset the bank reconciliation?

• Use the bank rec troubleshooter: https://www.cognitoforms.com/Me21/TroubleshootingABankRecWithSage50Canada

ID:10237Last Modified Date:3-21-20181521659067957  

Thanks, Jo Anne, I'll go over this and see what I can discern--wish me luck!

Uncheck 'Do not show corrections'

Hi Randy,

Here it is:

When I started doing the account rec for March 1-31, 2018 on our new bank account, I had forgotten to select the new bank for some expenses... these are the reversals/corrections that you see here. The final reversal you see here for $91.84 is where I tried to make an expense entry to see if that would rectify the issue in anyway... it did not... made it worse... doubled the unresolved amount... so then I reversed out that entry out.

FYI, here is a screen capture from the tab, "Worksheet," and also note I have not made any entries on the tabs "Income" and "Expense," in the account reconciliation window.

Thanks for taking a look.

Cheers,
Kristine

Hi Kristine,

I can't see anything wrong with the data that Sage 50 is showing on the screen.  All I can suggest is to just leave the account as is, and keep all the backup that explains the apparent discrepancy.  

Sage won't support making an account inactive or otherwise restricting future entries, so you may have to put something in the account title (zzz in front, etc.) for three years until you can mark it inactive.

Thanks, Randy...yeah it's kinda odd, hey? And I'm thinking that because it's the final account rec for that bank account, that it's probably not really worth me spending still more time to solve the mystery, as much as it's driving me bonkers.

I'll keep records as you suggest, and come year end when we go to the accountant I'm hoping that they'll let me know where I buggered up and/or they'll have an adjusting journal entry for me on this or something.

Thanks again for taking a look!

Cheers,
Kristine