Shelly Cashman Excel 2013 Chapter 4: SAM Project 1a Flex Cab Company FINANCIAL FORMULAS AND FORMATTING WORKSHEETS 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Shelly Cashman Excel 2013 Chapter 4: SAM Project 1a

Flex Cab Company

FINANCIAL FORMULAS AND FORMATTING WORKSHEETS

1. 

Unhide the Loan Scenarios worksheet.

2. 

In the Loan Scenarios worksheet, fill the range B15:B27 with a number

series based on the values in range B12:B14.

3. 

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.

4. 

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.

5. 

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.

6. 

Center the contents of cells B11:F11.

7. 

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

8. 

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.

9. 

Format the range D12:F27 to be center-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 C12:C27

whose value is equal to cell D8. Apply the default formatting of Light Red

Fill with Dark Red Text.

11. 

Lock the cells in range D12:F27.

12. 

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

those ranges.

13. 

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

14. 

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

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. 

Italicize the text in range C7:F7.

18. 

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

19. 

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

20. 

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.

21. 

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

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:C11 CapitalPlan