I have been using the “Bank Statement Reconciliation Sheet” (aka “Statements”) for some time to reconcile my transactions against corresponding account statements. It works great, but it takes some time to go through and assign a statement to every transaction. It occurred to me that my statements are always correct, and my transactions are usually correct as well, so most of the time I just need to validate that the closing statement balance is equal to my account balance for that same date. I looked for simpler alternatives and found “Simple Account Reconciliations” by @martha.rudkin. It is a step in the right direction, but still requires touching every transaction. Not finding what I was looking for, I built my own template.
The concept is to compare the statement’s closing date and balance, with the sum of Tiller transactions for that account on the same date. This works great for 80% of my accounts, but I found that transactions in the other 20% don’t always respect the opening and closing dates of the statement. In most cases this would be a transaction with a posting date before or on the closing date of the statement, but it instead appears on the next statement. To accommodate this issue, the template uses “Start/Open” dates and balances for each account, and a new column on the Transactions sheet to hold an alternative date to evaluate these “problem” transactions.
The Account Reconciliation template provides two functions:
- Track if the transactions in your accounts match those in your account statements
- Provide tools to troubleshoot transaction balances that don’t match your account statements.
It is designed to work with the Tiller Foundation Template, and references the Transactions and Accounts sheets. The general process is that when a new account statement arrives, you update the “Statement End/Close Date” and “Statement End/Close Balance” for the account with those from the statement. If the “Tiller Ending Balance” matches the “Statement End/Close Balance”, you’re done! If not, you can use the “Troubleshoot Unbalanced Statement” section to find the “problem” transactions and fix them.
- You’ll need to add a column to your Transactions sheet called
ReconcileDate. To do this, in your Transactions sheet, right-click on the letter above any column to the right of the Amount column and choose “Insert”. A new column will appear to the left of the one you clicked on. Change the column header of the new column from “Column1” to “ReconcileDate”.
- Download the Account Reconciliation workbook.
- Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data. (The instructions are for the desktop version of Excel, the web version is not recommended for the installation but does work once the Account Reconciliation sheet is installed.)
At this point your new template should be functional and linked to your local workbook’s data.
- In column B, use the drop-down menus to add each account you’d like to reconcile.
- For your first account, find the oldest statement you have with an “Open/Start” date that comes after your oldest Tiller transaction for that account (we need all transactions in the statement to be present in Tiller to get a clean start). Note you don’t necessarily have to include ALL of your transactions, you could start with your latest statement and move forward from there, but anything older than that will not be ‘reconciled’.
- Open the “Account Start/Open Info” section by clicking the “+” above column K.
- Enter the “Statement Start/Open Date” for the account. Most of my statements included this date on it, but if not, it would be the day after the “End/Close” date of your previous statement. Any Tiller transactions for this account that are older than this date will not be evaluated for reconciliation. You should never have to change this again.
- Enter the “Opening” or “Previous” balance from the statement into the “Tiller Opening Balance” column. You should never have to change this again.
- Enter the “Statement End/Close Date” from your statement. You’ll change this every time you get a new statement.
- Enter the “Statement End/Close Balance” from your statement. You’ll change this every time you get a new statement.
- Check the “Tiller Ending Balance” column. If the number there matches your “Statement End/Close Balance”, it will be colored green, and you are done! If it doesn’t match, it will be colored red and you’ll need to troubleshoot what doesn’t match between your statement and your Tiller transactions.
- Repeat this for each account. Once you’re done, you can close the “Account Start/Open Info” section by clicking the “-” above column K. You shouldn’t need to change this info again.
- Open the “Troubleshoot Unbalanced Statement” section by clicking the “+” above column R.
- Select the Account you’re troubleshooting from the dropdown in cell L5.
- Enter the “Start/Open Date” of the statement you’re troubleshooting in cell L7.
- Enter the “End/Close Date” of the statement you’re troubleshooting in cell L9.
- Enter the “Starting Balance” of the statement in cell L18.
- Enter the “Deposits/Payments” of the statement in cell L20.
- Enter the “Withdrawals/Charges” of the statement in cell L22.
The “Ending Balance” of the statement will be calculated, be sure it matches the “Ending Balance” of the statement. If they don’t match, there could be a problem with the statement.
The “Actual” column shows the same information based on the actual transactions in Tiller. If one of these numbers doesn’t match between “Statement” and “Actual” the difference between them is shown in cell L26. Potential problems include:
- A transaction(s) from the previous statement is being shown in this statement period
- A transaction(s) from this statement is dated in the next statement period
- A transaction(s) is missing from your feed for this account
- An extra/duplicate transaction(s) exists for this account
Whatever the case, the summed amount of the transaction(s) that are in “error” is the number you see in cell L26.
A list of transactions from the statement period is shown to the right of where you entered the statement info. If a single transaction appears in that list that has the same amount shown in L26, it will be highlighted in red so you can easily see the transaction that needs “fixing”. If you don’t see anything in red, try entering “1” in cell L11 to “Prepend Start/Open Date” (extend the statement period one day earlier). If you still don’t see a highlighted transaction, try entering “1” in cell L13 to “Extend End/Close Date” (extend the statement period one day later).
If a transaction isn’t being highlighted, it could be that it’s more than one transaction, in which case you’ll need to do some manual investigation. In my experience, most of the “problem” transactions were on the closing date of the statement, so that would be the best place to start.
Once you find the problem transaction(s) you need to “fix” them. To do that, go to the Transactions sheet and find the first transaction with a problem. In the new column you entered (“ReconcileDate”), enter a date of when you want the transaction to be evaluated for reconciliation. For example, if a transaction is being counted in the current statement period in Tiller, but doesn’t actually appear on the current account statement, you could set the ReconcileDate to be the day after the closing day of the statement.
As an example, I have a transaction that was posted on 4/27/20 for some cat food from Chewy. The account it is charged on closed on 4/28/20, but the statement doesn’t show that transaction, it instead appeared on the next statement. To fix this problem, I added an alternate “ReconcileDate” of 4/29/20. Now, the formulas on the Account Reconciliation sheet will use that date instead of the normal date when evaluating the account. This leaves the transaction unaltered for all other functions in Tiller, yet still allows you to make adjustments that correspond to account statement periods.
Once you’ve added the new date, look back at the Account Reconciliation sheet and see if your balances are now “Balanced”. If not, look back at the list of transactions and see if any are highlighted red, and repeat the same process until you get the period balanced.
Once you have all your accounts in and balanced, when the next statement shows up, enter the “End/Close Date” and “End/Close Balance” and if all the transactions are good, you’ll see a green balanced “Tiller Ending Balance”. If not, use the troubleshooting steps above to find the “problem transaction(s)” until it’s balanced.
As a point of reference, I am reconciling 11 accounts and have data for all of them back to the beginning of 2020. I found 18 transactions that needed “fixing”, all of them within two of the accounts: Discover and American Express. For the other 9 accounts, all I had to do was set the “Start/Open” information, and the “Close/End” information for the latest statement and they came up as balanced. It should be noted that I was already using the “Statement” sheet, so I had already cleaned up missing and/or duplicate transactions before using this new template, so I only had transactions showing up in the wrong statement period.
I hope this works as well for others as it has for me so far! Please let me know any issues you run into so I can get them fixed. Also let me know if you have ideas on how to improve it! I’ve also released a Google Sheets version here.