Shelly Cashman Excel 2019 | Module 4: SAM Critical Thinking Project 1c Smith & Lyngate Insurance CREATE A LOAN ANALYSIS   GETTING STARTED Open the file SC_EX19_CT4c_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

Shelly Cashman Excel 2019 | Module 4: SAM Critical Thinking Project 1c

Smith & Lyngate Insurance

CREATE A LOAN ANALYSIS

 

• GETTING STARTED

• Open the file SC_EX19_CT4c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_CT4c_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file SC_EX19_CT4c_FirstLastName_2.xlsx 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. Liam Richardson is the business manager for the Smith & Lyngate Insurance agencies in the state of Maryland. Liam is interested in increasing the number of agents in Baltimore and plans to buy an office building for the new operation. He has asked for your help in creating a loan analysis that summarizes information about the loans to cover the cost of the building.
   
   Go to the Loan Calculator worksheet. Several cells in the Loan Payment Calculator section have defined names. However, one defined name is incomplete and could be confusing; another is unnecessary for a cell that will not be used in a formula.
   
   Update the defined names in the worksheet as follows:
   1. Delete the Loan_Calculator defined name.
   2. Change the defined name to Loan_Amount for the cell that will contain the loan amount.
2. Calculate the Loan Amount by entering a formula that subtracts the down payment from the price. Make sure to include the defined names in your formula.
3. Liam also wants to use defined names in other calculations to help him interpret the formulas.
   
   In the Loan Payment Calculator section, create defined names in column D based on the values in column C.
4. Liam needs to calculate the monthly payment for a loan to purchase the Charles Street property. Calculate the monthly payment as follows:
   1. Divide the Rate by 12 to use the monthly interest rate.
   2. Use the Term_in_Months to specify the number of periods.
   3. Use the Loan_Amount to include the present value.
   4. Insert a negative sign to display the result as a positive amount.
5. Determine the Total Interest using a formula that multiples the monthly payment by the term in months and then subtracts the loan amount from the result.
6. Determine the Total Cost using a formula that adds the price to the total interest.
7. Liam wants to compare monthly payments for interest rates that vary from 3.95% to 5.15% and for terms of 120, 180, and 240 months. He has already set up the structure for a data table with the heading Varying Interest Rates and Terms.
   
   Provide the comparison that Liam requests as follows:
   1. In the blank cell above the percentage list, enter a formula that references the Monthly Payment from the Loan Payment Calculator section because Liam wants to compare the monthly payments.
   2. Create a two-variable data table that uses the term in months as the row input cell and the rate as the column input cell.
8. In the list of interest rates, create a conditional formatting rule to highlight the rate that matches the rate for the Charles Street property in light red fill with dark red text.
9. For the Varying Interest Rates and Terms range, change the color of the left, right, and bottom borders to match the other outside borders in the worksheet.
10. Liam has three other options for purchasing the Charles Street property. In the first scenario, he could borrow additional money to fund a renovation of the building. He wants to determine the monthly payment for the renovation.
    
    Use the monthly interest rate, the loan period in months, and the loan amount to calculate the monthly payment for the renovation scenario.
11. In the second scenario, Liam could pay back the loan in 20 years instead of 15 and reduce his monthly payments to $6,000 with an annual interest rate of 4.5%. He wants to know the loan amount he should request with those conditions.
    
    Use the monthly interest rate, the loan period in months, and the monthly payment to calculate the loan amount for the 20-year scenario.
12. In the third scenario, Liam could pay back the loan for eight years with a monthly payment of $8,000 and then renegotiate better terms. He wants to know the amount remaining on the loan after eight years, or the future value of the loan.
    
    Use the monthly interest rate, the loan period in months, the monthly payment, and the loan amount to calculate the future value of the loan for the eight-year scenario.
13. Liam plans to print the Scenarios section of the Loan Calculator workbook. Prepare for printing as follows:
    1. Set the entire row that contains the headings as the print titles for the worksheet.
    2. Set the area that contains the headings and data as the print area.
14. Hide the Properties worksheet, which contains data Liam wants to keep private.
15. Go to the Equipment Loan worksheet, which contains details about a loan the company used for computer network equipment. Make sure Excel is set to check all types of errors, and then resolve the two errors on the Equipment Loan worksheet as follows
    1. Convert the year to a number.
    2. Correct the error to the Total paid formula, which should multiply the scheduled payment amount by the scheduled number of payments.
16. Add a thick outside border using the Orange, Accent 3 shape outline color to draw attention to the range containing the optional extra payments label and the amount.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.