How do you analysis bank accounts when producing accounts?

How do you analysis bank accounts when...

Didn't find your answer?

Hi everyone!

I was talking with someone earlier who said that at the firm he works at they use Sage Accounts Production to analyse and reconcile bank accounts. The firm I work with uses an excel based program which isn't exactly ideal.

I was just wondering what everyone else uses to analyse client bank accounts and how does it work for you?

Replies (20)

Please login or register to join the discussion.

Teignmouth
By Paul Scholes
22nd Nov 2011 23:50

These days....

analysing a bank account is rarely our job, ie the client does it using whatever method they feel works best for them, be it something like Sage, spread sheets or online books.  I know it's a bit idealistic but anyone running their own business should be capable of at least analysing their own expenditure.

Thanks (1)
avatar
By happy
23rd Nov 2011 09:41

Dont do it

Haven't done one for ages we get clients to do it and if they don't do it they employ a bookkeeper.

Thanks (0)
Replying to sarah douglas:
avatar
By loguie668
23rd Nov 2011 18:54

How?

Roughly how many clients do you work with? 90% of our clients just leave in a box of bank statements. They don't do any bookkeeping themselves and would resist any request to do. Many can't afford to hire a bookkeeper. Do you not find that if the client does do the bank account themselves then it still takes a great effort to try and correct what they have done?

Thanks (0)
avatar
By Jimess
23rd Nov 2011 10:15

Client records dictate treatment

It depends on the clients records. Obviously if the client maintains their own bookkeeping package we would work from that and expect the client to carry out the necessary reconciliations. 

In a lot of incomplete records cases we post directly from the bank statements to Iris Accounts Production - that way you automatically get analysis data in the nominal and the Iris posting routine helps you to cross check the bank balance with the statements as you post, so you never get to the end of the bank statements to find that the bank balance is out.  A review of cheque stubs, post year end statements and pay in books enables us to pick up unreconciled items. Keeps the files tidy and everything is easily traceable. 

If the client maintains a cash book and the records are reliable then we would just summarise their cash book put a bank reconciliation and bank account together and post from there.

My biggest nightmare was a client who kept her own version of an Excel spreadsheet to record everything from bank, to cash, paypal, credit card, etc that ran into over 30 columns across the page. It was a horror to balance and the client resisted all suggestions to change. I got fed up of doing the work and not getting paid just reward for the amount of time and effort we put in so last year I charged precisely what was on the time ledger.  The client paid up and went elsewhere - I have to say I don't like losing clients but this one felt good to let go.

Thanks (0)
avatar
By shwinky
23rd Nov 2011 11:29

VT

We use VT Transaction+ or even just VT cash book depending on the need. This can be a fairly time intensive task and these days if the bank statements are required then often asking the client to provide the statements in electronic format is the best solution. A simple excel spreadsheet can then be used to analyse the data in a fraction of the time.

 

 

Thanks (0)
By Tonykelly
23rd Nov 2011 12:08

simplest way

By far the quickest way is to use Excel.

You can get the figures in CSV format which can be read by Excel.

Typing all the figures into Sage, Iris or VT would be far too time consuming.

 

Thanks (0)
Replying to jefflcbba:
avatar
By Jimess
23rd Nov 2011 12:18

Simplest way?

Tonykelly wrote:

By far the quickest way is to use Excel.

You can get the figures in CSV format which can be read by Excel.

Typing all the figures into Sage, Iris or VT would be far too time consuming.

 

Surely that would depend on the client having the data/statements available in CSV format.  Hardly any of my incomplete records jobs would know a CSV file if it hit them!

Thanks (0)
Replying to The Imprudent Accountant:
avatar
By cparker87
23rd Nov 2011 13:09

.

Jimess wrote:

Tonykelly wrote:

By far the quickest way is to use Excel.

You can get the figures in CSV format which can be read by Excel.

Typing all the figures into Sage, Iris or VT would be far too time consuming.

 

Surely that would depend on the client having the data/statements available in CSV format.  Hardly any of my incomplete records jobs would know a CSV file if it hit them!

 

Sit them down and make them do it. Will still save time overall.

Thanks (0)
Replying to The Imprudent Accountant:
By Tonykelly
23rd Nov 2011 13:22

not necessarily

Jimess wrote:

Surely that would depend on the client having the data/statements available in CSV format.  Hardly any of my incomplete records jobs would know a CSV file if it hit them!

If the statements are not supplied in csv format, they can be scanned into Excel using Able2Extract.

Thanks (0)
Teignmouth
By Paul Scholes
23rd Nov 2011 23:47

To expand on previous point

If your clients can't afford a bookkeeper how can they afford to pay you to do the bookkeeping for them?  In other words, if the client turned up with a full set of books each year, how much would your fees drop by?

Assuming your clients dump their box on you at the end of the year then, in my view, proper business records are not being kept and you'd face problems should HMRC visit for a records check.

Have a run through the tool on the following link to see what I mean:

http://www.businesslink.gov.uk/bdotg/action/layer?r.l1=1073858808&topicId=1085697567

With regard to clients keeping their own basic records, we chose the method best suited to their abilities and show them how to do it.  By the way online bookkeeping's great for this.

 

Thanks (0)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
24th Nov 2011 10:34

Interesting topic that's cropped up before

The original question is about which tools. If you've got bookkeeping/accounts software, the bank rec modules should be able to do most of the donkey work for you.

But as we've seen from those who have already posted, not all small businesses have access to good accounts software or know how to use it. For most people, this means a trip into Excel, and if you can get access to a bank data feed, Simon Hurst offered some advice back in 2006 on Using Excel with downloaded bank statements.

This issue has fascinated David Carter for many years, and in 2007 he tackled the subject in the article Bank reconciliation, what's a bank reconciliation? (a complaint from a Dutch software developer Twinfield, who had never heard of anything so quaint).

David's main point is that if you can discipline a small business to use a debit card and electronic payments for their expenditures, you don't really need a reconciliation - you can just produce final accounts using their electronic bank statements.

Software companies - particularly Cloud accounting providers such Twinfield, Xero and KashFlow - are building this facility into their bookkeeping systems. A debate in our Cloud computing forum rounded up current thinking earlier this year in an artice, Are we ready for electronic cashbooks? More recently, I wrote about BankLink, which says that more than 90% of accountants in New Zealand use its electronic bank feed service.

You can follow the full sweep of the previous debates on our Bank reconciliation page.

Thanks (0)
avatar
By lh3f9764bg1g
24th Nov 2011 11:15

Autorec

We use software called Autorec.

You should check out their website;-

http://www.woodswhelan.com/

It is very good value for money (in my opinion).

    Chris.

Thanks (0)
Replying to ShirleyM:
avatar
By BrendanWoods
07th Jun 2012 15:25

AutoRec software for scanning bank and CC statements

lh3f9764bg1g wrote:

We use software called Autorec.

You should check out their website;-

http://www.woodswhelan.com/

It is very good value for money (in my opinion).

    Chris.

Brendan Woods here from WoodsWhelan ... just to let you know that we've changed the company name to OCRex Ltd and our new website is www.ocrex.com 

The woodswhelan website is still online, but only because we still get a lot of traffic to it from forums like this. Note also that there is a much improved version of AutoRec available and you can sign up for a free trial on ocrex.com

Regards,

Brendan

 

 

Thanks (0)
avatar
By sayersdave
24th Nov 2011 12:01

I'm a bookkeeper. For all clients that I work on; I maintain a cashbook on excel, Balance Sheet Control accounts on excel from which a monthly batch to post to Sage is extracted.

I reconcile the bank after each update of the cashbook; I find it far easier on an ongoing basis.

When the records are past to the accountants for the year end reports I very rarely get any requests for clarification. In most cases the only postings I am asked to make are Accruals and Tax journals.

David

Thanks (0)
avatar
By JWB
24th Nov 2011 12:12

VT

Hello

I used to use Excel but have now moved to VT transactions and would never go back to manually reconciling excel spreadsheets to bank statements.

It's just as easy to enter the data into excel as it is in VT and the bank reconciliation is a doddle.

PS I've used quite  few software packages to do bank recs and VT is by far the best.

Give it a try .. it's very cost effective, it will save you lots of time and you can easily export the data to excel if that's the format that the Accountant prefers.

Thanks (0)
avatar
By MartinLevin
24th Nov 2011 12:18

Free ABC Accounts Books

I addressed this problem back in 1979.  I designed and marketed these Accounts Books, and gave each and every client one free.  I've been doing this for over 30 years.  As a result,when we receive:

a) The free ABC Book back, written up by the client,

b) The client's bank statements,

we analyse: the bank, then the cash.  This takes around 1.5 to 2 hrs.  We then have a proven set of summaries to use in Accounts Production.  (Excel today).  As a result, we have the strength of good records, that can sustain a zealous tax-man in his allegations.  The time released, has allowed us to do so many other things.  Including two successfull VAT Appeals, (and looking at Accounting Web).

The price of the books is still £8, and are available via our Website, which so many Practitioners have subscribed to.    

 

Thanks (0)
avatar
By stewartr
24th Nov 2011 12:43

Bank accounts analysis/reconciliation

I have in the past used Sage Final Accounts production and found it very useful for creating working papers ie bank, vat, accruals prepayments, fixed assets schedules and even used it for correspondence although it obviously does not have all the facilities or more sophisticated programs designed for this purpose. The point was that all files, working papers, correspondence and then the final accounts where held under one client file on our computers rather than excel, word etc. You could actually link the working papers to the NL code to ensure the working paper correctly reflected the nl balance. This was quite useful.

Thanks (0)
avatar
By AdShawBPR
24th Nov 2011 16:37

BankLink's pretty neat ....

I've been using BankLink for a few months now (mentioned in John's post above).  This is a very serious piece of kit.  BL takes a secure feed of transaction data from the bank direct to my PC (product is for accountants, not for clients) - no spreadsheets, no csv downloading, etc.  It's then just a matter of assigning the correct N/L code to each transaction and the job's done.  What's more, the system can automatically code up payments/receipts it's seen before (assuming you want it to).  After a few months, most suppliers have been paid at least once, so the coding's 99% done as soon as it hits my PC.  No chasing up the client, job done in seconds for some, reduced possibility of coding errors.  Not right for all my clients (try to use Xero for the others), and not all banks yet signed up (HSBC, RBS, NatWest certainly are with others in the pipeline) but for the right client it's saving me huge chunks of time.

Thanks (1)
avatar
By Jimess
25th Nov 2011 09:45

Client bank records

A lot of the posts above rely on the assumption that the only method of payment used by a client is the bank.  In these days of hard cash flow a lot of clients use various methods of payment for their goods and services.  The bank account and reconciliation is only one part of the story.  Downloading bank statements into a CSV file would only give you the bare bones - you then have to identify what has been paid through the bank, what has been paid in cash and credit card and peice it all together. 

The clients we are talking about here are the very small traders who are already having a hard time keeping their businesses running with all of the red tape they have to deal with.  I do provide advice to them in basic record keeping and at the minimum I insist on weekly cash records balanced to a counted cash balance for cash based businesses and identification of method and date of payment on customer and supplier invoices if they cannot maintain written or computer records themselves. The main point I wish to make is that these people look to us to provide the link between the basic stuff they can do and the stuff that they cannot deal with either because of time constraints or because they lack the abilities, skills or resources themselves to do it.  One client quite succinctly put it - If we (the accountants) did not do it he would not know where to start. For the size of jobs I am probably talking about I would say that the average time we spend on posting up and reconciling banks should not be more than a few hours per client - I don't really see how forcing such clients to use technology they are uncomfortable or unfamiliar with is going to be of benefit to them. At the end of the day it is all about the sort of service you want to provide.

From an accounts prep point of view the analysis work you do at the beginning of the project is a foundation stone for the rest of the accounts to sit on - there are ways of cutting down time - a lot of it comes down to getting to know the client and the way they work. You can also manage the cost to the client by educating staff to carry out the prep work with a consistent and logical approach - for example whilst reviewing supplier invoices pull out creditor information at the same time so that you are not backtracking on your work. 

Thanks (0)
avatar
By Roy Price
25th Nov 2011 09:48

Bank accounts and book keeping

I employ a range of solutions and use the most appropriate to the client's circumstances and wishes.

I can post directly to Digita Accounts pro for a typical incomplete records job.  The programe has a journal styled like a cash book and works very well. 

I provide book keeping services using Quick Books Premier which is an excellent product and has a good bank reconciling routine.  Quick Books can also take direct input from Banks but have not tried it myself.

I will instal quick books for client's to use and provide some training for use; I tend to find most people work from bank statements down loaded from the internet; of course the more sophisticated the client then the more selective one has to be with software.

I would be interested to know whether many people use the auto download facilities for importing bank data.

I do have an Excel cash book that I have designed but have found that it can easily get out of line and needs too much de bugging - I encourage client's away from DIY Excel solutions as they are dangerous in a business environment.  A client can end up spending inordinate amounts of time with these solutions which often takes them away from the important matter of developing their business.

Sorry but I prefer not to use Sage.

Thanks (0)