Finance 3.0 - Social Network for Finance

Smart financial thinking

Bank Reconciliation in Excel with automation / macros

Dear All,
Is it possible to have a bank reconciliation format/template in excel with macros or with advance keys, so I can reduce my huge taks. Like only dowloading bank statment in excel and comparing with bank book in excel with short span of time and update all items/ heading in bank reconciliation like : cheque issued but not cleared : unaccounted bank charges etc. A ready made solution will be highly appreciated or any website having free templates for bank recon is also welcom.

Thanks and regards

Rajesh

Views: 2729

▶ Reply to This

Replies to This Discussion

Permalink Reply by mohd arsalan on June 24, 2010 at 4:26pm
Post examples of your files for reference
Permalink Reply by Rajesh Eknath Parsekar on June 25, 2010 at 1:19am
Please find attached herewith file for your basic idea for bank reconciliation.
I have mentioned schedules in Front sheet only. Instead of that if you can create individual worksheet for those schedules would be great help. Because in BRS generally there is huge entries of bank books and bank statements in month.
I hope I have given you exact impressin of how bank reconciliation works in smaller format.
Generally there is requirement of finding out unmatching entries by comparing two books with schedules of adding or substracting entries with amounts from Bank Book , where taking bank book as a base.

Thanks and regards - Rajesh
Attachments:
Permalink Reply by Yasir Bilal on June 25, 2010 at 3:35am
I feel there are some logical errors in your bank book and bank statement .i.e. interest recieved should be debited to bank book and credited in bank statement and similarly entry of 1,500,000 od debtors is also having this logical error hence your file is incomplete and not correct to be worked upon.

Sory but thats what i feel. please attach complete information i will try to do it for you.
Another question of mine is that does bank book means bank ledger?

Regards.
Permalink Reply by Rajesh Eknath Parsekar on June 25, 2010 at 4:46am
Dear Yasir,
Actually this is artificial bank book I have created, you treat as a bank ledger too.
Basic point is there are receipts and payment matched and unmatched which need to be figure out by creating seperate worksheet with individual schedule mentioned as per sample workbook.
Data entered in it is all artificial. My point is if we have such a huge bank book/ pass book with huge entries and bank statment also , then how could we reduce stock, instead of finding cheq no and similar entries either by Find or by Vlookup instead need a small process macro application who can run for segregating data from both the workshee and mismatch can be pointed out in different worksheet is great help.
Later we can decorate or align it according to users requirement. But this basic work will help to get the clear idea about how to apply in large scale reconciliation. Even in debtors reconciliation / stock reconciliation etc.
I would appreciate if you could develope this work book according to your point of view and can give me solution. So I will change my requirement accordingly while inputing data in bank book and bank statement.
Permalink Reply by Yasir Bilal on June 25, 2010 at 6:02am
Dear Soon i will contact u with solution

Need some time to do so because me too busy in official work.

Hponing to find some solution soon
Permalink Reply by Rajesh Eknath Parsekar on June 25, 2010 at 6:11am
No problem ! infact I should thanks to you for helping me to find out one time solution for it.
Take your own time.
Thanks and regards - Rajesh
Permalink Reply by Amby Sabio on July 7, 2010 at 8:03pm
Hi Rajesh,

I am just new to this site and i find it very helpful because of the knowledge sharing of the member. Hope you can accept me, furhter would be generous enough to email me attahced excel file at amboy_sabio@yahoo.com.

Regards,

Amby
Permalink Reply by Rajesh Eknath Parsekar on July 7, 2010 at 11:20pm
Dear Amboy,
Thanks for showing your interest. And WELCOME.Really this website is worth surfing. Because every single day you gain variety of knowledge from it, even though your/our profile is to the limited work area. But by these sort of discussions we can gain good knowledge with other people. Who are expert in this field. For me I feel even though I have a more than 15 years of experience in accounts filed, I think I know only 1% out of it.
Sending in your given mail file for your perusal.
Even you can retrive from this site. If you are logged in then you can view this file.
I hope you will give me solution. Irrespective of format of BRS.
Basically matching entries of both the bank and bank book with receipts and withdrawals, is the basic task and to take out all the unaccounted/excess entries from both the side in schedule or in separate sheets.
Permalink Reply by rajesh manjrekar on June 27, 2010 at 11:43pm
Rajesh,

My name is Rajesh Manjrekar, i am a Finance professor, are you from mumbai, because if you are call me immediately on 932-304-5443
Permalink Reply by Rajesh Eknath Parsekar on June 28, 2010 at 12:59am
Nice talking to you and showing interest for the solution.
My email id is rajeshparsekar1972@hotmail.com - Thanks a lot
Permalink Reply by nor2008 on June 29, 2010 at 1:41am
its anice work book there is different format for bank reconcalation


but can you give aformat for reconcaltion for inventory , share or securutes with custodum
Permalink Reply by Kamran Ahmed on June 29, 2010 at 5:51am
Please give me the exact Bank book i am going to automate it in VBA for the unlimited data plus auto formatting.

RSS

Create Excel dashboards quickly with Plug-N-Play reports.

Continuing Education

Accounting
Banking Regulations
Certification Training
Commercial Bank Management
Corporate Finance & Valuation
Treasury Management
Credit Risk Management
Financial Derivatives
Financial Institution Management
Financial Markets
Financial Modeling
Financial Planning
Market Risk Management
Operational Risk Management
Operations & Compliance
Quantitative Analysis
Strategic Financial Management

Connect With Us

Newsletter Linkedin RSS Facebook Widgets Twitter

© 2012   Created by Finance 3.0.

Badges  |  Report an Issue  |  Terms of Service

Sign in to chat!