Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Monday 14 July 2014

Sage Line 50 / Sage Instant e-reconcile - it can work well for you

At first sight Sage's e-reconcile option doesn't seem all that attractive. The main reasons for not adopting (in no particular order) are:

  1. Banks normally charge extra for the statement files facility;
  2. e-reconcile doesn't allow the matching of offsetting receipts and payments records in the cashbook;
  3. e-reconcile only allows one-to-many or many-to-one matching - you can't match many-to-many; and,
  4. e-reconcile doesn't allow the matching of bank errors and their correction on the bank statement.
All of the above are easily countered:
  1. Banks normally charge extra for the statement files facility: My post at e-reconcile for everyone in Sage Line 50 / Sage Instant explains how any bank's basic statement export facility (e.g. CSV, xml or Excel format) can be used in e-reconcile. No extra cost. 
  2. e-reconcile doesn't allow the matching of offsetting receipts and payments records in the cashbook: OK, take the receipts and payments you need to clear against each other (because they'll never appear on a bank statement and you don't want them to be listed as outstanding forever) and make sure the net total is zero (say Receipts = £1,000 and Payments = £1,000. Net = Zero). When matching a single payment include the extra payment transactions totalling £1,000 and, when prompted with "Match though totals differ?", click on Yes. Then match a single receipt and include the extra receipt transactions totalling £1,000 and, when prompted with "Match though totals differ?", click on Yes. The two "matching errors" net to zero and everything will be OK.
  3. e-reconcile only allows one-to-many or many-to-one matching - you can't match many-to-many: This scenario requires a response similar to 2 above. Higlight the multiple statement items and multiple cashbook items you want to reconcile and make sure the totals are the same. Then de-select all but one statement item and only one cashbook item (noting their transaction numbers for future reference) and match them clicking on Yes when prompted with "Match though totals differ?". Then select the bank statement and cashbook items whose transaction numbers you noted earlier and match them in the same way. The two "matching errors" net to zero and everything will be OK.
  4. e-reconcile doesn't allow the matching of bank errors and their correction on the bank statement: To deal with errors on the bank statement (and their corrections) that never appear in the cashbook - post them to the cashbook as soon as the initial error appears on the bank statement. Say erroneous charges of £10 were applied to your account. Enter a journal for £10 Dr (erroneous charge by bank) and £10 Cr (erroneous charge correction awaited from bank) and code both sides to the bank account code. Then reconcile the erroneous charge entry with the bank statement leaving you with an outstanding receipt for £10 (the awaited correction on the bank statement) to reconcile when the correction is actioned by the bank.
e-reconcile has so many advantages, not least the auto-reconcile features and being able to sort both sets of transactions by amount to facilitate the easy identification of matches. If you haven't tried it before why not put a month's bank transactions into the practice company and activate e-reconcile, import a bank statement (remember 1 above) and give it a try. Most people don't go back to manual if they try it.


6 comments:

  1. Hi would it be possible to share the format of the csv file you use and which bank plug in to use?

    I use Hsbc which says the e-rconcile function isn't available, but I can download everything from my online banking as a csv file, just need to know how to format it.

    ReplyDelete
    Replies
    1. Hi Chris,

      If you send me contact details via the contact page on the Onion Reporting Software site I'll get back to you with more information.

      Delete
    2. My CSV file just has 4 columns: Date; Ref; Amount; and, Balance.

      The format is as mentioned in the post linked to at 1 in the post above. The format is Northern Bank / Danske Bank so that is the plug-in to use. My Excel macro converts the CSV/Excel output from, say, your HSBC into the Northern / Danske format and the plug-in sucks it into Sage e-reconcile.

      Delete
  2. Hi, I have a slightly different issue with e-reconcile, which I was wondering if you have a solution to.

    Have you come across the situation with e-reconcile where you need to match a receipt and payment in the Sage Transactions, with a single receipt in the Bank Transactions? For example, you have a customer receipt to your bank, but a bank charge was deducted from it and it appears on the statement as less than the invoiced amount. In Sage, you record a customer receipt for the full amount and then a bank payment to bank charges, but then you are left with two Sage Transactions (a receipt and a payment) to match with a single receipt on your Bank Transactions.

    Sorry if I have not explained this clearly enough, but I would be happy to clarify if you have any questions.

    Thanks!

    ReplyDelete
    Replies
    1. Hi,

      I think a variation on item 2 in the post above would work.

      Say you have Sage with a receipt for £100 and a payment for £5 matching a statement receipt for £95. You also have another Sage payment for £20 matching a statement payment for £20. You could manually mismatch the £100 Sage receipt with the £95 statement receipt as well as manually mismatching the Sage payments of £5 and £20 with the statement payment of £20. The mismatches are equal and opposite.

      If you don't have a Sage payment available to reconcile in this way I think I'd be inclined to leave the items unreconciled until one came along.

      Would that do it for you or have I not fully understood your situation?

      Delete
    2. Ah yes, that sounds like it would work. Thanks for your time! :)

      Delete