Near-term Account Balance Forecast

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…

Screen Shot 2022-04-23 at 1.19.31 PM

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.

Great work @Cowboy13 !

I’m delighted to hear how you were able to take my Bill Payment Tracker and extend it to better fit your needs. And then to share it with the community! Even better.

Whenever I create a new template, this is exactly what I hope will happen.

Jon

1 Like

Thank you, it was a great start, some very cool formulas.

2 Likes

I can’t get the accounts in column BM to fill and the dropdown in column BP. Could this be due to your use of “checking and savings in the formula” I also have the same issue with your starting balances.
Thanks

Could be. The formula is looking for Account Type “Checking” or “Savings” on your Accounts sheet. On my Accounts sheet, this is column M. If you don’t have any Checking and Savings Account types, column BM will be blank.

If this is the issue, a quick fix; delete this restriction in the formula in cell BM1…

Let me know if this fixes it.

It fixes it but I’m unsure why it failed in the first instance because I have the “savings and Checking” type in my Account Sheet Column M. Can the same principle be applied to “Starting Balance” to make it work?

Column BP does not auto populate. You need to choose which accounts you want to include in column BP by using the dropdown and then check the box in column BO.

FYI- the reason you don’t want column BP to auto populate with the accounts is- if you add or delete or hide an account, it could rearrange the list of accounts you previously checked and the check box does not get rearranged. The sheet will then be using accounts you did not intend to use until you go back and recheck the correct accounts.

Hope this helps.

Also, no idea why column BM did not populate. What do your columns AI:AM show?

28.04.2022_12.00.08_REC

Sheet Column Name Column Lookup Start
Accounts Group P ‘Accounts’!P
Accounts Account H ‘Accounts’!H
Accounts Account # I ‘Accounts’!I
Accounts Last Update K ‘Accounts’!K
Accounts Last Balance J ‘Accounts’!J
Accounts Class O ‘Accounts’!O
Accounts Hide D ‘Accounts’!D
Accounts Type M ‘Accounts’!M

That looks correct. Not sure why BM didn’t populate.

Hopefully, you have you need now.