devry BIS155 Lab 2 of 7: First National Bank New Loan Report

You are an intern at First National Bank working in the loan department, and your boss has asked you to prepare the monthly "New Loan Report" for the Board of Directors. This analysis report will clearly list and summarize all new loans for residential housing in the past month. The summary area includes the loan statistics as labeled data in the data file. The format of the report is appropriate for the Board of Directors for the First National Bank.

Deliverables

Submit one workbook title Lab2_YourName.xlsm to the Dropbox. (Note that files containing macros have the extension, .xlsm rather than .xlsx.) When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity.

iLAB STEPS

STEP 1: Open, Save, and Document the Workbook (3 points)

1. Locate the file chap2_cap_housing.xlsx in Lab Materials in Doc Sharing. Open the file in Excel, and save it as Lab2_yourlastname.
2. Add a documentation sheet that provides a spreadsheet title, author, date, and purpose. Format the documentation sheet. The Bank's logo is dark blue, so use a color scheme that reflects that logo. Select complementary Font and Fill colors for the documentation sheet. Ensure that the sheet tab is named Documentation and that the documentation sheet is the first sheet in the workbook.
   

   (To get help on creating a documentation sheet, refer to Lab 1, Step 1.)

   How to use the lab videos

   Please do not rely solely on the videos to complete this week's lab. The videos may not exactly match the official lab instructions and may not show all the steps required. For full credit, follow the written instructions on the main iLab page and the linked Step-By-Step guides. Check your work against the screen shots in the written instructions and the Lab and Project Grading Criteria provided in Doc Sharing. The videos can still help you by showing operations that are similar to, but not exactly the same as, those needed for the lab. Use the videos as a rough visual guide, but not as your only source of directions. If you have any questions, please post in the Q & A Forum or contact your instructor.

STEP 2: Create Calculations (10 points)

Functions are used to calculate the interest rate, down payment, monthly payment, and average selling price for each residential home in the worksheet. You need to create a formula to determine the down payment. Finish the calculations by using the appropriate functions to complete the Loan Statistics summary area of the worksheet.

1. Use a VLOOKUP function to determine the interest rates in column D.
   
   

   Lab 2, Step 2A Video This video walks through the current step(s) in this week's Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides. ** Please be patient when loading these videos as they can be large. Download the Lab 2, Step 2A Instructions.



2. Calculate the down payment by multiplying the results of a VLOOKUP function by the selling price. Enter the formula in column E.
   
   

   Lab 2, Step 2B Video This video walks through the current step(s) in this week's Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides. ** Please be patient when loading these videos as they can be large. Download the Lab 2, Step 2B Instructions.



3. Calculate the amount financed by subtracting the down payment from the selling price. Enter the formula in column F.
   
   

   Lab 2, Step 2C Video This video walks through the current step(s) in this week's Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides. ** Please be patient when loading these videos as they can be large. No detailed written instructions are necessary for the Lab 2, Step 2C. The only written instruction needed is already listed after the outline letter C, above the Step 2C box.



4. Use a PMT function to determine the monthly payments in column G.
   
   

   Lab 2, Step 2D Video This video walks through the current step(s) in this week's Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides. ** Please be patient when loading these videos as they can be large. Download the Lab 2, Step 2D Instructions.



5. Use appropriate formulas and functions to calculate over loan statistics for the month:
   
   

   Lab 2, Step 2E Video This video walks through the current step(s) in this week's Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides. ** Please be patient when loading these videos as they can be large. Download the Lab 2, Step 2E Instructions.

Number of Loans, Highest Amount Financed, Lowest Amount Financed, Total Amount Financed.

STEP 3: Format the Worksheet (9 points)

Now that you have finished the calculations, you must format the worksheet in a professional manner suitable for delivering to the Board of Directors. Your final worksheet will look something like the worksheet below:

1. Format all money figures as currency with two decimals and all percentages as percents.
2. Adjust columns sizes to fit the data.
3. Merge and Center titles and size appropriately.
4. Change Font color to dark blue.
5. Add a small graphic appropriate for the purpose of the worksheet.
6. Separate sections of the worksheet and provide borders as appropriate.