New Cash Flow Forecast Sheet

Google Sheets Show & Tell
1

Overview

To control our family finances, I really need two things: A budget vs actual (Yearly Budget sheet) and a look at what our projected cash flow will be. The projected cash flow is the actual spending to date plus the budgeted spending for remaining months against the budget for the remaining months. I struggled with this for a long time until I found the following quick work around. Thanks to @AHB for enhancing the formulas.

The forecast tab gives a snapshot of where you will be over and under budget at the end of the year, without disturbing the original budget figures in the Categories tab.

Screenshot 2024-09-17 at 6.38.15 AM

Setup

  1. Copy the Yearly Budget tab to a new Forecast tab
  2. In the Forecast tab: Add a new Column B to the left of the current Column B (Budget)
  3. In Cell B6: Enter this formula
=‘Yearly Budget’!B7
  1. Copy this formula to the entire column. This ensures that the budget figures taken from the Categories tab will always display in the Forecast tab.
  2. In Cell C6: Change the label to “Projection.”
  3. In cell F4: Use the formula below to automatically change the cell value to Actual Cashflow or Budgeted Cashflow based on whether the month has already passed or not.
=IF(EOMONTH(F$3, 0) < TODAY(), "Actual Cashflow", "Budgeted Cashflow")
  1. Copy cell F4 to I4, L4, etc. (for each month)
  2. In cell F6: Use the formula below to automatically change the column header from Budget to Actual once the month has passed.
=IF(EOMONTH(F$3, 0) < TODAY(), "Actual", "Budget")
  1. Copy cell F6 to I6, L6, O6, R6, U6, etc. (these are the Budget column headers for each month)
  2. In cell F7: Use the formula below to automatically take the Actual value for the month once the month has already passed.
=IF(isblank($A$7:$A),iferror(1/0),if(EOMONTH(F$3,0) < TODAY(), G7,if(isna(match(row(F7:F)-row(F$7),'Monthly Budget'!$N$16:$N,0)),if(isna(match(row(F7:F)-row(F$7),'Monthly Budget'!$O$16:$O,0)),iferror(VLOOKUP($A$7:$A,{INDIRECT("Categories!$"&$AT$3&"$2:$"&$AT$3),offset(Categories!$A$1:$A,1,match(F$3,Categories!$1:$1,0)-1)},2,FALSE)),iferror(sumifs(offset(Categories!$A$1:$A,1,match(F$3,Categories!$1:$1,0)-1),INDIRECT("Categories!$"&$AV$3&"$2:$"&$AV$3),$A$7:$A,INDIRECT("Categories!$"&$AW$3&"$2:$"&$AW$3),"<>Hide",INDIRECT("Categories!$"&$AU$3&"$2:$"&$AU$3),if(counta('Monthly Budget'!$N$16:$N)=2,if(row(G7:G)-row(G$7)>=max('Monthly Budget'!$N$16:$N),"Expense","Income"),'Monthly Budget'!$Q$16)),0)),iferror(SUMIFS(offset(Categories!$A$1:$A,1,match(F$3,Categories!$1:$1,0)-1),INDIRECT("Categories!$"&$AU$3&"$2:$"&$AU$3),$A$7:$A,INDIRECT("Categories!$"&$AW$3&"$2:$"&$AW$3),"<>Hide"),0))))
  1. Copy Cell F7, highlight cells F8 to the last row of column F and select Edit > Paste Special > Formula Only.
  2. Once you have done that, highlight cells F7:F, select copy, then select cell I7 and select Edit > Paste Special > Formula Only.
  3. Repeat for each of the months.

I think (hope) I’ve integrated AHB and my instructions together properly. Let’s see!!!

Permissions

It’s fine for others to copy, use, and modify your workflow.

2

Thank you @bbruck for sharing this new template. I am very much enjoying it.

AHB

1 Like
3

You’re more than welcome. And thank YOU for the enhancements!

1 Like
4

Thank you @bbruck. Making use of something close is a great way to go when building a solution to your needs.

2 Likes
5

@bbruck Projecting Cash Flow is very important to me as well, especially the forecasted running cash flow. Like you, I started to add into the Yearly Budget worksheet but didn’t like the format. So, I have created a couple of worksheets that you may find useful…

  1. Expected End of Year Cash - This shows your cash flow by Group and Category through the month you choose (only through completed months for current year) and remaining year budget. You can also choose beginning of year to see what your Cash Flow was expected to be for the year.

  2. Cash Flow by Month Chart - Same concept as 1, but summarizes total cash flow by month- actuals for months completed then budget forcasted for remaining months of year. This one can also show monthly Expenses or Income vs Budget and choose specific Groups or Categories. This got posted today.

Let me know if you have any questions or comments.

Thanks.

1 Like
6

Nice work! I’m going to really use these!