Bank Reconciliation

One of our client feels that the Bank Reconciliation process is very cumbersome. First the user has to use the suggest lines function and then manually compare the bank statement to the suggested lines and delete all lines in the suggested lines not on the statement. If by chance the user does delete 4 or 5 lines, does the user have to redo the whole process of comapring the lines to the bank statement or is there a way to retrieve the lines? The user will then have to post all transactions on the statement not suggested by the suggest lines function? If the client has like 4000 customers making payments every month, then even I agree that it would be a very tedious process. Have any of you’ll encountered the same problems? How would you convince a prospective client that it is really not that hard? Has anyone done any developement to make the process easier? I would really appreciate any input in this area, as I sure all my future clients would also have the same problem. Thanks Anu N.Anu

We made the process more useful as follows: * Obtain each days bank statement electronically from the bank. * Upload the bank statement transactions from a file into the reconciliation screen each day (using a dataport) * Change the apply entries option so it searches for a cash book entry with the same value as the current transaction. * Create a report which shows the unapplied cash book entries, unapplied bank statement entries and produces a summary of the reconciliation. Using this process our bank reconciliation has been reduced from a 50 page spreadsheet nightmare to a 20 minute per day exercise of matching statement transactions against cash book entries. (We currently have approx 1500 bank transactions per month).

If you do not have an electronic bank rec available, there is way to make the manual reconciliation a bit easier. Currently you are telling clients to delete lines that do not exist. However, if you mark all the suggested lines and then select View, Marked Only, you can then un-mark the lines that aare found on the bank statement, this means you are not looking for lines that do not show on the statement, which can be quite difficult and time consuming. When you un-mark the lines they will not be shown anymore, eventually you will be left with only the lines that do not appear on the bank statement. Delete these lines, now untick the View Marked Only and you will show all the lines that you have previously unmarked (ie those lines matching to the bank statement). I have found this to be the easiest way to handle bank rec’s manually. Cheers Peter

Thanks Paul and Peter. Looks like what Paul suggested will work for larger clients who have the facility. The other option suggested by Peter would work for smaller less sophisticated clients. I have one more question. If what has been suggested by Peter needs to work then the order of the Bank statement has to match the order of the Suggested lines. How do you manage to do that Peter? N.Anu

Hi Anu If your client does not have electronic banking the you have two options. 1) Allow the sort order of the Bank Acc. Reconciliation Line to be changed. Create a new key and always start it with Bank Account No.,Statement No. they any fields you want; alway include the sum index fields Statement Amount,Difference. This is very quick and can make a lot of difference to a client. You can add a sort for document date, another for document number or amount it depends on the client. 2) Change the code behind Report 1496 Suggest Bank Acc. recon. Lines. This way you descide the order of the lines as they are suggested. You should look how the lines are ordered on the clients statement and try and reproduce this. Hope this Helps Paul Baxter

Anu Unless your client has only elctronic entries into and out of their bank you really cannot match Navision and the Bank statement order, eg the bank only records cheques when they are presented and this does not necessarily match the order the cheques are entered into Navision. There will allows be timing differences. Cheers Peter

Paul, I was wondering if you could throw more light on the development part of the solution. I understood upto the point where we import the bank statement into the Bank Acc. Reconciliation Line table. But, after that the apply entries part of it way what was not too clear to me. Is there any impact on the check ledger entries that we need to take care of. How would you handle let’s say for example a single deposit consisting of multiple customer checks, as this would have only one entry in the client’s books and multiple lines in the bank statement. Thanks, Anu N.Anu

The way that we handled this was as follows: * When the “Apply Entries” option is selected by the user the form (381) will try to find an entry in the cash book with the same value as the current line (this is only useful if a clients cash book does not usually contain many entries with the same value). The form will open with the found entry selected so the user can apply this entry or manually search for another one. * This process could be automated for cheques using the cheque number but for cash books with less than 50 entries per day it does not seem worth it as it only takes a maximum of 10 minutes per day to do the whole reconciliation process. * If a single entry in the cash book corresponds to several entries on the bank statement then the cash book entry is usually reversed out and entered as individual items (in the UK, 4 cheques banked together will appear as a single entry on the bank statement - so this is not often a problem). Hope this helps.

Hi i am a bit confused as to bank reco being done for the first time. how will the system take care of differences in opening balance. Ideally speaking i would enter the statement ending balance, balance last statement field will be left blank as this is the first reco being done then i suggest lines function the period required which will be same as the period in the bank statement.lines to be deleted and added as in the bank statement. in the end i post when total balance is equal to statement ending balance since i don’t enter a balance as per last bank statement the total balance does not equal the statement ending balance. ??? where am i wrong? pls help!!! thks