Scenario\/Summary\tYou 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.\tBe sure to watch the tutorials at the end of the Lesson page.\tDeliverables\t\u00a0Step 1: Open, Save, and Document the Workbook\tA. \ufffd\u00a0\u00a0 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:\u00a0 You may get a popup message that asks you to “Enable Saving”.\u00a0 Simply click “Yes” to Enable and you will be able to save the file]\tB. \ufffd\u00a0\u00a0 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.).\tC. \ufffd\u00a0\u00a0 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.\tStep 2: Create Calculations\tFunctions 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.\tA. \ufffd\u00a0\u00a0 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.\tB. \ufffd\u00a0\u00a0 Calculate the down payment by multiplying the results of a VLOOKUP function by the selling price. Enter the formula in column E.\tC. \ufffd\u00a0\u00a0 Calculate the amount to be financed by subtracting the down payment from the selling price. Enter the formula in column F.\tD. \ufffd\u00a0\u00a0 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.\tE. \ufffd\u00a0\u00a0\u00a0 Use appropriate formulas and functions to calculate our loan statistics for the month.\t“\ufffd \ufffd \ufffd Number of Loans\t“\ufffd \ufffd \ufffd Lowest Amount Financed\t“\ufffd \ufffd \ufffd Total Amount Financed\t“\ufffd \ufffd \ufffd Highest Amount Financed\tStep 3: Format the Worksheet\tNow 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.\tA. \ufffd\u00a0\u00a0 Format all money figures as currency with two decimals and all percentages as percents.\tB. \ufffd\u00a0\u00a0 Adjust columns sizes to fit the data.\tC. \ufffd\u00a0\u00a0 Merge and center titles and size appropriately.\tD. \ufffd\u00a0\u00a0 Change font color to dark blue.\tE. \ufffd\u00a0\u00a0\u00a0 Add a small graphic appropriate for the purpose of the worksheet.\tF. \ufffd\u00a0\u00a0\u00a0 Separate sections of the worksheet and provide borders as appropriate.\tStep 4: Create an Excel Application With Macro and Macro Button\tA. \ufffd\u00a0\u00a0 Add a new worksheet to the workbook, and name it Loan calculator.\tB. \ufffd\u00a0\u00a0 Create a user input section that asks users to input the following:\t1. \ufffd\u00a0\u00a0\u00a0 What is the price of the home?\t2. \ufffd\u00a0\u00a0\u00a0 Do you wish to put any additional money down?\t3. \ufffd\u00a0\u00a0\u00a0 Pay at the beginning of the month?\tC. \ufffd\u00a0\u00a0 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.\tD. \ufffd\u00a0\u00a0 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.\tE. \ufffd\u00a0\u00a0\u00a0 Unlock the cells in which users will input data so that you may protect the sheet to keep users from changing other areas.\tF. \ufffd\u00a0\u00a0\u00a0 Create a Macro.\tG. \ufffd\u00a0\u00a0 Use Insert Shapes to add a Macro button that will allow users to clear values and start over. Name the button Enter New Values.\tH. \ufffd\u00a0\u00a0 Protect the worksheet, but do NOT require a password.\tI. \ufffd\u00a0\u00a0\u00a0\u00a0 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:\u00a0\u00a0\tSave 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 Read More …<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-202948","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/202948","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=202948"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/202948\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=202948"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=202948"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=202948"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}