Shelly Cashman Excel 365 | Module 4: SAM Critical Thinking Project C Mendoza Group

GETTING STARTED

1. Save the file SC_EX365_CT4C_FirstLastName_1.xlsx as SC_EX365_CT4C_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_EX365_CT4C_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Latonya Hines is the business manager for the Mendoza Group, a commercial real estate developer in Minneapolis, Minnesota. Latonya is identifying buildings downtown that could be renovated as mixed-use properties for offices, retail stores, and housing. She has asked for your help in creating a loan analysis that summarizes information about the loans to cover the cost of the buildings. Go to the Loan Analysis worksheet. Several cells in the Loan Payments section have defined names, but one cell needs a defined name so it can be used in formulas. Add Loan_Amount as the defined name 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 defined names in your formula.

3. Latonya also wants you to use defined names in other calculations to help her interpret the formulas. In the Loan Payments section, create defined names in column E based on the values in column D.

4. Latonya asks you to calculate the monthly payment for a loan to purchase the Riverfront Drive property. Calculate the Monthly Payment as follows:

a. Divide the Rate by 12 to use the monthly interest rate.

b. Use Term_in_Months to specify the number of periods.

c. Use Loan_Amount to include the present value.

d. Insert a negative sign to display the result as a positive amount.

5. Determine the Total Interest using a formula that multiplies 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. Latonya wants you to compare monthly payments for interest rates that vary from 4.75% to 5.95% and for terms of 180, 240, and 360 months. She has already set up the structure for a data table with the heading Varying Interest Rates and Terms. Provide the comparison that Latonya requests as follows:

a. In the blank cell above the percentage list, enter a formula that references the Monthly Payment from the Loan Payments section because Latonya wants to compare the monthly payments.

b. 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 Riverfront Drive property in green fill with dark green text.

9. For the Varying Interest Rates and Terms range, change the color of the left, right, and bottom borders to match the other thin outside borders in the worksheet.

10. After the Mendoza Group purchases the Riverfront Drive building, it needs to renovate the property. Latonya has three options for funding the renovation. In the first scenario, she could borrow additional funds for a basic renovation and pay for the full amount of the loan over five years. She wants you to determine the monthly payment for the first scenario. Use the monthly interest rate, the loan period in months, and the loan amount to calculate the monthly payment for the first renovation scenario.

11. In the second scenario, Latonya could borrow money for a more extensive renovation and pay back the loan in 10 years instead of 5. She could also reduce her monthly payments to $15,500 with an annual interest rate of 5.15%. She wants you to determine a calculation that can provide the loan amount if she requests those conditions. Use the monthly interest rate, the loan period in months, and the monthly payment to calculate the loan amount for the 10-year scenario.

12. In the third scenario, Latonya could fund a major renovation by paying back the loan over 12 years with a monthly payment of $15,000 and then renegotiating better terms. She wants you to determine the amount remaining on the loan after 12 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 12-year scenario.

13. Latonya plans to print parts of the Loan Analysis workbook. She asks you to prepare the workbook for printing as follows:

a. Set the entire row that contains the section headings as the print titles for the worksheet.

b. Set the area that contains the headings and data as the print area.

14. Hide the Property Details worksheet, which contains data Latonya wants you to keep private.

15. Go to the Business Expansion worksheet, which contains details about a loan the company is considering for business expansion. Make sure Excel is set to check all types of errors, and then correct the error to the Total paid formula, which should multiply the scheduled payment amount by the number of scheduled payments.

16. Add a thick outside border using the Blue, Accent 1 shape outline color (5th column, 1st row of the Theme Colors palette) to draw attention to the optional extra payments 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 website to submit your completed project.

Final Figure 1: Loan Analysis Worksheet

Final Figure 2: Business Expansion Worksheet