11 3.5 Chapter Practice
Household Budget
Download Data File: PR3 Data
Etta and Lucian Redding are a recently married couple living in Portland, Oregon. Lucian 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.
-
- Open the file named PR3 Data and then save it as PR3 Redding.
- Insert two new rows at the top of the worksheet.
- Enter the following text:
A2 Category
B2 Item
C2 January
O2 Yearly Total (adjust column width as needed to fit this text)
- 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.
- Bold and center align all of the headings in Row 2.
- Type “Redding Family Budget” in A1. Merge & Center A1:O1. Make this text 22 point bold.
- 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.
- 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.
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. - In C6: N6, use the SUM function to calculate the Total Income for each month.
- 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.
- Use the SUM function to calculate the Yearly Total Personal Expenses in cell O45.
- Format the numerical data in Row 3 as Currency with no decimal places, and with a top border. Format all the total rows as Currency with no decimal places and with a top border (Rows 6, 16, 23, 31, 37, and 45).
- Apply the Comma format with no decimal places in all the other rows.
- In A47, type “Total Expenses”.
- In C47, enter a formula that adds together all of the expense category totals for January. Copy the formula in C47 to D47:O47.
- In A49, type “Net Income”. Bold and indent this text. Also bold C49:N49
- 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.
- Format the data in Rows 47 and 49 as Currency with no decimal places. Bold O47 and O49. A