Overview
This forecasts the total near term balances for the accounts I pay bills from based on current Balance and upcoming Income and Expenses from those accounts.
I keep the bare amount of cash in my checking accounts to cover bills. To make sure I have enough cash in these accounts to cover upcoming bills, I wanted a way to forecast my account balance based on current balance and future bills and deposits.
I was checking out @Jono’s The Bill Payment Tracker and thought it would be a good starting point.
Installation
Copy the sheet found here into your workbook. It uses the Accounts, Categories, and Transactions foundation sheets.
How it Works
It works similar to Jono’s Bill Payment Tracker referenced above. Plagiarizing his description, but updating the terminology, etc…
In the Transaction Setup section, you need to enter the details for the transactions you want to track that affect your chosen accounts balance.
Using this information, the sheet forecasts the total balance of your chosen accounts using today’s balance and the next 27 transactions you choose to include. Transactions that are overdue are listed in red. Ones that are due in the next 7 days are listed in yellow.
When the transaction is downloaded, select the checkbox in the correct month column to indicate it’s been downloaded. This will remove the transaction from the Account Balance Forecast section above.
For each Transaction, the Last Occurrence and Last Amount columns will automatically show the latest Transaction data where the Category matches.
How to use it
Expand the hidden columns to the right and in column BO and BP, choose the account names in drop-downs and check the “Include” box for accounts you want to include in the starting balance…
FYI- “BH Balance” is taken from the Balance History sheet. “SB Balance” uses the Starting Balances sheet I created which can be found here. If you don’t use the Starting Balances sheet, it will just use the balance from the Balance History sheet.
After this, it’s the same philosophy as Jono’s Bill Payment Tracker.
At the top, you enter the Year and Month you want to start tracking…
In the Transaction Setup section, choose first expected date, frequency of transaction, which categories you want to track, and estimated amount of next transaction. You do not need to put negative number for estimated expenses. The sheet handles that by knowing if the category is an income or expense.
As transactions come in, check the box for that month. This will remove it from the balance forecast above.
Permissions
It is ok for others to copy, use, and modify your workflow.
Notes
Because I get paid twice a month and the highest frequency the sheet supported is once per month, I started to update this sheet to allow twice a month tracking; but it got more complicated than it was worth. Instead, I created 2 categories for my 2 separate paychecks each month- Paycheck 10 and Paycheck 25.
In the Transaction Setup section, you can update the Est Amount as often as you want. For example, I pay off my credit card bills every month, but the amount changes each month. I update the Est Amount for these bills each month after the previous month bill is cleared, which is when you check the box as being completed.
Jono’s original Bill Payment Tracker sheet allowed you to match either Category or Description. I only allow Category.
Enjoy.
