how to deal with large unresolved bank rec amount

I have a new client, fiscal year start is nov 1st
last bank rec was done on nov 30, 2012
financial statements have been prepared to the end of fiscal 2012

I'd like to start the bank rec starting nov 1, 2013 (can't / shouldn't change earlier transactions)
the actual bank stmt opening balance is 253K, ending balance 229K

after resetting the bank rec,
and setting the starting and ending balances to 253K, the unresolved amount should be 0.00....
and no o/s transactions,
  my discrepancy is 378K and unresolved -378K

setting the opening balance to 631K makes the discrepency 0.00
the ending balance is still 229K,
   but the unresolved is still -378K

how do I make the unresolved 0.00 so I can start reconciling ?

Parents
  • 0

    You need to do a manual bank reconciliation on paper or in a spreadsheet, before entering the outstanding cheques into Sage 50.

    Also, a manual reconciliation will help you find or at least point out any past data entry errors such as duplicated entries.

  • 0 in reply to RandyW

    since I can't record transactions prior to Nov 1 2013, (2 fiscal year rule)

    are you saying that I need to take 11 actual/physical bank stmts from Dec 1 2012 to Oct 31 2013 (that have not been / will not be reconciled with Sage) and manually record all the deposits/withdrawals in a worksheet

    that this will give me a list of deposits/withdrawals that need to be 'inserted as outstanding transactions' once I've reset the bank reconciliation flag for the account ?

    and then, when I start reconciling my bank stmt from Nov 1 2013 to Nov 30 2013, I select all these o/s transactions plus Nov's transactions - to arrive at a 0.00 unresolved amount ?

  • 0 in reply to Roger L

    Roger L said:
    I select all these o/s transactions plus Nov's transactions - to arrive at a 0.00 unresolved amount ?

    Yes, and before you 'resolve' any November transactions the 'Unresolved' should be zero. - the statement vs. book difference should be explained by the sum of the 'prior' transactions.   If not, there's an error or omission in the accounting data or in the selection of outstanding amounts.  (or... in the bank's record!)

    If I have to go low-tech, I usually:

    Photocopy the last 6 bank statements.  

    Print off the last 12 months' bank transactions from SA50 in date order, excepting corrections.   Showing corrections will drive you mad.   Widen the reference column so that there's room for a note. (12 months, so that you can sort out the 6 month old statement's prior outstanding.)

    Put a line through each matched transaction on *both* the printout and the statement copy, Pencil in the cleared date on the printout, and pencil in the document date on the statement copy.   Otherwise it's easy to miss an amount that's recorded twice)

    Or do the electronic equivalent, export the (correction / Adjustments OFF !!) a full year's bank transaction list to Excel / LibreOffice, and record the cleared date, then highlight the amount cell with green for each cell.  

    Access to online banking is of course a bonus, it sounds like you won't have that.

    If you take the exported transactions from SA, and to the right of the exported data titles put Statement start and end dates across the top (one each for withdrawal and deposit), then fill 'if' statements in columns to the right of the exported data that will sort the data into columns by clearing date, so you can then check the total cleared debit and credit amounts against each statement's deposit / credit amounts.

    Once you're done you will be able to see exactly which transactions need to be put in, and if there are any entry errors. 

    It's possible there was an error way in the distant past, and the book balance has been wrong for a long time.

    I hope that helps, 

    Randy

  • 0 in reply to RandyW

    cool, thanks Randy  

    so once I have a list of missing transactions (assuming there are no duplicates), do I just recorded them on the first day of the fiscal year (Nov 1, 2012) that I can ?

    using some misc income and expense accounts as required ?

    or do I reset the bank rec, and add them as outstanding transactions as part of the first reconciliation ?

    and if I reset the bank rec, will the existing sage transactions after Nov 1, 2012 be available to clear ?

Reply
  • 0 in reply to RandyW

    cool, thanks Randy  

    so once I have a list of missing transactions (assuming there are no duplicates), do I just recorded them on the first day of the fiscal year (Nov 1, 2012) that I can ?

    using some misc income and expense accounts as required ?

    or do I reset the bank rec, and add them as outstanding transactions as part of the first reconciliation ?

    and if I reset the bank rec, will the existing sage transactions after Nov 1, 2012 be available to clear ?

Children
  • 0 in reply to Roger L

    Roger L said:

    once I have a list of missing transactions (assuming there are no duplicates), do I just recorded them on the first day of the fiscal year (Nov 1, 2012) that I can ?

    using some misc income and expense accounts as required ?

    or do I reset the bank rec

    Which one you need to do will depend on what you find - if it's an inconsequential amount, it's probably good enough to record it in the current year.  If it's a large amount, you may want to discuss it with owners or any external accountant.

    I expect you may find that transactions were deleted but the reversal was not marked 'cleared', or duplicate entries were made but ignored when reconciling.  (assuming that prior reconciliations actually did reconcile, and someone didn't just hit 'Post' and dump the difference to bank charges every month).

    Roger L said:
    or do I reset the bank rec, and add them as outstanding transactions as part of the first reconciliation ?

    You cannot add outstanding transactions that weren't entered, so you have to sort it out before the first reconciliation.

    Roger L said:
    if I reset the bank rec, will the existing sage transactions after Nov 1, 2012 be available to clear ?

    I believe so, when you set up reconciliation it should transfer all the transaction data for that account from the first statement date onward, into the reconciliation data table.  But...

    Make a backup, and restore it with another name, before you do anything.  You always want a way to back out of major changes such as clearing the bank reconciliations.   In any case, you should print off all the existing reconciliations (or save them by exporting as PDF)