Broward Community College Household Worksheet

It’s easy to get smarter about your money when you have a head start.

In this assignment, you will use Excel to create a household budget to help maintain control over your finances and see how your money is being used.Conduct a short internet job search for the type of position you will be applying for once you have completed your education. Consider your work experience in this search. For example, if you have no experience in field, please search for entry level positions or the type of position someone with your background could reasonably qualify for. Look for three positions that include wage/salary information. Once you have found 3 job ads that include salary information, determine the average starting salary and use this as the basis for your household budget.Create an Excel spreadsheet of your own design to breakdown your household budget. List monthly expenses and monthly income. You will need to list your expenses for 12 months (so you should have columns for each month).  Remember your expenses are not always the same every month (for instance your electric bill could fluctuate based on how hot it is). Be sure to format the expenses and income as currency using one of the formatting options in Excel. Do NOT use an online template. The designs must be easy and logical to read.  You should start from a blank, clean, Excel document.

Requirements of the assignment

Title your spreadsheet with a meaningful title, in bold, at least 16 point size, centered above your spreadsheet content and is centered over more than 3 columns. Use “Merge & Center” provided in Excel. The rest of the document should be in 12 point font.  All data that represents money is formatted as currency.

Write your job title and the city and state you will live in

Include 3 job titles, locations, and starting salaries from the job search you performed.  Find the average starting salary (using the AVERAGE function) Calculate and label your monthly income by dividing the annual income cell (reference the cell) by 12. Please make sure that your salary realistically and accurately matches your skill set (for instance, you will not start an engineering job making 90,000 a year).

Include at least 8 expense items such as electricity, telephone, gas, groceries, rent/mortgage, car payment, student loan payments, credit card payments, entertainment allowance, clothing allowance, etc.

Make all negative numbers appear in red using automatic formatting or conditional formatting provided in Excel. Choosing RED as the choice for the font color is not correct. Apply this formatting to the entire worksheet.  You must have a negative somewhere in your calculations

For the following items, you MUST use an Excel function. You cannot calculate the values and enter a number. Excel functions include SUM, AVERAGE, MIN, MAX, etc. You must choose the correct Excel function and then use it properly to get full credit.

Using an Excel function, calculate the average of each type of expense category for the months you have listed. You must select the right function, such as SUM or AVERAGE, and then use the function correctly in the cell you have selected. Be sure to label the row or column (note this will depend on how you set up your budget sheet).

Calculate a subtotal for each expense category, using a function provided in Excel. You must select the right function, such as SUM or AVERAGE, and then use the function correctly in the cell you have selected. Be sure to label the row or column (note this will depend on how you set up your budget sheet).

Calculate a subtotal for each month, then a grand total of all the months, using functions provided in Excel. You must select the right function, such as SUM or AVERAGE, and then use the function correctly in the cell you have selected. Be sure to label the row or column (note this will depend on how you set up your budget sheet).

Calculate your monthly income after expenses. For each month, subtract the monthly expenses from the monthly income.  Use an absolute reference to reference the cell of the monthly income. You must select the right function, such as SUM or AVERAGE, and then use the function correctly in the cell you have selected. Be sure to label the row or column (note this will depend on how you set up your budget sheet).

Include a pie chart to display the breakdown of expenses. Use the expense categories you have listed and the monthly totals you have calculated to create your pie chart.  Put the pie chart on its own worksheet labeled “Chart”.  Please note, the chart values should be from the budget page of the worksheet.  Do not copy the values into the new worksheet.

Living as an LPN
Nurse in Tamarc, FL
Job Title
Licensed Practical Nurse
(LPN) Job 1 Memorial
Hospital
Licensed Practical Nurse
(LPN) Job 2 Broward
General
Licensed Practical Nurse
(LPN) Job 3 Sunrise Rehab
Center
Average Salary
Month
January
February
March
April
May
June
July
August
September
October
November
December
Subtotal
Average Expense
Hourly Wage
Monthly
Salary
Annual
Income
$19.74
$3,158.40
$37,900.80
$24.96
$3,993.60
$47,923.20
$21.00
$3,360.00
$40,320.00
$21.90
$3,504.00
$42,048.00
Electricity
Telephone
Gasoline
Groceries
$100.00
$100.00
$100.00
$110.00
$115.00
$120.00
$120.00
$130.00
$120.00
$100.00
$110.00
$90.00
$1,315.00
$109.58
$55.00
$55.00
$55.00
$55.00
$50.00
$50.00
$50.00
$50.00
$50.00
$50.00
$50.00
$50.00
$620.00
$51.67
$100.00
$100.00
$100.00
$100.00
$100.00
$100.00
$100.00
$100.00
$100.00
$100.00
$100.00
$100.00
$1,200.00
$100.00
$115.00
$115.00
$115.00
$115.00
$140.00
$150.00
$150.00
$150.00
$150.00
$200.00
$200.00
$200.00
$1,800.00
$150.00
Monthly Expenses
Entertainment Rent
$200.00
$200.00
$300.00
$200.00
$200.00
$500.00
$200.00
$200.00
$400.00
$200.00
$300.00
$500.00
$3,400.00
$283.33
$900.00
$900.00
$900.00
$900.00
$900.00
$900.00
$900.00
$900.00
$900.00
$900.00
$900.00
$900.00
$10,800.00
$900.00
Car Payment Car Insurance
$340.00
$340.00
$340.00
$340.00
$340.00
$340.00
$340.00
$340.00
$340.00
$340.00
$340.00
$340.00
$4,080.00
$340.00
$110.00
$110.00
$110.00
$110.00
$110.00
$110.00
$110.00
$110.00
$110.00
$110.00
$125.00
$125.00
$1,350.00
$112.50
Excel Budget Example
Cable
Credit card
Gifts
$120.00
$120.00
$120.00
$120.00
$120.00
$120.00
$120.00
$120.00
$120.00
$120.00
$120.00
$120.00
$1,440.00
$120.00
$100.00
$100.00
$60.00
$60.00
$60.00
$60.00
$60.00
$60.00
$60.00
$60.00
$100.00
$100.00
$880.00
$73.33
$50.00
$50.00
$50.00
$50.00
$50.00
$50.00
$50.00
$50.00
$50.00
$50.00
$50.00
$1,000.00
$1,550.00
$129.17
Total Wages Left
Monthly
After
$2,190.00
$2,190.00
$2,250.00
$2,160.00
$2,185.00
$2,500.00
$2,200.00
$2,210.00
$2,400.00
$2,230.00
$2,395.00
$3,525.00
$28,435.00
$2,369.58
$1,314.00
$1,314.00
$1,254.00
$1,344.00
$1,319.00
$1,004.00
$1,304.00
$1,294.00
$1,104.00
$1,274.00
$1,109.00
($21.00)
$1,134.42
Yearly
Expenses
$2,190.00
$4,380.00
$6,630.00
$8,790.00
$10,975.00
$13,475.00
$15,675.00
$17,885.00
$20,285.00
$22,515.00
$24,910.00
$28,435.00
Annual Income
After Yearly
$39,858.00
$37,668.00
$35,418.00
$33,258.00
$31,073.00
$28,573.00
$26,373.00
$24,163.00
$21,763.00
$19,533.00
$17,138.00
$13,613.00
Household Budget for a Nurse
Nurse in Tamarc, FL
Job Title
Licensed Practical Nurse (LPN) Job 1
Memorial Hospital
Licensed Practical Nurse (LPN) Job 2
Broward General
Licensed Practical Nurse (LPN) Job 3
Sunrise Rehab Center
Average Salary
MONTHLY INCOME
Salary
Child Support
TOTAL
MONTHLY EXPENSES
Rent
Elctricity Bill
Phone Bill
Cable/ Internet Bill
Grcoceries
Car Payment
Car Insurance
Gas
Child Care
Monthly SubTotal
Money Left After Expenses
My Budget
Hourly Wage Monthly Salary Annual Income
$19.74
$3,158.40
$37,900.80
$24.96
$3,993.60
$47,923.20
$21.00
$3,360.00
$40,320.00
$21.90
$3,504.00
$42,048.00
Jan
Feb
March
$3,504.00
$300.00
$3,804.00
$3,504.00 $3,504.00 $3,504.00 $3,504.00 $3,504.00 $3,504.00 $3,504.00 $3,504.00 $3,504.00 $3,504.00
$300.00
$300.00
$300.00
$300.00
$300.00
$300.00
$300.00
$300.00
$300.00
$300.00
$3,804.00 $3,804.00 $3,804.00 $3,804.00 $3,804.00 $3,804.00 $3,804.00 $3,804.00 $3,804.00 $3,804.00
1,500.00 $
100.00 $
85.00 $
110.00 $
300.00 $
500.00 $
150.00 $
$200.00
$800.00
$3,745.00
$59.00
Expense Average Expense Subtotal
1,500.00 $ 1,500.00 $ 1,500.00 $ 1,500.00 $ 1,500.00 $ 1,500.00 $ 1,500.00 $ 1,500.00 $ 1,500.00 $ 1,500.00 $
1,500.00 $
18,000.00
120.00 $ 120.00 $ 120.00 $ 150.00 $ 175.00 $ 175.00 $ 150.00 $ 120.00 $ 120.00 $ 120.00 $
130.83 $
1,570.00
85.00 $
85.00 $
85.00 $
85.00 $
85.00 $
85.00 $
85.00 $
85.00 $
85.00 $
85.00 $
85.00 $
1,020.00
110.00 $ 110.00 $ 110.00 $ 110.00 $ 110.00 $ 110.00 $ 110.00 $ 110.00 $ 110.00 $ 110.00 $
110.00 $
1,320.00
400.00 $ 400.00 $ 400.00 $ 600.00 $ 600.00 $ 600.00 $ 500.00 $ 500.00 $ 500.00 $ 500.00 $
466.67 $
5,600.00
500.00 $
6,000.00
500.00 $ 500.00 $ 500.00 $ 500.00 $ 500.00 $ 500.00 $ 500.00 $ 500.00 $ 500.00 $ 500.00 $
150.00 $ 150.00 $ 150.00 $ 150.00 $ 150.00 $ 150.00 $ 150.00 $ 150.00 $ 150.00 $ 150.00 $
150.00 $
1,800.00
$200.00
$200.00
$200.00
$200.00
$200.00
$200.00
$200.00
$200.00
$200.00
$200.00 $
200.00 $
2,400.00
$800.00
$800.00
$800.00
$200.00
$0.00
$200.00
$800.00
$800.00
$800.00
$600.00 $
616.67 $
7,400.00
$3,865.00 $3,865.00 $3,865.00 $3,495.00 $3,320.00 $3,520.00 $3,995.00 $3,965.00 $3,965.00 $3,765.00 $
3,759.17 $
45,110.00 Total Expense
($61.00)
($61.00)
($61.00)
$309.00
$484.00
$284.00
($191.00) ($161.00) ($161.00)
$39.00 $
44.83 $
538.00 Total Savings
$
45,648.00 Total Income
$3,504.00
$300.00
$3,804.00
$ 1,500.00 $
$
100.00 $
$
85.00 $
$
110.00 $
$
300.00 $
$
500.00 $
$
150.00 $
$200.00
$800.00
$3,745.00
$59.00
April
May
June
July
Aug
Sept
Oct
Nov
Dec
Total Income
$42,048.00
$3,600.00
$45,648.00