Shelly Cashman Excel 2013Chapter 4: SAM Project 1aFlex Cab CompanyFINANCIAL FORMULAS AND FORMATTING WORKSHEETSPROJECT DESCRIPTIONWalter Xiang works in the Operations department of Flex Cab Company, a...

Shelly Cashman Excel 2013Chapter 4: SAM Project 1aFlex Cab CompanyFINANCIAL FORMULAS AND FORMATTING WORKSHEETSPROJECT DESCRIPTIONWalter Xiang works in the Operations department of Flex Cab Company, a taxi service inToronto, Ontario. Flex Cab Company needs to replace its aging fleet of taxis in the nearfuture. Walter has asked you to expand on a workbook he has started to explore fundingoptions for the proposed new taxis. You will need to develop scenarios to examine loanoptions and savings scenarios. Additionally, you will need to format and protect theworksheets to make them easier to read and use.GETTING STARTEDDownload the following file from the SAM website:oSC_Excel2013_C4_P1a_FirstLastName_1.xlsxOpen the file you just downloaded and save it with the name:ooSC_Excel2013_C4_P1a_FirstLastName_2.xlsxHint: If you do not see the .xlsx file extension in the Save file dialog box, donot type it. Excel will add the file extension for you automatically.With the file SC_Excel2013_C4_P1a_FirstLastName_2.xlsx still open, ensure thatyour first and last name is displayed in cell B6 of the Documentation sheet. If cell B6does not display your name, delete the file and download a new copy from the SAMwebsite.PROJECT STEPS1.Unhide the Loan Scenarios worksheet.2.In the Loan Scenarios worksheet, fill the range B15:B27 with a number seriesbased on the values in range B12:B14.3.Enter a formula in cell D12 using the PMT function to calculate the monthlypayment 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 monthlypayment as a positive number. Use absolute cell references for the term(nper) and loan amount (pv) arguments. The interest rate argument should bea 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 monthlypayment 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 monthlypayment as a positive number. Use absolute cell references for the term(nper) and loan amount (pv) arguments. The interest rate argument should bea relative reference.) Copy the formula from cell E12 to the range E13:E27.Shelly Cashman Excel 2013| Chapter 4: SAM Project 1a5.Enter a formula in cell F12 using the PMT function to calculate the monthlypayment 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 monthlypayment as a positive number. Use absolute cell references for the term(nper) and loan amount (pv) arguments. The interest rate argument should bea 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 numberformat of this range to display 0 decimal places.10.Create a conditional formatting rule to Highlight Cells in range C12:C27whose value is equal to cell D8. Apply the default formatting of Light RedFill 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 thoseranges.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 D7using the FV function to calculate the accrued savings realized from theparameters displayed in the range D4:D6. (Hint: Enter a negative sign in frontof 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 accruedsavings 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 savingsas a positive number.)16.Enter a formula in cell F7 using the FV function to calculate the accruedsavings 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 savingsas a positive number.)17.Italicize the text in range C7:F7.18.Create names for the following cells as described in Table 1 on the followingpage.CellDefined NameD7AggressiveSavings© 2014 Cengage Learning.Table 1: Defined Names for Range D7:F7Shelly Cashman Excel 2013| Chapter 4: SAM Project 1aE7ModerateSavingsF7ConservativeSavings19.Apply the style 40% – Accent 5 to the range B10:F10.20.Apply conditional formatting to Highlight Cells in range D13:F27 with avalue between $250,000 and $275,000. Apply the default formatting ofLight Red Fill with Dark Red Text.21.Navigate to the Capital Plan worksheet. Using the custom cell names, enter aformula in cell C11 that adds the value in the Savings cell to the value in theLoan 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 theleft of each cell as the cell name. (Hint: Use the Create Names From Selectioncommand.)23.Name the range B2:C11 CapitalPlanYour 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 yourcompleted project.Shelly Cashman Excel 2013| Chapter 4: SAM Project 1aFinal Figure 1: Capital Plan WorksheetMicrosoft product screenshot reprinted with permission fromMicrosoft Incorporated. Copyright © 2014 Cengage Learning. AllRights Reserved.Shelly Cashman Excel 2013| Chapter 4: SAM Project 1aFinal Figure 2: Loan Scenarios WorksheetCopyright © 2014 Cengage Learning. All RightsReserved.Shelly Cashman Excel 2013| Chapter 4: SAM Project 1aFinal Figure 3: Savings Scenarios WorksheetCopyright © 2014 Cengage Learning. All RightsReserved.