Editorial team
AccountingWEB.co.uk
Share this content
0
20
90303

How to use Excel for Bank Reconciliation.

Hi,

I am looking for Tips from my senior colleagues, who using Excel for Bank Rec, and would highly appreciate your comments.

I want to speed up Bank Reconciliation process when using Excel. How can we use Excel effeciently when doing Bank Reconciliation jobs to fine out Differences.

I have spent Hours in the Past to find differences .

Kind Regards.
Rehan Arain

Replies

Please login or register to join the discussion.

avatar
24th Feb 2009 11:30

Bank Recs in Excel
I strongly recommend to see the link http:/controlbancos.blogspot.com/, they have a very good solution.
Best regards, Oscar

Thanks (0)
avatar
25th Jan 2008 22:27

btw, use google translate
Just as an aside, re Martin's post and link to a spanish-language site, I use google translation website (http://www.google.com/translate_t) to render any text or web page in to english from a number of languages. Works very well.

Regards
Mike

Thanks (0)
avatar
25th Jan 2008 17:33

SQL database for reconciliations - Auto Bank Rec
Following up on Alastair's earlier comment, we have used Auto Bank Rec which plugs into a SQL database and can carry out one-to-one and one-to-many matches across hundreds of accounts at once. Unlike Excel, of course, there's a price tag attached but if you need to reconcile lots of transactions quickly, it's a very decent product. There's a demo on their website http://www.autobankrec.com/products_bankrec.htm

Thanks (0)
avatar
04th Dec 2006 10:08

Where are you starting from?
Hi Rehan

As Alastair has said, it depends on the whereabouts of the data. Also, I agree with him that Excel is not a tool that springs immediately to mind for this task.

However, assuming that you have the cash book in Excel already, either because that is what you use, or by importing it from another application (why hasn't this got its own bank rec. facility?), then the simplest way to do a rec would be to construct a pivot table.

Assuming that your references (cheque numbers, etc) are in col. A, the amounts in Col. B, then allocate a column (say C) called 'Cleared'. Create a pivot table with the refs. as rows, the Cleared as column headings, and the amounts as the body. You will need to allocate a referencing scheme to bank side entries, charges, standing orders, etc. and mark these on your statements.

As bank statements come in place a marker in the cleared column (say a 'Y') against the relevant entries. You will also need to insert any bank side entries as you go along.

If you refresh the pivot table all the cleared entries will be in the first column which, assuming that you have incorporated the b/f balance into the data, should agree with the statement. Any uncleared items will show as 'Blank' in the last column, with any stray characters entered by mistake in between!

Of course you will need to extend the source range of the pivot table as the cash book grows.

Thanks (0)
avatar
04th Dec 2006 09:10

its all about matching?
presumably you have a data source for the cashbook transactions and a data source for the bank statements? In which case I would expect you to make extensive use of vlookup.

To my mind the ticking is a two stage process. Firstly you have to make the matches and then you have to review them and decide which you agree with. If you want to add user "ticking" then use of data validation to provide the ticks might sound promising (one of the webdings fonts has ticks).

The other part of the bank rec process is to prepare a statement showing how the difference between cash book and bank statement is explained. This is a reporting exercise, and requires some classification of the reconciling items. For this a pivot table report would seem most promising.

HOWEVER, this is not a good customer for bank reconciliation. This is a process that requires security and control. You would do better to develop a database application.

Thanks (0)
avatar
04th Dec 2006 10:23

hi Richard - pivot table tip
re your pivot comment, if you make the pivot refer to a named range then rather than alter the pivot when data is added, you simply have to make sure the range name covers all of the new data

Thanks (0)
avatar
05th Dec 2006 09:15

Alastair, I agree
I sort of took naming the range for granted; a bad fault for someone supposedly trained in teaching practice!

When using named ranges it's a good idea to add an extra row, colour or shade it (?red) and insert the text 'Insert new rows ABOVE this one' or similar. This should, for all but the very gifted, prevent the data leaking out from the range.

This is primarily with other users in mind, but we've all done it, haven't we?!

Thanks (0)
avatar
15th Dec 2006 16:41

Excel version
Excel 2003 bundled with MS Office Pro SP1

Go to main toolbar >>> Data >> List

Thanks (0)
avatar
16th Dec 2006 00:03

Data List feature
This was a new feature in Excel 2003, you won't see it in earlier versions.
Jim

Thanks (0)
By Rehan1
04th Dec 2006 22:01

Thanks Very Much.
Alistair and Richard Many Many Thanks for your comments.
I would use your recommendations.

Thanks (0)
avatar
15th Dec 2006 13:25

Data 'leakage' from named ranges
Excel will automatically extend a named range as data is added if you use the list function under the data menu.

1. Create your worksheet with the first row as headings
2. Highlight this row and the row below and create a list
3. Create your named ranges on columns within the list

As you add new rows to the list, the named range automatically extends to include the new rows.

The list also carry down any formulas and formats as new rows are added

Using the list ensures your named range always includes all data.

The list also ensures your worksheet is the optimum size as you do not have to have empty rows formatted for future data entry


Thanks (0)
avatar
07th Dec 2006 13:25

Try using bank statement numbers & filters.
Instead of ticking items to your bank statement use that column in your cash book for the bank statement number when it is checked off. Then filter on that column and total the filtered value items. You will be easily able to reconcile each bank statement in turn or a range. The items that have a blank in the checking column will be the outstanding/unmatched entries. It's a simple method but it works! To make it a little more sophisticated add a column for each bank statment closing balance.

Have fun!

Thanks (0)
avatar
07th Dec 2006 14:26

I'd personally would use the "if" formulae and the "auto filter"
I take it you have a column list for bank payments and one for bank receipts going down through your spreadsheet.

Add another two column next to these two headed "Reconciled Y/N" and "Reconciled amount £".

(Abbreviations:- Bank Payments = BP; Bank Receipts = BR; Reconciled Y/N= RYN; Reconciled Amount £ = RA£ below).

Beneath the heading in RA£ type the formulae =if(RYN="","",sum(BR)-sum(BP))

Obviously substitute the abbreviations for you cell references.

Highlight you heading labels and click on the menu of "Data; Auto Filter".

Now once you have your list of bank receipts and bank payments listed on your spreadsheet you can check them off against the bank s/mnt and put a Y in the RYN column. Provided you have a sum total of the RA£ column and add this to your B/fwd balance you will find your bank balance will agree to the bank s/mnt.

You will soon enough find items on the Bank S/mnt that have not been listed in your BR or BP columns in which case you just add them in.

Now to find the items that have been recorded but have not gone through the bank account on the heading label of RYN there will be a small drop down arrow box, click on this and choose show non blanks. This will give you all the items that have not been checked off against the bank s/mnt and you will be able to print this of as viewed.

Thanks (0)
avatar
07th Dec 2006 16:00

Excel Reconciliation Function
I am currently developing a reconciliation function as an "Add-in" for Excel, that should save you many hours if you have to perform reconciliations regularly.

Although Excel is an excellent general tool, it does not provide direct functions to meet all of what we, as accountants, need to do. I often used VLOOKUP alongside each table to find the matching values in the other table being reconciled. I will write a post about this in my blog sometime soon.

A database application might be better suited to your task, as Alastair Harris mentioned, although again, databases are general tools that need to be managed to accomplish a specific task.

My software will work directly in Excel, where many accountants are very comfortable, but will take away much of the manual effort involved in this task. Visit my website, http://moverve.com for more information, and good luck on your progress.

Jim

Thanks (0)
avatar
15th Dec 2006 15:20

Steve
which version of Excel are you using? I have Excel 2000 - can't see the list item you refer to!

Thanks (0)
avatar
20th Dec 2006 13:41

Steve, can you Group dates in a List?
This List feature looks interesting. But if it extends over a blank row, does it cause the Group function to fail? (If you try and Group dates in a pivot table and one record contains a blank row, you get the "Cannot Group This Selection" message.)

Thanks (0)
avatar
By oliv-33
20th May 2010 17:46

Excel for Bank Reconciliation

I found a free here:

rapprochement.xls  on http://utilexcel.tripod.com/

 

 

 

Thanks (0)
avatar
30th Mar 2012 17:13

Excel Tool to Reconcile Bank to Books

I wrote a very simple but powerful tool to reconcile statements.  It is available for free at http://excelonfire.com.  There is a 1:30 video that quickly shows how to use it.  It's virus free.  It's actually downloaded at http://download.com which scans everything.

Thanks (0)
avatar
By AlBear
19th Mar 2013 14:38

Bank Reconciliation

There is a low cost Excel Add-in that finds groups of numbers that add up to a target sumfrom SumMatch.com they also sell ReconciliationWizard which is a comprehensive Reconciliation Software that can completely automate reconciliation tasks or just give hints to reconcile manually. Trial versions are available.

 

 

Thanks (0)
avatar
By l_manju
28th May 2014 13:11

Simple Excel tool to reconcile

There is one simple, flexible tool

check this, watch demo video

http://www.xlvba.net/RECXLVB11001.php

Thanks (0)