Download Solution here Exel IT HW

 

In this assignment you will create a personal monthly budget in Microsoft Excel. The budget will be for a person who is just starting out in the work world.

 

You can choose one of the following 5 jobs: Lawyer
      Teacher
      Nurse
      Taxi driver
      Construction laborer.

 

The screen shot below shows an example of the row titles to be used in Excel:

 

Instructions:

 

  1. In the first row of the spreadsheet enter your name
  2. Enter the row titles given on the attached form, with the same formatting
  3. Enter the career chosen

 

  1. Income Section
    1. Go online and find the average annual beginning salary for the job you chose (use the following website http://www.bls.gov/search/ooh.asp?ct=OOH)

 

  1. If you can only find salary per hour, take that number and multiply it by 2,080 (the number of hours worked in a year)
  1. Enter the amount of federal taxes
i. Lawyer $22,670
ii. Teacher $8,516
iii. Nurse $10,664
iv. Taxi driver $2,650
v. Construction laborer $3,549

 

 

c.  Enter the amount of state taxes  
i. Lawyer $6,561
ii. Teacher $2,359
iii. Nurse $2,978
iv. Taxi driver $732
v. Construction laborer $948
  1. Enter a formula to compute Salary after Taxes (Salary before taxes minus federal taxes and state taxes)
  2. Enter a formula to compute Monthly Salary after Taxes (divide Salary after Taxes by 12)
  1. Expenses Section
    1. Medical insurance cost is $0 for lawyers, teachers and nurses (they get it paid for by work), otherwise it is $218 per month
    2. Rent – go online to the Burlington Free Press web site and find an apartment available for rent – enter the monthly rent amount

 

  1. Car – you have the option of buying one of the following three cars, enter the
monthly loan amount for the car chosen  
i. 2008 BMW 5 Series $847 per month
ii. 2005 Honda Accord $341 per month
iii. 2000 Chevy Impala $120 per month
  1. Auto insurance –$129 is the average monthly auto insurance rate in Vermont
  2. Food:
    1. A month of breakfasts costs about $38
    2. A month of lunches costs about $218
  • A month of dinners costs about $240
  1. A month of snacks costs about $60
  2. Put a formula in the spreadsheet adding these numbers up
  1. Clothing – figure out an appropriate amount that this person would spend on clothes in a month
  2. Entertainment – figure out an appropriate amount that this person would spend on entertainment in a month (this includes, movies, music, concerts, sporting events and anything else that they might do for fun)

 

  1. Student loans
    1. Taxi drivers and construction laborers have no student loans
    2. Lawyers pay about $1,110 per month for student loans
  • Teachers pay about $444 per month for student loans
  1. Nurses pay about $333 per month for student loans
  1. Compute your total expenses (the sum of all of the expense amounts, use the SUM formula)
  2. Compute your net income, the amount you save at the end of each month (monthly salary after taxes minus total expenses)
  1. Pie Chart
    1. Create a 3-D pie chart that shows your monthly expenses
    2. Include an appropriate chart title, a legend, and labels for what each section of the chart represents

 

  1. Print
    1. Print out your spreadsheet (make sure it fits on one page) and hand it in