Finance Excel helper

Unhide the Loan Scenarios worksheet.

In the Loan Scenarios worksheet, fill the range B15:B27 with a number series based on the values in range B12:B14.

Enter a formula in cell D12 using the PMT function to calculate the monthly payment on a loan given the loan parameters listed in cells D4, D6, and C12. (Hint: Enter a negative sign in front of the PMT function to display the monthly payment as a positive number. Use absolute cell references for the term (nper) and loan amount (pv) arguments. The interest rate argument should be a relative reference.) Copy the formula from cell D12 to the range D13:D27.

Enter a formula in cell E12 using the PMT function to calculate the monthly payment on a loan given the loan parameters listed in range E4, E6 and C12. (Hint: Enter a negative sign in front of the PMT function to display the monthly payment as a positive number. Use absolute cell references for the term (nper) and loan amount (pv) arguments. The interest rate argument should be a relative reference.) Copy the formula from cell E12 to the range E13:E27.

Enter a formula in cell F12 using the PMT function to calculate the monthly payment on a loan given the loan parameters listed in range F4, F6, and C12. (Hint: Enter a negative sign in front of the PMT function to display the monthly payment as a positive number. Use absolute cell references for the term (nper) and loan amount (pv) arguments. The interest rate argument should be a relative reference.) Copy the formula from cell F12 to the range F13:F27.

Center the contents of cells B11:F11.

Apply Bold formatting to the text in cells D3:F3.

Add the following borders to the ranges specified below:

a. Apply an Outside Border to range B10:F27.

b. Apply a Bottom Border to range B11:F11.

c. Apply a Left Border to range D12:D27.

Format the range D12:F27 to be center-aligned. Then, modify the number format of this range to display 0 decimal places.

Create a conditional formatting rule to Highlight Cells in range C12:C27 whose value is equal to cell D8. Apply the default formatting of Light Red Fill with Dark Red Text.

Lock the cells in range D12:F27.

Select the non-adjacent ranges D4:F4 and D7:F7. Unlock the cells in those ranges.

Protect the worksheet. You do not need to include a password.

Switch to the Savings Scenarios worksheet, and enter a formula in cell D7 using the FV function to calculate the accrued savings realized from the parameters displayed in the range D4:D6. (Hint: Enter a negative sign in front of the FV function to display the accrued savings as a positive number.)

Enter a formula in cell E7 using the FV function to calculate the accrued savings realized from the parameters displayed in the range E4:E6. (Hint: Enter a negative sign in front of the FV function to display the accrued savings as a positive number.)

Enter a formula in cell F7 using the FV function to calculate the accrued savings realized from the parameters displayed in the range F4:F6. (Hint: Enter a negative sign in front of the FV function to display the accrued savings as a positive number.)

Italicize the text in range C7:F7.

Create names for the following cells as described in Table 1 on the following page.

Table 1: Defined Names for Range D7:F7

© 2014 Cengage Learning.

Apply the style 40% - Accent 5 to the range B10:F10.

Apply conditional formatting to Highlight Cells in range D13:F27 with a value between $250,000 and $275,000. Apply the default formatting of Light Red Fill with Dark Red Text.

Navigate to the Capital Plan worksheet. Using the custom cell names, enter a formula in cell C11 that adds the value in the Savings cell to the value in the Loan cell. (Hint: Do not use the SUM function.)

Name the cells in the range C4:C6, using the text in the column directly to the left of each cell as the cell name. (Hint: Use the Create Names From Selection command.)

Name the range B2:C11 CapitalPlan

Your workbook should look like the Final Figures on the following pages. Save your changes, close the document, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Capital Plan Worksheet

Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 2: Loan Scenarios Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 3: Savings Scenarios Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.