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.
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
Do you need an answer to this or any other questions?
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will
direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.
