3.6 Chapter Practice
Household Budget
Etta and Luca Redding are a couple living in Portland, Oregon. Luca works part time and attends the local community college. Etta 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.
Preparing the Worksheet
- Open the file named PR3 Data and then save it as PR3 Redding.
- Insert two new rows at the top of the worksheet.
- Change the font of entire worksheet to Calibri Light, size 12.
- In cell C2, enter the text Jan (the abbreviation for January).
- Use Autofill to fill in the months Feb through Dec in cells D2:N2. (Hint: Click on cell C2 and drag the fill handle through cells N2)
- In cell O2, enter the text Yearly Total (adjust column width as needed).
- Bold and center align all of the headings in Row 2.
- Type “Redding Family Budget” in A1. Merge & Center A1:O1. Make this text 24 point bold.
- Increase the height of Row 1 to 60px (0.6″). (Hint: Format – Row Height)
- Copy the January values through the other months (February through December) for the following items: (Hint: Use either Copy and Paste or AutoFill)
- Luca’s Income
- Etta’s Income
- Rent
- Renter’s Insurance
- Car Insurance
- Car Payment
- Gas (Car)
- Gym Membership
Calculating Income and Expense totals
- Use the Totals tab in the Quick Analysis tool to add the SUM to Column O. (Hint: Select C3:N24 , click the Quick Analysis Tool button in the bottom-right of the selection, click the Totals tab, then select the SUM column option).
Mac Users should use the AutoSum tool to calculate the totals in Column O. Since you are using the AutoSum tool, you may not have to delete any formulas in the cells listed in Step 5 above. Also, the Quick Analysis tool will automatically bold the values in Column O. Mac Users should bold cells O3:O45. - Delete the SUM functions in the cells for the rows between the categories. (Hint: O6, O12, and O20)
- In C5: N5, use the SUM function to calculate the Total Income for each month.
- Similar to Step 3, use the SUM function to calculate the Total Housing Expenses, Total Essential Expenses, and Total Optional Expenses for each month.
- Format the numerical data in Row 3 as Accounting with no decimal places.
- Format all the total rows as Accounting with no decimal places and with a top border. (Hint: Rows 5, 11, 19, and 24)
- Apply the Comma format with no decimal places in all the other rows. (Hint: Use the Format Painter to make the process quicker)
- In B26, type “Total Expenses”. Apply bold and right-alignment.
- In C26, enter a formula that adds together all of the expense category totals for January. Copy the formula in C26 to the other months and the yearly total (D26:O26). (Hint: the formula for January needs to add C11, C19, and C24).
- Format the