10. Personal Budget

Use Google Spreadsheet to create a budgeting spreadsheet reflecting your estimated income and expenses after graduation.

You will use a spreadsheet to:

• Enter formulas using =
• Format how cell contents are displayed using menu format options
• Use absolute and relative cell referencing using $ in front of a row or column. This allows copying a formula instead of having to type in each one separately.
• Graph results using chart feature

Pre-Lab Activities

Complete the following Microsoft Excel tutorial as preparation for the lab quiz and activity. It covers addressing, formatting, using formulas, and a brief intro to graphing.

https://www.youtube.com/watch?v=k1VUZEVuDJ8

Lab Activity

1. Start with this spreadsheet: https://docs.google.com/spreadsheets/d/12Ib-QhMhCfwMoRm5DN_9-2r9Nkl8eWescOCqEpwUI1Y/edit?usp=sharing
2. You will need to use File / Make a Copy to make your own version of the Student Sample Budget to edit for yourself. Even though you are working as a group, each of you should create your own version. Talk through the steps as a group, ensuring everyone in your group can complete each step.
3. The starting spreadsheet looks like the following:

1. Insert new or more new rows (in the range of rows 17..24) for additional expenses (e.g. gaming, Netflix, Cell phone, Internet, Entertainment) and provide your best estimate of reasonable monthly values. Modify your salary depending on estimates for your major and expected job, using values from this page.
2. Enter formulas into all the green cells. These formulas should calculate the values that show up there. This way when any value changes in the spreadsheet, the rest of the spreadsheet updates automatically.
3. Format columns B through M to display numbers using dollar signs (currency format) and no decimal places.
4. Use formulas to place averages into the blue cells.
5. At this point your spreadsheet should look something like the following, except your should have a few extra rows in the list of expenses. Changing any of the values (salary, expenses) should automatically result in everything else being updated.

1. Extra Credit: For the month of January create a pie chart which shows the total expenditure including the savings and the other categories.
2. Draw a bar graph showing potential savings (Amount remaining / month) for the four months.

Rubric:

The grading criteria for this lab are:

• 1 point: Two or more new categories have been added to the monthly expenses list. Values in green highlighted cells all come from formulas, though you don't yet need to implement the formulas for averages for the blue cells in column M.
• 1 point: Numeric values in columns B..M are formatted as dollar amounts, with no decimal places. Averages are shown in the blue cells in column M.
• 1 point extra credit: Jan pie chart graph and bar graph for Amount remaining / month.

Submitting your Work

Submit your document as a file attachment using the google form, calling the essay topic Budget 11 (for the 11 am lab) or Budget 12 (for the 12 noon lab) .