Posted: August 1st, 2022

Unit 2 Assignment: Case Study 1 Attached Files: File CIS250 Unit 2 Case Study Assignment.pdf CIS250 Unit 2 Case Study Assignment.pdf – Alternative Formats (361.746 KB) File case_study1_data.xlsx (14.264 KB) In this case study, you have been tasked with g

case_study1_data.xlsxCIS250Unit2CaseStudyAssignment
 

Unit 2 Assignment: Case Study 1

Attached Files:

 CIS250 Unit 2 Case Study Assignment CIS250 Unit 2 Case Study Assignment – Alternative Formats (361.746 KB)

  •  case_study1_data.xlsx (14.264 KB)
  • 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.Refer to the attached document for assignment details and grading rubric. 

    2

    >Approval Form

    s

    Payment

    2

    1st Street

    ,000

    0

    Credit Score

    Philadelphia, PA 19104

    7

    500

    1

    Philadelphia, PA 19111

    60

    500 $ 10,000 120

    et

    West Chester, PA 19380 $ 25,000

    20

    West Chester, PA 19380

    $ 5,000 60

    West Chester, PA 19380

    120

    Application For Personal Loan
    First Name Last Name Address City, State Zip Annual Income Credit Score Requested Amount # of

    Payment Decision Rate Periodic Rate Look Up Table
    Mary Bell 1 3 Philadelphia, PA 1

    9 10 4 $

    8 5 7 20 $ 25,000 12 Current Rate
    Joan Hill 321 Main Street $ 45,

    500 5

    6 $ 10,000 3

    60 6.50%
    Luke Skywaker 435 2nd Street Philadelphia, PA 19

    11 $ 120,000 754 $ 35,000 240 550 6.25%
    Tom Harold 987 2nd Street $ 80,000 625 $ 5,000 600 6.00%
    Jill Matters 876 6th Street West Chester, PA 19380 $ 60,000 650 5.25%
    Steven Mark 654 6th Street 789 $ 2,000 700 4.50%
    Henry Fields 897 6rh Steet $ 45,000 820 750 3.75%
    Joey Ruth 342 7th Street $ 95,000 599 $ 20,000 800 3.25%

    Existing Accounts

    First Name Last Name

    Rate

    1 Mark

    $ 25,000

    2

    3

    4 Steven

    5

    6

    7

    8

    3.85%

    9

    %

    10

    11 Harold

    12

    14

    ID Original Amount Maturity Date Days Remaining
    Smith 3.50% 3/24/23
    Jennifer Jones $ 32,000 3.32% 4/30/30
    Robert White $ 36,000 4.15% 9/11/22
    Johnson $ 44,000 4.21% 7/19/24
    Amy Decker $ 2,500 3.85% 6/15/22
    Veronica Sanders $ 3,600 5.50% 11/20/28
    Larkin Baker $ 27,000 6.12% 9/7/26
    Bethany Chapman $ 30,000 1/7/24
    Alex Wells $ 55,000 3.

    14 10/24/29
    Frank Parker $ 62,000 4.65% 4/23/25
    Hall $ 34,000 4.00% 3/11/25
    Louis McDonald $ 5,500 4.99% 7/11/28
    13 Daniel Cole $ 7,500 5.35% 12/22/23
    Wendy Connors $ 9,000 3.80% 5/6/28
    Max:
    Min:
    Ave:

    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

    Expert paper writers are just a few clicks away

    Place an order in 3 easy steps. Takes less than 5 mins.

    Calculate the price of your order

    You will get a personal manager and a discount.
    We'll send you the first draft for approval by at
    Total price:
    $0.00