Personal Budget Template Apple Numbers

Keep the Transactions on one table.

Keep your annual budget on one table.

Your added Sheets ('tabs' are just the 'index tabs' that hold the name of the Sheet and allow you to bring that Sheet forward) will each contain a Table similar to the one on the Budget sheet.

The Category column on that table will contain the same categories as listed on the annual budget table

The Budget column on that table will draw its data from the annual budget table, modified to show the amounts applicable to the period shown by this table..

The Actual column will use SUMIFS to return the actual expenditures on in each category, and in this time period.

The Difference column and the totals will use the same formulas as on the Annual budget table.

To create the first Monthly Sheet and table:

Hover the mouse pointer over the Budget tab. Click the v that appears toward the right end of the tab and choose Duplicate.*

Rename the new tab Budget - Jan

Click the tab to bring the new Sheet to the front.

Click any cell in Row 1 of the table (eg. "Category), then press option-up arrow to add a second Header row above the first.

Enter the text "From: " (without the quotes) in cell A1, and the text "To: " in cell C1

In cell B3 (first data cell in the Budget column), enter = to open the formula editor.

Click on ƒx at the left end of the formula editor and drag it up to the top of the table.

Click in the formula editor to ensure the insertion point is in the editor box, then

• Click the Budget tab to bring the annual budget to the front,
• Click cell B2 (first data cell in the Budget column) in the (annual) budget table to insert the cell reference in the formula.
• type "/12" (no quotes) after the cell reference token in the formula. (the / will change to ÷)
• Click the green checkmark on the formula editor to confirm the formula, close the editor and jump back to the Budget - Jan sheet.

Cell B3 in the (monthly) budget table will display the amount from the annual budget table divided by 12.

With the cell selected, bring the mouse pointer to the cell, and when the yellow Fill control appears on the bottom edge of the cell, grab it with the mouse and drag down to fill the formula to the rest of the cells in column B.

Your table should now look like this*:

*The example being constructed is in Numbers 3.6.2.

Numbers 4 may be missing the Duplicate item in the indicated menu. If so, you will need to insert and rename a new tab, copy the Table from the Budget Sheet, delete the existing (empty) table from the new Sheet and paste the copied (annual) budget table in it's place before making the edits above (and those to come)

*The template may have been changed in Numbers 4, making your table look slightly different.

A Pause for questions at this point.

Remaining formula changes to follow soon.

Regards,

Barry

Part 2:

During the pause, I reread the Budget page of the template, and realized the table there was intended to contain a monthly budget, not an annual budget.

The instructions below assume that you will KEEP the original Budget table and use it to ENTER your MONTHLY budgeted amounts for each month.

If that is the case, the formula in column B of the new table should be a simple transfer and eliminate the /12 part.

B3: =Budget::Summary by Category::B2

Column C: This column will contain the expenditures occurring in the period between the dates in B2 and D2 in each of the categories. On January's table, these dates would be Jan 1, 2017 and Jan 31, 2017.

(In the version shown, I used Nov 15 and Nov 30 of 2014 to include only some of the dates on the table supplied with the Numbers 3.6.2 template's sample transactions.)

The formula in column C of the template uses SUMIF, as the amounts need to match only the category to be included in the sum. As we want the amounts to be in the correct category, on or after the beginning date, and on or before the ending date, we will use SUMIFS to match those three conditions.

C3: =SUMIFS(Transactions::D,Transactions::C,A3,Transactions::A,">="&B$1,Transaction s::A,"<="&D$1)

Column D: No changes.

Row 12 (Footer row): No changes.

This Sheet may now be duplicated 12 times , with eleven copies renamed to match the coming months, and their From and To dates edited to match the first and last date for the respective month.

The twelfth Sheet's table will be edited to become the Q1 summary.

Edit the formula in B3 to add *3, making the budget amounts cover the three month period.

B3: =Budget::Summary by Category::B2*3

(Numbers will replace the * with the multiplication sign ( × ), which is not a lower case letter x)

Fill down the column as before.

Edit the dates in B1 and D1 to show the first and last dates of Q1.

Duplicate the Sheet 4 times, renaming the first three to Budget - Q2, Q3 and Q4, and the last to Budget - First half.

Edit the dates in B1 and D1 of the Q tables to fit the beginning and ending dates of the quarters.

Edit the dates on the last table to match the first half start and end dates.

Edit the formula in B3 to replace 3 with 6:

B3: =Budget::Summary by Category::B2*6

Fill down.

Duplicate the last Sheet twice.

Edit the dates to match the start and end of the first half, the second half, and the full year, and rename the sheets to match.

Edit the formula in B3 of the full year table:

B3: =Budget::Summary by Category::B2*12

The charts should require no changes.

Regards,

Barry