Shelly Cashman Excel 2013 Chapter 4: SAM Project 1b  Bradley Stein 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Shelly Cashman Excel 2013 Chapter 4: SAM Project 1b 

Bradley Stein

1. 

Unhide the Loan Options worksheet.

2. 

In the Loan Options worksheet, fill the range B17:B29 with a number series

based on the values in range B14:B16. (Hint: Use the Fill Without Formatting

option.)

3. Enter a formula in cell D14 using the PMT function to calculate the monthly

payment on a loan given the loan parameters listed in cells D5, D7, and C14.

(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 D14 to the range D15:D29.

4. Enter a formula in cell E14 using the PMT function to calculate the monthly

payment on a loan given the loan parameters listed in range E5, E7, and C14.

(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 E14 to the range E15:E29.

5. Enter a formula in cell F14 using the PMT function to calculate the monthly

payment on a loan given the loan parameters listed in range F5, F7, and C14.

(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 F14 to the range F15:F29.

6. 

Right-align the contents of cells B13:F13.

7. 

Apply Italic formatting to the text in cells D4:F4.

8. 

Add the following borders to the ranges specified below:

a. Apply an Outside Border to range B4:F9.

b. Apply a Top Border to range D5:F5.

c. Apply a Right Border to range C4:C9.

9. 

Format the range D14:F29 to be right-aligned. Then, modify the number format

of this range to display 0 decimal places.

10. 

Create a conditional formatting rule to Highlight Cells in range C14:C29 whose

value is equal to cell D9. Apply the formatting of Yellow Fill with Dark Yellow

Text.

11. 

Lock the cells in range B14:F29.

12. 

Select the non-adjacent ranges D5:F5 and D8:F8. Unlock the cells in those

ranges.

13. 

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

14. 

Switch to the Savings Plan 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.)

15. 

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.)

16. 

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.)

17. 

Bold the text in range B3:F3.

18. 

Create names for the following cells as described in Table 1 in the Assignment

file.

19. 

Apply the cell style 40% - Accent 2 to the range D7:F7.

20.

Apply conditional formatting to Highlight Cells in range D12:F27 with a value

between $125,000 and $150,000. Apply the formatting of Yellow Fill with Dark

Yellow Text.

21. 

Switch to the Capital Plan worksheet. Using the custom cell names, enter a

formula in cell C10 that adds the value in the AccruedSavings cell to the value in

the Loan cell.

22. 

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.)

23. 

Name the range B2:C10 ProposedCapitalPlan.