Bank book template in Excel

Bank book template in Excel

Keeping track of the money that moves through a business does not end when the sale or the purchase is recorded. The bank account has a movement of its own: transfers received, payments to suppliers, fees, debit notes and credit notes the bank applies without warning. If that movement is not written in one place, the book balance ends up different from the statement balance and nobody can say where the two parted ways.

This bank book template in Excel gathers the movements of one bank account into a single sheet with an automatic running balance: you enter the date, the document, the description and the amount in the deposit or the withdrawal column, and the template works out the resulting balance on the line.

What a bank book is and who it is for

A bank book is the company's own record of one bank account. It is not the statement the bank sends, and it is not the bank reconciliation: it is the company's version, written from its own documents, holding the same movements that will later be compared against the statement. Each line is one movement, with its date, document number, description and counterparty.

One file is kept per account. If a business has a checking account at one bank and a savings account at another, the tidiest approach is a copy of the template for each, with the header filled in with the bank name, the account number and the currency. That way the balance of each account is read on its own, and movements from different sources are not mixed together.

It works for any business that receives or pays through a bank: shops, workshops, clinics, service offices, haulers. It also works at home, when someone wants to know what is left in the account after the month's payments. You do not need to be an accountant to fill it in; you need to write it up every day and not leave the close for the end of the month.

A bank account moves for reasons that do not always arrive with a document in hand. A maintenance fee, a loan instalment, interest earned or an adjustment note show up straight on the statement. Those are the movements most often forgotten, and the ones that later force a hunt for the difference. The bank book is built to record them: the template has a remarks column to explain what each note is about.

How it differs from the cash book

The cash book records the money that physically comes in and goes out, the cash counted at the close of the day. The bank book records the money that moves through the account, with no notes or coins in between. They are two separate books and they are best kept apart: a sale collected by card does not go into the cash box, it goes into the bank; a cash withdrawal at the machine shows as a withdrawal in the bank book and as a receipt in the cash book. Kept apart, the transfers between the two are clear; mixed together, the cash balance and the bank balance blur and the day's count will not agree.

What this file is not

The template is an internal control tool. It is not an official book and not a tax document, and it does not replace the records each business must keep on its own. Its value is in the order: one movement per line, with its supporting document and the balance in view.

What the template includes

The template is ready to use the day it is downloaded. Here is what it brings:

The monthly summary shows at a glance which months brought more money in and which took more out, without checking line by line. It is useful for comparing against a budget and for explaining changes from one month to the next.

Columns in the sheet

Eight columns carry the whole record. These are they, and this is how they are used:

The balance column is never typed. If someone writes it by hand, the line stops reacting and the running balance breaks. The same happens if a row is deleted with the delete button: it is worth checking that the formula on the next row still takes the previous balance.

How the balance is calculated, with an example

The balance on each line is the previous balance plus the deposit minus the withdrawal. The first line takes the opening balance from the header. Take an account with an opening balance of 3,000,000, three movements in September and the result at each step.

The first movement adds 1,500,000 to the opening balance of 3,000,000 and leaves 4,500,000. The second subtracts 800,000 and leaves 3,700,000. The third subtracts 20,000 and leaves 3,680,000. At the foot, deposits total 1,500,000, withdrawals total 820,000 and the closing balance matches the balance on the last line: 3,680,000. That match is the first sign that the record is being kept well. If the closing balance in the closing block does not match the last line, there is a row with no date or a deleted formula, and it is worth finding it before carrying on.

Step by step to get started

1. Download the template and save it under the bank name and the account number, so it is not confused with another account.
2. Fill in the header: bank name, account number, currency and opening balance. The opening balance comes from the statement on the day the record starts; if the account is new, write zero.
3. Write each movement of the day on its own line. One movement per line, without grouping several payments onto one row: if they are grouped, each one can no longer be matched to its support later.
4. Record as well what has no support of its own: fees, adjustment notes, interest. They go on their own line, with a remark explaining what they are.
5. At the end of the day, check that the last line's balance makes sense against what online banking shows. They do not have to match to the cent every day, but a large difference is investigated straight away.
6. At the month end, use the summary sheet to review the twelve months and note the closing date and closing balance in the closing block.

Tips and common mistakes

• Do not delete or insert rows in the middle of the record: deleting a row breaks the running balance on the rows below. If a line is spare, leave it empty.
• Do not type the document number as text with invented leading zeros; use the real number on the voucher or the transfer, exactly as it appears.
• Keep grouped payments apart. If a supplier collects three invoices in one transfer, use one line per invoice with the same transfer number in the document field; that way it is clear what was paid.
• Write movements up the same day. Leaving the record for the end of the month turns the close into a hunt for paperwork, and there is always a line nobody remembers.
• Mind the date. A date typed as text does not enter the monthly summary and the month ends up with less movement than it had. Type the date in day, month and year order.
• Use the remarks column. It is the one that later explains an odd fee, an adjustment or a note the bank applied without warning.

When to move to software

As long as the account movements can be reviewed on a screen and the month-end close takes an afternoon, a spreadsheet is enough. The moment to change comes when there are several bank accounts, several people entering movements at the same time, or a volume of movements that can no longer be reviewed. It also comes when the bank book has to be matched against the accounting records and against receivables, because then the sheet starts duplicating work a system does on its own.

A good starting point is a tool that is already designed for bookkeeping and not just for holding rows. Kardex Tauro is an example of that kind of tool: it keeps accounts, movements and balances in one place and lets you look up what passed through each account without opening each month's file.

Either way, the sheet remains useful as a backup and as a learning tool. Many businesses start with the template, learn the order of the record, and only then look for a system. The important thing is not to stop writing things down.

In short

An orderly bank book does not prevent differences with the bank, but it makes sure they are found in time. One movement per line, the date and the document always at hand, and the balance checked daily are enough for the month-end close to stop being a problem. When the record grows, Kardex Tauro can take on that same movement without losing the order.