Making Ends Meet

Download project(120kb PDF)

Summary:

In this real-life simulation, students create a monthly budget, showing income earned from an imaginary job versus estimated monthly expenses. They then encounter unexpected monthly expenses and must adjust their budget to compensate for those expenses. Finally, they write a report evaluating the activity and what they learned about "making ends meet."

Overview
Program: MS Excel

Skill Level: Challenging

Activity Goals:
Students will prepare a monthly budget using MS Excel
Estimate a monthly food bill based on a weekly food menu

Prerequisite Skills:

• Knowledge of MS Excel-creating worksheets, formatting cells, creating basic formulas, and saving and printing the worksheet.
• Knowledge of the World Wide Web using MS Internet Explorer
  

• Begin with a discussion about the "cost of living" by asking students questions such as: "How much do eggs cost?" What is the bus fare from your home to another part of town?" "What is the average rent for a one-bedroom apartment?"
• Next, discuss the average beginning salaries for various jobs. Suggest that students research salaries by using Internet Explorer to visit the Job Market section of the The New York Times Online. You can also have students consult the Help Wanted database published by CareerPath.com for a list of jobs advertised in selected newspapers around the country. (Recommended Web Links: NYTimes.com, Career Parth.com)
• Explain to students that in this activity, they will create a monthly budget based on income earned from a fictitious job versus estimated living expenses (including rent, transportation, food, and leisure time activities). If their expenses exceed their income, they will need to determine where to cut back monthly expenses in order to "make ends meet." They will also encounter unexpected monthly expenses which will force them to adjust their monthly budget. Finally, they will write an evaluation of the activity using Microsoft Word.

Step 1
Creating your Budget

• Each student will create a fictitious monthly budget for herself. Categories include: rent, utilities, transportation, food, medical expenses, clothing, leisure activities, and miscellaneous.
• Use Internet explorer to find accurate costs for clothing items, medical expenses, food etc. Consult your local newspaper or real estate online listings to get an idea of how much it costs to rent a house or an apartment in an area you would like to live.
• Open an MS excel spreadsheet and save it in your student folder as "student name_making ends meet". Next, type each category listed above, separating each one by one cell that are all titled "cost". For example rent will be listed in cell A1, cost will be listed in cell B1 and utilities will be listed in cell C1. Under each category list individual items within that category and in the cell next to it place the expense. Aim to list no more than 4 subcategories. See example.
• In cell A5 write TOTAL. And using the sum formula key, total each of the dollar amounts. Make sure all cells containing cost estimates are written with dollar signs, but do not manually type in the $ sign. Format the cells to automatically put in $ signs. (Hint: Highlight desired columns-hold down the control key to select individual columns-- B,D,F,H,J,L,N and P, Go to Format>Cells>Number> and select currency, and choose 2 decimal places and press OK). Now all of your costs are in dollar amounts.
• Rename this sheet from Sheet 1 to Budget summary. (Right click on tab at bottom of spreadsheet that currently says sheet1).
• Format the chart with colors, boarders and font changes to make it easy and enjoyable to read. (see example)
• Finally, go to: File>Page Setup and change the document to be landscape and click OK. Notice that the Excel sheet now has a dotted line at the edge of where the page will be. Find a way to fit all of the information onto ONE page.

Step 2
Prepare a Budget Worksheet

Monthly budget worksheet created in Microsoft Excel

• Click on the "sheet 2" tab at the bottom of your page to open a new excel spreadsheet within the same document. In Cell A1 type: expense categories. In cell B1 type: Total Cost. Make these words bold and expand width of cells to fit these words.
• Again list the following expense categories, but this time vertically, in column A, rows 2 through 9: rent, utilities, transportation, food, medical expenses, clothing, leisure activities, and miscellaneous. (Press save!)
• Now, in column B, rows 2 through 9 plug in the expenses you totaled in your Budget Summary sheet.
• In cell A10 write: TOTAL and in cell B10 type the formula: =sum(B2:B9) or use Microsoft Excel's AutoSum function to add up the cost of these expenses (the capital E button on the task bar next to the fx button.)
• In Cell A12 now type: Monthly net income and in cell B12 estimate your monthly net income based on your hourly salary and taxes deducted. If a person made $9 per hour and worked 40 hours a week in a week she would make 9x40 = $360 and in a month she would make $360x4=$1,440. If taxes are 8.5%, then $122.40 would be deducted each month, leaving this individual with a NET income of $1,317.60. Insert your total for this fictitious budget into Cell B12. (Make sure you have formatted Cells B2 through 10 to be currency with 2 decimal places).
• In Cell B14, insert a formula to calculate the difference between Income and Expenses, such as: =(B12-B10). If the number that appears is red this indicates that you have a negative number! Meaning that the person using your budget will be spending far more than he or she earns!!
• Finally, rename this sheet from "sheet 2" to "My Monthly Budget" or a fictitious person's budget such as "Jo's Monthly Budget".

Step 3
Evaluate Your Budget

• What to do: Once you have typed in all of your monthly expenses, your worksheet will show you whether or not you are "making ends meet." Do your expenses exceed your income? If so, you must decide where to cut back so that your expenses do not exceed your income.
• In a MS Word document titled: Making Ends Meet, explain which aspects of your budget you will adjust to make ends meet. Include ways in which you might be able to stretch your budget (for example, take in a roommate to share the rent). If you were within your budget and had leftover money, explain which items you might spend a little more on, or what you plan to do with the money that you are saving each month.
• Now select one of the unexpected financial problems listed below. In an MS Word document, explain how you would adjust your budget to account for this unexpected expense. Explain your rationale. Then adjust your budget in your Microsoft Excel worksheet to account for this additional monthly expense.
• You forget to pay your phone bill and your phone service is shut off. To turn it back on, the phone company wants $50. OR
• The lease on your apartment is up for renewal and the landlord wants a 5% increase. How much will your increase be? How will you adjust your budget to account for this increase? OR
• Your dog gets sick and the medicine costs $150.
• Finally, use Word to write a report explaining what you've learned from this activity. Include information copied from your Excel worksheet as illustration whenever possible.