When using the Savings & Debt template to track wedding savings, I noticed an issue where I’d have to adjust the Savings Goal whenever I spent money in the Savings Category before reaching the Savings Goal. The spending would count against my accrued savings and so I’d need to “correct” the sheet by decreasing my Savings Goal by the amount I spent.
I’ve modified the sheet so that I can keep my overall target Savings Goal constant while spending in my wedding Savings Category. In addition, I wanted to calculate how much I’d need to budget in future months based on my current month’s budget. That way if I get ahead on my savings by budgeting more than the Required Budget, I can put the Future Budget amount into my Categories sheet and see the projected impact on my Yearly Budget sheet.
Here’s what my columns look like with these changes:
Available:
={"AVAILABLE";arrayformula(if(isblank(A7:A),iferror(1/0),iferror(vlookup(A7:A,{AB7:AB,AI7:AI},2,false))))}
Actual:
={"ACTUAL";arrayformula(if(isblank(A7:A),iferror(1/0),iferror(-vlookup(A7:A,{AB7:AB,AF7:AF},2,false))))}
Remaining:
={"REMAINING $";arrayformula(if(isblank(A7:A),iferror(1/0),if(isblank(C7:C),iferror(1/0),if(C7:C<=E7:E,"✓",round(C7:C-E7:E-F7:F,2)))))}
Progress:
=if(OR(ISBLANK($E7),ISBLANK($C7)),iferror(1/0),iferror(SPARKLINE(max(0,$E7+$F7),{"charttype","bar";"min",0;"max",max($E7,$C7);"color1",if($E7 >= $C7,"#2E86DE","#69C569")})))
Budgeted:
={"BUDGETED";arrayformula(if(isblank(A7:A),iferror(1/0),iferror(vlookup(A7:A,{AB7:AB,AD7:AD},2,false))))}
Required Budget:
={"REQ'D BUDGET";arrayformula(if(isblank(A7:A)+ISBLANK(D7:D)+(E7:E>=C7:C),iferror(1/0),Iferror(ROUNDUP((G7:G+J7:J)/(I7:I+1),2))))}
Future:
={"FUTURE";arrayformula(if(isblank(A7:A)+ISBLANK(D7:D)+(E7:E>=C7:C),iferror(1/0),Iferror(ROUNDUP((G7:G)/(I7:I),2))))}
