I’d like to see an alternative way to project account balances forward. Similar to the existing Labs Projected Balances sheet, but deeper. This new sheet would let you pick a starting date, a time period, and which accounts to include (in the manual sheet I’ve been using I just track my checking and savings accounts; I don’t think other types of accounts would work as well for this). This proposed sheet would have a list of dates in the first column, starting with your starting date at the top and then going down until the end of your time period (my current sheet is projected out 12 months). The next column contains expenses in each corresponding day, the next column is income for each day, and then the next columns are balances for the accounts chosen. So by pulling in the transaction data and inputting some known recurring expenses and income streams, we can answer a couple of useful questions such as: what will my checking/savings account balance look like a year from now? Is it increasing or decreasing? And because I can see individual dates in the list, I can modify recurring transactions to test what would happen if, say, my paycheck gets halved for the next few months, or how much can I put into savings before my checking account starts seeing negative growth? This sheet would include a graph of the balances over time and a trendline to clearly show whether the accounts are trending positive or negative. I know the current Labs Projected Balances sheet does much of this, but in it’s current form I don’t find it very useful. I’d like to be able to see several accounts, and I’d like to fine grain tweak the recurring items to get a more accurate projection.
Could you share a mockup of what you are looking for? It doesn’t need to include the formulas. Just what the sheet would look like.
@jonorlin, I’ve attached a pair of screenshots to show what my current solution looks like. The first screenshot is my daily ins and outs and the account balances are shown on the right. Transfers between the 2 accounts get put in column E. I am of course manually putting all of this data in myself, and the balances in columns H-J are a rolling calculation based on the cells above them. Luckily, I don’t use my checking and savings accounts often so the manual data entry isn’t too bad. But you’ll notice that I’m rolling up my previous months into hidden cells in rows 3-2608 (I’ve been doing this for a while…) so that past data doesn’t show up in the charts. For future transactions that I can predict, I just put in my best guess in those future dates and highlight the cell yellow to remind me to replace my prediction with the correct value when I get to that date.
And so all of this work is to produce the graphs in the second screenshot, with the first graph being the total projected cash balance through the next 12 months, followed by charts for each account.
That’s quite impressive. Thanks for sharing the details.
I agree that doing it manually is not the ideal way of doing it. Especially when there are many transactions. And if a person had many accounts.
But this does sound like something that could be automated at least in part. It’s not something I can offer a quick solution on though.
Projecting future balances in more detail has been requested already in a few posts here. We will consider this concept, but I can’t make any promises.