🏆 Account Reconciliation - Google Sheets

[jpfieber]

Overview

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:

1. Track if the transactions in your accounts match those in your account statements
2. 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.

Installation

Install the template from the Tiller Community Solutions Add-on

Setup

1. In column B, use the drop-down menus to add each account you’d like to reconcile.
2. 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’.
3. Open the “Account Start/Open Info” section by clicking the “+” above column H.
4. 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.
5. Enter the “Opening” or “Previous” balance from the statement into the “Tiller Opening Balance” column. You should never have to change this again.
6. Enter the “Statement End/Close Date” from your statement. You’ll change this every time you get a new statement.
7. Enter the “Statement End/Close Balance” from your statement. You’ll change this every time you get a new statement.
8. 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.
9. Repeat this for each account. Once you’re done, you can close the “Account Start/Open Info” section by clicking the “-” above column H. You shouldn’t need to change this info again.

Troubleshooting

1. Open the “Troubleshoot Unbalanced Statement” section by clicking the “+” above column K.
2. Select the Account you’re troubleshooting from the dropdown in cell L5.
3. Enter the “Start/Open Date” of the statement you’re troubleshooting in cell L7.
4. Enter the “End/Close Date” of the statement you’re troubleshooting in cell L9.
5. Enter the “Starting Balance” of the statement in cell L18.
6. Enter the “Deposits/Payments” of the statement in cell L20.
7. 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.

Usage

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.

Note

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! Once we get the bugs worked out (and confirm others find it useful) I’ll release a version for Excel as well.

[martha.rudkin]

I’m glad to see that my template inspired yours. Your solution is another great way to approach monitoring those monthly statements. Thanks for sharing and reaching out to me.

[jpfieber]

I found an issue in column F so I updated the formula in the shared template.

[randy]

This is super cool, @jpfieber. Thanks again for providing all your great templates on both Excel and Sheets.

After spending some time learning how to use your Excel version earlier today, this Sheets version was immediately familiar. You already saw some notes and feedback I provided on the Excel side.

One super minor fix… you are missing some currency and date formatting in the detail section.

Thanks again! You are crushing it!

[jpfieber]

Thanks! Just updated the shared template with the formatting corrections.

[jpfieber]

The Account Reconciliation template is now available through the Tiller Community Solutions Add-on, I’ve updated the documentation to reflect this.

[YouBet96]

Thanks for this sheet. Came here to see if there was something different than the “Statements” sheet because that one is showing I’m out of balance with my latest CC statement and it’s hard to nail down the problem without manually going through every item. I can’t seem to find one.

Downloaded this one and it shows I’m in balance so that tells me there is an error in the formulas on the “Statements” sheet.

[jpfieber]

I used the Statements sheet for quite a while and never had an issue with it, besides it taking a while to complete. I would double-check the month/account that Statements found an issue with to be sure it’s clear before moving forward. It could be that their is an missing transaction but your opening balance is off so it’s not showing up in the Account Reconciliation sheet.

[sfergus1]

I’m just getting started with Tiller and this template is fantastic. Coming from Quicken, it is so much simpler and the “errors” around the beginning/end are so much easier to fix.

[YouBet96]

N/M. Figured it out.

You paste over previous reconciles.

[bryman]

Hi,

I just installed this on top of Ultimate Envelopes 22.2 which is based on the foundation template. The Tiller Ending Balance and Tiller current balance show “#REF” for any account I choose. However, the troubleshooting pane seems to work fine. I’m guessing a column is out of place. What should I check or modify to get it back in alignment?

Thank you

[bentyre1]

Thank you @jpfieber, the new template of Account Register - Google Sheets led me here to start the process for that sheet. This is very close to what I have been looking for and I feel like I may be able to use these “verified” balances to project my balances more appropriately.

[skwirler]

This is great! Thank you. I have two questions. For one of my accounts, I need to enter the “Account Start/Open Info” every month. Do you know why that might be? The “Tiller Ending Balance” turns green after I enter that each month. Second, have you given any thought on how to manage all the previous reconciles as you keep using it? I’m scrolling down to line 50 now, and soon it won’t be too many months before it’s over 100 lines down and farther. I tried adding cells under row 5 but that caused problems. I guess I could hide from row 27 to the current reconciliation month, but maybe you’ve already thought of something more elegant?

[jpfieber]

You should only need one line per account that gets reused every month. Once you have the first month reconciled, the opening balance stays the same forever, you just update the date and closing balance each month when you get your statement. If, after doing that, the Tiller ending balance doesn’t turn green, then that means somethings wrong. Either you’re missing or have duplicate transactions, or the date one of your transactions needs to be adjusted to fall in the previous or next statement period (using the “Reconcile Date” column on the Transactions sheet). If it turns green, your statement matches your transactions and you’re good until the next statement. I have 11 accounts that I reconcile, so only 11 lines are used in the Account Reconciliation sheet. Each time I get a statement I just update the end date and end balance for that account, verify it matches the tiller ending balance, and that’s it. Rarely have I had to go and fix transactions (I think I had 12 that needed fixing over the 7000+ transactions I have).

[skwirler]

Got it! I worked it all out. Thanks so much. I’m 100% reconciled now! So easy.

[ctrella]

Thanks for the great template. One suggestion, if you could highlight rows if your last statement is more than a month old which would be a reminder to get your new statement

[jpfieber]

I like that idea! I just updated the shared template to include it, you should see an update available in Tiller Community Solutions once @randy pushes the update.

Change: Added conditional formatting to highlight accounts that haven’t been updated within 31 days.

[randy]

Just updated this, @jpfieber and @ctrella. Should be live shortly. (Thanks for your patience.)

[judyandjeff2]

Hi - how can I get my transactions on the transactions tab to be marked with an “r” for reconciled once I’ve reconciled? Please advise. Thanks!

[jpfieber]

This workflow was specifically designed so you don’t have to worry about that. If you prefer each transaction to be marked, “Bank Statement Reconciliation Sheet ” or “Simple Account Reconciliations ” may be worth looking at.