eSteps4Occ.html Occasional Expenditures Tables Step-by-step

APPENDIX D

Numbers platform

The Tables step-by-step

Bg Image: white life preserver w/red stripes, blue water/sky

Shall we begin?

4

The OCCASIONAL EXPENDITURES Table - step by step

Estimated time to completion: 30 min

⇧⌥⌃⌘

The Occasional Expenditures Table calculates the amount each Income Earner needs to contribute toward infrequent Scheduled expenses like Auto Insurance (if paid quarterly), Vehicle Registration, Income Taxes due, Dues paid annually, etc, and Non-scheduled (random) expenses, like Groceries, Gas, Entertainment, Special Occasions, Professional Fees, Bail, etc.

The OCCASIONAL EXPENDITURES table

Perform one of the following sequences (A, B, or C), depending on the situation that applies:

Situation A

If Numbers is already open and the User Guide Workbook is active,

  1. Hold down both the shift (⇧) and command (⌘) keys, and press “N” (⇧⌘N) (or, from the Menu bar at the top of the screen, select Insert > Sheet).

    2. result:  A Worksheet with the default name "Sheet 1" will be added to the Workbook

Skip B and C below, and proceed to "EXPENSES".


Situation B

If Numbers is open, but the User Guide Workbook is not active,

  1. From the File menu at the top of the screen, select Open Recent. [File > Open Recent].
  2. On the pop-up menu, click "User Guide Workbook.pages".

    3. result:  The User Guide Workbook opens.

  1. Go to Step A1 above.

Situation C

  1. Open the Numbers app by clicking on the Numbers icon.
  1. Go to Step A1 above.


This is the second of two Expenses List tables; Occasional Expenditures. Because the two Expenses tables are similar in many respects, it will be quicker to modify a copy of the Monthly Payments Table, rather than build the Occasional Expenses Table from scratch.

OPEN THE WORKBOOK

Open the User Guide Workbook, and click the Expenses tab atop the Format pane.

If not already selected

  1. Click the "Monthly Payments" Sheet Tab.
  2. Copy the entire Table
  3. Insert a Sheet
  4. Delete the default Table
  5. Paste the copy
  6. Rename the Sheet

INSERT A SHEET

CREATE A TABLE

    Import a Table

    1. Select the entire Monthly Payments table.
    2. Press command-C [⌘-C].
    3. Press command-V [⌘-V].

    The copy is pasted atop the original.

    The circle where the white guides meet also serves as a handle for grasping the table.

    1. Click the handle of the copy and drag it off the original to the right, until one table no longer covers the other.
    2. Release the click.

    Yellow-orange lines (barely visible on a light grey background) serve as guides to help align the tables.

    Format the Table

    With the entire Table selected,

    1. Select the Table tab above the Format pane.

    Rename the Table

    1. Highlight the default title, and type “Occasional Expenses”.
    2. Press return/enter.

    note: If Numbers won’t accept your entry, it’s because you already have a Title with that name.

    In the Headers & Footer section,

    1. Set the number of Header Rows (middle drop-down menu) to “2”.

    Add Columns

    1. Select Column (E), and add a column to its right [Table > Add Column After].
    2. Select Columns (E:F).
    3. Add two columns to their left [Table > Table > Add Columns Before].
    4. Zoom to "150%".

    Format Cells

    1. Click the Cell tab.

    Delete Cell Contents

    1. Select Cell (C3).
    2. Press the delete key.

    Delete a Row

    1. Select Row (5).

    While holding down the option/alt and command keys,

    1. Press delete (⌥-⌘-delete).

    Expand a Section

    1. Select Columns C through E
    2. Under the Table menu, choose "Add Columns After" (Table > Add Columns After)

    3. Merge Cells (E1:H1) (hint: control-command-M).
    4. Type “AMOUNT”, and press return/enter.
    5. Type “Per Week”, and press tab.
    6. Type “Bi-weekly”, and press tab.
    7. Type “Per Month”, and press tab.
    8. Type “Per Year”.

    9. Delete contents of Range (B4:B10).

    10. Select Cell (E2).
    11. Hold down the command key, and select Cell (H17).

      12. result:  Range (E2:H17) becomes the active selection.

    Click the Cell tab.

    1. Set the Left borderto “0.5”.
    2. Set the Right borderto “0.5”.

FORMULAS

    Enter a Formula

    1. Select Cell (B4).
    2. Type an equal sign (“=“).

    A formula entry bar appears.

    1. Type the following (including the quotation marks), exactly as it appears:

    IF(ISBLANK(D4),””,DATEDIF(NOW(),D4,”D”))
      or, because this is an eBook, simply copy and paste it to the formula bar

    1. Copy the formula down the Column to (and including) Cell (B17).
    2. Select Range (D4:D17).
    3. Press delete.

    In the Data Format section,

    1. Select “Date & Time” from the upper drop-down menu.

    From the Date: drop-down menu,

    1. Select “1/5”.

    From the Time: drop-down menu,

    1. Select “None”. Press return/enter.

    Enter and Copy a Formula

    1. Select Cell (I4).
    2. Type an equal sign (“=“).

    A formula entry bar appears, with a blinking cursor in it.

    1. Type the following (not including the quotation marks), exactly as it appears:

    IF(ISBLANK(C4),"",SUM(E4×52+F4×26+H4÷12+G4)×Income 1::F$8÷Summary::C$2)

      or, because this is an eBook, simply copy and paste it to the formula bar

      1. Copy the formula down the Column to (and including) Cell (E17).
      2. Press return/enter.

    Format Entire Table

    Click on the circle at the top of the Worksheet, where the white Column Letter and Row Number guides intersect.

    In the Data Format section of the Format pane,

    1. Click the “up-down” caret in the green round-cornered square, and select “Number” from the drop-down list.
    2. Select the contents of the “Decimals: “ text box, and type “2”, or click the “up” caret to the right of the text box (three times), to set the number of decimals to “2”.
    3. Click the check box next to “Thousands Separator”.

    Below the Data Format section is the Fill section, and below that is the Border section.

    The Border section contains a group of icons (Outline, Inside borders, All borders, etc.). It also contains two drop-down menus (“Border Styles” and a thick, solid black line), a black rectangle with a colorful circle attached to it, and a selection box with an “up” caret and a “down” caret beside it.

    Format a Column

    1. Select Column (B).

CONDITIONAL HIGHLIGHTING

    For Alert!

    1. Select Cell (B4).
    2. Click the “Show Highlighting Rules…” button.
    3. Change “Less than” 3 to “Less than” 15.
    4. Click the Done” button.

    In the Conditional Highlighting section,

    1. click the “Show Highlighting Rules…” button.
    2. Click the “Clear All Rules” button.
    3. Click the “Add A Rule” button.
    4. Select “Number” from the list on the left.
    5. Select “Less than” from the list on the right.
    6. Type “15” in the text box with the green border surrounding it.
    7. Click the “down” caret in the Red Fill menu below that.
    8. Scroll to the bottom of the list and select “Custom Style”.
    9. Copy down.

    For Appearance

FORMATTING

    Text

    1. Click the Text tab.
    2. Select Range (C4:C17).
    3. Text Color: “Hex Color #”, “402121”.
    4. Press return/enter, and close the Text Color dialog.
    5. Select Cell (I18).
    6. Increase Font Size.
    7. Have Numbers enter a formula (Insert > Formula > Sum).
    8. Press ⇧tab (shift-tab).
    9. "Bold" the text you are about to type into this cell.
    10. Type “Occasional Expenses Subtotal”.
    11. Press ⇧tab.
    12. Press (⌫) (delete).
    13. Select Row (18).
    14. Select Rows (1:2).
    15. Unbold, make black, and center the text.
    16. Unbold, black, and center Range (E1:H1)

    Select a Column

    1. Select Column (B).
    2. Color the Text ”d1d1d1”, press return/enter, and close the Text Color dialog box.

    Format Entire Table

    Enter a Formula

    Copy a Formula

    1. Select Rows (1:17).
    2. Add Rows below. [Table > Add Rows Below].
    3. Populate Range (C4:C34) with items from the CIE.
    4. Populate Range (D4:D34) with due dates for Scheduled Expenses.
    5. Populate Range (E4:H34) with one amount per row.

SAVE YOUR WORK [File > Save].



Prev    Next
aphorism

comment box


Nashville, IN USA

This exercise and the next provide the framework for a Preliminary Draft. It may take several draft revisions to reach a Final Draft of your Spending Plan. The process should be undertaken with care and attention to detail.

The Occasional Expenditures Table

If the Format pane is not visible along the right edge of the Workbook Window, click the Format button near the right edge of the title bar, to open it.

SELECT THE WORKSHEET

  1. Click anywhere on the empty Workspace surrounding "Table 1".

    2. result:  The Sheet properties are displayed in the Format pane.

FORMAT THE WORKSHEET

In the Sheet properties pane, there are three sections: Sheet Name, Background, and the Duplicate Sheet and Delete Sheet buttons.

  1. Click inside the text box in the Sheet Name section, and drag over the default sheet name (“Sheet 1”).

    2. result:  A transparent brown background highlight indicates the value is selected.

  1. Press the caps lock key.
  2. Type “EXPENSES”, and press the return/enter key.

    3. Note: If Numbers won’t accept your entry, it’s because you already have a sheet with that name.

  1. Press the caps lock  key again, to release it.

    2. result:  Your entry appears in the Sheet Tab, above the Workspace (Sheet).

In the Background section,

  1. Click the colorful circle to the right of the white rectangle.

    result:  The Sheet Color dialog box appears.

If “RGB Sliders” is not already showing in the selection box,

  1. Click the RGB Sliders icon (second icon from the left), or click on the “up-down” carets and select it from the drop-down menu.
  2. Click (or click-drag across) the value in the “Hex Color #” text box.

When a brown background highlights the text, and a thick green border surrounds the text box.

  1. Type “dcdcdc”, and press return/enter.

    2. result:  The Workspace (white Table Cells and surrounding background) changes to light grey.

  1. Close the Sheet Color dialog by clicking the tiny red button at the top of the dialog box.

ENLARGE THE VIEW

Absolute and Relative Cell References

It's hard to explain the difference between Absolute and Relative references.

When you put your dog on a leash and let him take you for a walk, you go wherever he goes. His position and distance, relative to you, is always the same, no matter where you go.

When you chain your dog to a stake, and go for a walk without him, you're not only a dick, his position is absolute.

Understand?

Say my dog and I live on a spreadsheet. I'm out, walking my dog. You call, and want to know where we are. The convesation might go something like this: "Well, right now, I'm at Cell (F4), and Harvey's at Cell (F6). And now, I'm at Cell (F5), and Harvey's at Cell (F7)". In a few minutes I'll be at Cell (F11), and Harvey will be at Cell (F13), and so on. That's relative.

If I'd gone for a walk by myself, the conversation would be different. Maybe something like: "Well, right now, I'm at Cell (F4), and Harvey's at Cell (A1). And now, I'm at Cell (F5), and Harvey's still at Cell (A1)". In a few minutes I'll be at Cell (F11), and Harvey, well, he'll still be at Cell (A1), because I absolutely refuse to take him for a walk.

Now, why is this important?

Well, let's say you give me a shopping list: 4 pears, 5 apples, and 6 sticks of dynamite. Pears are $0.79 each, apples are $0.89 each, and sticks of dynamite are $0.99 each. You give me $15 and tell me I can keep the change. The first thing I want to know is, what's in it for me. I can't do the math in my head. So I pull out my spreadsheet.

In Column (A), Row (1) I write, "4", drop down a Row and write, "5", drop down a Row and write "6". In Column (B), Row (1) I write, "".79", drop down a Row and write, ".89", drop down a row and write, ".99".

In Column (C), Row (1) I write, "= Cell (A1) x Cell (B1)", and click the green check mark, then click on the yellow dot, and drag down to (and including) Cell (C3). 3.16 4.45 5.94

But what if you gave me $15 and a shopping list that just said: pears, apples, dynamite, 5 each? My spreadsheet and formula would look different. Because I wouldn't need to list three quantities. I could. But then my example falls apart. So bear with me.

In Column (A), Row (1) I write, "5". In Column (B), Row (1) I write, "".79", drop down a Row and write, ".89", drop down a row and write, ".99".

In Column (C), Row (1) I would write, "= Cell (A$1) x Cell (B1)", and click the green check mark, then click on the yellow dot, and drag down to (and including) Cell (C3). 3.95 4.45 4.95

When you copy and paste, or copy a formula down (or up, or over), Numbers automatically fills in the formulas, using relative references. If I want to refer to the same Cell in the formulas I copy, I have to make that the reference to that Cell "absolute". The way that is done is to put a dollar sign ("$") in front of it. If I want to copy down a Range, and refer to the same cell in each formula, I have to make the Row number reference absolute. If I want to copy across a range, and refer to the same Cell in each formula, I have to make the Column Letter reference absolute. And the way to make a reference absolute is with a dollar sign ("$"), like the $ in the first formula you entered, in this table (Income 1::F$8 ...) The reference to Row "8" was absolute. Had you left out the dollar sign, the formulas copied down the range whould have failed, because they would have referred to Row "9", then Row "10", Row "11", and so on, and there was no data in Row "9", and there was no Row "10" in the table.

APPENDIX C

(eStepsExpenses.html)

Part 3:

Step-by-step Construction of the Occasional Expenses Table

Numbers platform

⇧⌥⌃⌘

Review:

tag line

The OCCASIONAL EXPENDITURES table

Because the two Expenses tables are similar in many respects, it will be quicker to modify a copy of the Monthly Payments Table, rather than build the Occasional Expenses Table from scratch.

The OCCASIONAL EXPENDITURES table

step by step

⌫⇧⌥⌃⌘

Open the User Guide Workbook, and click the Expenses tab atop the Format pane.

    Copy a Table

    1. Select the entire Monthly Payments table.
    2. Press command-C (⌘C).
    3. Press command-V [⌘V].

    The copy is pasted atop the original.

    The circle where the white guides meet also serves as a handle for grasping the table.

    1. Click the handle of the copy and drag it off the original to the right, until one table no longer covers the other.
    2. Release the click.

    Light orange lines serve as guides to help align the tables. They are not easy to see on a light grey background, but you can’t have everything.

    Format the Table

    With the entire Table selected,

    1. Select the Table tab above the Format pane.

    Rename a Table

    1. Highlight the default title, and type “Occasional Expenses”.
    2. Press return/enter.

    note: If Numbers won’t accept your entry, it’s because you already have a Title with that name.

    In the Headers & Footer section,

    1. Set the number of Header Rows (middle drop-down menu) to “2”.

    Add Columns

    1. Select Column (E), and add a column to its right [Table > Add Column After].
    2. Select Columns (E:F).
    3. Add two columns to their left [Table > Add Columns Before].
    4. Zoom to "150%".

    Format Cells

    1. Click the Cell tab.

    Delete Cell Contents

    1. Select Cell (C3).
    2. Press the delete key (⌫).

    Delete a Row

    1. Select Row (5).
    2. Press and hold the option/alt and command keys, and
    3. Press delete (⌥⌘⌫).

    NAME THE TASK

    1. Select Columns (E:H)
    2. Left border: “75%”, “0.5”.
    3. Right border, same.
    4. Delete contents of Range (B4:B9).
    5. Click-drag to select Range (E1:H1).
    6. Merge Cells (control-command-M) (⌃⌘M).
    7. Type “AMOUNT”.
    8. Select Cell (E2).
    9. Type “Per Week”, and press tab.
    10. Type “Bi-weekly”, and press tab.
    11. Type “Per Month”, and press tab.
    12. Type “Per Year”.
    13. Select Rows (1:2).
      14. In the Border section, select “Inside borders”, “No Border” (from the Multiple Styles d-d menu).
    14. Select Row (2).
    15. Select Cell (B4).
    16. Type an equal sign (“=“).

    A formula entry bar appears, with a blinking cursor in it.

    1. Type the following (not including the quotation marks), exactly as it appears:
    IF(ISBLANK(D4),””,DATEDIF(NOW(),D4,”D”))

      or,

    Because this is an eBook, simply copy and paste it to the formula bar.

    1. Copy the formula down the Column to (and including) Cell (B17).
    2. Select Range (D4:D17).
    3. Press delete.

    In the Data Format section,

    1. Select “Date & Time” from the upper drop-down menu.

    From the Date: drop-down menu,

    1. Select “1/5”.

    From the Time: drop-down menu,

    1. Select “None”. Press return/enter.

    Enter Formulas

    1. Select Cell (I4).
    2. Type an equal sign (“=“).

    A formula entry bar appears, with a blinking cursor in it.

    1. Type the following (not including the quotation marks), exactly as it appears:

    single:

    SUM(E4×52+F4×26+H4)÷12+G4

    married:

    IF(ISBLANK(C4),"",SUM(E4×52+F4×26+H4÷12+G4)×Income 1::F$8÷Summary::C$2)

    or,

    Because this is an eBook, simply copy and paste it to the formula bar.

    1. Copy the formula down the Column to (and including) Cell (E17).
    2. Press return/enter.

    Format Entire Table

    Click on the circle at the top of the Worksheet, where the white Column Letter and Row Number guides intersect.

    In the Data Format section of the Format pane,

    1. Click the “up-down” caret in the green round-cornered square, and select “Number” from the drop-down list.
    2. Select the contents of the “Decimals: “ text box, and type “2”, or click the “up” caret to the right of the text box (three times), to set the number of decimals to “2”.
    3. Click the check box next to “Thousands Separator”.

    Below the Data Format section is the Fill section, and below that is the Border section.

    The Border section contains a group of icons (Outline, Inside borders, All borders, etc.). It also contains two drop-down menus (“Border Styles” and a thick, solid black line), a black rectangle with a colorful circle attached to it, and a selection box with an “up” caret and a “down” caret beside it.

    Format a Column

    1. Select Column (B).

    Conditional Highlighting

    1. Select Cell (B4).
    2. Click the “Show Highlighting Rules…” button.
    3. Change “Less than” 3 to “Less than” 15.
    4. Click the Done” button.

    In the Conditional Highlighting section,

    1. click the “Show Highlighting Rules…” button.
    2. Click the “Clear All Rules” button.
    3. Click the “Add A Rule” button.
    4. Select “Number” from the list on the left.
    5. Select “Less than” from the list on the right.
    6. Type “15” in the text box with the green border surrounding it.
    7. Click the “down” caret in the Red Fill menu below that.
    8. Scroll to the bottom of the list and select “Custom Style”.
    9. Copy down.

    Format Text

    1. Click the Text tab.
    2. Select Range (C4:C17).
    3. Text Color: “Hex Color #”, “402121”.
    4. Press return/enter, and close the Text Color dialog.
    5. Select Cell (I18).
    6. Increase Font Size.
    7. Have Numbers enter a formula (Insert > Formula > Sum).
    8. Press ⇧tab (shift-tab).
    9. "Bold" the text you are about to type into this cell.
    10. Type “Occasional Expenses Subtotal”.
    11. Press ⇧tab.
    12. Press (⌫) (delete).
    13. Select Row (18).
    14. Select Rows (1:2).
    15. Unbold, make black, and center the text.
    16. Unbold, black, and center Range (E1:H1)

    Select a Column

    1. Select Column (B).
    2. Color the Text ”d1d1d1”, press return/enter, and close the Text Color dialog box.

    Format Entire Table

    Enter a Formula

    Copy a Formula

    1. Select Rows (1:17).
    2. Add Rows below. [Table > Add Rows Below].
    3. Populate Range (C4:C34) with items from the CIE.
    4. Populate Range (D4:D34) with due dates for Scheduled Expenses.
    5. Populate Range (E4:H34) with one amount per row.

SAVE YOUR WORK [File > Save].


The MONTHLY PAYMENTS Table    EXCALINDUR


¡ ™ £ ¢ ∞ § ¶ • ª º–≠

œ ∑ ´ ® † ¥ ¨ ˆ ø π “ ‘ «

å ß ∂ ƒ © ˙ ∆ ˚ ¬ … æ

Ω ≈ ç √ ∫ ˜ µ ≤ ≥ ÷

TAB, ⋬⋭⋮⋯⋰⋱⋲⋳⋴⋵ TYPE

TAB, ⋶⋷⋸⋹⋺⋻⋼⋽⋾⋿ TYPE

TAB, ⌀⌁⌂⌃⌄⌅⌆⌇⌈⌉ TYPE

TAB, ⌊⌋⌌⌍⌎⌏⌐⌑⌒⌓ TYPE

TAB, ⌔⌕⌖⌗⌘⌙⌚⌛⌜⌝ TYPE

TAB, ⌞⌟⌠⌡⌢⌣⌤⌥⌦⌧ TYPE

TAB, ⌨〈〉⌫⌬⌭⌮⌯⌰⌱ TYPE

TAB, ⌲⌳⌴⌵⌶⌷⌸⌹⌺⌻ TYPE

TAB, ⌼⌽⌾⌿⍀⍁⍂⍃⍄⍅ TYPE

TAB, ⍆⍇⍈⍉⍊⍋⍌⍍⍎⍏ TYPE

APPENDIX C

(eStepsExpenses.html)

Part 3:

Step-by-step Construction of the Occasional Expenses Table

Numbers platform

⇧⌥⌃⌘

Review:

tag line