steps 8-16 please

Open the flle SC_EX19_4b_FirstLastName_1.xisx, avaliable for download from the SAM website. Save the file as SC_EX19_4b_FirstlastName_2. x _. If you do not see the x isx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the flle SC_EX19_4b_FirstLastName_2.xisx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Ricardo and Miranda Ramos are considering whether to buy their first home, and have spoken to three lenders about taking out a mortgage for the house purchase. They now pay $1,500 per month in rent and can pay up to $1,600 per month for a mortgage. Miranda has created an Excel spreadsheet to compare the terms of the mortgage. She asks you to help her complete the analysis of their loan options. Go to the Mortgage Calculator worksheet. The cells in the range B5:B7 have defined names, but one is incomplete and could be confusing. Cell A2 also has a defined name, which is unnecessary for a cell that will not be used in a formula. Update the defined names in the worksheet as follows: a. Delete the Loan_Payment_Calculator defined name. b. For cell B7, edit the defined name to use Loan_Amt as the name. [Mac Hint: Delete the existing defined name "Loan_Am" and add the new defined name.] 2. In cell B7, calculate the loan amount by entering a formula without using a linction that. subtracts the Down_Payment from the Price. 3. Miranda also wants to use defined names in other calculations to help her interpret the formulas. In the range D3:D7, create defined names based on the values in the range C3:C7. 4. Miranda wants to calculate the monthly payment for a loan to purchase the Beecher Street house at the most favorable interest rate she found online. Caiculate the payment 35 follows: a. In cell D5, start to enter a formula using the PMT function. b. For the rate argument, divide the Rate (cell D3) by 12 to use the monthly interest) rate, c. For the nper argument, use the Term_In_Months (cell D.4) to specify the number) of periods, d. For the pv argument, use the Loan. Amt (cell B7) to include the present value. e. Insert a negative sign () after the equal sign in the formula to display the result as a positive amount. 5. In cell D6, enter a formula without using a function that multiples the Monthly_Payment (cell D5) by the Term_in_Months (cell D4), and then subtraids the Loan_Amt (cell B7) from the result to determine the total interest on the mortgage. 6. In cell D7, enter a formula without using.a function that adds the Price (cell B5) to the Total_Interest (cell D6) to determine the total cost of the house. 7. Miranda wants to compare monthly payments for interest rates that vary from 4.00 to 4.96 percent and for terms of 180,240 , and 360 months. She has already set up the structure for a data table in the range A11:D24. Create a two-variable data table as follows to provide the comparison that Miranda requests: a. In cell Al1, enter a formula without using a function that references the Monthly_Payment amount (cell D5) because Miranda wants to compare the monthly payments. b. Based on the range A11:D24, create a two-variable data table that uses the term in months (cell D4) as the row input cell and the rate (cell D3) as the folumn input cell, 8. In the list of interest rates (range A12:A24), create a Conditional Formatting Highlight Cells Rule to highilght the listed rate that matches the rate for the Betcher Street house (cell D3) in Light Red Fill with Dark Red Text. 9. Change the color of the left, right, and bottom borders of the range A9:D24 to Tan, Accent 4, to match the other outside borders in the worksheet. 10. Miranda and Ricardo talked to three local banis about securing a mortgage for the Beecher Street house. With the first bank, they could borrow $315,000 at 4.40 percent, annual interest and pay back the loan in 30 years. She wants to determine the monthly payment with the first bank. In cell 69 , enter a formula using the PMT function and the monthly interest rate (cell) G5), the loan perlod in montins (cell G7), and the foan amount (cen 63) to calculate the monthly payment with Banik 1. H5), the loan period in months (cell H7), and the monthly payment (cell H9) to calculate the loan amount for Bank 2 . 12. The third bank will allow Miranda and Ricardo to borrow $337,500 at an annual interest rate of 4.56 percent and a monthly payment of $1,500. Miranda wants to know the amount remaining on the loan after 20 years, or the future value of the loan, so that she and Ricardo can start to pay it off more quickly at that point, In cell I10, insert a formula using the FV function and the monthly interest rate (cell) 15), the loan period in months (cell I7), the monthly payment (cell 19), and the loan amount (cell I3) to calculate the future value of the loan with Bank 3 , 13. Miranda plans to print parts of the Mortgage Calculator workbook. Prepare for printing as follows: a. Set row 2 as the print titles for the worksheet. . b. Set the range F2:113 as the print area, 14. Hide the Listings worksheet, which contains data. Miranda wants to keep private. 15. Go to the Car Loan worksheet, which contains details about a loan Miranda and Ricardo took out for a new car. The worksheet contains two errors. Make sure Excel is set to check all types of errors, and then resolve the ones on the Car Loan worksheet as follows: a. Display the possible errors and solutions for cell B2, and then ignore the error- b. Trace the precedents to the formula in cell H7, which should multiply the optional extra payments by the scheduled number of payments. Correct the error. 16. Draw attention to the optional extra payments in the range B10:D10 by adding a thick outside border using the Tan, Accent 4 shape outline color. Your workbook should look like the Final Figures on the following pages. Save your changes, dose the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Flgure 1: Mortgage Calculator Worksheet Final Figure 2: Car Loan Worksheet Open the flle SC_EX19_4b_FirstLastName_1.xisx, avaliable for download from the SAM website. Save the file as SC_EX19_4b_FirstlastName_2. x _. If you do not see the x isx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the flle SC_EX19_4b_FirstLastName_2.xisx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Ricardo and Miranda Ramos are considering whether to buy their first home, and have spoken to three lenders about taking out a mortgage for the house purchase. They now pay $1,500 per month in rent and can pay up to $1,600 per month for a mortgage. Miranda has created an Excel spreadsheet to compare the terms of the mortgage. She asks you to help her complete the analysis of their loan options. Go to the Mortgage Calculator worksheet. The cells in the range B5:B7 have defined names, but one is incomplete and could be confusing. Cell A2 also has a defined name, which is unnecessary for a cell that will not be used in a formula. Update the defined names in the worksheet as follows: a. Delete the Loan_Payment_Calculator defined name. b. For cell B7, edit the defined name to use Loan_Amt as the name. [Mac Hint: Delete the existing defined name "Loan_Am" and add the new defined name.] 2. In cell B7, calculate the loan amount by entering a formula without using a linction that. subtracts the Down_Payment from the Price. 3. Miranda also wants to use defined names in other calculations to help her interpret the formulas. In the range D3:D7, create defined names based on the values in the range C3:C7. 4. Miranda wants to calculate the monthly payment for a loan to purchase the Beecher Street house at the most favorable interest rate she found online. Caiculate the payment 35 follows: a. In cell D5, start to enter a formula using the PMT function. b. For the rate argument, divide the Rate (cell D3) by 12 to use the monthly interest) rate, c. For the nper argument, use the Term_In_Months (cell D.4) to specify the number) of periods, d. For the pv argument, use the Loan. Amt (cell B7) to include the present value. e. Insert a negative sign () after the equal sign in the formula to display the result as a positive amount. 5. In cell D6, enter a formula without using a function that multiples the Monthly_Payment (cell D5) by the Term_in_Months (cell D4), and then subtraids the Loan_Amt (cell B7) from the result to determine the total interest on the mortgage. 6. In cell D7, enter a formula without using.a function that adds the Price (cell B5) to the Total_Interest (cell D6) to determine the total cost of the house. 7. Miranda wants to compare monthly payments for interest rates that vary from 4.00 to 4.96 percent and for terms of 180,240 , and 360 months. She has already set up the structure for a data table in the range A11:D24. Create a two-variable data table as follows to provide the comparison that Miranda requests: a. In cell Al1, enter a formula without using a function that references the Monthly_Payment amount (cell D5) because Miranda wants to compare the monthly payments. b. Based on the range A11:D24, create a two-variable data table that uses the term in months (cell D4) as the row input cell and the rate (cell D3) as the folumn input cell, 8. In the list of interest rates (range A12:A24), create a Conditional Formatting Highlight Cells Rule to highilght the listed rate that matches the rate for the Betcher Street house (cell D3) in Light Red Fill with Dark Red Text. 9. Change the color of the left, right, and bottom borders of the range A9:D24 to Tan, Accent 4, to match the other outside borders in the worksheet. 10. Miranda and Ricardo talked to three local banis about securing a mortgage for the Beecher Street house. With the first bank, they could borrow $315,000 at 4.40 percent, annual interest and pay back the loan in 30 years. She wants to determine the monthly payment with the first bank. In cell 69 , enter a formula using the PMT function and the monthly interest rate (cell) G5), the loan perlod in montins (cell G7), and the foan amount (cen 63) to calculate the monthly payment with Banik 1. H5), the loan period in months (cell H7), and the monthly payment (cell H9) to calculate the loan amount for Bank 2 . 12. The third bank will allow Miranda and Ricardo to borrow $337,500 at an annual interest rate of 4.56 percent and a monthly payment of $1,500. Miranda wants to know the amount remaining on the loan after 20 years, or the future value of the loan, so that she and Ricardo can start to pay it off more quickly at that point, In cell I10, insert a formula using the FV function and the monthly interest rate (cell) 15), the loan period in months (cell I7), the monthly payment (cell 19), and the loan amount (cell I3) to calculate the future value of the loan with Bank 3 , 13. Miranda plans to print parts of the Mortgage Calculator workbook. Prepare for printing as follows: a. Set row 2 as the print titles for the worksheet. . b. Set the range F2:113 as the print area, 14. Hide the Listings worksheet, which contains data. Miranda wants to keep private. 15. Go to the Car Loan worksheet, which contains details about a loan Miranda and Ricardo took out for a new car. The worksheet contains two errors. Make sure Excel is set to check all types of errors, and then resolve the ones on the Car Loan worksheet as follows: a. Display the possible errors and solutions for cell B2, and then ignore the error- b. Trace the precedents to the formula in cell H7, which should multiply the optional extra payments by the scheduled number of payments. Correct the error. 16. Draw attention to the optional extra payments in the range B10:D10 by adding a thick outside border using the Tan, Accent 4 shape outline color. Your workbook should look like the Final Figures on the following pages. Save your changes, dose the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Flgure 1: Mortgage Calculator Worksheet Final Figure 2: Car Loan Worksheet