Tracking cash transactions with automated balance updating

This workflow allows you to keep track of cash transactions and for each manual transaction see your balance update on the Balances sheet in the Tiller Foundation Template (or any sheet where a Balances, Balance History, and Transactions sheet exists).

1. Manually add transactions for cash including the “transfer” to the cash account from the ATM.

You should already understand the basics of cash tracking for transactions with Tiller. In my example, I use the Super Tidy Cash Tracking method.

2. Create a “cash” account in the Balance History sheet.

In order to have the “cash account” show up on the Balances sheet we need to enter a Balance History record for the “account” - everything appearing in the Balances sheet comes from Balance History.

Insert a row above row 2 on the Balance History sheet (possibly hidden in your sheet) and enter =today() for the date. This formula will ensure that your balance for Cash is always current and at the top of your Balance History sheet.

Fill in the other details

  • for Time you can just enter 12:00AM
  • for Account enter “Cash” (or whatever name you like for cash)
  • make up a unique account number formatted like the others
  • make up a unique account ID that starts with manual:xxxxxx (NOTE: this column is probably hidden. It is required for the account to show on Balances. Unhide it to enter a manual ID)
  • enter =SUMIFS(Transactions!E2:E, Transactions!H2:H,"Cash")for the amount. This will sum up transactions on the Transactions sheet that are assigned to the “Cash” account. If you use the Super Tidy Method this balance should be whatever cash is still in your wallet.
  • Enter =(if(row($B2)=1,"Month",iferror(date(year($B2),month($B2),1)))) for the Month. This assumes your dates are in column B and your cash balance line is in row 2.
  • Enter =(if(row($B2)=1,"Week", iferror(DATE(YEAR($B2), 1, -3 + 7 * WEEKNUM($B2) - WEEKDAY(DATE(YEAR($B2), 1, 4), 1) + 1))) ) for the Week. This assumes your dates are in column B and your cash balance line is in row 2.
  • Enter Cash as the Type and Asset as the Class.

Now each time you add a new Cash transaction to the Transactions sheet your balance will update automatically on the Balances sheet.

Important notes:

This method will not give you a historical view of your Cash account. It gives you a current day snapshot based on what’s in your Transactions sheet. If you prefer to have a historical view of your cash account balances you’ll need to create a manual entry in the Balance History sheet each time you add a cash transaction.

If you get a negative balance for your cash it means you likely didn’t enter the transfer from ATM to the “Cash account” as two transfer transactions on the Transactions sheet.

2 Likes