Cash Flow Forecast hidden column AC doesn't have formulas for all categories

Google Sheets
1

I have 124 categories (some are hidden, about 102 show), and in the Cash Flow Forecast categories budget column AC, the formula stops at row 60, which means it’s not pulling in all my expenses. I’ll fix it by dragging the formula down. That seems to work. Maybe a future update can fix this? @jono

2

Hi @Richard ,
Well, you found the best solution. Take row 60, including the hidden part and drag the formula down for as many new category rows as you want. This will copy the formulas in columns P, Q, S, T and AC.

That should work just fine.

While you are certainly welcome to add all your categories to the Cash Flow Forecast, another option is to include only the categories on this sheet where you expect significant non-standard changes in the future.

All your budgeted income and expenses, your ‘core’ income and expense budget amounts, are already built into the sheet and can be given an overall growth rate.

From the initial How to Use post:

Core Income and Expense Budgets

The Cash Flow Forecast sheet looks at the Categories sheet to establish your baseline cashflow from your existing income/expense budget. If you have monthly budget columns for the current year, those budget values will be annualized and used.
Income or expenses in categories that are hidden from reports are not included.

If you do not have budgets for the current year or want to use a different initial range, see the FAQ #1 below for how the initial Income and Expense range is determined and can be overridden.

This sheet doesn’t use any information from the Transactions or Balance History sheets. It only uses the amounts in your budget located in the Categories sheet.

I don’t think we are going to update the sheet with more rows by default. It adds some extra processing and may not be useful to most users. If I’m wrong about this, we can adjust.

Jon

3

Thank you for confirming the solution @jono . To be clear, the problem was it wasn’t including all my budgeted expenses from the Category sheet.

I don’t add in all the categories as life events. I actually only have 6 life events I’m using for scenarios right now. The problem was only in the hidden columns, specifically AC not bringing in my yearly totals for each category that has one (from the Category sheet). Columns Z, A, and AB all were correct and listed the categories automatically as they should but then next to them was a blank cell in AC. Because the missing formulas in AC, the forecast was missing 60% of my expenses. I was playing with the Retirement Planner and absolutely LOVE it, but couldn’t figure out why it was wrong then realized it was the Cash Flow Forecast.

I re-read your comments and see why it might be important to extend the formulas in P, Q, S, T - to run scenarios 60+ years into the future. That’s handy!

So huge thanks to the team for these amazing tools. I built my own retirement planner and your solution is exactly like mine taking into consideration all the details and adding a bunch more features!! It’s much easier to use and prettier too. I can think of 3 people that need this!! Thank you. Happy 4th.

4

@Richard ,
Thanks for alerting me to this issue. I now see what you mean about the problem in column AC if you have more than 60 non-hidden income or expense categories. More columns are needed no matter how many life event rows you use. (My testing didn’t hit the maximum so i didn’t realize the issue until you pointed it out.)

I will get the template sheet updated with more rows next week.

Thanks for the kind words about the template.

Jon

1 Like
5

Update: The revised version of the Cash Flow Forecast sheet has been uploaded. It works with 200 non-hidden, income and expense categories.

You can get it from the Tiller Money Labs Add-On. Either Add a Solution or use Manage Solutions to update to Version 0.93.

Thanks again for alerting us to this issue @Richard.

Jon

1 Like