Bertrand Family Budget Andrew and Maria Bertrand of Santa Fe, New Mexico, are hoping to purchase their first home and they are using Excel to help manage their family budget. The couple needs to estimate the monthly payments required for a $275,000 mortgage. They want to track their income and expenses using tables, charts, data bars, and sparklines. You will help them do all of these tasks. Complete the following:
1. Open the Bertrand workbook located in the Excel4 Case1 folder included with your Data Files, and then save the workbook as Bertrand Budget in the location specified by your instructor.
2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4.
3. In the Budget worksheet, in the range O4:O6, enter the parameters of a $275,000 loan that is repaid at an annual interest rate of 4.35 percent over 30 years.
4. In the range O8:O9, calculate the total number of months to repay the loan and the interest rate per month.
5. In cell O11, use the PMT function to calculate the monthly payment. Multiply the PMT function by –1 so that the result appears as a positive currency value rather than a negative value.
6. In the range D25:O25, enter the value of the monthly mortgage payment by creating an absolute reference to the value in cell O11.
7. In the range D18:O18, calculate the total income per month. In the range D27:O27, calculate the total expenses incurred each month. In the range D28:O28, calculate the couple’s net income (total income minus total expenses) each month.
8. In the range C4:C11, calculate the average monthly value of each expense category.
9. Add green gradient data bars to the values in the range C4:C11. Set the maximum length of the data bars to a value of 2500.
10. Insert line sparklines in the range D4:D11 using the expense values in the range D19:O26. On the SPARKLINE TOOLS DESIGN tab, in the Show group, click the High Point check box to mark the high point of each sparkline.
11. Create a clustered column chart of the income, expenses, and net income for each month of the year based on the data in the nonadjacent range D15:O15;D18:O18;D27:O28. Place the chart within the range E2:K13.
12. Format the clustered column chart by making the following changes: a. Format the chart with the Style 8 chart style. b. Change the chart title to Income and Expenses and format it in a Calibri, non-bold 12-point font. c. Change the vertical scale of the chart to range from –1000 to 6500 in steps of 1000. d. Change the series overlap of the columns to 0% and the gap width to 200%. 13. Save the workbook. 14. Perform a what-if analysis by changing the length of the loan from 30 years to 15 years. Determine the monthly payments under this new mortgage plan, and then analyze its impact on the couple’s projected income and expenses. 15. Save the workbook as Bertrand Budget 2, and then close it.
