20 Mar In this case study, you have been tasked with generating a loan approval workbook that will determine if an applicant is eligible
In this case study, you have been tasked with generating a loan approval workbook that will determine if an applicant is eligible for a personal loan and if so, what the loan rate should be based on a set of pre-defined criteria. The bank manager has created the basic layout of the application she wants to use but has asked you to provide the formulas that will drive the initial quote
CIS250 – Advanced Excel
Case Study 1
Copyright 2022 Post University, ALL RIGHTS RESERVED
Due Date: 11:59 pm EST Sunday of Unit 2
Points: 100
Overview:
In this case study, you have been tasked with generating a loan approval workbook that
will determine if an applicant is eligible for a personal loan and if so, what the loan rate
should be based on a set of pre-defined criteria. The bank manager has created the
basic layout of the application she wants to use but has asked you to provide the
formulas that will drive the initial quote.
Instructions:
Determine Rate: You need to provide a rate for each customer based on their credit score. Any user who
has a score less than 600 should be denied.
1. Open case_study1_data and save as case_study1_LastFirst (use your own
Last and First name).
IF Function – Use the IF Function to figure out if an applicant is eligible for a personal loan. Please review this tutorial: Excel IF Functions For Beginners for help.
2. Display the Approval Form worksheet and create an IF statement in cell I3 to
determine if the applicant is eligible for a personal loan. Use the criteria below as
part of your logic statement:
Credit Score < 600 = “Denied”
Credit Score >= 600 = “Approved
3. Copy using the Fill Handle the IF statement in cell I3 to into the Range I4:I10.
VLOOKUP Function – Use the VLOOKUP Function to determine the rate of for those
customers that were approved based on their credit scores. Please review this tutorial:
VLOOKUP Functions for Beginners for help.
4. Create a VLOOKUP function in cell J3 that uses the Credit Score in cell F3 to
generate a rate based on the array in cells N4:O10. NOTE – Make sure to use
the correct cell referencing.
5. Copy using the Fill Handle the VLOOKUP statement in cell J3 to into the Range
J4:J10.
6. Delete out any Rate value for those customers that were denied.
Copyright 2022 Post University, ALL RIGHTS RESERVED
Calculate Payment:
PMT Function – Use the PMT Function to calculate the payment. Remember that the
Yearly Periodic Rate is the Rate divided by the number of Months. Please review this
tutorial: Excel PMT() Function Basics for help.
You will calculate the payment quote for each customer.
7. Calculate the periodic rate of the loan in cell K3.
8. Copy using the Fill Handle the Periodic Rate in cell K3 to into the Range
K4:K10.
9. Delete out any Rate value for those customers that were denied.
10. Use the PMT Function in cell L3 to calculate the payment. Make sure to return a
positive value.
11. Copy using the Fill Handle the Payment in cell L3 to into the Range L4:L10.
12. Delete out any Rate value for those customers that were denied.
Determine Maturity Dates: You will create a working list of all existing accounts that provides the number of days remaining on their loan and an overall look at all the accounts maturity dates.
13. Display the Existing Accounts worksheet and calculate the number of days
remaining for each existing account based on today’s date. You will need to use
the Today() Function in your calculations.
14. Calculate the maximum number of Days Remaining for all existing accounts in
cell C17.
15. Calculate the minimum number of Days Remaining for all existing accounts in
cell C18.
16. Calculate the average number of Days Remaining for all existing accounts in cell
C19.
17. Save and close the file.
Requirements:
Submit the completed case_study1_LastFirst Excel file.
Be sure to read the criteria below by which your work will be evaluated before you write and again after you write.
Copyright 2022 Post University, ALL RIGHTS RESERVED
Evaluation Rubric for Case Study 1 Assignment
CRITERIA POINTS
Save as case_study1_ LastFirstName 3
Add logical statement the evaluates if the credit score is over 600 in cell I3. If so, return Approved, else return Denied.
15
Filled range I4:I10 and deleted out customers Denied. 5
Use a VLOOKUP in cell J3 to determine the rate based on cell F3. 10
Filled range J4:J10 and deleted out customers Denied. 5
Calculate the Periodic Rate in cell K3. 10
Filled range K4:K10 and deleted out customers Denied. 5
Calculate the Loan Payment in cell L3. 10
Filled range L4:L10 and deleted out customers Denied. 5
Calculate the number of days remaining on each existing loan. 8
Calculate the maximum number of days remaining on Existing Accounts.
8
Calculate the minimum number of days remaining on Existing Accounts.
8
Calculate the average number of days remaining on Existing Accounts.
8
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 WriteEdu. 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 help with this question?
Get assignment help from WriteEdu.com Paper Writing Website and forget about your problems.
WriteEdu provides custom & cheap essay writing 100% original, plagiarism free essays, assignments & dissertations.
With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Chat with us today! We are always waiting to answer all your questions.