Creating a Projected Balances sheet

Google Sheets
1

I’m a new Tiller user and working on a new template (initially for me, but something I’d like to share), separate from but built on top of the core Balances sheet. My idea is to take the reported bank balances, and subtract off any manually entered transactions that marked to be cleared. My goal is to give a clear projected future cash flow.

So for example if I had a $2000 checking account balance today, but several future payments totaling $1500 that I’ve entered manually, the Pending Balances sheet would report $500 for this account.

Working from a copy of the Balances sheet, I’ve made some initial edits to get the Sheet References updated (including the Account, Amount, and Metadata columns from Transactions). My desired algorithm from there is to add a column to the “Sorted Assets” and “Sorted Liabilities” table data computing the sum of the transactions, and then modify the values displayed with Balance - Transactions.

Assuming the Account reference is in $Z$23 and the Amount reference is in $Z$24, then I can use a (simplified) formula like =SUMIF(INDIRECT($Z$23), "=Checking", INDIRECT($Z$24)) (“Checking” would be dynamically replaced with the Account in each row of Sorted Assets/Sorted Liabilities). This gives me all transactions for that account.

The trick here is parsing the JSON stored in metadata. I could do it with a script, but from the builder’s guide, looks like those are discouraged. I could do it with a regex, but that could get a bit slow for an account with a lot of history (to be fair, so could a script). For anyone not sure what the metadata looks like, here’s a sample:

{"manualTransaction":{"reconcile":true,"ignoredIds":[]}}

I need to go after anything where manualTransaction.reconcile = true.

Any suggestions on the best way to accomplish this? What does the Reconcile Transaction logic use to get at this data? Or where can I find that logic to inspect myself?

2

I think I have the kernel of a solution to my problem, using QUERY.

Something like this does what I want:

=SUM(QUERY(Transactions!$A$1:$T$735, "SELECT F WHERE G = 'Checking' AND S MATCHES '.*reconcile.:true.*'", 1))

Where I can copy that down through the account listing and fill in 'Checking' with an appropriate cell reference. I already have the code to dynamically get the letters for the columns and the range for the query.

3

I got it working for my use case, and it’s relatively generic, so could in theory be used by others with a similar need. I have some questions about doing things “the right way” to make it redistributable, if anyone with more experience in the community would have time to look it over and send me feedback.

4

@sfergus1 I have a feature request for a similar idea. See here: Projected Balances. I’m still doing this manually but I’ve changed my template up quite a bit. Altogether, though, it’s much more dumbed down solution compared to what you’re trying to do. I’d be curious to see what you ended up with if you care to share.

5

Sounds like you are on the right track, @sfergus1. I think you could probably make it work with =SUMIFS() (where you use both the account and the reconcile flag) or =SUM(FILTER()) though QUERY() works fine too. I’d consider extending the (hidden) Accounts sheet where latest balances are tabulated to feed the Balances sheet. You could add a column in there called “Pending Transactions” or something, aggregate it for each account, and then roll it into the “Last Balance” column (with a custom formula) which feeds other sheets like Balances.

6

@chrisgp123 I have something working that I should be able to share. What’s the best way to do that? Just copy the tab to a new sheet and share that, for you to copy?

7

@sfergus1 Yeah I think something like that could work, if you’re willing to share. Thanks!

8

Feels like this is similar to the “Account Register” template I released, but I come at it the opposite way. Instead of subtracting from “Balances” I add up “Transactions”, and then show the balance for the account after each transaction. I would be interested in seeing your solution to compare how they differ, and which approach works better for which situations.

9

I’ve copied my “Projected Balances” sheet to a new Google sheet. I’m assuming this should work okay if copied into a Tiller sheet. My implementation is essentially a drop-in replacement for the “Balances” sheet. Anyone that tries it out, let me know if you have any trouble, or suggestions for improvement.

I’ll grant that it doesn’t necessarily make sense for all account types to have a projected balance. My use case is mainly for my Checking and Credit Card accounts, so I can see side-by-side what the current and future balances are with manually entered transactions that have not yet cleared.

10

I just copied it to my template, and it seems to be working. I don’t normally work with reconciling transactions using the reconcile flag, but I can now see I have some reconciling to clean up! Nice job, you should create a Show & Tell post for this, I’m sure there are others that might benefit from your work. I’d hide the ‘helper’ columns (I usually group them and then collapse the group), and add some column headers to help new users understand which column is Actual, and which is Projected.

1 Like
11

Thanks for the feedback! I should have some time next week to tinker with it a bit more and write up a post. In the meantime, I’ve hidden the helper columns in the sheet I linked earlier in this thread.

12

Just to follow up, I haven’t been ignoring this. I just found that it doesn’t play nice with manually managed accounts (and reports misleading or incorrect data in some circumstances). I need to set aside some time to figure out how to adapt it so that balances + transactions are not double-counted (for example: a manually entered future payment with “reconcile:false” does not get counted in the projected balance). Once I get that resolved, I’ll put together a Show & Tell post.

I think I just need to look at the account type (automated vs. manual) and adapt the expression in the query based on that, but haven’t had time to test. In the meantime, I’ve just been deleting the metadata for manually entered transactions on my one manual account, which is not a long-term solution.

13

I’ve been thinking about this and I was thinking more of a two sheet solution. You have the Projected Balance sheet that has all the transactions and balances from the Transactions sheet and then you’d have a Manual Transaction sheet where you put you’re manual or expected transactions. Then, you’d simple sum the Transactions and Manuals. Once the item is downloaded into Tiller, you toggle the transaction on the manual sheet, so it doesn’t show up in the formulas.

14

Based on your initial post of what you’re trying to accomplish “take the reported bank balances, and subtract off any manually entered transactions that marked to be cleared. My goal is to give a clear projected future cash flow”, I wanted to do this as well so I created this sheet- Near-Term Account Balance Forecast.

This may accomplish what you want.