A company is involved in various transactions with a bank which if not recorded and reconciled with the bank records properly would lead to misunderstandings and mismanagement of funds. Hence, a company reconciles its own records with the records of the bank in the bank statements.
Discrepancies in the company and bank records are common in the real world due to many reasons such as:
- The company or the bank had made mistakes.
- The banks have not yet received the checks issued by and recorded in the company records.
- The company has not been informed yet by the bank about the bank charges and so they are absent in the company records.
The discrepancies can be sorted out but their identification is the first step in the sorting out process. If there are no proper records and the company does not reconcile the figures, it would not have the knowledge of discrepancies which might affect the fund’s management and bookkeeping. In addition, if these discrepancies are ignored for a longer period, the overall accounting records might provide wrong information which would lead to the stakeholder objections.
To fill this gap, monthly reconciliation template is often opted for by the companies to keep a record of their deposits, withdrawals and balances and the figures can then easily be compared against the bank statement records to find any discrepancies and deal with them.
What is a monthly bank reconciliation template?
This template is a spreadsheet in MS Excel which helps a company in recording its financial transactions of deposits and withdrawals over a period of a month as well as aids in tracking the balances (starting and ending) for reconciliation purposes.
This template is easy to customize and update and is readily available to be downloaded. It has inbuilt formulas for the calculations which reduces errors of manual calculations or putting up wrong formulas. Any type of organization can use this template due to its feature of customization as per the needs. In fact, this template can even be used for personal purposes if an individual has a number of transactions in a month and want to reconcile for personal reasons.
Generally, the template has two sheets. The first one has details of the month under consideration, starting balance, total deposits, total withdrawals, ending balance along with the details of all the deposits which, usually, include:
- Deposit number
- Date of deposit
- Amount deposited
- Description or reference
- A column to ensure reconciliation
In this sheet, description filters/key are set so that when an amount is deposited, the type of deposit can easily be chosen from the filter. In addition, this filter helps in sorting out the categories of deposits.
The second sheet is for the details of withdrawals and usually includes the fields for:
- Type
- Date of withdrawal
- Amount withdrew
- Purpose of withdrawal
- The column to ensure reconciliation
This sheet also has a filter for the ‘purpose of withdrawal’ which is coded and categorized beforehand.
The template automatically calculates the totals of deposits, withdrawals, and the ending balance. In addition, if the reconciliation has not been checked, corrective action can be taken by the user.
Download
[For Excel: xlsx & Polaris Office] [Size: 173 KB]
OTHER OPTIONS
Download
[OpenDocument Spreadsheet .ods] [Size: 84 KB]
How to use a monthly bank reconciliation template?
The template can be easily downloaded and updated based on the user’s requirements. After the customization, the required data can be inserted.
If monthly spreadsheets are being kept in separate files, the opening/starting balance needs to be inserted. Else, the opening balance of a month can be automatically updated based on the closing/ending balance of the last month.
The details of the deposits and withdrawals are filled in. After that, the built-in formulas automatically calculate their totals. Once this is updated, the ending balance is automatically calculated as well. After the figures are available, they can be reconciled with the bank statement records. If they match, the reconciliation column can be checked otherwise, the reasons of discrepancies are found and addressed.
The filters can also be modified if required and the spreadsheet can be printed as well if the discrepancies are to be analyzed or discussed.
Benefits of monthly bank reconciliation template
The template offers numerous benefits such as:
- It saves time and effort.
- It is easy to use, customize and update.
- The inbuilt formulas can reduce the work and minimize errors.
- The filters or key have already set categories which reduces the time to update the field every time as the filter can be selected. These filters can be modified as well.
- All the detailed information about deposits and withdrawals can be viewed.
- The opening and ending balances are available for analysis purposes.
- The reconciliation column would highlight any action required to sort out the discrepancies.
- The chances of ignoring discrepancies are reduced.
- The template helps in reconciliation, management, and disbursement of funds.
- The spreadsheet can be easily printed for the analysis.
This template is widely used for personal and professional purposes in order to reconcile the records and make cash management and bookkeeping effective.
We hope that we helped you with this article and the attached template and you would easily record and keep a track of your bank reconciliations every month. Feel free to contact us with any questions or feedback. Your views hold great value for us. Thank you!