Yearly Budget

Don’t forget the FORMULAS

USE THE CELL NAMES IN YOUR FORMULAS 

Your Yearly Budget Project

1. Change the layout of the page to Landscape
2. Set your margins on the left and right for 0.5”
3. Insert the heading “(Your name)’s Yearly Budget” in cell A1
4. Merge and center this heading across columns A through M.
5. Change the color of the cell and the text color to match the sample below.
6. Select the entire row 3 by clicking on the actual row number on the left side.  Then Right click on the selected area and choose Format Cells/Alignment.  Then change the options by clicking on Wrap Text.

Link to Sample

1. Add the other table headings just as they appear in the image.  The headings should be font Arial, size 10 and bold.
2. Insert the name of the Months just as they appear in the image.  The months are font Arial, size 9 and not bold.
3. Insert a borderline just below the Headings in Row 3 (Select cells/format/cells/borders/OK)
4. Insert a borderline on the right side of Column A just as it appears in the image.
5. Now you must use formulas to calculate the information for your table:
6. Follow the instructions below. Make sure you are using the correct Cell Names in your formulas

Step 1: 

Fill in formulas for the month of January.  Fill them in according to the information below.

1. Utilities (Electric, Gas, Water, Wastewater)

a.      Utilities are Average Yearly Utility Cost divided by 12

o          San Diego is $5412/year.  Place this amount  in the TOTAL row.  Now calculate the monthly amount by dividing the TOTAL by the number of months in a year.  This should be a formula. 

• In Cell C5 type        =C4 , continue using this for all of the months in this column

2. Phone bill is the cell phone amount (E4) multiplied by 0.75.   Select your cell phone plan at the link below

o        http://www.lowermybills.com/twi/index.jsp?sourceid=seogoo8twi1a0320562

o        Enter your zip code, and select the best plan per month.

3.                  Housing Prices – Select the Apartment in San Diego

    http://www.apartmentcities.com/San-Diego-Apartments/

 This has a per month rate.  Put this amount into the respective monthly cells.

4.                  Cell Phone

q       http://www.lowermybills.com/twi/index.jsp?sourceid=seogoo8twi1a0320562

• Car Gas is your apartment expense (B4) multiplied by 0.25. or 25%  
• Car insurance is your apartment rent expense (B4) multiplied by 0.20. or 20%
• Food is your rent expense (B4) multiplied by 0.35 or 35%
• Entertainment is your rent (B4) multiplied by 0.23  or 23%
• Clothing is your rent (B4) multiplied by .09 or 9%
• Highlight cells A - M through row 17.   Right mouse click.  Format Cells, Click on the Numbers tab.  Click on currency, two decimals, $ symbol turned on.  Select the Red negative format. $1,234.00

Step 2: 

Use the links below to fill in the information for the first row for only the following item:

1. To calculate the Total Expenses column, you need to use a formula that will add all of cells for the month of January beginning with Rent and ending with Clothing.  To do this, you will use the AutoSum feature that will create the formula for you.  Follow these steps to write the equation:

                        åSelect the Total Expense cell for January.

åClick on the AutoSum Icon, å , located near the top of the document window.

                        åThen select cells B4 through J4.

                                    åThen press the Enter key.

I know your table is still not complete, but that is OK.  It will be soon.

Step 3:

1. Now you need to calculate your net income (how much you have to spend each month). To do this you must calculate the number of hours per week that you will work.  You can use 40 hours for this amount.  Insert this number into cell H20.  In cell G20, label the cell “Work hours/week.”

1. Then you must figure out how many weeks per year you will work.  (Count the number of weeks in a year.  (There are 52 weeks in a year.)  Then subtract out a couple of weeks for vacation).  Put the number of weeks that you came up with in Cell H21.  In cell G21, label the cell “Weeks/year.”
2. Then you must figure out how much money per hour you will make.  Take the amount of minimum wage and add $2.00 to that amount.  Insert that new number in cell H22.   In cell G22, label the cell “Hourly wage”.

Now you have enough information to calculate the rest of your table.

Step 4:

1. To calculate the monthly income for January, you must multiply the rate of pay located in cell H22 by the number of hours worked per week located in cell H20 and then by 4.  (There are approximately 4 weeks in the month of January).  Once you get the correct formula in cell L4, type =L4 in the cells below this to get your consistent monthly income.
2. Savings per month is your Income/month minus your total expenses for the month.
3. Once you have all of the formulas and information filled in for the month of January, you are almost done.

Step 5:

Step 6:

1. Reality calls…  There is a rent increase in June.  Increase the rent amount $100 for the rest of the year beginning in June.

Step 7:

Questions to answer:

.  Answer these questions under your spreadsheet .  Type your answers beginning in cell A25.

1. How much money in debt are you at the end of the year?   This is answered with your original salary. 
2. To answer this, you will need to change the rate of pay until you find out the correct answer.  To do this select cell H22 and change the number until your total savings in cell M17 changes to a positive number.  What is the rate of pay required to end up with approximately $0 total savings at the end of the year? Get it as close as possible.
3. What is the required rate of pay so that you end up with $500 in savings at the end of the year?
4. Besides increasing your rate of pay, what else could you do to increase your total income per year? Remember there are only so many hours in the week.  60 is the maximum that you can work for this assignment.  SO--------   what else do you have to do to come out even?  List three possibilities.