Managing money is a crucial life skill. For small business owners and personal accountants, keeping a log of your check transactions can help you track your running balance and keep an internal record you can compare with your official bank statement at the end of any given accounting period – say, on a daily or monthly basis.
With these goals in mind, individuals or companies often use a Checkbook Register as a real-time Excel-based ledger of their cash flow. This allows you to easily track past transactions, minimize errors in financial calculations, and ensure your internal or self-kept records match your official bank ones.
In our template, you manually enter your account and transaction details, and pretty much let us do all the tedious math for you. For generalizability, we have divided transaction types into 10 categories (Withdrawal, Transfer, Deposit, etc.) while expenses are divided into 17 types (Food, Entertainment, Misc.). Our template offers automated features that help you track your cash flow and remaining balance, identify any transactions or sums of money you still need to reconcile, and be warned in case your balance goes below a certain (adjustable) value.
Now, let’s explore these finance-savvy features of our template in more detail:
Key Features:
- Automatic Balance Alert
- Adjustable Expense Categories
- Opening & Closing Balance At a Glance
- Cash Flow Tracker
- Reconciliation Feature
Account Overview
In the first part of our Register, you add key details of the account you want to track.
For this, write down the account number, name of the account holder, and the bank where your account is registered, followed by the Account Title (say, a checking or current account).
Opening & Closing Account Balance
Right at the top of our main spreadsheet, we show the Opening and Closing balance of your checking account. Note that the opening balance, which refers to the money in your account plus cash in hand when you open a new check register, will be shown as the balance carried forward in the mainsheet.
Ideally, the total closing balance shown at the top should be the same as the balance detailed in the bank statement. When the two values don’t match, say you are still waiting for a bank transfer or cheque to be processed, you will end up with a difference which is shown at the top-right as well as under the Outstanding Column in the mainsheet.
Having this information available to you at a glance shows you whether the internal and bank records of your transactions match from the start to the end of the accounting period. This accounting period may be a day, a month, or a year – depending on how regularly you need to track your check transactions.
Balance Alert Feature
This special feature gives you a heads-up when your balance hits below a certain threshold amount. You can adjust this amount to whatever you think is an adequate warning of when you should take steps toward increasing your income and cutting back on expenses.
For example, if you add 100k as your ‘alerting limit’, any time your balance plummets to a value below 100k, a red circle with a cross will pop up in the Balance column (Column K).
The Mainsheet
Date, transaction type & ref id.
In the first column, specify the date when a transaction was made to keep a clear chronological record of your transactions.
In the next column, select one option from our default drop-down menu to identify the nature of the transaction being tracked. 10 commonly used categories are built into our template: Deposit, Withdrawal, Check, Electronic Payment, Transfer, Transfer, ATM Withdrawal, Fee, Interest, Adjustment, and Refund.
We have also added a separate configuration sheet where you can easily customize this classification system to suit your unique personal or business needs. Remember, organizing and analyzing your expenses and income regularly helps you check your spending impulses and budget your way to a more financially secure future.
Withdrawal, deposit, and remaining balance: cash flow tracker
In Columns, I and J, add information as to how much money is flowing into and out of your account. Based on this, our template calculates your remaining account balance and shows it in the next column – helping you track your running balance after each transaction.
Note: To make sure our programmed functionalities remain intact, we have enabled protection for cells under Balance, so this information can only be automatically calculated based on the transaction data you input in the preceding columns.
Reconciliation feature
This is probably the most useful feature of our template: a reconciliation tracker that lets you compare your internal records and your bank records for any given accounting period.
Let’s say, you want to reconcile your transactions after one whole day of running a business.
In Column H of the template, you need to identify if a certain translation is cleared (C) or reconciled (R). If a translation is only Cleared, it means that it is yet to be reconciled or matched in the two sets of financial records (say, as a result of pending or unprocessed transactions). This feature provides clarity on your translation’s current account status.
At the end of your business hours, your goal is to compare and match your closing balance (both, bank balance and cash in hand) with the balance in the second last column of our template – and make sure that the difference between these two values is zero. We calculate this difference for you automatically and show it on the top-right column above our main sheet.
This difference is reflected in the last column of the sheet (Outstanding), where a value in red font denotes withdrawal transactions that are yet to be reconciled, while an entry in black font shows a deposit transaction that is yet to be reconciled.
Adjustable expense categories
Knowing what you spend your money on helps you find the right balance between spending on things you need (such as utilities or groceries etc.) versus things you want (traveling, entertainment etc.).
In our template, we have added 17 categories we believe cover the most typical expenses in the life of an average account holder. However, we have also provided a configuration sheet where you can adjust and personalize these categories to suit the types of transactions likely to be made in your account.
The categories used in our template are: General, Transfers, Income, Savings, Charity/Gifts, Housing, Utilities, Food, Transportation, Health, Daily Living, Children, Obligations, Business Expenses, Entertainment, and Subscriptions).
We have also added a Miscellaneous category to cover any expenses you incur that are not covered in our default list.
Who is our template for?
- Small business owners
- Financial analysts
- Professional accountants
- Individual or families tracking household expenses
Why you need our template?
- To Track Transactions: By keeping a detailed record of your deposits and withdrawals, our template makes it easier for you to track your spending habits.
- Prevent Overdrafting: When you maintain an accurate balance, you also avoid overdrawing from your accounts, which incurs fees.
- To Budget Better: Our template helps you categorize expenses and monitor spending against your budgetary limits.
- Reconciliation: By helping you reconcile your bank statements, our template helps you identify discrepancies and errors.
- To Become More Financially Aware: Regular use of the template fosters financial mindfulness, encouraging you to be aware of your spending and saving patterns
Wrap Up
Regardless of whether you use our template for work or to manage finances at home – our professionally-designed Excel Sheet makes it a great way to analyze and regulate your spending and saving habits, and develop a more financially responsible behavior.
Our systematic finance management tool, the Checkbook Register (Excel) is fully customizable to your individual personal or business needs!
Note
Be aware that spreadsheets are somewhat prone to error. Even if the spreadsheet is completely free of errors at the time you download it, there is always a possibility that you might accidentally introduce errors as you edit it. That’s why we would recommend using this investment tracking template only if you are comfortable using Excel and can identify and fix errors that may be introduced. With that said, download and enjoy!
Our editable template can be instantly accessed in .xlsx, .xltx, and .ods formats.
