Helpful Tool: Reserve Spending and Budget Tracker

oddzac

New Member
Joined
Aug 12, 2022
Messages
25
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
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):
Excel Formula:
=-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))))

1674922154059.png



Formula Step Breakdown:
1674922738712.png


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

Excel Formula:
=(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"))))

1674923822776.png





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

Tell me what you think!!







Reserve Forecast + Tracker.xlsx
ABCDEFGHIJKLMNOP
1Current Balance
21318
3In OptionsBal. OptionsReference table not pictured (privacy)
4$175$768.00(Current)
5$287.17$1,055.17(+Next Deposit)
6$300
7
8
9
10
11
12Set Aside:$287.17
13This Period
141/27/202327
152/3/20233Current Bal.Amount Due per Period
16$1,318.00Years2023
17DAYDATEINOUTBALANCEDATEFeb
18271/27/2023$287.17-$550.00$768.00
1932/3/2023$287.17-$84.52$970.65DateSum of OUT
20102/10/2023$287.17$0.00$1,257.833($84.52)
21172/17/2023$287.17-$350.00$1,195.0010$0.00
22242/24/2023$287.17-$605.00$877.1817($350.00)
2333/3/2023$287.17-$84.52$1,079.8324($305.00)
24103/10/2023$287.17$0.00$1,367.00
25173/17/2023$287.17-$350.00$1,304.18
26243/24/2023$287.17-$55.00$1,536.35Bills on Auto Pay
27313/31/2023$287.17-$634.52$1,189.00NameAmountDue Date
2874/7/2023$287.17$0.00$1,476.18Rent$500.001
29144/14/2023$287.17$0.00$1,763.35Storage$50.001
30214/21/2023$287.17-$405.00$1,645.53Progressive$84.526
31284/28/2023$287.17-$550.00$1,382.70Credit Card$350.0021
3255/5/2023$287.17-$84.52$1,585.35Internet$55.0025
33125/12/2023$287.17$0.00$1,872.53
34195/19/2023$287.17-$405.00$1,754.70
35265/26/2023$287.17-$550.00$1,491.88
3626/2/2023$287.17-$84.52$1,694.53
3796/9/2023$287.17$0.00$1,981.70
38166/16/2023$287.17$0.00$2,268.88
39
40Due This Week
41RentReserve Table Step LogicD1 = Day 1
42StorageInitial SUM of Amount with Due Date (> D1) AND (< D2)D2 = Day 2
430
44If Zero AND D2 =1, SUM of Amount with Due Date (<= EOM) AND (> D1)
450
46If Zero AND D2 <=6 OR D1 >=24, SUM of Amount with Due Date (<= D2) + (> D1)
47-550
48Else, Zero
49
Sheet1
Cell Formulas
RangeFormula
E4E4=O2+D18
E5E5=E4+C12
C14:C15C14=DAY(B14)
B15,B19:B38B15=B14+7
E16E16=O2
A18A18=DAY($B$18)
B18B18=B14
C18:C38C18=$C$12
D18:D38D18=-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))))
E18E18=O2+D18
A19:A38A19=DAY(B19)
E19:E38E19=SUM(E18,[@IN],[@OUT])
A41:A42A41=(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"))))
E43E43=-SUM(FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),0))
E45E45=-SUM(IF(A19<2,FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0)))
E47E47=-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
CellConditionCell FormatStop If True
A41:A45,G28:G32Cell ValueduplicatestextNO
A41:A45Cell ValueduplicatestextNO
E18:E38Other TypeColor scaleNO
E16Cell Value<#REF!textNO
E16Cell Value>#REF!textNO
Cells with Data Validation
CellAllowCriteria
C12List=$C$4:$C$6
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top