Simple Account Reconciliations

Google Sheets Show & Tell
1

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:

2

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

3

@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
4

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

5

Can this be used with the Envelope Budget template?

6

@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

7

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
8

Hi @martha.rudkin!

I added the “R” column in Transactions and added your Reconciliations and Account Registers tabs to my Tiller Foundation Template budget. I’m getting argument range errors so I know I’m still missing something.

The Accounts and Balance History tabs are hidden by default so I did not copy yours over and did not modify mine. Is that correct?

If I understand your notes, My Balances is optional and I did not copy that over.

Am I missing anything obvious?

I appreciate your help!

9

Are you using the foundations template? The necessary Tiller sheets are Transactions and Accounts. You will need to have at least row filled in on Reconciliations before the Account Register will work. My balances is not necessary. If you let me know which sheet is showing the errors and what cells, I can look later tonight to help troubleshoot it. I know at least 1 other person has successfully used it. Thanks.

Martha

10

I am using the foundations template.

In the Reconcilations tab, the Total, Unreconciled and Balance Status fields are all #N/A
In the Account Registers tab, I get

  • “0 transactions” for Unreconciled where I would expect hundreds since most transactions have nothing in the “R” column.
  • “-2 transactions” where I would expect nine which is the number that have a character typed in the “R” column.

Does that help?

11

Here is another clue:
D3 in the Reconcilations tab looks like this:

=if(isblank($A3),“”,(if((VLOOKUP($A3,{INDIRECT($K$4),INDIRECT($K$5)},2,FALSE))=“Liability”,(round(sumifs(indirect(#REF!),indirect(#REF!),$A3)-$G3,2)*-1),(round(sumifs(indirect(#REF!),indirect(#REF!),$A3)+$G3,2)))))

So all of the indirect calls to $K(something) fail. That tab only goes up to “G” columns so I don’t see how it works in the original either.

12

If you expand the hidden columns in the Reconciliations sheet, there is a column K which is trying to map back against the Accounts and Transactions sheets. Do you see the reference errors there as well?

It is expecting the Accounts sheet to have columns named Account, Class and Hide. It is expecting the Transactions sheet to have columns named R, Account and Amount. For instance in my spreadsheet, Accounts is mapping to columns H, O, Q and Transactions is mapping to columns H, I, G.

My column D3 expands to:

=if(isblank($A3),"",(if((VLOOKUP($A3,{INDIRECT($K$4),INDIRECT($K$5)},2,FALSE))=“Liability”,(round(sumifs(indirect($K$13),indirect($K$11),$A3)-$G3,2)*-1),(round(sumifs(indirect($K$13),indirect($K$11),$A3)+$G3,2)))))

Martha

13

Thank you! That was very helpful.

I now have the Reconciliations tab fixed. What happened is that I did not realize you had the hidden columns and I do not have so many accounts. So I deleted the extra accounts by deleting rows that had important cells on the hidden side of the sheet.

I still have an issue on the Account Registers tab. There I get an error on the “Last Statement Date” and on “Starting Balance”. The error is:

Did not find value ‘My Bank Account Name’ in VLOOKUP evaluation.

I noticed that the Accounts tab has all blank rows. Do I need to enter my accounts there for this to work?

Thank you so much for your help! I can already see that this will be much better than the old way.

14

Glad that we are making progress on getting this to work for you!

The Accounts sheet also has hidden columns (this is a Tiller sheet that I have made no modifications to). I THINK it does not matter if columns A-D are empty because Tiller has duplicated the information in their hidden column area and I had my sheets pull info from the hidden column area. I think they are building it from the Balance History sheet. Saying that, I populated Columns A-D with all my accounts when I first starting working with Tiller. You might try unhiding columns there and see if it continues to look blank or if you see your account names.

If you have an account listed on the Reconciliations sheet, you should be able to pull the same account up on the Account Registers sheet. I tried blanking out the Last Statement Date and Ending Balance on one of my accounts, and I am not seeing a VLOOKUP error.

I’ll keep tinkering and see if I can come up with any other suggestions.

15

I do see my accounts on the pick list in Account Registers and they are also in the hidden part of the Accounts tab.

I’m so happy with the Reconciliations tab I’m not even sure I need the Account Registers functionality. Is that running independent that I could just delete it?

I don’t mind keeping it around to help troubleshoot in the meantime.

Thank you!

16

Those 2 sheets are independent of each other. I have some time this morning again to see if I can recreate your problem and fix it. Glad to hear the reconciliation sheet is working for you.

17

@ehorlbeck

If you still have the Account Registers sheet and have time, would you mind expanding the hidden columns JKL and send me a screen shot of just rows 1-28 in columns JKL for troubleshooting? There shouldn’t be any private information there - the account list is farther down those columns and I don’t need to see the account list since you say the dropdown selector is working correctly.

Thanks.

Martha

18

Here you go!

image.png
image.png996×1536 193 KB

19

@ehorlbeck

Thank you! I can see where none of the Reconciliations sheet mapping has happened and that Note and Tags are missing from the Transactions sheet. (Do you see how Date down thru Class looks different for Letter and Range?)

Do you have the columns Note (singular) and/or Tags (plural) in your Transactions sheet? I am guessing that maybe you don’t.

Did you keep the sheet name “Reconciliations” (plural) when you copied it from my shared file? The sheet name needs to be Reconciliations in order for Account Registers to work correctly.

Would you mind trying to reinstall the Account Registers sheet from my shared file now that you have a good working copy of Reconciliations? Maybe you added Account Registers before you added Reconciliations the first time? I’m not sure why it hasn’t remapped correctly when you re-visit the sheet. If the reinstall doesn’t fix it, then I am stumped and I will try reaching out to the Tiller team to find out why the mapping is not working as I copied the mapping concept from their sheets.

Thank you!

Martha

20

Martha,

That is right. I do NOT have Note or Tags on Transactions.

The Reconciliations sheet is named correctly. I also recopied the Account Registers tab but get the same results.

However, I did more digging and can prove the issue is around the missing Notes and Tags. If I replace L:10 and L:11 with

=“Transactions!C2:C”

the page works with Notes and Tags filled in with Description data.

Does that make sense to you?

Kind regards,

Eric