🏆 Scheduled Transactions template: Project future account balances; calculate credit card payments; auto-reconcile transactions

Google Sheets Show & Tell
, , , ,
18

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

image
image1592Ă—709 130 KB

image
image1567Ă—707 126 KB

image
image1386Ă—703 120 KB

image
image1643Ă—706 156 KB

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. If you find this to be a problem, the sheet can be set to use the account balances as of the date of the most recent transactions rather than the latest account balances available, but it’s not foolproof. (Columns R & S show the date and amount of the balance that the sheet is using.) If you want to use the balances as of the most recent transaction dates, set hidden cell DZ1 to a value greater than 0. (The default, 0, sets the sheet to use the latest account balances available.)
  • Check the in-cell notes in cells C1, J1, K1 and L1 to calculate credit card balances.
2 Likes