Free Bank Reconciliation Template

Our free Bank reconciliation template provides a simple way to reconcile your cashbook with your bank statement. Hit the download button and follow our guide to learn more.

<Not final link>

Example of our Excel bank reconciliation template:

How to do bank reconciliation?

Bank Reconciliation is the process of matching the company’s cashbooks to the bank statement. Our goal is to ensure all the transactions are recorded accurately in the cashbook and detect any errors or fraud. Here’s how we can do bank reconciliation:

1. Gather documentsYou will need the company cashbook and bank statements. Download them in CSV format and paste them into individual Excel sheets.Also, find all the outstanding checks, deposits, and any pending transactions. In case of any discrepancy, this will help you confirm the transaction details, the authorization of the transaction, and expense categorization.
2. Match documentsWe need to find out the matching transactions in the bank statement and the cashbook. You can do an exact match if your cashbook records the transaction ID.If the transaction ID isn’t recorded, you can match the transaction based on other information like date, amount, or description.

   Example: We have a $50 vendor payment charge recorded in the company cashbook; the transaction can be matched with the bank statement using the transaction ID.

3. Create a bank reconciliation statement.

   Create a reconciliation statement that marks all the transactions that match and tracks all the unmatched transactions. You can update the cashbook to reflect any valid transactions that might have been missed. In case of any unauthorized transactions or banking errors, please contact your bank.

4. Adjust Balances

   You will have to reconcile each transaction on a line-by-line basis. For discrepancies, you will have to adjust the bank balance and the cashbook. The bank statements must be adjusted by adding pending deposits (deposit-in-transit) and deducting pending outgoing checks (outstanding checks). The logic here is:

   Bank Balance + Deposits-in-transit – Outstanding Checks = Adjusted Bank Balance.

   The cashbook balance needs adjustment for bank service fees, accrued interest, and rejected checks (NSF Checks). The logic here is:

   Cashbook Balance + Interest – Bank Fees – Rejected Checks = Adjusted Cashbook

   Here is an example of a Bank reconciliation statement:

Why bank reconciliation?

Bank reconciliation is essential for identifying accounting errors and detecting fraud & theft. Without bank statement reconciliation, a company risks financial losses due to errors and fraud. Moreover, inaccuracies in the financial statement can lead to issues in financial planning, tax compliance, and legal matters. 

Automate Bank Statement Reconciliation

Manual reconciliation using Excel is time-consuming and can be a headache. This is a bigger problem with companies with high volume and quick turnaround times. Reconciling 100s of transactions can take days to resolve completely.

You can reduce the reconciliation process to minutes using automation software. This would require aggregating data from multiple financial sources, extracting relevant data from documents, matching data across different sources, and fraud checks.

Reconciliation software can automate 3 key items for you:

1. Data collection – Automation software like Nanonets can seamlessly integrate with your ERP or Email to gather documents like cashbooks, bank statements, invoices, and receipts. The software will only pull relevant information from each document through OCR technology.
   statements
2. Data matching – With no code automation, you can easily set up rules to match the two documents. You can set up new rules with time & don’t have to struggle with formulas.
3. Identifying error & fraud-check – Setup flags to identify any irregular transactions, duplicates, or unauthorized transactions.

   Find the right reconciliation software based on your business needs and whether the tool has the features you need.