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.
Setup
- Copy the Yearly Budget tab to a new Forecast tab
- In the Forecast tab: Add a new Column B to the left of the current Column B (Budget)
- In Cell B6: Enter this formula
=‘Yearly Budget’!B7
- 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.
- In Cell C6: Change the label to “Projection.”
- 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")
- Copy cell F4 to I4, L4, etc. (for each month)
- 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")
- Copy cell F6 to I6, L6, O6, R6, U6, etc. (these are the Budget column headers for each month)
- 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))))
- Copy Cell F7, highlight cells F8 to the last row of column F and select Edit > Paste Special > Formula Only.
- Once you have done that, highlight cells F7:F, select copy, then select cell I7 and select Edit > Paste Special > Formula Only.
- 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.