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