Windward Software Customer Care

First of all there were not really any bank rec integrity checks in System Five when this article was started. There are some integrity problems that do end up relating to bank rec problems because any time you have a problem with a cheque you could have a problem with a bank rec. 

Look at the back end of a journal entry via “edit single transaction” and right click on the GL line for the bank, and then click “Bank Reconciliation Details”. This will list the bank recs that are attached to this line. If you then compare this to what you expect to see, you should be able to identify a problem.

I would like to clearly define what constitutes a correct bank rec report, and discuss the idea of reconciling the bank rec. Lets start with an opening bank rec.

An initial bank rec, or opening bank rec is the first bank rec you do for a bank account in System Five. This bank rec sets up the bank rec for future use. The how-to of doing a bank rec is not covered in this article as it is meant more as a checklist for making sure you have done things right.

To start off you should have a bank rec from your accountant printed from outside System Five for your go live date, and a trial balance for the same date. These are 2 of the reports you need to go live in System Five. You may need your accountant to prepare these statements. The bank rec from your accountant should have these elements:

1. A statement starting date
2. A statement ending date
3. A statement starting balance
4. A statement ending balance
5. A list of cleared transactions. The sum of these transactions must equal the statement ending date less the statement starting date.
6. A list of uncleared transactions. The statement ending balance plus the sum of the uncleared transactions must equal the the GL bank account balance from the go live trial balance.

It is important to have a good starting point in moving forward with your initial bank rec. If you do not, it will be easier to get things wrong and make mistakes that you don't catch till much later. As a result you could be forced to re-do all of your bank recs to get accurate and valid bank rec reporting. Here are some things you need to make sure are correct on your starting bank rec:

1. The statement starting date can be any date before the statement ending date from your accountants bank rec. I suggest the start of the month, the start of the calendar year, or the start of the fiscal year.
2. The statement ending date should be the date before your go live. If your go live is January 01, 2000 then the statement ending date should be December 31, 1999.
3. The statement starting balance is $0. Basically before you enter anything in the system 0 is always the start. There is never any valid reason the starting bank rec to have a statement starting balance that is not $0
4. The statement ending balance should match the statement ending balance from your accountants bank rec.
5. There should generally be 2 cleared transactions on the initial bank rec and several uncleared transactions for any uncleared cheques or deposits. If there are no uncleared entries then there will be only 1 line showing on your
6. starting bank rec in System Five. We will discuss the details of the cleared and uncleared transactions in a section below.
7. The computer ending difference must be $0
8. The ledger difference plus the outstanding difference must equal $0

If your bank rec does not follow the rules stated above it would be a good idea to review the bank rec for correctness and adjust, or contact technical support for assistance.

initial bank rec lesson

You need the bank statement to start this job. The bank statement must have these components:

• A statement starting balance. This should be the same as the statement ending balance from your previous System Five bank rec as well as the last statement you received from your bank.
• A statement ending balance.
• A statement start date. This should be the day after the statement end date from your previous System Five bank rec. It should also be the day after the statement and date from your last bank rec.
• The details of all transactions processed in that period including their amounts, dates, and whether they were deposits or withdrawals.

Without going into any real detail on how to do the bank rec here is a brief description. You mark off all transactions that have cleared the bank leaving only uncleared items left unchecked. Once you are finished please review the checklist below to make sure the Bank rec is correct and ready to be closed. The checks below can also be used to see if any period in the past is still correct.

Quick list of checks/requirements:

1. Computer Ending Difference must equal $0. System Five will not allow you to close the bank rec unless this is true.
2. The Ledger Difference plus the Outstanding Difference must equal $0.
3. The statement starting balance of the statement you are viewing must equal the statement ending balance of the previous statement.
4. The statement ending balance must equal the statement starting balance of the next statement. If one does not exist make sure it also matches the statement from your bank.
5. The statement end date is equal to the last date the statement reports on. If you are trying to report on all January transactions but the statement end date is January 29th it will not work.
6. The statement start date is not as important. For display purposes it should match the correct start date. This is the day after the end date of the previous bank rec in System Five.

At this point the bank rec is truly ready to be closed.

You might ask yourself “Why does the ledger difference plus the outstanding Difference have to equal $0?”, or “What does the ledger difference, and what is the outstanding difference? The quick check for ledger difference plus outstanding difference to equal $0 comes from the following basic bank reconciliation concept: The cleared balance plus the uncleared balance must equal the GL balance for the same bank account. This basic bank rec concept allows us to use the bank balance on our GL and be able to trust that if all the checks we have written and received were to clear right now we would have this much money in the bank.

The ledger difference is the difference between the GL bank account and the statement ending balance. If it is $0 then everything has cleared.

The Outstanding difference is the total of all uncleared transactions. If all transactions are marked as cleared then the Outstanding difference will show $0.

As part of your month end procedures it is normal to do a Bank Reconciliation along with your other month end procedures. At that time you close your month and you can now be confident that the appropriate measures have been taken to keep your data consistent. If you do not or can not follow such a tidy month end closing procedure and keep your month and open longer than the 1 - 4 weeks that might be considered normal, it becomes very important to be aware of your minimum book month and the date of your last cleared bank rec.

You need to make sure the minimum book month is higher than the last closed bank rec period. This means that if I have closed my bank rec to Jan31 then my minimum book month should be February. The reason this is important is that it will prevent people from posting to this bank during this closed period. These new transactions will not show in the closed bank rec, and can not be re-attached to the bank rec unless you delete all bank recs following that period, re-open the period in question, and re-do all of the bank recs from that period on. If you have the printouts from your previous bank recs, it should not be too difficult to re-do these bank recs.

To re-cap: The minimum book month needs to be higher than the last closed bank rec period. If you have more than one bank account it should be higher than the highest last closed period.

What not to do:

• If you have to re-open a bank rec period, you need to make sure you do not delete or change the value of any line that posts to a closed bank rec period.
• Run in supervisor override mode: Unless you are doing corrections to the bank rec that require supervisor override, please make sure that you are not running in this mode. You could; change the date of your bank rec; accidentally change an items status from cleared to uncleared or visa versa.

There are features in the bank rec that help you not mess up the bank rec.

If a posted transaction is marked as cleared then you can not unpost it or edit it normally. This is true even if the transaction is cleared on a bank rec that is not closed. So if you have checked of the transaction in a current and open bank rec you need to uncheck it before it can be changed or fixed.

Be very careful with edit single transaction and bank rec lines. If for example you open the back end of a check with edit single transaction, it will let you change and/or delete the GL line. Doing this will damage the bank rec that it is attached to.

WARNING: Deleting a Bank line on a closed bank rec will permanently damage that bank rec. The only way to fix it is to delete all bank recs back to that date, re-open that bank rec and redo all of the bank recs from then on.

Information: There is a feature request that will help more easily fix these old bank recs. Please see incident #20069. If you are a customer, please call and request your name be added to this incident. It is for “Bank Rec needs the ability to add a transaction to a closed historical bank rec to fix it”. If you are a technician reviewing this article, please add the customer to the incident.

• WARNING: This query is a database query and is only meant to be ran under the direction of an advanced support technician as it requires knowledge of advanced techniques. Please backup before running any queries on your data.

So when would you need to do this? If you have an old bank rec that used to come to a difference of 0, but now a transaction no longer shows on it, you may need to re-attach the bank account lines from it to the old bank rec. The idea for the missing line is that the cleared date needs to be the correct date and fall within the dates set on the old bank rec. You can normally check this on the current bank rec because the line will be added to the current bank rec because it no longer shows as cleared on its original bank rec. These queries only work on cleared entries. This way we don't have to deal with supervisor override unless we need to unclear the transaction.

If you only need to fix one of many transactions that show you can add a line to the end of BOTH queries to fix just the one line: Where XXXX is the value of that line from the BRD_Unique column

  and D.BRD_Unique = XXXX

SELECT D.BRD_Unique, D.BankRecUnique, D.TrandataUnique, D.Cleared,  D.ClearedDate, H.BRH_Unique, H.Account, H.StartDate, H.EndDate, T.TUnique, T.Send, T.DataUnique, T.Account FROM ("BankRecD" D JOIN "Trandata" T ON D.TrandataUnique = T.TUnique) JOIN "BankRecH" H ON (D.ClearedDate >= H.StartDate and D.ClearedDate <= H.EndDate) WHERE D.BankRecUnique <> H.BRH_Unique  and T.Account = H.Account  and D.Posted = 'Y'  and D.Cleared = 'Y'

UPDATE D SET D.BankRecUnique = H.BRH_Unique from ("BankRecD" D JOIN "Trandata" T ON D.TrandataUnique = T.TUnique) JOIN "BankRecH" H ON (D.ClearedDate >= H.StartDate and D.ClearedDate <= H.EndDate) Where D.BankRecUnique <> H.BRH_Unique  and T.Account = H.Account  and D.Posted = 'Y'  and D.Cleared = 'Y'