Bank reconciliation statement template Excel

Bank reconciliation statement template Excel

This article provides details of Bank reconciliation statement template Excel that you can download now.

The bank reconciliation report compares the figures in the accounting records with the figures on the bank statement.

A bank reconciliation is a verification process that compares the bank account statement sent by the bank with the accounting data. The goal: to be aware of the current status of the accounts, to ensure that all payments, disbursements and receipts have been made, detect any errors made and protect against theft.

Microsoft Excel software under a Windows environment is required to use this template

These Bank reconciliation statement template Excel work on all versions of Excel since 2007.

Examples of a ready-to-use spreadsheet: Download this table in Excel (.xls) format, and complete it with your specific information.

To be able to use these models correctly, you must first activate the macros at startup.

The file to download presents three Bank reconciliation statement template Excel

• Tow basic Bank reconciliation statement template Excel
• Excel Cashbook & Bank Reconciliation Template

This template enables users to record deposit and withdrawal transactions for multiple bank accounts and automatically produces a monthly cashbook report and a bank reconciliation. The cashbook report can be compiled for any 12 month reporting period by simply entering the appropriate start date in a single input cell and the report can be viewed on an individual account or consolidated basis. The 30 default accounts that are included on the report can be customized and an unlimited number of additional accounts can be added if required. The template also accommodates petty cash transactions and sales tax calculations.

A bank statement is issued by the bank at the end of each month and will contain details of the money paid out and into the firm's account.

In order to be able to understand the terms used in a bank statement some important terms need to be defined:

• Direct Bank Transfer à An automatic transfer of money from a firm's account to a creditor, or to employees to pay their salaries and wages.
• Bank Overdraft à When we have paid more out of our bank account than we have paid into it. In this situation the firm will owe the bank the difference in the balance.
• Direct Debit à An agreement by the firm for a creditor to automatically draw out a variable amount of money from its bank account. For example, at the end of each month a firm might set up a direct debit which gives the mobile phone supplier the power to draw out enough money to pay the monthly charges for phone calls.
• Standing Order à An agreement by the firm for a creditor to automatically draw out a fixed amount of money from it's bank account. For example, a firm may set up a standing order for it's insurance company to take out a fixed sum of money from it account over an agreed time period.
• Drawer à The person writing the cheque and using it for payment. 6- Payee à The person to whom a cheque is paid.

Unpresented Cheques, Bank Lodgements and Dishonoured Cheques

When a firm pays awcrwediwtor .biygchceqsuee itawcillcuosuaullyntatkse .acpeoriomd of time before the amount is deducted from the balance in your account. The following diagram explains why this occurs:

1st January: GLX firm pays its creditor Jones $700 by cheque which it sends by letter in the post.

3rd January: The creditor Jones receives the cheque and pays into the firm's HSBC account.

4th January: The HSBC bank will send the cheque back down to GLX's Lloyds bank so that they may verify that he has enough money to pay the amount written out in the cheque.

5th January: Lloyds bank confirm that GLX has enough money in their bank account to pay creditor Jones. An electronic transfer of money is then made out of GLX firm's account into creditor Jones' account.

As can be seen from the example illustrated above the process of money changing hands from the firm to the creditor takes at least five working days. This time delay will often mean that the Bank Statement and the Cash Book do not match.

The three possible terms that might be used to describe this time delay are:

• Unpresented Cheque à A cheque paid by your firm to a creditor which has not yet cleared through the banking system.
• Bank Lodgement à A cheque received by your firm from a debtor which has not yet cleared through the banking system.
• A Dishonoured Cheque à When a cheque is received from a customer and paid into the bank, it is recorded on the debit side of the cash book. It is also shown on the bank statement as a banking by the bank. However, at a later date it may be found that his bank will not pay us the amount due on the cheque. Therefore the bank has failed to honour the cheque.

A Bank Reconciliation Statement

Obviously when an accountant pays or receives a cheque he/she will immediately record it on the credit or debit side of the Cash Book bank colums. However very often cheques will not clear through the banking system until at least five days after they have been written and as a result there is often a time delay between when the money is recorded in the Cash Book and when it actually leaves or enters the firm's bank account. This time delay often means that the bank statement balance at the end of the month and the bank balance brought down in the Cash Book will be different. As a result accountants use a system to verify that the difference between the bank statement and the bank balance in the Cash Book is a result of a time delay and not an accounting error. This system is called a Bank Reconciliation Statement.

Where the closing balances differ between the Bank Statement and Cash Book

Where there is a difference between the closing balances in the Bank Statement and the Cash Book the following two steps must always be followed:

• Update the Cash Book à Check the bank statement for transactions that have not been entered in the cash book.
• A Bank Reconciliation Statement à A reconciliation of the difference between the remaining balance must be performed using an agreed format. This statement will prove that the different balances between the two business documents are a result of a time delay and not an accounting error.

The credit and debit entries on a bank statement

Obviously the bank statement that the firm receives at the end of the month is prepared by the bank's accountant and as a result from their point of view the business in question is a creditor of money. This is because they owe the firm the amount shown in the final balance as the end of the month. Consequently debit and credit entries are reversed in the bank statement. This means that on the Bank Statement a debit entry is shown for money leaving your account and a credit entry is shown for money entering your account.