College Expenses Spreadsheet Activity 2015

You have researched the costs of attending various universities and community colleges. You have also researched living expenses, comparing the costs of living at home vs. living in an apartment or living in a dorm. Now it is time to place those findings in spreadsheets. **Additionally, you will need to figure in a 5% per year increase due to inflation, tuition increases, etc. Use the following directions to help you create a spreadsheet that will assist you in making those comparisons.

NOTE: If you have never used Excel, follow the below steps exactly as written and you will be able to create your first Excel spreadsheet (you can then add Excel to your list of skills).

Complete two spreadsheets:

Using your Expenses worksheet, you will create ONE spreadsheet for EACH school based on your most likely living situation (i.e., “University of Arizona-living in dorm” or “Mesa Community College – living at home”). After you create your first spreadsheet, you will have to change the heading at the top, and the rows that relate to housing, utilities, food, etc. You will have six (6) total columns for each spreadsheet: One for the header and row labels, one spacing column, and one for each of the four (4) years of school (For community colleges, you will have two years of expenses for a total of 4 columns) and calculate the total for each year, including inflation. If you know for certain that you are first attending a community college and living at home, then transferring to a four year university, you may create a hybrid spreadsheet reflecting comm. College costs for years 1 and 2, then university costs for years 3 and 4.

Do the following to begin preparing your spreadsheet:

Point to Start, point to All Programs, and openthe Excel program, point to File and New.

Heading

Place the appropriate information in the following cells, by clicking on the cell itself:

A1: Your full name, a dash (-), and then the period you are in

A2: College name and Residence Option (dorm, home, or apartment)

A3:City and State location for school

A4:Date (in day/month/year order only - e.g. 17 Sep. 2015)

Skip to cell A6

Titles

Type the labels of all the different types of expenses you might incur. Do this from cell A6 down through whatever cell you need. For example, the below list runs through cell A20 (see sample spreadsheet).

Include the following (and more if you need to, but not less):

Tuition

Rent (apt) or Room and Board (dorm)

Groceries/Toiletries

Books

Phone/Cellphone

Cable/Satellite

Internet

Electric

Healthcare

Cost of Travel (plane/bus/subway costs –car transportation costs are $.50// mile. This number was included on your school research sheet and incorporates gas and routine maintenance costs)

Car Payments

Car insurance

Entertainment (be realistic - err on the side of caution)

Eating out

Gym membership

Haircuts

Clothing

Miscellaneous/Incidentals (unexpected expenses, birthday gifts, emergency flights home, etc. – estimate at $100 per month)

Skip one cell (leave a blank cell between the last item and the next cell) and title this one “Totals.” Once done, move the pointer back up to A1. Holding down the SHIFT key, press the KEYBOARD’S DOWN ARROW until you get to the “Totals” row. Release the SHIFT button, click on FORMAT, then COLUMN, and then AUTOFIT. This will ensure that, no matter how long the name of each entry, all will fit within the column.

Years

Click on cell C5, and type 2014. Go to cells D5, E5 and F5, and type, respectively, 2015, 2016 and 2017. Highlight each cell and both underline and bold each column title, and then center each column title by clicking on the CENTER TEXT icon at the top of the page (shortcut: Hold down the CTRL key and the U key at the same time for underlining, and the CTRL and B key for bolding – CTRL and I key for italics).

Amounts for first year

For the first year column, enter the anticipated amount, in whole dollar amounts, for each category. Press Enter after each dollar amount, and the cursor will automatically move down to the next row.

Totaling the first column

Highlight cell C6 and, while holding down the SHIFT key, press the KEYBOARD’S DOWN ARROW until you are at the same row as the “Totals” label (row 23 on the sample spreadsheet). Release the SHIFT key, and click on the SUM icon at the top of the screen (it looks like a Greek “E” or a sideways “M”). If you did this correctly, you should automatically have a total amount in the “Totals” row under the first column.

Inflation increase and final spreadsheet formatting

Apply the 5% increase to all categories for each subsequent year.

  1. Highlight cell D6 and type the following (every formula in Excel must start with an “=” sign): =C6*1.05 and then press the ENTER or RETURN key. You will notice that cell D6 now shows an amount equal to the contents of cell C6 with a 5% increase. The “*” (asterisk) is Excel’s code for multiplication. The 1.05 is telling Excel to multiply the contents of cell C6 by a factor of 1.05, equivalent to an increase of 5%.
  1. Highlight cell D6 again. Move the cursor to the bottom right of cell D6 until it becomes a black plus sign. Click and hold down the mouse button, and drag the black plus sign until cells E6 and F6 are highlighted. Release the mouse, and you will see respective 5% increases for each column. You have just copied the formula in D6 to the other two columns.
  1. Highlight once again cell D6. Move the cursor to the bottom right of cell D6 until it becomes a black plus sign. Click and hold down the mouse button, and drag the black plus sign until cells E6 and F6 are highlighted. Release the mouse button, and then move the pointer until you see the black plus sign at the bottom right corner of cell F6. Click and drag so all the cells under columns D6, E6 and F6 are highlighted, all the way down to your last row of titles (but NOT the “Totals”row). Release the mouse, and you will automatically have the “plus 5% increase” formula applied to every single cell.
  1. Click on the second year column of the “Totals” row (D23 on sample spreadsheet), and apply the same formula as in # 1 above: For our example, you would click on cell D23, and then type: =C23*1.05 and then press enter. The total in column D will increase by 5%. With D23 highlighted, move the pointer until you see the black plus sign, and then drag to cell F23 and release so that all 3 cells are highlighted. The inflation increase formula will now be in each of the “Totals” columns, and you can see the yearly 5% increase automatically applied.
  1. Highlight all the cells in the “Totals” row (from A22 through F22) and press the B icon to bold these entries (or hold down the CTRL and B keys at the same time).
  1. Click on cell C6, and highlight all the cells with dollar figures in them (C6 through F23 on sample spreadsheet). Point to FORMAT at the top of the page, and point to STYLE in the drop down box. When the STYLE dialog box opens, select CURRENCY by the STYLE NAME area, then press the MODIFY button, and change the DECIMAL PLACES option to “0.” Every cell should now have a whole number in it, with a dollar sign to the left of it.
  1. SAVE each spreadsheet under a unique name (e.g. ASU Rent or ASU Dorm), and select PRINT PREVIEW under FILE. Click on the SETUP button at the top to change printing options; then click on SHEET, and make sure “gridlines” is the ONLY BOX CHECKED. Do not check the option to include column and row headings on your spreadsheet. They are included on the sample spreadsheet for instructional purposes only.
  1. Move the cursor to cell A1, and highlight all cells through to the last year of your spreadsheet, and down to one row below your last source entry. Release the mouse button and your selected print area is now highlighted. Click on FILE and PRINT AREA, then select the SET PRINT AREA option. This is all that will be printed from the spreadsheet. If you make a mistake, you can click on FILE and PRINT AREA, then select the CLEAR PRINT AREA option.
  1. You can now “copy” the entire sheet and “paste” it into a new workbook. Change the school name and living situation, then revise the numbers in the FIRST column only. The rest of the spreadsheet will revise itself if you did the formulas correctly.
  1. Print the spreadsheets. You are now an Excel beginner!