Data File needed for this Case Problem, Bertrand.xlsx 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 04:06, 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 08:09, calculate the total number of months to repay the ban and the interest rate per month.

5. In cell 011, use the PET function to calculate the monthly payment. Multiply the PET function by —t so that the result appears as a positive currency value rather than a negative value.

6. In the range 025025, enter the value of the monthly mortgage payment by creating an absolute reference to the value in cell 011.

7. In the range D16:018, calculate the total income per month. In the range 027:027, calculate the total expenses incurred each month. In the range D28028 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 D4011 using the expense values in the range D19:026. On the SPARKLINE TOOLS DESIGN tab, in the Show group, click the High Point check box to mark the high point of each sparkline.

Data File needed for this Case Problem, Bertrand.xlsx 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 04:06, 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 08:09, calculate the total number of months to repay the ban and the interest rate per month.

5. In cell 011, use the PET function to calculate the monthly payment. Multiply the PET function by —t so that the result appears as a positive currency value rather than a negative value.

6. In the range 025025, enter the value of the monthly mortgage payment by creating an absolute reference to the value in cell 011.

7. In the range D16:018, calculate the total income per month. In the range 027:027, calculate the total expenses incurred each month. In the range D28028 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 D4011 using the expense values in the range D19:026. On the SPARKLINE TOOLS DESIGN tab, in the Show group, click the High Point check box to mark the high point of each sparkline.

Data File needed for this Case Problem, Bertrand.xlsx 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 04:06, 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 08:09, calculate the total number of months to repay the ban and the interest rate per month.

5. In cell 011, use the PET function to calculate the monthly payment. Multiply the PET function by —t so that the result appears as a positive currency value rather than a negative value.

6. In the range 025025, enter the value of the monthly mortgage payment by creating an absolute reference to the value in cell 011.

7. In the range D16:018, calculate the total income per month. In the range 027:027, calculate the total expenses incurred each month. In the range D28028 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 D4011 using the expense values in the range D19:026. On the SPARKLINE TOOLS DESIGN tab, in the Show group, click the High Point check box to mark the high point of each sparkline.

2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4.

8. In the range C4:C11, calculate the average monthly value of each expense category.