4 Personal Budget

The purpose of this assignment:

1. Create a spreadsheet
2. Add, delete cell content
3. Copy, move cell content
4. Insert, delete cells, columns, rows
5. Create simple formulas
6. Format cell content

Preparation: (Review these terms)

• AutoSum
• Auto Fill

New terms: (Terms are bolded in the steps)

• Merge and Center a Title (#3)
• Center Column headings (#6)
• Widen column (#8)
• Insert New Row (#15)
• Insert Currency Symbols (#19)
• Create simple formula without using AutoSum (#24)
• Seeing a string of ####### signs in a column after a formula applied
• Wrap Text (#26)

Narrative:

A budget is a plan for your money. Sometimes, as individuals, sometimes we make purchases without remembering other obligations we already had. It is good to have a budget and to work within that budget. This exercise will give you the basics to set up your own budget. (THIS ASSIGNMENT IS WORTH 5 POINTS. THE RUBRIC OF THIS ASSIGNMENT IS ON PAGE 4.)

Personal Budget Assignment:

Create a worksheet for your personal budget following the instructions given below the illustration. After completing Steps 1 –11, your worksheet will look similar to the example shown below. Additional steps will add to this example.

1. Starting with a blank spreadsheet (see Review of Spreadsheet Features in this assignment), start in cell A1 type the words” Personal Budget” in ALL CAPS.
2. Select PERSONAL BUDGET and change the font to Arial Black 14 pt
3. Merge and Center a Title. Select cells A1 to G1. From the HOME tab, find the Alignment Group. Click on Merge &Center. The title, “Personal Budget”, is now centered on the table.
4. In cell C3 type the abbreviation of January (Jan)
5. In cell D3 type Feb; in cell E3 type Mar; in cell F3 Apr; and in cell G3 type May.
6. Center Column Headings. Select C3 to G3. From the HOME tab, Paragraph group, click on the center alignment choice.
7. In cell A4 type the word EXPENSES in all capital letters. Select EXPENSES. Bold, underline and change the font to Calibri, 14 pt.
8. Widen column. Place your cursor between the letters A and B indicating the columns just below the ribbon. Double click your mouse. This will widen the column to accommodate the information in that cell.
9. In B5, listing five expenses you might have. Place each expense on a separate row. Expense examples might be rent, car insurance, phone, date night, gas, bus ticket, food, credit card, loan repayment, etc. Once all your expenses are listed, widen the column between B and C to accommodate the length of the words used for expenses.
10. After your last expense, skip a row and in cell A11 type the word INCOME in all capital letters. Bold, underline and change the font to Calibri, 14 pt.
11. Starting in column B12, list three income sources. For example, job, scholarship, parents, tips, loan, etc.
12. Next return to Cell C5 and start enter expense amounts for each month. Try not to use the same amount for each month. Be creative (it doesn’t have to be accurate.)
13. Continue to enter different amounts for the other four expenses across the five months without duplicating numbers if you can.
14. Next move to the INCOME section. Enter some amount for each income source across the five months. Try to make your amounts for the income section a bit higher than your expense amounts.
15. Insert a new row. Select the word “INCOME. Right-click the mouse and choose INSERT from the shortcut menu. This will insert one blank line above the word INCOME. INCOME should now be in cell A12.
16. In Cell A11, type the words Total Expenses capitalizing the first letter of each word. Bold these words.
17. AutoSum key. Click in Cell C11, add up the totals for January using the AutoSum key. Press the ENTER key or click the checkmark on the formula bar to display the Total Expenses for Jan.
18. Using AutoFill. Use the AutoFill square in the cell to drag the formula in Column C to columns D-G. If done properly, D11 formula should be =SUM(D5:D9)
19.  Inserting currency symbols. You will be adding dollar signs and decimals points to C11 to G11 only. Select C11-G11. Click on the HOME tab and find the group labeled NUMBER.(the square with an arrow to the right of the word NUMBER). Look at the list of choices in the drop down dialog box and choose currency with 2 decimals.]
20. In Cell A16, type the words Total Income capitalizing the first letter of each word. Bold these words.
21. Using the same procedure used to AutoSum the total expenses for each month, total your income for the month. (See Step 17-18 for additional help.)
22. Select C18 to G18 and change the amounts to currency. (See Step 19).
23. Click in Cell A18 and type in the word BALANCE in all capital letters. Bold it and change the font to 14 pt.
24. Create simple formula without using AutoSum:
    • In cell C21 Start with an equals sign (=) followed by the word SUM all in capital letters
    • Without spacing, after the word SUM, place a forward parenthesis; example (.
    • We want to subtract the Total Income in January with the Total Expenses in January. NEVER USE FIGURES. ONLY USE CELL REFERENCES.
    • Following the forward parenthesis after the word SUM, click in the cell that contains the Total Income for January. The cell reference C18 should appear in the formula.
    • Next input a minus sign (-) without spacing. Click in the cell for the Total Expenses for January. The cell reference C10 should appear.
    • Insert a closed parenthesis. You should see the entire formula in the formula bar as well as in cell C21. (Note: If the resulting amount is in red or has a minus sign in front of it, it just means that your expenses are larger than your income. If this was really your personal budget, you would have to figure out how to make that a positive number. You might have to reduce your expenses or find other income sources.)
    • ENTER. An amount should appear that is the difference between your income and your expenses. 25.
25. Using the AutoFill (see step 18), drag the formula from January to the other four months.
26. Note: Seeing a string of ####### signs in a column after a formula applied. This is the result of a number being larger than the space permitted in the column. Double click between the two columns to widen the space and the amount should appear
27. Select the BALANCE row and apply dollar signs and decimals to the figures.
28. Wrap Text. Type your name in cell A21. Use Wrap Text to keep your name in the cell without having to widen the column. The Wrap Text button is found on the HOME tab, Paragraph group.
29. Save your work and submit it through Blackboard where indicated.
30. If you are not using Blackboard, print your personal budget. Your instructor may want to see the formulas you used. If that is the case, your instructor will tell you how to print the formulas used.

Check the rubric below to be sure you have completed all the tasks