Google Answers Logo
View Question
 
Q: Bank reconciliations using MS Excel ( Answered,   4 Comments )
Question  
Subject: Bank reconciliations using MS Excel
Category: Business and Money > Accounting
Asked by: ricky1240-ga
List Price: $50.00
Posted: 09 Jan 2003 05:28 PST
Expires: 08 Feb 2003 05:28 PST
Question ID: 139777
I wish to do a bank reconciliation using Excel 2000. I will have a
column of cash book entries and a column of bank statement entries. I
need
a formula or macro/s to compare the two columns. Where the
entries match both entries (inc description) would then be removed to
another worksheet leaving only unreconciled items (all vacant rows to
be removed)
Answer  
Subject: Re: Bank reconciliations using MS Excel
Answered By: jbf777-ga on 10 Jan 2003 11:42 PST
 
IMPORTANT -- PLEASE READ  
This answer is not finished until you're satisfied with it.  If you
choose to rate this answer, please only do so AFTER allowing me the
opportunity to make it satisfactory to you.  Thank you for your
understanding.
===============================================================================

Greetings -

This is actually much more easily handled in Microsoft Access, but if
I were to handle this in Excel, the following is how I would do it:

The way I read your setup, you've got 4 columns: your cash book
entries, your bank statement entries, a cash book description column,
and a bank statement description column.  You need to make a fifth
column.  Label it RECONCILED.  When we're done making the formula,
each cell in this column will denote whether or not the contents of
your cash book entries and bank statement entries match.  So, here's
an example:

   Column A   Column B        Column C        Column D        Column E
1  CASHBOOK   BANKSTATEMENT   CASHDESCRIPT    BANKDESCRIPT   
RECONCILED
2  $400       $400            Bill Payment    Bill Payment    
3  $800       $293            Bill Payment    Bonus Payout    
4  $200       $200            Supplies        Lunch           

As we can see, the cells in row 2 match.  The third and fourth rows
don't.  We need to create a formula [also called "function"] in the
first row of the RECONCILED column [which will go in each cell of that
column] that will compare the cells of columns A and B and columns C
and D [on each row] and determine if they match.  If they match, a
TRUE will be entered in the cell.  If they don't match, FALSE will be
entered.  Please note, the description entries for your cash book and
your bank entries MUST have the same naming scheme in order for this
to work.  For instance, if your cash book description says "Bill
Payment" for when you've paid a bill, but your bank statement
descriptions says "Payment of Bill", then even though conceptually
these two things are the same, the formula will not be able to match
them.

In the first cell of your RECONCILED column, you want to enter the
following formula:

=AND(A2=B2,C2=D2)

This "AND" formula/function works by evaluating up to 30 expressions,
separated by commas [in the above example, we have 2 expressions being
evaluated].  Each expression must be true in order for it to return
[or, "render a calculation of"] TRUE. If one of the expressions is
false, this function will return FALSE.
Once you have that formula entered into that first cell, all you have
to do is "auto-fill" this formula into the rest of the cells in that
column by clicking and holding the bottom right corner of that cell,
and dragging it down into the rest of your cells.  This will
automatically fill the formula in for every row and do the associated
calculations for each row.

Now the first 4 rows of your spreadsheet should look something like
this:

   Column A   Column B        Column C        Column D        Column E
1  CASHBOOK   BANKSTATEMENT   CASHDESCRIPT    BANKDESCRIPT   
RECONCILED
2  $400       $400            Bill Payment    Bill Payment    TRUE
3  $800       $293            Bill Payment    Bonus Payout    FALSE
4  $200       $200            Supplies        Lunch           FALSE
.
.
.

All the way down your spreadsheet, the RECONCILED column should be
filled with TRUE or FALSE as appropriate [manually double-check a few
to make sure it's calculating correctly.

Now, all you have to do is highlight your entire spreadsheet and sort
it by your RECONCILED column [Column E in this example].  All of your
reconciled bank entries will be listed consecutively, and all of your
unreconciled entries will also be listed consecutively.  You can
simply copy and paste any entries you wish into another worksheet.

I hope this was clear.  If you have any questions, please don't
hesitate to ask.

Additional Link:
A few Useful Microsoft Excel Resources
http://www.possibility.com/Lgc/Excel.html

Excel Tutorials by John F. Lacher CPA
http://lacher.com/toc.htm

Search strategy:
See excel help file for more information on this and other functions


jbf777-ga
GA Researcher

Clarification of Answer by jbf777-ga on 10 Jan 2003 11:48 PST
Oops... the system misformatted my answer.  The "RECONCILED" should be
under the Column E heading.  Let my try it again:



IMPORTANT -- PLEASE READ  
This answer is not finished until you're satisfied with it.  If you
choose to rate this answer, please only do so AFTER allowing me the
opportunity to make it satisfactory to you.  Thank you for your
understanding.
===============================================================================

Greetings -

This is actually much more easily handled in Microsoft Access, but if
I were to handle this in Excel, the following is how I would do it:

The way I read your setup, you've got 4 columns: your cash book
entries, your bank statement entries, a cash book description column,
and a bank statement description column.  You need to make a fifth
column.  Label it RECONCILED.  When we're done making the formula,
each cell in this column will denote whether or not the contents of
your cash book entries and bank statement entries match.  So, here's
an example:

   Column A   Column B        Column C        Column D       Column E
1  CASHBOOK   BANKSTATEMENT   CASHDESCRIPT    BANKDESCRIPT  
RECONCILED
2  $400       $400            Bill Payment    Bill Payment    
3  $800       $293            Bill Payment    Bonus Payout    
4  $200       $200            Supplies        Lunch           

As we can see, the cells in row 2 match.  The third and fourth rows
don't.  We need to create a formula [also called "function"] in the
first row of the RECONCILED column [which will go in each cell of that
column] that will compare the cells of columns A and B and columns C
and D [on each row] and determine if they match.  If they match, a
TRUE will be entered in the cell.  If they don't match, FALSE will be
entered.  Please note, the description entries for your cash book and
your bank entries MUST have the same naming scheme in order for this
to work.  For instance, if your cash book description says "Bill
Payment" for when you've paid a bill, but your bank statement
descriptions says "Payment of Bill", then even though conceptually
these two things are the same, the formula will not be able to match
them.

In the first cell of your RECONCILED column, you want to enter the
following formula:

=AND(A2=B2,C2=D2)

This "AND" formula/function works by evaluating up to 30 expressions,
separated by commas [in the above example, we have 2 expressions being
evaluated].  Each expression must be true in order for it to return
[or, "render a calculation of"] TRUE. If one of the expressions is
false, this function will return FALSE.
Once you have that formula entered into that first cell, all you have
to do is "auto-fill" this formula into the rest of the cells in that
column by clicking and holding the bottom right corner of that cell,
and dragging it down into the rest of your cells.  This will
automatically fill the formula in for every row and do the associated
calculations for each row.

Now the first 4 rows of your spreadsheet should look something like
this:

   Column A  Column B       Column C       Column D       Column E
1  CASHBOOK  BANKSTATEMENT  CASHDESCRIPT   BANKDESCRIPT   RECONCILED
2  $400      $400           Bill Payment   Bill Payment   TRUE
3  $800      $293           Bill Payment   Bonus Payout   FALSE
4  $200      $200           Supplies       Lunch          FALSE
.
.
.

All the way down your spreadsheet, the RECONCILED column should be
filled with TRUE or FALSE as appropriate [manually double-check a few
to make sure it's calculating correctly.

Now, all you have to do is highlight your entire spreadsheet and sort
it by your RECONCILED column [Column E in this example].  All of your
reconciled bank entries will be listed consecutively, and all of your
unreconciled entries will also be listed consecutively.  You can
simply copy and paste any entries you wish into another worksheet.

I hope this was clear.  If you have any questions, please don't
hesitate to ask.

Additional Link:
A few Useful Microsoft Excel Resources
http://www.possibility.com/Lgc/Excel.html

Excel Tutorials by John F. Lacher CPA
http://lacher.com/toc.htm

Search strategy:
See excel help file for more information on this and other functions


jbf777-ga
GA Researcher

Clarification of Answer by jbf777-ga on 10 Jan 2003 11:49 PST
Well, this time it did the second spreadsheet example right... so you
know what the first should look like.

Request for Answer Clarification by ricky1240-ga on 11 Jan 2003 00:23 PST
Thanks 

Solution is good so far and will be a help, however it may require a
further preliminary step;

What can I do when, (as is the case) the list of figures are not
sitting side by side, eg. my present problem has circa 8000 entries in
one column and 8000 in another column. the first column is the "paid
or lodged" date and the second is the "bank entry" date. These entries
therefore will not be presented in a side by side fashion to allow the
"function matching" solution you proposed.


Cheers

Clarification of Answer by jbf777-ga on 11 Jan 2003 12:50 PST
Hello -

If I understand you right, you're saying your columns are not next to
each other?  For example, you have one column as column B and one
column as column F?  Columns don't have to be side by side in order
for any formula to work.  For instance in the following formula:

=AND(A5=V4, B2=R12, M7=X7)

Cell A5 is compared to V4 [column A vs. column V], cell B2 is compared
to R12 [column B vs. column R], and M7 is compared to X7 [column M vs.
column X].  If A5=V4 AND B2=R12 AND M7=X7 the function will be
evaluated as True.  You can simply adjust the formula as need-be to
reflect how your worksheet is set up.

Please let me know if you need any additional clarification.  I'm here
to help.

Thanks,

jbf777-ga
GA Researcher
Comments  
Subject: Re: Bank reconciliations using MS Excel
From: rac-ga on 11 Jan 2003 23:17 PST
 
Hi,
Well, the solution which will solve your probem is a simple Excel vba
macro written to compare values in all the rows of different column  ,
and if a matching value is found move them to next sheet and cleanup
the entries in the main sheet.

If you wish, post the sample format of your excel sheet data. I can
help by writing a macro for you.

Thanks,
RAC
Subject: Re: Bank reconciliations using MS Excel
From: ricky1240-ga on 13 Jan 2003 02:24 PST
 
Thanks RAC

I think you understand my prob.

I have set out a short example of my spreadsheet

Nominal ledger Bank Account             Bank Statement
  A        B      C      D        E          F     G         H       I
1/1/03 chq.no.  15455  Wages    -300     1/1/03 Bank Giro  333  
1,250
1/1/03 Bank Giro 332 Receipt     900     2/1/03 Chq no.   15454   
-50
2/1/03 chq.no.  15456  Fuel     -100     3/1/03 chq no.   15455  
-300
3/1/03 Bank Giro 334 Receipt     559     3/1/03 chq no.   15456  
-100
4/1/03 chq no.  15457  Expns     -25     4/1/03 Bank Giro   332   
900

The actual speadsheet extends to just short of 15,000 lines!

((As a temporary solution I have allocated a index column to the two
sections above. I have then amalgamated the two lists vertically (ie.
col I & E combine)
and sorted by a) value, b)ref no. This creates matching pairs one
after the other vertically. I can then enter a "Y" in the next column
to identify matched items, which can then be sorted by "y"s and cut &
pasted elswhere.Although this works it is clearly a fair bit of work
involved and I can visualize a little Macro would churn through this
very simply and quickly.))


I would appreciate if could send me sample macro


Thanks
Subject: Re: Bank reconciliations using MS Excel
From: rac-ga on 13 Jan 2003 11:37 PST
 
Hi,
Thanks for the dataformat and explaining the way you are working now.
Will write a macro in a day and let you know.

Thanks,
RAC
Subject: Re: Bank reconciliations using MS Excel
From: rac-ga on 15 Jan 2003 09:08 PST
 
Hi,
Attached in the following link sample xls file with macro. 
http://rac-ga.tripod.com/input.xls 

 Get the input.xls. The file has 3 sheets.
"Inputbefore" Sheet shows how the sheet with data before processing
will
looklike. "Output" Sheet shows the processed sheet and "Input After"
Sheet shows the sheet after processing run.

How to run the program:
Keep the data to be processed in a excel sheet as in the format of
InputBefore sheet.
First line is column heading and data starts from 2nd line.

1.Open the input.xls
2.Open your data xls file to be processed.
3.In the data xls file from tools menu-->Macro-->macros
4.Select Input!Reconcile macro.
5.Press the run button.
6.Wait until processing Over msgbox is displayed. It may take many
minutes based on the volume of data. The macro will create a new sheet
"Output" and add the processed data in that sheet.

Hope this will solve your problem. Please let me know if you need any
further help.

Thanks,
RAC

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy