Sage instant bank rec - is it possible to clear past transactions and reconcile from a given month?

Sage instant bank rec - is it possible to clear...

Didn't find your answer?

Hi,

I am helping a friend to catch up on almost a year's worth of bank payment/receipt information in Sage. The sales and purchases transactions have been entered as they went through the year but they did not attempt any bank entries. they have an awful lot of small value entires, around 200 a month.

I'm fairly new to Sage though I'm an industry accountant and have used ERP systems so I'm reasonably happy with what I'm doing. I am at a stage where the Sage bank account (1210 in this case) for each month end since April agrees exactly to the relevent bank statement so I know they reconcile in real terms.

It seems as though there has never been a "Sage" reconciliation done, even at end of March 2014 when the books were given to the Accountant (maybe Accountant did their own manual recon outside of sage?). I believe they moved on to Sage during the preceding accounting year. There are quite a few hundred transactions in the top section, going back to 2013, which have not been matched and moved to the bottom.

When I go back to the first month of this year, April 2014, I can see a balance on 1210 that agrees to the bank statement - but in the recon screen in sage, there is a "balance per books"  bottom left of the box that shows a different number. I was expecting that to be the same as the balance I can see in Sage for 1210 when I look at the CoA - because surely that's the balance per books? I'm not sure why the bottom-left box on the recon screen would be different to a reported balance elsewhere in Sage?

Also, Is there any facility in Sage Instant that will allow you mass-clear some or all those unmatched transactions and effectively create an opening balance at 1/4/14 which matches the closing balance in the published 2013/2014 accounts?

I would like to be able to have Sage bank recons for the 2014/2015 year, and am happy to build them one by one from end of April, but am unsure how to "get rid" of everything before - it looks to me as though I would have to manually click on every single item and click "match"? - which would then possibly leave an unreconciled balance anyway, even though I know I have the bank account reconciled to the current Sage bank balance.

Will take a month of Sundays (or more) !

Is there any way around it?

Replies (7)

Please login or register to join the discussion.

By Moonbeam
21st Feb 2015 19:25

Sage Bank Rec problems

You can reconcile all items in one go, by pressing swap and match and reconcile, but don't do this yet.

I have over the years inherited shoddy data from untrained users and had to reconcile the bank account manually in order to get only the unreconciled items showing in Sage.

It's simply a matter of exporting the bank current account nominal activity report to Excel, checking you've got the right balance and then reconciling it with the bank statements. My method of reconciling in Excel is to keep moving the reconciled items to the top of the screen so that I have a rolling balance agreeing to the bank statements, while still maintaining the Sage balance in another column to ensure there are no corruptions.

I recall 20 hours work to reconcile a company's bank statement for the year a few years ago, but it was worth it because it revealed - as I already suspected - that the payroll hadn't accounted for all payments to staff and lots more errors of non input etc. Doing the reconciliation however you do it outside Sage, you can then highlight the unreconciled items, swap and move the rest to be reconciled.

Before you spend time on this ask yourself if there are lots of questionable items elsewhere that make you doubt both the payroll and other items. If there aren't then roughly work out the reconciling items at the end of the last month of the 2014 accounts, and move everything else to the reconcile area.

Very small errors aren't worth your time and effort to find. Much better to do a very rough reconciliation and then go forward doing things properly.

Have a go in demo data to see what I mean.

Thanks (0)
Replying to ireallyshouldknowthisbut:
avatar
By Tilly58
22nd Feb 2015 08:11

Thanks very much for the advice, i will try that.

To be fair, that's how I've always done bank recons in my previous professional life. I just wasn't quite sure how I would then get all that work reflected back into Sage via this reconciliation screen.

I still don't quite understand what the relationship is between this "balance per books" box and the balance I can see on 1210 - it feels to me as though that should always be the same anyway, regardless of whether the account is reconciled or not, as it's based on the same figures?

When I downloaded the Sage bank data into excel, I could see that the "balance per books" was basically the net balance of everything that had gone through the bank account since it was opened in Sage around 18 months ago.

The Sage 1210 balance in the TB and balance sheet is a different number - but it's the Sage balance on 1210 in the TB that matches the latest bank statements - so if you didn't know about the bank recon screen and the "balance per books" box, you would say all is fine.

So if I were doing this manually, without a Sage download,  I'd easily be able to produce a reconciled bank statement on paper, showing the sage TB balance and the bank balances to be the same.

I'm trying to figure out why the net balance of all the entries through the bank in Sage (and there are so many!)  isn't the same as the latest balance on the bank account in Sage?

In my simple mind, if you made an entry that affected account 1210, I'd expect both the "balance per books" number and the balance on 1210 to change at the same time and therefore always show the same balance.

Are there any types of activities, adjustments, corrections, deletions, that could affect one of those reported balances and not the other? I'm wondering if maybe there was a year end adjustment or correction that only affected the balance as it's reported in the TB?

Having said that, the best way to find out is to just do it!    :D

 

Thanks (0)
By Moonbeam
22nd Feb 2015 11:08

I find the Sage Bank Rec full of puzzling totals

As an old stager who's used Sage for most of the years it's been in existence, as more and more sophistication has crept in I've kept to my old ways.

Your differences in balances may be to do with the date you may have set up as "today's date".

A client demonstrated to me a few years ago the way Sage had advised a bank rec should be done, and I nodded off half way through her demo. The fact is, she does an excellent job reconciling her bank account. She could do it in half the time if she ignored half the balances shown on the rec screen, but to be fair doesn't have my accounting knowledge or confidence.

When training people on Sage I tell them what to ignore and they have no problem following my system. They are all with-it people. Anyone who isn't would make a mess of this no matter how well you trained them.

I will just take you through a typical reconciliation using my own accounts:

1. Click on the bank and the reconcile button. You see a statement summary screen. I usually ignore this completely and just click the OK button. I am now looking at the Reconcile screen.

Book Balance at the LHS bottom is the total of the bank account in Sage TB.

At the top of the 2nd half of the screen it says last reconciled balance and that is the same as the Book Balance as my bank account is fully reconciled. It wouldn't be the same if I had some unreconciled items of course. The last reconciled balance should agree with a bank statement.

As I'm going through moving stuff from the top of the screen to the bottom reconciling screen I regularly check the running total of this second screen agrees to the bank statement.

I don't match more than about 20 items before hitting the reconcile button.

There are then 3 questions I've never understood once you press that button, and I can't be bothered to work out what they mean. So I tell everyone to press the button in the middle and that's that.

My bank accounts are always perfectly reconciled and my trainees seem to have no problems with my training.

If you're correcting or deleting an entry after a reconciliation has been done you should always check that your bank rec is OK, as it may need further work.

Don't forget - there will be errors in the data you've inherited.

If you would like me to talk you through it a bit more just pm me!

Thanks (0)
Replying to Tax Dragon:
avatar
By Tilly58
22nd Feb 2015 14:01

 

 

I suppose the thing that's puzzling me is that before I even start, the book balance at the bottom LH screen is NOT actually the balance on the Sage TB and I don't know how that's come about.

So I have a different number in every box!

I like a challenge, and your comments have helped enormously so armed with that advice, I'll go off and see if I can find where it's gone adrift!

Thanks for your help!

 

Thanks (0)
avatar
By Tilly58
28th Feb 2015 18:43

Hi to moonbeam,

Just a little update.

 

The "swap" button came in really handy for clearing everything up to the start of the year - the bank account had been reconciled outside of sage and once everything from the previous year had been "swapped and matched" by me, it reconciled and gave me my opening balance to work with.

I really thought I was going to have to individually "match" every item, so thanks for explaining that function!

When I did a full reconciliation in excel of April (it was way out to start with) I realised why the "balance per books" in the bottom left was different to the sage balance in the tb - There were some transactions I posted from the bank that had defaulted to the day I was doing the entry and I hadn't realised that I'd have to put the correct relevent date in.

They've all been corrected now, I know what to look out for and am now chugging my way through the last few months, reconciling as I go.

Thanks a lot for your help!  :D

 

Thanks (0)
By Moonbeam
28th Feb 2015 19:47

I'm pleased it worked out

I do know how awful it is to take over an imperfect set of bookkeeping on a system you're not used to, but I had a feeling that once you got started your expertise would take over!

It's nice to know it worked out for you. Now you've got the hang of bank rec on Sage you can sort out all sorts of mistakes other people have made!!

Thanks (0)
avatar
By hinakshi
10th Aug 2015 08:42

reconciliation
Moonbeam
I have jus seen ur post re reconciliation
I am in a similar position
I have taken over sage accounts which were not set properly - its my end of year and a nightmare to reconcile
All entries were posted to the one sage bank account 1200
However entries on the bank statement does not match the entries on sage
How do you reconcile - ive downloaded both bank statement and sage onto excel and reconciling manually each entry
Am i doing correctly

Thanks (0)