Monthly Personal Budget Using Numbers

Although the Budget sheet of this template is labeled "Monthly Budget" the calculations in it do not appear to be limited to transactions in a single month. I suspect the table here and the 'budget amounts'' are derived from an annual budget, with expenses distributed equally from month to month.

Should you want to change the monthly budget amounts in some or all categories from month to month, I would suggest:

Transactions:

Continue to record all transactions on the Transaction table. You can add a filter to this table to show only rows where the date column cell is empty or contains a date within the current month(s) (with the 'current month(s) being set in one or more cells in a Header row.

Budget:

Columns A and B of this table contain entered data showing each Category of expense and the amount budgeted for that expense this month. No changes are needed.

Column C contains a formula that calculates the actual amount spent on expenses in each category. The formula, as written includes ALL spending recorded on Transactions with no regard paid to the dates of those transactions. The SUMIF formula needs to be revised to a SUMIFS formula making the same calculations, but restricting them to transactions during the month applying to this copy of the table.

(discussed below in "Formulas")

Click on cell A1 of the table, then press option-up arrow to add a new Header row above the current Row 1.

Click on the new cell A1, click the format Brush to open the Format Inspector, choose Cell, and set the data format of the cell to Date and Time, with Date (format) set to Jan 6, 2021 and time set to none.

In cell A1, enter the starting date of the first month of your current budget (May 1, 2021).

Formulas: There are two formulas on this table.

C3 (after adding the new row 1) contains this formula:

SUMIF(Transactions::C,A3,Transactions::$D)

This sums all values in column D of Transactions whose category label in column C of Transactions matches the category named in this row of Column A of this table

We want that condition to apply, and want to add two more:

The date of the transaction must be n or after the date in cell A1 of this table.

The date of the transaction must be before the date one month after the date in cell A1 of this table (June 1, 2021

To handle more than one condition, we'll need to replace SUMIF with SUMIFS, making the new formula for cell C3:

SUMIFS(Transactions::D,Transactions::A,">="&A1,Transactions::A,"<"&EDATE(A1,1),Transactions::C,,A3)

In the Formula Editor, the formula should look like this:

After clicking the green checkmark to confirm the formula and close the editor, hover the pointer near the bottom of cell C3, click on the button and drag down to fill the formula into the rest of column C.

Called away. More to come later. (Not much more.)

Regards,

Barry

Two ideas for you

For what you are asking to do, you have to make those two sheets into one.

1. Select everything on the Transactions sheet
2. Cut
3. Go to the Budget sheet (don't worry about all the error triangles that will be there now)
4. Paste and be careful not to unselect what you just pasted because you want to click on it and drag and arrange it underneath the Summary by Category table.
5. You can duplicate this sheet for each month.

There is an expanded Monthly Budget template that may fit your needs. It is not mine but I have a copy of it. I have not used it. I assume it works. I made a change to it to provide the "monthly average" actual expenses for each category (column C of the Expenses by Category and Month table). The original had only the first month's actuals. Click on one of the cells to see how I calculated the average.

https://www.dropbox.com/s/hmkbj1uwunsypf8/Budget%20template%20expanded%20V2.numbers?dl=0

Later…

Once you have completed the changes to the Budget table, you can duplicate it several times to do the summaries for succeeding months.

Change the date in cell A1 to the first of the month that table is summarizing (Or automate the change using this formula: EDATE('previous table'::A1,1) in which you click cell A1 on the previous table to enter the part in italics.

Adjust the budgeted amounts foreach month as necessary.

You may also want to rename each of the sheets and each of the budget tables to include the month to which it applies.

Annualy, you'll need to change the date in A1 of the first Summary table, and possibly clear the Transactions table for the new year.

Regards,

Barry