Helpful Tool: Reserve Spending and Budget Tracker

[oddzac]

So it's not really a question (unless anyone has a suggestion for a better way to go about it) and if this isn't the place to post this sort of thing please let me know, but I've put together a sheet in my budget that tracks the account that my bills are set to auto-pay from and forecasts out the next 21 pay periods (6 months for me) to help me predict savings or dips in the balance (eg. all the bills hit at once). It's been immensely useful for me to see and I wanted to share it where it might be appreciated.

I posted here a few months ago looking for help with the FILTER function, like so many do. I got some great advice, but couldn't quite put the pieces together. A few weeks later, after a lightbulb moment as I talked about it with family, it clicked. I've finally gotten to a point where the formulas work *and* I can explain how.

With all the over-hype out of the way, here's The Thing:

"Out" column formula (where "A18" is the first cell of the "Day" column and "ReserveBills15" is the "Bills on Auto Pay" table):

=-SUM(FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),IF(A19=1,FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0),IF((A18<=6)+(A18>=24),FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=A19)+(ReserveBills15[Due Date]>=A18),0),0))))

Formula Step Breakdown:

Step 1: Check for Bills with due date greater than this payday and less than next payday

Step 2: If next payday is the first (breaks first formula), check for bills with due date greater than this payday and less than End of Month

Step 3: If this pay period wraps into next month, check for bills due after this payday or before next payday

Step 4: Zero (nothing due)




Bonus Formula:
If you take the SUM function out and change the return targets from "Due Date" to "Name", Excel will spill all the bills due within the specified date range

=(FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),IF(A19=1,FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0),IF((A18<=6)+(A18>=24),FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]<=A19)+('Monthly Budget.xlsx'!ReserveBills15[Due Date]>=A18),0),"None"))))

Including a mini-sheet with the pictured tables as a playground/reference.

Tell me what you think!!

Reserve Forecast + Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1														Current Balance
2															1318
3			In Options		Bal. Options									Reference table not pictured (privacy)
4			$175		$768.00	(Current)
5			$287.17		$1,055.17	(+Next Deposit)
6			$300
7
8
9
10
11
12		Set Aside:	$287.17
13		This Period
14		1/27/2023	27
15		2/3/2023	3		Current Bal.		Amount Due per Period
16					$1,318.00		Years	2023
17	DAY	DATE	IN	OUT	BALANCE		DATE	Feb
18	27	1/27/2023	$287.17	-$550.00	$768.00
19	3	2/3/2023	$287.17	-$84.52	$970.65		Date	Sum of OUT
20	10	2/10/2023	$287.17	$0.00	$1,257.83		3	($84.52)
21	17	2/17/2023	$287.17	-$350.00	$1,195.00		10	$0.00
22	24	2/24/2023	$287.17	-$605.00	$877.18		17	($350.00)
23	3	3/3/2023	$287.17	-$84.52	$1,079.83		24	($305.00)
24	10	3/10/2023	$287.17	$0.00	$1,367.00
25	17	3/17/2023	$287.17	-$350.00	$1,304.18
26	24	3/24/2023	$287.17	-$55.00	$1,536.35		Bills on Auto Pay
27	31	3/31/2023	$287.17	-$634.52	$1,189.00		Name	Amount	Due Date
28	7	4/7/2023	$287.17	$0.00	$1,476.18		Rent	$500.00	1
29	14	4/14/2023	$287.17	$0.00	$1,763.35		Storage	$50.00	1
30	21	4/21/2023	$287.17	-$405.00	$1,645.53		Progressive	$84.52	6
31	28	4/28/2023	$287.17	-$550.00	$1,382.70		Credit Card	$350.00	21
32	5	5/5/2023	$287.17	-$84.52	$1,585.35		Internet	$55.00	25
33	12	5/12/2023	$287.17	$0.00	$1,872.53
34	19	5/19/2023	$287.17	-$405.00	$1,754.70
35	26	5/26/2023	$287.17	-$550.00	$1,491.88
36	2	6/2/2023	$287.17	-$84.52	$1,694.53
37	9	6/9/2023	$287.17	$0.00	$1,981.70
38	16	6/16/2023	$287.17	$0.00	$2,268.88
39
40	Due This Week
41	Rent				Reserve Table Step Logic					D1 = Day 1
42	Storage				Initial SUM of Amount with Due Date (> D1) AND (< D2)					D2 = Day 2
43					0
44					If Zero AND D2 =1, SUM of Amount with Due Date (<= EOM) AND (> D1)
45					0
46					If Zero AND D2 <=6 OR D1 >=24, SUM of Amount with Due Date (<= D2) + (> D1)
47					-550
48					Else, Zero
49
Sheet1

Cell Formulas
Range	Formula
E4	E4	=O2+D18
E5	E5	=E4+C12
C14:C15	C14	=DAY(B14)
B15,B19:B38	B15	=B14+7
E16	E16	=O2
A18	A18	=DAY($B$18)
B18	B18	=B14
C18:C38	C18	=$C$12
D18:D38	D18	=-SUM(FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),IF(A19=1,FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0),IF((A18<=6)+(A18>=24),FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=A19)+(ReserveBills15[Due Date]>=A18),0),0))))
E18	E18	=O2+D18
A19:A38	A19	=DAY(B19)
E19:E38	E19	=SUM(E18,[@IN],[@OUT])
A41:A42	A41	=(FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),IF(A19=1,FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0),IF((A18<=6)+(A18>=24),FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]<=A19)+(ReserveBills15[Due Date]>=A18),0),"None"))))
E43	E43	=-SUM(FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),0))
E45	E45	=-SUM(IF(A19<2,FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0)))
E47	E47	=-SUM(IF((A19<6)+(A18>24),FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=A19)+(ReserveBills15[Due Date]>=A18),0),0))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A41:A45,G28:G32	Cell Value	duplicates	text	NO
A41:A45	Cell Value	duplicates	text	NO
E18:E38	Other Type	Color scale		NO
E16	Cell Value	<#REF!	text	NO
E16	Cell Value	>#REF!	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C12	List	=$C$4:$C$6