BIS 155 iLab 2: First National Bank
BIS 155 iLab 2: First National Bank Recent
Scenario/Summary
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 should be appropriate for the Board of Directors for the First National Bank.
Be sure to watch the tutorials at the end of the Lesson page.
Deliverables
Step 1: Open, Save, and Document the Workbook
A. Download the Week2_iLab_FirstNatlBank_Students.xlsx Workbook. Open the file in Excel, and save it as the file name as describe above (i.e., Doe_J_Week2 iLab.xlxsm). [Note: You may get a popup message that asks you to “Enable Saving”. Simply click “Yes” to Enable and you will be able to save the file]
B. It is recommended that as you progress through this assignment, that you work on what you have learned so far, in terms of readability. Be sure to keep your worksheet professional with easy to read column headings, shading, spacing, boxes, logos, and so on. Remember to label your worksheets with appropriate labels (i.e., not sheet1, sheet2, etc.).
C. 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
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. There is a tutorial video on creating and using VLookup tables at the end of the Lesson page.
B. Calculate the down payment by multiplying the results of a VLOOKUP function by the selling price. Enter the formula in column E.
C. Calculate the amount to be financed by subtracting the down payment from the selling price. Enter the formula in column F.
D. Use a PMT function to determine the monthly payments in column G. You should assume that the payment is being made at the beginning of the month. Remember that the Interest Rate being shown is an annual percentage rate (APR), yet the payments are being made monthly. Be sure to make your adjustment to the interest rate for this. In addition, the term of the loan is being shown in years. Be sure to adjust the years to months in your PMT function argument.
E. Use appropriate formulas and functions to calculate our loan statistics for the month.
• Number of Loans
• Lowest Amount Financed
• Total Amount Financed
• Highest Amount Financed
Step 3: Format the Worksheet
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.
A. Format all money figures as currency with two decimals and all percentages as percents.
B. Adjust columns sizes to fit the data.
C. Merge and center titles and size appropriately.
D. Change font color to dark blue.
E. Add a small graphic appropriate for the purpose of the worksheet.
F. Separate sections of the worksheet and provide borders as appropriate.
Step 4: Create an Excel Application With Macro and Macro Button
A. Add a new worksheet to the workbook, and name it Loan calculator.
B. Create a user input section that asks users to input the following:
1. What is the price of the home?
2. Do you wish to put any additional money down?
3. Pay at the beginning of the month?
C. Create an Output section that uses the user-provided data and calculates the mortgage payments for a 15-year loan, a 20-year loan, and a 30-year loan.
D. Add user input validation to ensure that users input at least $50,000 for the cost of the home and answer Yes/No to the payment question.
E. Unlock the cells in which users will input data so that you may protect the sheet to keep users from changing other areas.
F. Create a Macro.
G. Use Insert Shapes to add a Macro button that will allow users to clear values and start over. Name the button Enter New Values.
H. Protect the worksheet, but do NOT require a password.
I. Format the worksheet so that it is easy to understand and use. The following is a sample of what your final worksheet might look like:
Save your file as an xlsm file and submit to the Dropbox. Make sure you post a comment about what you learned when submitting the file.