Short Term Cash Flow template - project an account balance over future months, reconcile checks

Note: I’ve released an entirely new version of this sheet, called Scheduled Transactions: Short Term Cash Flow template - project an account balance over future months, reconcile checks - #18 by rhowell
It has the same purpose but automates transaction reconciliation, calculates credit card payments, and projects balances for multiple accounts.

OVERVIEW

This sheet projects an account’s cash flow over the next few months by pulling its most recent balance from the Tiller core Accounts sheet and adding it to future transactions. Transactions can be set to recur and are used to automatically generate transactions for the next month.

It allows for free entry of future income and expense transactions. It generates future transactions based on current ones, with a recurring frequency of any number of days or months. It maintains a record of reconciled transactions for as long as they’re wanted. Transactions can be freely sorted, moved, copied, and pasted; rows can be inserted and deleted; and columns can be added and moved; all without breaking the formulas. It does this on a single sheet using only in-sheet formulas. The chart reflects balances obtained from both entered and generated transactions, and differentiates between them. (I think this makes the chart similar to Quicken’s Projected Balances graph, though I haven’t used it.)

I built the sheet because I needed it. My wife and I share a checking account. Historically, we’ve had a positive cash flow, and haven’t paid much attention to the account balance except when periodically transferring money out for savings. Recently, we undertook a large home project. We paid for it from the checking account, but funds came from other accounts. We quickly realized we needed a way to know when to transfer money into the account to cover upcoming expenses. I started with a simple sheet that pulled our checking account balance and added it to income and expense transactions that we entered below. This worked, but many of our expenses recur periodically and it became tedious to re-enter them each month. I considered using a few Tiller solutions, e.g., the Projected Balances and Bill Payment Tracker sheets, but they didn’t seem to incorporate one-off transactions such as the checks we were writing for our project. I wanted to maintain the simplicity of our table, with its balance line and free-entry transactions, while incorporating an easy way to generate copies of recurring transactions for the next month. After many months, this is the result.

INSTALLATION

Requirements

Your Tiller workbook must contain the core Accounts sheet. It supplies
– the list of accounts to select from and
– the current balance (Last Balance) of the selected account and the date it was last updated.

Copy to your Tiller spreadsheet

To uninstall

  • Delete the tab.

SETUP

Select an account

Find the balance row. It’s the blue-highlighted row with – surprise! – BALANCE in the Description column. Select the account you want to use from the drop-down in the Notes column in this row. The balance row’s Actual Amount column will now always reflect the most recent balance for the account and its Actual Date column will reflect the date it was last updated. (See the Options section, below, if the account you want to use doesn’t appear in the drop-down.)

Enter transaction data

Start to enter data for future transactions in the green area. You can delete all the existing transactions or edit them. Insert, delete, move, and sort rows as you wish – mostly. Specifics are under “(Lack of) brittleness,” in the Notes section, below.

Refer to the annotations in the header row to learn how the values are used.

Set transactions to generate future copies automatically

The sheet automatically generates future copies of transactions that are set to recur periodically. Generated transactions appear to the right of the chart. To edit them, you’ll need to copy and paste them into the main (green-celled) table.

To make a transaction recur, enter values in its row for the columns labeled
Recurs Every,
Day(s) or Month(s), and
Recurring Date.

The sheet will generate future copies of any transaction row in the main (leftmost, green-celled) table where
– the three recurring columns columns, above, aren’t blank,
– the sum of Recurs Every and Recurring Date is within the period set in cell Y1, and
– the sum of Recurs Every and Recurring Date doesn’t appear as the Recurring Date of another transaction in the main table with the same Description.

Future copies will be generated until
– the sum of Recurs Every and Recurring Date exceeds the period set in cell Y1, or
– the sum of Recurs Every and Recurring Date exceeds the Recurring Date of another transaction in the main table with the same Description.

Transactions that generate future copies appear in bold.

Set the period for which transactions will be generated.

Change the number of months in cell Y1 to adjust how far into the future transactions will be generated and charted.

WORKFLOW

Daily-ish

I look over the sheet every day or two.

  • I check the Reconciled box for transactions that have hit the account since the last time I looked. (Note that the balance available to Tiller usually lags the actual account balance; reconcile accordingly. A transaction that has hit your bank hasn’t necessarily hit Tiller.)
  • I refine the list of transactions and their amounts.

Monthly-ish

  • I prune the rows of reconciled transactions. Use caution when deleting rows that appear in bold since those rows are used to generate future transactions.
  • I move generated transactions for the next month to the main table by scrolling to the right, copying the generated transactions for the next month, scrolling back to the left, and pasting those transactions into the green cells at the bottom of the main table. (Paste ‘values only’ to preserve formatting). I then go through the transactions I copied, checking that they’re still needed and entering actual amounts.
  • I enter new rows in the main table to capture future changes to recurring transactions, such as a pay raise or the last car payment, setting the Recurring Date to the effective date of the change.

PERMISSIONS

If you share this sheet, here or elsewhere, modified or not, you must credit me and link back to this post.

OPTIONS

There is one editable cell in the header row in the hidden columns at the right side of the sheet. It controls the filtering of data queried from the Accounts sheet.

  • If the account you want to use doesn’t appear in the drop-down in the balance row, uncheck cell AN1 (if you haven’t moved anything around). The drop-down in the balance row will now list all accounts classed as Asset rather than listing only checking and savings accounts.

TROUBLESHOOTING

  • If you’ve made changes to a transaction but they’re not reflected as expected in the generated copies of it, you may not have edited the most recent (by Recurring Date) transaction with the same Description. If there are multiple copies of a transaction with the same Description and the same recurring settings, only the copy that appears lowest in the main table will be used to generate future copies.

  • If multiple copies of the same transaction are being generated, check that all copies of it in the main table have the same Description and recurring frequency.

  • See above, under Options, if the account you want to use doesn’t appear in the drop-down on the balance line.

  • To end a recurring transaction, you’ll need to maintain a copy of it with either a $0 Estimated Amount or the recurring data deleted (from the Recurs Every, Day(s) or Month(s), and Recurring Date columns) until you choose to delete all copies of it from the main (left) table.

NOTES

Limitations and Known Issues

  • Transactions can only be set to recur by multiples of days or months. This means that, for example, bimonthly paychecks must be entered as two separate transactions with different Description values, such as John paycheck 1 and John paycheck 2, set to recur on a different days of the month. The lack of additional options is intentional. It reduces the complexity of parsing more recurrence types, increases the readability of the formulas, and makes the expected results very clear at the small cost of requiring a few more transactions rows.

  • There’s no option to set recurs-by-month(s) transactions to recur at the end of the month, so any set to recur on the 29th, 30th, or 31st will be forced to recur on the 28th. I may adjust the logic in the future to address this issue. For now, you’ll need to edit the Actual Date after copying to the main table.

  • Only one account can be tracked. I considered including the ability to add additional accounts, but I think it’s easier and clearer to make a copy of the sheet for each account you want to track. You won’t break anything by renaming the sheets.

  • Transaction entry and reconciliation is completely manual. I considered pulling and manipulating data from the core Transactions sheet, but I decided against it for a few reasons.
    – There’s no way I can see to definitively link balance data to transactions. This makes automatically reconciling transactions difficult. It could be done approximately, but I think I would spend as much time checking and overriding auto-reconciliations as I do manually reconciling. (My thinking might change if the Transactions sheet pulled a balance along with each transaction, but I don’t know if this is even an option from Tiller’s data provider.)
    – Using the Transactions sheet to generate recurring transactions would require adding a column to that sheet; scrolling, searching, or filtering past a lot of irrelevant data; and lots of toggling back and forth between sheets. It would also require maintaining the list of one-off (non-recurring) transactions in a separate table from the recurring ones. I prefer to see all transactions in one table, even if that has a higher setup cost.
    – I don’t want to be forced to maintain one-to-one relationships between rows on this sheet and rows on the Transactions sheet. For example, I don’t want to have to split cash withdrawals on that sheet just to reconcile them on this one. I also like the simplicity and speed of entering single-row transactions on this sheet that summarize what may show up as multiple transactions on the Transactions sheet.
    – I like that checking a box is all that’s required to reconcile a transaction. I don’t have to enter the precise amount of the actual transaction if I don’t want to.

  • Erroneous $0 points may appear at the right end of the chart:
    If data doesn’t exist in one or more series in a chart’s range, Sheets won’t preserve the settings applied to each series, but rather will apply them only to the series that do have data, in the order they appear. In order to keep the colors and shapes in the chart assigned to the same series (“Projected Balance (+)”, “Projected Balance (-)”, etc.) as the data changes, I’ve had to create a $0.0001 balance at the end of any series that would otherwise have no data.

(Lack of) brittleness

This sheet is designed to be as robust as possible because I find that the freedom to insert, delete, move, and sort rows makes it easier to capture transactions as I think of them and keep the sheet up to date. You should be able to

  • delete any row except the header row and the balance row,
  • move any row except the header row,
  • insert row(s) anywhere except above the header row,
  • copy and paste* in the editable (green) area,
  • sort rows, and
  • move and insert columns

without breaking the sheet. Please let me know if you do break it so I can improve it.

*Note that cut, copy, and paste operations are likely to mess up formatting (including conditional formatting) and data validation, none of which will break the sheet, but may make it less useable. You’ll want to avoid cut operations and use ‘Paste values only’ to hedge against this problem. This applies to any spreadsheet, not just this one, because of the way formatting is implemented.

Most of the sheet’s flexibility is achieved by putting every formula in the header row. As long as the first row isn’t changed, the formulas are preserved.

There’s an annoying Sheets behavior that, if a row is moved from a position covered by a result array to a position below (or, presumably, above, but I haven’t tested) the result array, it brings with it the result row from its old position. To ensure that rows can be moved freely, we need to prevent the accumulation of these orphaned results from blocking the automatic expansion of arrays. My solution is to stack empty arrays below all result arrays that don’t fill to the bottom of the sheet (e.g., below query results):

=let(this_result_,
query(...),
{this_result_; makearray(rows(A:A)-rows(this_result_), columns(this_result_), lambda(r_, c_, iferror(1/0)) )})

Array formulas with input that includes one or more entire columns (e.g., A2:A) should already fill to the bottom of the sheet and don’t need the padding.

Dynamic references

I wish the query() function lived up to the promise of its documentation and accepted references to header names. As of October, 2023, it doesn’t. There’s a clever workaround that wraps query() in a lambda function to replace header name inputs with Col1-type references while remaining reasonably readable: google sheets - Can I use column headers in a =QUERY? - Web Applications Stack Exchange
This eliminates the helper table and indirect()s otherwise needed to create dynamic references to the Tiller Core sheets and, I think, speeds things up a bit.

CHANGELOG

1.12 (2023-11-15)

  • Bug fix: corrected problem where a missing Recurs Every value stopped transactions from generating.

1.11 (2023-11-09)

  • Bug fixes: refined transaction generation formula to stop rejecting end-of-month transactions in short months; corrected mangled query resulting from custom error messages from previous debugging; and stopped transactions from recurring if a future copy exists in the main (left) table, whether or not that copy is set to recur.

1.1 (2023-11-01)

  • Added ability to generate transactions for a user-entered number of months into the future.
  • Added ability to generate (fill in) transactions between two user-entered transactions.
  • Changed chart to include balances calculated from generated transactions. Changed appearance of chart to reflect the source of each balance.
  • Removed option to calculate balances by position.
  • Hid column labeled If row is used to generate transactions or is the balance row. Added conditional formatting to highlight (in bold) transactions that were previously identified by this column.
  • Swapped the columns under which “BALANCE” and the selected account appear in the Balance Row.

1.0 (2023-10-16)

  • Initial release.

Planning for future cash flows! This addresses a really important riddle, especially for an expensive endeavor like home projects (my wife and I went through this ourselves a couple years ago). Thanks for building, sharing, and documenting this @rhowell !

2 Likes

Hey @rhowell!

Thanks for documenting and sharing your short-term cashflow template. Forecasting balances based on recurring transactions is quite a riddle. While many people throw up their hands, it does seem that some brave builders have optimized workflows that work well for them particularly once the groundwork is laid and the forecast transactions can be refined with time to improve accuracy.

Your sheet allows a user to forecast cashflow and works particularly well for those in the midst of a home project and using one account for the transactions (like a checking account). It uses Tiller Money feeds to maintain the latest balance from the funding account and then projects from there as forecast transactions are reconciled manually. When the forecast and reconciled transactions are maintained, the chart is a really cool tool to see where your balance is heading. The tooling to build the recurring transaction rows is well implemented— much like what we’ve seen in some other solution shares.

It would be pretty cool if the tool integrated more with the Tiller Feeds transactions data— both to pull in recurring transactions as the forecast is constructed and also to help reconcile transactions as they land in the Transactions sheet.

We are grateful for your share as it inspires and provides a launching pad for others facing similar riddles. Through our Builder Rewards Program, we offer cash awards for template submissions. Your template is a great starting point for someone who wants to project their cash flow into the future during a project and who is willing to do the manual work to keep up with their project. Tiller is excited to award you $75.

:trophy:

3 Likes

Thanks @randy ! I’ve been thinking about ways to incorporate the Transactions sheet, but haven’t decided how or if to proceed.

I’ve uploaded version 1.1 to my shared spreadsheet, which

  • makes the transaction generation more robust (will fill in missing transactions),
  • allows the user to generate and chart transactions more than one month at a time, and
  • removes the option to calculate balance by position.

For now, both versions are available from my shared sheet. Once I update the original post, I’ll remove the older version.

I love that you’re already thinking about enhancements, @rhowell. No pressure at all to implement. Just an idea if you wanted to go further with what you’ve built.

Thanks again for sharing your workflow.

VERSION 1.1

I’ve updated the sheet to generate and chart transactions for a user-entered number of months into the future. It also will generate transactions to fill in between two entered transactions. This allows the user to capture a future change to a recurring transaction and have the sheet generate the recurring transactions both from now until that change and from that change on. For example, to capture a raise, you enter a new transaction with the same name as your other paycheck transactions, set the recurring date to the first pay date that will reflect the raise, and set the amount to your new pay.

The chart reflects balances obtained from both entered and generated transactions, and differentiates between them. (I think these changes make the chart similar to Quicken’s Projected Balances graph, though I haven’t used it.)

I’ve made a few more minor changes as well; they’re reflected in the changelog.

I’ve been experimenting with this template today and I really like it. It does almost everything I want. It will be replacing a more labor-intensive solution that I created for my own use a few years ago.

Bug report

I think I’ve found a bug. Here are the steps I used to reproduce it.

  1. Copied your shared sheet to my Tiller spreadsheet.
  2. On the BALANCE line, changed the dropdown value to my checking account.
  3. Selected and copied all of the generated transactions (cells AA2 to AH58).
  4. Pasted (values only) at cell A57.

This removes all of the original generated data rows, but leaves two “Sequence Error” entries in cells AA2:AA3 and AF2:AF3 and some random-looking data in other cells.

I originally triggered this bug while experimenting with my own transaction data, but was able to boil it down to this sequence of steps.

Enhancement request

I typically pay off the total balance due on each of my credit card statements each month. I’d like the Short Term Cash Flow (“STCF”) sheet to more accurately track the effect of my credit card balances
on the projected balance of my checking account. I think this can be done without having to grovel through data in the Transactions sheet.

Proposal: add a Closing Date column to the STCF sheet. When a cell in this column contains a date, the Notes column in that row gains a dropdown containing the credit card accounts in the Tiller Accounts
sheet. Selecting a credit card account in the Notes column causes the most recent Balance column value for this account in the Balance History sheet that chronologically precedes or equals the closing date to be copied to the Actual Amount column in the STCF sheet.

As Tiller adds Balance History entries, the most recent actual amount for the credit card account is automatically updated in the STCF sheet. Once the closing date passes, the Actual Amount stops updating and should now equal the credit card statement amount due. The Recurring Date for the row indicates the date this amount is deducted from the checking account.

When a new row is generated and pasted in for this credit card account, its Closing Date column is incremented by a month and its Actual Amount column is once again filled with the most recent Balance column value for this account in the Balance History sheet that chronologically precedes or equals the new Closing Date, minus the Amount in the preceding row for this credit card account.

Once the Recurring Date of the preceding row passes, we can assume that more recent Balance History entries for this credit card account are net of the payment on the last statement, so the Actual Amount for the latest row no longer needs to subtract the Actual Amount in the preceding row.

A user who doesn’t wish to track credit card balances in this way can delete the Closing Date column, hide it, or ignore it.

I hope this is both understandable and feasible to implement.

1 Like

Hey @sskennel ,

Thank you for the feedback! I’ll look into the bug and post a fix asap.

As for the enhancement request, that’s an interesting idea. Let me play around with it to gauge what would be required and it’s effect on the legibility of the sheet.

I think I understand what’s happening. When you copy and paste all the generated transactions, there’s nothing left for the sheet to generate, so you see the sequence error. I’ll update with a more descriptive error message.

If you have a chance, would you confirm that if you either
(1) copy and paste fewer than all the generated transactions, or
(2) increase the number of months in cell Z1,
there are no errors?

Thanks for looking into the error. I did two experiments:

  1. I copied all but the last row of the generated data (2/28/2024 cable bill) and pasted the copied rows (values only) at A57. The 2/28/2024 cable bill remained in the generated data area, followed by the two sequence error rows.

  2. I increased the months value in cell Z1 to 4, then copied all of the generated data and pasted it to cell A57 (values only). All of the generated rows were removed, and a row was added containing only 03/29/2024 in the two Generated Date columns, a Generated Estimated Amount of $2,000, and a Gen. Reconciled cell set to FALSE. There is no Generated Description.

I don’t think your hypothesis that emptying the generated data area always leads to the error is correct. In a STCF sheet I’ve populated with my personal data, I copied all of the generated rows to columns A:I and was left with “No transactions have a Recurring Date plus Recurs Every value that falls between 11/1/2023 and 2/29/2024.” in cell AB2, which seems exactly correct. There’s something more complicated going on.

1 Like

Thanks for looking further into this. I’ll let you know what I discover.

1 Like

@sskennel You actually found two bugs and I caught a third while investigating the two you found. Thanks very much for the testing you did! I’ve uploaded an updated copy of the sheet to my shared drive – it’s called Short Term Cash Flow 1.11. I’ll leave it there for a day or two to ensure I’ve solved the problem and haven’t introduced any other bugs, and then I’ll make it the only available version.

  • One bug was a result of careless use of the edate() function when generating dates for recurs-by-month(s) transactions. A comparison rejected transactions set to recur on days 29, 30, or 31 when the number of days in the month decreased (such as in February). I’ve updated the sheet to force all recurs-by-month transactions with recurring days > 28 to recur on day 28, which I erroneously assumed would eventually happen and is what the documentation says should happen.
  • The second had to do with the case where there is only one generated transaction. The Description and Day(s) or Month(s) values weren’t getting copied over. When a transaction was generated for only one period type, a result was still generated for the other period type , but it was filtered out by a query. (E.g., if one recurs-by-month transaction was generated, a recurs-by-day transaction was also generated, but hidden.) The hidden result was mangling the query result because the result returned values (custom errors from previous debugging) of different types than the columns in which they occurred.
  • The third was that the sheet wasn’t gracefully handling cases where there are several copies of a recurring transaction in the leftmost table and the user deletes the recurring information (columns D, E, and F) in one of those copies. I think most users would expect that this would stop the transaction from recurring from that point on (unless another copy restarted it), and I updated the logic to reflect this expectation.

Thanks for this, Im trying to convert my wife from her budget to mine. Its quite painful . its compounded because im such a noob at this. Im hoping this will help us see how much we have to pay after each paycheck(every two weeks) instead of monthly. A month is a long time to wait to see how much you have.

Excellent! Thanks so much.

Just wanted to say this is really awesome and exactly what I was looking for. I’m nearing the end of my Tiller trial and before I found this, I wasn’t sure if I’d continue because I hadn’t found a template that does what I need. Now I’ve found it!

1 Like

Glad you’ve found it helpful!

I’m close to releasing a new solution that builds on this template but with the major additions of automatic transaction reconciliation and automatic credit card payment amounts.

1 Like

Sorry I missed your reply to my bug report back in November. I really appreciate the bug fixes. I’ve updated my copy of the sheet to 1.12 now and everything seems to be working well. I’m looking forward to the enhancements you mentioned.

— Roger

1 Like

@sskennel and others who may be interested:

I’ve re-worked the Short Term Cash Flow template to add

  • ability to track multiple accounts
  • automatic reconciliation of transactions (complete with links to the Transactions sheet) and
  • automated credit card balance calculation.

The changes are substantial enough that I’m renaming the template (to Scheduled Transactions) and re-starting the versioning. Please check it out:

I’ll formally release it when I have time to create documentation. However, the in-cell notes should be good enough to get you up and running. I’d very much appreciate feedback and bug reports.

Some additional info:

  • Your Tiller workbook must contain the core Accounts, Balance History, and Transactions sheets.
  • Install is the same: open my shared spreadsheet and copy the Scheduled Transactions tab into your Tiller spreadsheet.
  • Setup requires you to copy your Transactions sheet’s URL (“https://docs.google.com/spreadsheets...”) and paste it into cell BC1. After you’ve done that, you can click the plus sign above cell BD1 to hide the setup area.
  • There’s no longer a balance row. Instead, each scheduled transaction is assigned to an account.
  • To clean up the sheet, I’ve collapsed various columns into groups. Click the plus signs above columns M, Q, and T to see the collapsed columns. (You can reorder columns and change the grouping to fit your workflow: Right click on the column header(s), scroll down and select View more column actions, and find the group/ungroup actions.)
  • You should be able to copy and paste transaction data from your Short Term Cash Flow sheet and have it work. Just make sure to paste it in the correct columns. (There’s now an Account column to the left and the reconciled column is separated from the rest of the entry data by additional columns.)
  • Note that there’s still no way (that I can figure out) to definitively link data from the Balance History sheet to data from the Transactions sheet. This means that you’ll need to double-check (and possibly override) the reconciled status of recent (within the last few days) transactions to ensure that they’re applied correctly. To help, this sheet uses the account balance as of the date of the most recent transaction rather than the latest account balance available, but it’s not foolproof. (Columns R & S show the date and amount of the balance that the sheet is using.) If you’d rather use the latest balances available, set hidden cell DZ1 to 0. (The default is 2, which minimizes the need to manually reconcile transactions that are only a day or two old.)
  • Check the in-cell notes in cells C1, J1, K1 and L1 to calculate credit card balances.
2 Likes

Fabulous. I can’t wait to try this!

Here’s a problem: Asset Account Start Balance in column R1 did not update when Tiller pulled in a fresh balance for my checking account. (The balance did update in my Short Term Cash Flow sheet.)