Household Budget

Elijah and Kelly Williams are a recently married couple living in Portland, Oregon. Elijah works part-time and attends the local community college. Kelly works as a marketing manager at a clothing company in North Portland. They are trying to decide if they can afford to move to a better apartment, one that is closer to work and school. They want to use Excel to examine their household budget. They have started their budget spreadsheet, but they need your help with it.

When saving files for submission, include your last name in the file name.

1. Open the file named PR3 Data from the Data Files and then save it as PR3 Williams.
2. Insert two new rows at the top of the worksheet.
3. Enter the following text:

A2 Category B2 Item C2 January O2 Yearly Total (adjust column width as needed to fit this text)

1. Using the text in cell C2, use Autofill to fill in the months February through December in cells D2:N2. Adjust column widths as needed to fit the names of the months in these columns.
2. Bold and center align all of the headings in Row 2.
3. Type Williams Family Budget in A1. Merge & Center A1:O1. Make this text 22 point bold.
4. Next, you need to complete the monthly values for some of the income and expense items. In the rows for Income #1, Income #2, Mortgage/Rent, Homeowners/Rent Insurance, Car Insurance, Car Payment, and Gym Fees/Memberships, copy the values for January to the cells for February through December.
5. Use the Totals tab in the Quick Analysis tool to add the SUM to Column O. Delete the formulas from O7, O17, O24, O32, and O38.
6. In C6: N6, use the Sum function to calculate the Total Income for each month.
7. Similar to step 6, use the Sum function to calculate the Total Home Expenses, Total Daily Living Expenses, Total Transportation Expenses, Total Entertainment Expenses, and Total Personal Expenses for each month.
8. Use the Sum function to calculate the Yearly Total Personal Expenses in cell O45.
9. Format the numerical data in Row 3 as Currency with no decimal places. Format all the total rows as Currency with no decimal places and with a top border.
10. Apply the Comma format with no decimal places in all the other rows.
11. In A47, type Total Expenses.
12. In C47, enter a formula that adds together all of the expense category totals for January. Copy the formula in C47 to D47:O47.
13. In A49, type Net Income. Bold and indent this text.
14. In C49, enter a formula that calculates the difference between Total Income and Total Expenses (=Total Income-Total Expenses) for January. Copy this formula to D49:O49.
15. Format the data in Rows 47 and 49 as Currency with no decimal places. Bold O47 and O49. Add a Top and Double Bottom Border to the data in Row 49.
16. Select C49:N49. Use the Quick Analysis tool to add data bars to this data.
17. In B50, type New Apartment?. Enter an IF statement in C50 that displays the word No if the amount in C49 is less than or equal to zero and Maybe if the amount is greater than zero. Copy C50 to D50:N50.
18. Check to see if your IF statement worked correctly in row 50. If the cells say No when the data bar in the cell above it is red and Maybe when the data bar in the cell above it is blue, your IF statement is correct.
19. Review the worksheet in Print Preview. Make any changes needed to make the worksheet print on one page with landscape orientation.
20. Save the PR3 Williams workbook.
21. Submit the PR3 Williams workbook as directed by your instructor.

1 2 3 4 Total Income 5 6 Home 7 $ 10 11 12 13 Income 14 Total Home Exponror 15 16 Daily Living 17 18 19 20 21 Total Daily Living Exponror 22 23 Transportation 24 25 A 26 27 28 29 Total Transportation Exponror 30 31 Entertainment 32 33 34 36 35 Total Entertainment Exponror 37 Personal 38 39 40 41 42 43 Total Personal Exponror 44 B Income $1 Income $2 Other Income Mortgage Rent Hameauner/Rent Insurance Utilition Cable internet Trarh Home Maintenance Mirc. Haure Stuff Laun/Garden Graceries Clothing Books & Supplier Child Care Potr Car Insurance Car Maintenance Car Payments Bur/Train Fair Gar (Car) Parking Travel Movier Special Occarians Giftr Subscriptions Phone Gym Foor/Memberships Salant Barbor Eating Out Miscellaneour C 1650 2000 900 $ 255 $75 2115 375 210 120 50 200 230 730 131 250 E 200 795 125 120 F 195 $25 138 180 G 150 $55 120 250 H 165 $15 2419 145 120 175 $85 140 150 J 165 920 3275 135 135 K 160 942 145 200 L 160 $75 135 250 M 200 165 N 975 1050 270 235 175 350 0 1 2 3 4 Total Income 5 6 Home 7 $ 10 11 12 13 Income 14 Total Home Exponror 15 16 Daily Living 17 18 19 20 21 Total Daily Living Exponror 22 23 Transportation 24 25 A 26 27 28 29 Total Transportation Exponror 30 31 Entertainment 32 33 34 36 35 Total Entertainment Exponror 37 Personal 38 39 40 41 42 43 Total Personal Exponror 44 B Income $1 Income $2 Other Income Mortgage Rent Hameauner/Rent Insurance Utilition Cable internet Trarh Home Maintenance Mirc. Haure Stuff Laun/Garden Graceries Clothing Books & Supplier Child Care Potr Car Insurance Car Maintenance Car Payments Bur/Train Fair Gar (Car) Parking Travel Movier Special Occarians Giftr Subscriptions Phone Gym Foor/Memberships Salant Barbor Eating Out Miscellaneour C 1650 2000 900 $ 255 $75 2115 375 210 120 50 200 230 730 131 250 E 200 795 125 120 F 195 $25 138 180 G 150 $55 120 250 H 165 $15 2419 145 120 175 $85 140 150 J 165 920 3275 135 135 K 160 942 145 200 L 160 $75 135 250 M 200 165 N 975 1050 270 235 175 350 0