Bank Reconciliation

Ezyrecon Bank Reconciliation Overview

2011-03-24T21:17:00.000-07:00

Ezyrecon can

1a). Reconcile from the general ledger or control account. The system can accept additional file to support some of the lump sum entries in the ledger for deposits and disbursements. For example, in the payroll account, the entry may be for the total net payroll only. This entry is supported by another spreadsheet coming from the payroll provider. So, the disbursement in the book is supported by a check register coming from another source (e.g. ADP Payroll register). This is acceptable for Ezyrecon. You can have support files that are outside of your accounting system.

1b. Prepare the final report, the AICPA suggested two column bank reconciliation, from unadjusted to reconciled book and bank balances. This report is on the tab called "Main". All supporting working papers for the reconciliation can be printed on the fly.

1c. Comply to Sarbanes-Oxley Act, which is mandatory for publicly listed companies. There is an adequate audit trail to support all the reconciling items and items that were matched in total against the book or vice versa. For example, the Human Genome Sciences, Inc. (www.hgsi.com) , a publicly listed company is using Ezyrecon for their bank reconciliation module. However, they are using SAP for their accounting system.

1) It can reconcile from raw data of the book and bank. No formatting is necessary, unless, it is for credit card transactions. Then, we may need to modify the interface to accept the data.

2) It can monitor reconciling items from previous month and traced its subsequent recording. For example, an unrecorded manual payroll check dated March 31 was issued and cleared by the bank in March 31. Since, it is a payroll check, it is recorded in batches. The next payroll batch will be in April. In March the system will show the check as an unrecorded check, part of reconciling item. In April, at the time it was recorded the system will show it as an adjustment to clear the March reconciling item- it will no longer be a reconciling item in April.

3) It can accept consolidated deposits, provided there is a spreadsheet that details the actual deposits. For example, the Head office will record the collections from 25 stores as one lump sum entry per store per month. So, in the books, the deposits will be comprised of 25 lines of deposits representing the total deposits for the month. However, there is a spreadsheet that details the daily collection per store, per day, to support the entry. Ezyrecon is designed to accept this kind of data. Ezyrecon will look at the GL to control the deposits but will look at the spreadsheet in making the comparison from the book to the bank.

4) It can create the journal entry complete with individual explanation to book the reconciling item by the detail and by cost center. You will need to enter the chart of accounts that is normally affected by the reconciling items (i.e. merchant fee, bank charge, cash shortage and overage, interest payment, loan payment, etc.) to be able to do this. In the journal entry, the level of details that will be provided will include the check number and payee, if a check number is involved.

5) It can monitor the sweep account, if any. It can correctly present the balance of the sweep account as part of the bank reconciling items.

6) It can match the ach debit payment to a dummy check number. For example, some companies will record an ach debit as a dummy check to remove the vendor invoice from AP. The system can look for the dummy check using the amount, FIFO.

7) You have the option to tag manually the uncleared items to remove them from reconciling items. For example, you can put tag “1” on three book deposits that when added together will to two bank deposits with a tag of “1”.

8) Reconciling items can be presented as either book or bank error. For example, a check difference can be a bank error. Initially, the system assumes that the bank is correct. Nevertheless, you can always change the presentation to a bank error, if needed.

9) For your credit card reconciliation, we are assuming that you are reconciling it by store. We can provide a consolidated report for the whole store reconciliation, if needed. ( this will be done separately for a fee).

10) Here is our understanding of the credit card reconciliation. We are assuming that daily, the store individual terminal is reconciled to the processor’s report. Thus, at month-end the processor’s report will be the same as the books. We can then use the processor’s report, to trace the deposits to the bank. In order to make the comparison easy, you may want to suggest to your credit card processor to remit the gross amount, not net of the merchant fees. Then, charge you separately from the charge back and merchant fees. If you do this, the recon will be easy. This is due to the fact the gross receipts will tie into the bank deposits.

11) Ezyrecon customer support is free for three months. Normally, after three runs of reconciliation, you will get used to it. You can then opt to pay the separate customer support.

A comment from an accountant consultant

2009-10-21T16:58:00.000-07:00

This is a new customer, a consultant, just signed in for a test a few weeks ago, please read his comment

Thanks. I have our biggest recon almost done, same issue only one check that lists as OS and should be an adjustment. I did have to tag quite a few ACH’s re on book we use several dummy checks to match one bank ACH. Also, when I tagged mismatched totals on purpose to force a diff, it seemed to throw the whole thing out of balance so I started over a few times. Have it down pretty well now and love it- you’ve done an amazing job of trying to cover all the different variables and I know it’s next to impossible. I’m looking forward to talking to Agustin and will email and/or call you later. I’ll show the big recon to our GM when I’ve tested it and know it will recomputed in a couple minutes. What I might do is figure out a way to buy it directly from you (for my consulting business), then have (my client) buy it from me as my first customer under our new agreement ( when we have it in place), if that works for you.

I really can be selling at least a couple of these a week directly with training fees, and a whole heck of a lot more using the internet and direct marketing via regular and email. If I do that, naturally I’d approve everything through you first. I have a ton of marketing/training programs I am really looking forward to putting to good use with Ezyrecon if you’re open to it; I think we could all make a lot of money and help a lot of companies while we’re doing it. Thanks again for everything so far.

Mike

56,000 checks to reconcile

2009-08-21T16:55:00.000-07:00

This customer, type an amount on the date column on the book data. This error created an overflow error. She requested help from us. She reconciles about 56,000 checks. After a careful review we noted that the error was on her book data. An amount was entered on a date column. We pointed the error to her and ask her to re-run the program. We checked with her to make sure it is ok. This is her reply

To: "Conrad Natac" Date: Friday, August 21, 2009, 10:04 AM

It worked!! Thanks and don’t work too much.

Accounting Manager
Stream Energy

Goodwill Industries comment

2008-10-17T13:14:00.001-07:00

FYI Conrad I forwarded your contact information to our auditors and highly recommended you for their clients.

Constandinos Saketos
Accounting Manager, A.V.P.
Goodwill Industries of Greater NY and Northern NJ, Inc.

Rolling the August to November

2008-01-08T22:31:00.000-08:00

This customer was behind on his reconciliation. His last recon was in June. So, using EzyRecon we started his July- we helped him set up all the parameters in July. Then, we left him to do the August on his own. Later, we asked him, if he was able to do the September too. Apparently, he did not only do, September on his own but rolled it to November too. Down below is his reply. One note, the EzyRecon will work in Vista, you just need to change the security settings in Office 2007.

Conrad,
Hope you are having a nice day.
Yes, I was able to do it; in fact right thru November. In working with the program( I was at home) it does not function on Vista; no problem, I put it on my Laptop ( XP) and rolled along. When you finish a month and initialize for the subsequent month it retains the old bank and book files which was problematic at first until I remembered your instructions on removing them. I didn’t see anything in the instructions on this. Absolutely a great program!!! Let me know if you have any other time savers or if you update EZY RECON.
Thanks again

John F McAndrew
BettcherLLC

City of Hawthorne

2007-02-22T11:22:00.000-08:00

I called the accountant of City of Hawthorne yesterday 02/21/2007. She said that their general ledger is not yet ready for uploading. I told her that once her gl can be downloaded, her bank reconciliation is done in just a few minutes.

One hour flat

2006-12-15T11:37:00.000-08:00

that includes collecting 6 months of pertinent data to start the bank rec process. hey it takes about 20 minutes to finish a chilled san miguel, right? (who says you can't enjoy a li'l brewsky with EZY RECON?) - just kidding.

one hour compared to over 85 hours is awesome.

salamat

gary

Suggestion

2006-11-22T14:18:00.000-08:00

Conrad,

Thanks for your email. I haven't forgotten about you either.

It sounds like your proposed upgrade would be able to handle the situation whereby there is a batch code for A/P checks and a different batch code for payroll checks.

I have another thought, I don't know if you may have considered this yet...it involves using the Solver Add-on in Excel. Let's say after running the reconciliation, there are a hundred and fifty reconciling items originating from the books and forty from the bank (just for sake of example) and lets say that you know that most of these book items represent sales (checks and cash) on the books which have been grouped together to make one daily deposit per day to the bank. And let's say that you know that the person compiling the deposits is very accurate but very busy and sometimes she only makes two or three deposits per week and sometimes up to a week late...would it be possible to incorporate the Excel Solver into your program such that it selects groups of reconciling items from the books (within a two week date range preceding a suspected bank item representing the total of the book items, let's say) and attempts various groupings of those items to try to solve for the exact amount of a bank reconciling item. And that it would do this for all bank items. This "extra step" would result in the knowledge that any remaining unmatched items represent entirely unbooked items as opposed to items which have been booked but at the wrong amounts.

I hope what I've laid out is clear and makes sense.

The product you have created addresses a need I have been thinking about for a number of years now, and if it could do what I have proposed, I think you could dominate this market niche and make a lot of money.

In a nutshell, I think the product should be able to do it's thing without so much "scrubbing" of the data beforehand. If one is going to do all that "scrubbing" beforehand, he might as well dive into doing the reconciliation "by hand" (using Excel as a "scratchpad") and could probably complete the rec, with a minimum of reconciling items, in as much time as it takes to scrub the data, run the program, interpret the results, re-scrub data and re-run program if necessary, and segregate the reconciling items into "non-booked" vs. "incorrectly-booked", all of which he would do as he goes along doing the manual reconciliation.

What do you think?

Mike

Bank Reconciliation demo with City of Hawthorne

2006-11-22T14:15:00.000-08:00

Nov 9, 2006 – I met with the accountants from City of Hawthorne, I demonstrated to them that with EzyRecon, they will be able to do their bank reconciliation in minutes. One of their big problem in doing their manual bank reconciliation, is how to find out the other book entries that needs to be adjusted. I showed them during the demo that the system can easily segregate those book entries that are incorrect. I showed them how the system can reconcile using raw data of book and bank files. During the presentation the system was able to reconcile the sample data of 2,000 checks in less than 5 minutes. The system is able to detect those bank checks showing as zero check numbers and misposted check numbers.

Their bank files can be downloaded directly from the bank . However, their general ledger is not yet ready to be downloaded. We are still awaiting from their IT department to make the general ledger as a txt file. I emphasized to them that once their general ledger is downloaded as text file then their reconciliation is done.

Great Service

2006-06-09T08:38:00.000-07:00

Paul Gardner wrote:
Conrad, thanks so much for your help. I have reviewed your mapping and understand it correctly. The column you set up for book debits makes sense. I have copied the control total pages and will use them going forward. I deleted the old system and used your new version. It runs much faster, saves faster, and I like it. The one question I have, is now that we have a good run on May, can I just keep the system in the Excel folder I am running off without having to change the name of the system each month? I assume that I can keep it as BankReconSD and just name the input files with the correct names and then change the control portion to agee to the new monthly names for that period. So for June, I would use JuneBank2006.xls and Junechecks2006.xls but keep the system as BankReconSD. I hope I don't have to call you too many more times, but thank you for your patience and help. You provided great service!!!Thanks, Steve.

An appreciation for the design

2006-05-24T10:05:00.000-07:00

Hi Conrad!

Thanks for sending the latest version... I certainly appreciate it!

My day has turned into a very hectic one...by chance, would you available to speak on Sunday, May 21st? I'm coming into the office then, and would have more time available to speak... If you're not available on Sunday, I certainly understand, and I'll just call you on Monday ...

In the meantime, I have a couple questions ...

My company has a very unique and complex bank reconciliation process...due to sweep accounts (all our accounts, except for our operating and line of credit account, are $0.00 balance accounts)...our operating account funds 2 separate disbursement accounts, and we have a daily sweep to our line of credit account from our operating account ... we also have inter-company accounts and a main lockbox account, along with 4 subsidiary lockboxes (our incoming deposits post to these)... so, setting up a bank reconciliation process using your program would take a great deal of analysis on my part...and, would take a bit of time... but, I'm hoping (and praying) I could make it work ...

my question to you is this ... until such time I could set this up, does your program allow for just doing a check reconciliation...meaning downloading a "checks written" file, and a "checks cleared" file, and producing an "oustanding check list"...although this is not a full bank reconciliation process, it would significantly improve our current process...

Your program is exactly what I've been searching for ...using raw data and downloading the files...I applaud the concept and design of your product! KUDOS!

By the way, why is the macro "disabled"...I'm just curious...

And, did you personally design and code this program? I'm just curious (again) whether you could perhaps customize this program for us (if needed)... and, what you would charge?

Thanks again!

Kelly

Comments from users of Ezyrecon bank reconcilation (www.ezyrecon.com)

2006-05-05T11:47:00.000-07:00

Conrad, this note is from Steve at the Village Sports Den in Salt Lake. I want to thank you for the assistance and patience you have provided in helping us set up the EZYRECON bank reconciliation program. It is exactly what we needed to match our bank activity to our book activity and create an accurate listing of outstanding items. A job which formerly required a good portion of a day now takes only a few minutes. If I have any problems with sending the praises for your product to your website blog, I will let you know. Thanks again for your help.
Please provide us with the new serial number for our product and we look forward to your support in the future. We will mail your payment tomorrow.

Did a reconciliation on a year of transactions

2006-03-23T20:42:00.000-08:00

We have a customer who did not do their bank reconciliation for a year. At year end, they were cramming to produce their bank reconciliation for the external auditors. The external auditor will not sign on the audit report, unless the Company can reconcile the general account. The Finance Manager was looking for some easy and quick way to reconcile their general account. So, we recommended to him the following:

1) to download from their general ledger the whole year transactions of the bank account to be reconciled.

2) Next, they dowloaded from their bank the last six months of bank transactions, the other six months they copied to Excel from the actual bank statements.

3) Using EzyRecon, we processed and reconciled the whole year of transactions- the whole processing took less than two minutes. Actually, the Finance Officer gave the two files to us to use and took a coffee break. When he returned, he was amazed that it was done. You are done?, is the first question that came from his mouth in total disbelief, when we gave him the initial reconciliation for his review. Initial because, he still needs to review the reconciling items.

The reconciliations produced a two column AICPA format bank reconciliation from unadjusted to adjusted book and bank balances. There are about 10 reconciling items that they need to review and verify. It took them almost an hour to resolve the reconciling items.

Among others, the reconciling items were comprised of overstated checks, understated checks, unrecorded checks; unrecorded interest and bank charges- all these reconciling items were fully documented in the EzyRecon working papers. Oh, there are also, some typing errors when they transferred the first 6 months bank statement into Excel- the bank can allow them to download only 6 months of prior transactions.

So, in less than 2 hours a final accurate bank reconciliation was produced for the external auditors. The final reconciliation reflects the accurate outstanding checks and deposit-in-transit as of December 31, 2005.

The Finance Officer looks good to the President for delivering what was needed from his department. Also, we look good to the client for delivering what we said and promised we can do.

I asked the client to blog his experience with us. You may see his actual experience on your next visit to this site.

A year work of bank reconciliation all done in less than half a day's time. What is your story? Maybe you can blog it here.

You can download our software for a free 60 day trial of at www.ezyrecon.com

Conrad

How to do bank reconciliation in a breeze

2006-03-07T20:12:00.000-08:00

How to do bank reconciliation in a breeze

Doing Bank Reconciliation in a Breeze

2006-03-07T19:04:00.000-08:00

I used to do bank reconciliation manually. We have about 1,200 checks to reconcile in a month. Sometimes, it will take me an hour to reconcile, if I lost continuity of thought in the process. Then, I decided to do a bank reconciliation program. I said to myself that there should be a software out there that can do the process by just giving the book and bank files to use. Unfortunately, I did not find any- most of them requires you to do data entry

So, I did one and called it EzyRecon ( www.ezyrecon.com). It took me a year to perfect the program for commercial use. The process to reconcile is made up of three (3) steps, as follows:

1) dowload the book file ( book transactions) in a csv or MS Excel file
2) dowload the bank file ( bank transactions) in a csv or MS Excel file.
3) hit the reconcile button, review the 2 column AICPA format bank reconciliation.

As you can see from these steps, you did not do anything except to dowload the files.

The EzyRecon software is witten in Visual Basic, with MS Excel as the interface. I chose the MS Excel as the user interface for ease of use. But behind the program is a complex Visual Basic modules, doing the data manipulation. Since, it is in Excel, the user can do their own corrections and data manipulations as needed.

With 2,000 checks you can finish your reconciliation in less than 2 minutes, with virtually nothing to do-except to give the files to use. There is no reconciliation in the market that is program to process this way- simply define or give the files to use.

You can download the program and use it for free for 2 months at www.EzyRecon.com

What do you have to loose, test drive the EzyRecon reconciliation today.

Should you have any questions, please write to me at sales@ezyrecom.com

Conrad

I want to help you do your bank reconciliation for free

2006-03-07T16:56:00.000-08:00

If you are having a hard time doing your bank reconciliation, let me help you for free. All you need to do is send me your book and bank file in a csv or Excel format. You can delete some information like payee and payor, if you want. Normally, you can download from your accounting software the general ledger for the bank account you are reconciling. Also, you can download from your bank the bank transaction for the period in a csv or MS Excel format. If you can download these two files, your reconcilation is practically done- I guarantee you.

I am in the process of thoroughly testing my software called EzyRecon. In the process, I want to get all kinds of book data from all software out there.

You can write to me at sales@ezyrecon.com

Conrad