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
- Open my shared spreadsheet: Tiller Foundation Template FOR SHARING - Google Sheets
- Right-click on the Short Term Cash Flow tab and copy it into your Tiller spreadsheet. It will appear in your workbook as Copy of Short Term Cash Flow. You can rename it.
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
Reconciledbox 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 Dateto 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
Assetrather 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 sameDescription. If there are multiple copies of a transaction with the sameDescriptionand 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
Descriptionand 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 Amountor the recurring data deleted (from theRecurs Every,Day(s) or Month(s), andRecurring Datecolumns) 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
Descriptionvalues, such asJohn paycheck 1andJohn 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 Dateafter 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 Everyvalue 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.
