Assignment
Lab Steps
Preparation
Be sure you have reviewed the Tutorials located on the Lesson page before you begin the iLab.
Please do not rely solely on the videos to complete this week's iLab.The videos provide detailed examples, using the Week2_BIS155_Tutorial_Loan_Calculations_Student.xlsx workbook. Applying the examples to the loan assignment will give you both practice and instruction what to complete.
For example, your final assignment file should include three worksheets (Documentation, Loan Data, and Loan Calculator), each with a corresponding example, as shown in the video tutorials.
Step 1: Open, Save, and Document the Workbook
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]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.).
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.
1. 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.
2. Calculate the down payment by multiplying the results of a VLOOKUP function by the selling price. Enter the formula in column E.
3. Calculate the amount to be financed by subtracting the down payment from the selling price. Enter the formula in column F.
4. 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.
5. 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 one attached.
A-Format all money figures as currency with two decimals and all percentages as percents.
1. B-Adjust columns sizes to fit the data.
2. C-Merge and center titles and size appropriately.
3. D-Change font color to dark blue.
4. E-Add a small graphic appropriate for the purpose of the worksheet.
5. F-Separate sections of the worksheet and provide borders as appropriate.
Step 4: Create an Excel Application With Macro and Macro Button
1. Add a new worksheet to the workbook, and name it Loan calculator.
2. 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?
3. 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.
4. 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.
5. Unlock the cells in which users will input data so that you may protect the sheet to keep users from changing other areas.
6. Create a Macro.
7. Use Insert Shapes to add a Macro button that will allow users to clear values and start over. Name the button Enter New Values.
8. Protect the worksheet, but do NOT require a password.
9. Format the worksheet so that it is easy to understand and use.
Attachment:- Week2_iLab_FirstNatlBank_Students.xlsx