Comparing Savings Budget to Account Balances

What is the goal of your workflow? What problem does it solve, or how does it help you?
It has always bothered me that there’s no good way to compare the amount of rollover in my Savings Budget to the amount of money actually in my bank accounts. I understand that trying to do a net zero calculation would be basically impossible, since transactions and balances all update at slightly different times. But I figured that maybe if I charted the data I would at least be able to see general trends, and reassure myself that the Savings Budget dollar amounts are generally accurate.

How did you come up with the idea for your workflow?
This sheet is the result of lots of different attempts to reconcile budget and account data. As I learned how Tiller Money works, I realized that what I needed was really just a combination of the Net Worth snapshot and the Savings Budget sheet. Starting there, I hacked together something that technically worked, but I originally had it only gather data on a monthly basis. I realized that it really wasn’t granular enough to be useful, so I decided that daily was really the best way to go. It’s unfortunate how computationally intense the sheet is, but I can’t really figure out a better way to do it. I’m definitely open to suggestions though!

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
The only custom sheet is my “Savings Budget Audit” sheet. Everything is contained within that sheet, no custom scripts. It does require the following sheets: Transactions, Categories, Budget Journal, Balance History, and Accounts.

To set it up, first scroll down to the “Accounts to Include” section and select all of the accounts that are included in the Savings Budget. Then back at the top select the range of dates you want to examine. There’s also a hidden option, if you unhide the columns to the right there’s a checkbox to determine whether to include or exclude hidden categories.

The “balances offset” at the top of the page shifts the account balances forward or backward by the input number of days. Feel free to leave this at zero, but examining the chart you may find that the two plots align better with the offset. For mine, the ideal offset is around -2.

The error is the difference between the Account Balances and the Budget Actuals. A positive error means there’s more money in your accounts than the budget would indicate (which is better than the alternative!). I suppose “Budget Actuals” is a bit of a contradiction, but what I mean is that it’s the Actuals from the Savings Budget, in reality just collected from the Transactions sheet. The percent error is this error divided by the Account Balances.

I have emphasized the median error because I feel like that gives the best picture of how far off the budget is from reality. The error fluctuates quite a bit day-to-day, but the median can hopefully show how it’s trending.

Anything else you’d like people to know?
I’ve tried to make this a fairly polished sheet, but really I just designed it for my personal use. I also only just finished it and I haven’t rigorously tested it, so I’m sure there will be bugs. Let me know and I’ll try to fix them when I can

Is it ok for others to copy, use, and modify your workflow?
Absolutely! I’d be thrilled to see other people take this and improve it, or customize it for their needs.

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

@matt This looks really awesome, I’m planning to add this to my worksheet. Thank you for sharing this with the community!

As I’m thinking through this, what do you do when the number is significantly plus or minus? Do you have to reset the savings budget somehow? I can’t figure out what I’d do if I found the balances were off from the budget.

1 Like

@Ngb12347 glad you like it, let me know if you have any more questions! I haven’t rigorously tested it so if you do things differently than I do it’s possible there are things I missed.

I don’t really have a mechanism for correcting the budget if it gets off. You could add our subtract from your Savings in whatever category makes sense I suppose. But since theoretically they should always match, a large discrepancy means that either I set my Savings up incorrectly to begin with, or not all of my actual transactions are being categorized in the budget. In that case I would have to do some investigating to figure out how to fix it.