Our free bank reconciliation template provides a simple way to reconcile your cash book with your bank statement. Hit the download button and follow our guide to learn more.
Example of our bank reconciliation template in Excel:
How to do bank reconciliation?
Bank reconciliation is the process of matching the company's cash books with the bank statement. Our aim is to ensure that all transactions are accurately recorded in the cash book and to detect any errors or fraud. This is how we can do the bank reconciliation:
- Gather documentsYou will need the company's cash book and bank statements. Download them in CSV format and paste them into individual Excel sheets. Plus, find all pending checks, deposits, and transactions. In case of any discrepancy, this will help you confirm the transaction details, transaction authorization and expense categorization.
- Match documentsWe need to find out the matching transactions in the bank statement and cash book. You can make an exact match if your cash book records the transaction ID. If the transaction ID is not recorded, you can match the transaction based on other information such as date, amount, or description.
Example: We have a supplier payment charge of $50 recorded in the company's cash book; The transaction can be compared with the bank statement using the transaction ID.
- Create a bank reconciliation statement.
Create a reconciliation statement that flags all transactions that match and tracks all transactions that do not match. You can update the cash book to reflect any valid transactions that may have been missed. In case of unauthorized transactions or banking errors, please contact your bank.
- Adjust balances
You will need to reconcile each transaction line by line. In case of discrepancies, you will need to adjust the bank balance and cash book. Bank statements should be adjusted by adding outstanding deposits (deposit in transit) and subtracting outstanding outgoing checks (outstanding checks). The logic here is:
Bank balance + Deposits in transit – Outstanding checks = Adjusted bank balance.
The cash book balance needs adjustments to account for banking service fees, accrued interest, and returned checks (NSF checks). The logic here is:
Cash Book Balance + Interest – Bank Fees – Returned Checks = Adjusted Cash Book
Below is an example of a bank reconciliation statement:
Why bank reconciliation?
Bank reconciliation is essential to identify accounting errors and detect fraud and theft. Without bank statement reconciliation, a business is at risk of financial losses due to errors and fraud. Additionally, inaccuracies in financial statements can cause problems in financial planning, tax compliance, and legal matters.
Automate bank statement reconciliation
Manual reconciliation with Excel is time-consuming and can be a headache. This is a bigger problem in companies with high volume and fast response times. Reconciling hundreds of transactions can take days to fully resolve.
You can reduce the reconciliation process to minutes by using automation software. This would require aggregating data from multiple financial sources, extracting relevant data from documents, comparing data from different sources, and performing fraud checks.
Reconciliation software can automate 3 key elements for you:
- Data collection – Automation software like Nanonets can seamlessly integrate with your ERP or email to collect documents such as cash books, bank statements, invoices and receipts. The software will only extract relevant information from each document through OCR technology.
statements - Data Matching – Without code automation, you can easily set up rules to match the two documents. You can establish new rules over time and you won't have to fight with formulas.
- Error identification and fraud verification – Configure indicators to identify irregular transactions, duplicates or unauthorized transactions.
Find the suitable reconciliation software depending on your business needs and whether the tool has the features you need.
