.XLSM

# BIS 155 Week 2 iLab Data Analysis with Spreadsheets with Lab

BIS 155 Week 2 iLab Data Analysis with Spreadsheets with Lab

DeVry University Data Analysis - Week 2 assignment, Lab 2

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.

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.

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

A. Locate the file chap2_cap_housing.xlsx in Lab Materials in Doc Sharing. Open the file in Excel, and save it as Lab2_yourlastname.

B. 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.

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.

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