Microsoft Excel Personal Budget Assignment
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:
- In the first row of the spreadsheet enter your name
- Enter the row titles given on the attached form, with the same formatting
- Enter the career chosen
- Income Section
- 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)
- 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)
- 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 |
- Enter a formula to compute Salary after Taxes (Salary before taxes minus federal taxes and state taxes)
- Enter a formula to compute Monthly Salary after Taxes (divide Salary after Taxes by 12)
- Expenses Section
- Medical insurance cost is $0 for lawyers, teachers and nurses (they get it paid for by work), otherwise it is $218 per month
- Rent – go online to the Burlington Free Press web site and find an apartment available for rent – enter the monthly rent amount
- 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 |
- Auto insurance –$129 is the average monthly auto insurance rate in Vermont
- Food:
- A month of breakfasts costs about $38
- A month of lunches costs about $218
- A month of dinners costs about $240
- A month of snacks costs about $60
- Put a formula in the spreadsheet adding these numbers up
- Clothing – figure out an appropriate amount that this person would spend on clothes in a month
- 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)
- Student loans
- Taxi drivers and construction laborers have no student loans
- Lawyers pay about $1,110 per month for student loans
- Teachers pay about $444 per month for student loans
- Nurses pay about $333 per month for student loans
- Compute your total expenses (the sum of all of the expense amounts, use the SUM formula)
- Compute your net income, the amount you save at the end of each month (monthly salary after taxes minus total expenses)
- Pie Chart
- Create a 3-D pie chart that shows your monthly expenses
- Include an appropriate chart title, a legend, and labels for what each section of the chart represents
- Print
- Print out your spreadsheet (make sure it fits on one page) and hand it in