Simple Account Reconciliations

What is the goal of your workflow? What problem does it solve, or how does it help you?
The Statements sheet in Tiller Labs is great but it takes a lot of time to use month after month. I decided that tracking the specific statement was unnecessary to me - those statements are available online if I need to refer to one. I just want to mark a transaction as Reconciled or Unreconciled.

Now when a new statement is available, I just need to enter the statement date and the ending balance. I mark the appropriate transactions as Reconciled and my Reconciliations sheet tells me if I am balanced.

How did you come up with the idea for your workflow?
I modified the Statements sheet and Statement Details sheets. My sheets are Reconciliations and Account Registers. They use most of the same formulas as Statements and Statement Details.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?

These sheets need the Transactions sheet and the Accounts sheet to work properly.

Reconciliations Sheet

  1. You need to add a Column named “R” to your Transactions sheet. I placed mine right after the Column named “Amount”, but you can put it anywhere.
  2. On the Reconciliations sheet, select an Account (Col A), enter the last statement date (Col B)and the ending balance (Col C).
  3. If the initial opening balance of your account was NOT $0, enter the amount in Col G. This number will not change again unless you archive data out of your sheet. Once all my accounts were loaded and balanced, I hid Col G so that I would not accidentally change it.
  4. Go the Transactions sheet and update your Column named “R” to be anything you choose for the transactions you want to Reconcile. I am using an “R” but the logic of the sheet is looking for blank (un-reconciled) and not blank (reconciled).
  5. Go back to Reconciliations and see if Balanced appears in Col F. If it is not balanced, it will show you an amount number to help you locate the potential problem.

Account Registers Sheet

Once you have entries in the Reconciliations sheet, the Account Registers sheet will show you running balances for either your Reconciled Transactions or your Un-Reconciled Transactions. Select the account and the type of transactions you want to see. If you do not want to see all the columns (Category, Note, Tags), simply hide the appropriate column.

Anything else you’d like people to know?

I have also included a sheet called “My Balances”. If you use Manual Transactions and/or future dated transactions, you likely want to know how your balances are affected by those transactions. Tiller has this information available on the Business Dashboard sheet - but I find it more useful to see the same information in the Balances sheet format.

I’ve been using these sheets for a couple of months now and hope someone else might find them useful too.

Is it ok for others to copy, use, and modify your workflow?
Yes.

If you said yes above, please make a copy of your workflow and share the copy’s URL:

Hi @martha.rudkin! Thanks so much for sharing this workflow and your sheet! I look forward to diving into it.

@marthagaultois I just used this workflow to reconcile and it worked great. It certainly was a faster workflow than the Tiller Labs solution… even with the learning curve!! I like the “My Balances” sheet that takes into account future dated transactions. I also like the concept of the running balance for reconciled or un-reconciled transactions… I found this really helpful for outstanding checks.

Thanks for sharing. I’m always looking for more intuitive ways to manage my finances.

1 Like

Hi Warren! Thanks for letting me know you liked it. Martha

Can this be used with the Envelope Budget template?

@ricklee57

I use the Foundations template and am not familiar with the Envelope Budget template.

The only Tiller sheets it needs to work are Transactions and Accounts. If you have those 2 sheets and can see/use the Bank Statements solution that is part of Tiller Labs with the Envelope Budget, then I imagine my sheets might work for you.

Maybe someone from Tiller will know what sheet differences exist between the Foundation and Envelope Budget, and offer an opinion.

Thanks for trying my idea out.

Martha

Thank you for creating this! I have used it on the standard Tiller Foundation Template and love it. If I can get it to work with the Envelope Template that would be great . I will let you know how it goes.

1 Like