You are a car dealership loan officer. In order to determine the loan interest rate, you always check for customer qualifications by looking at their financial stability and risks. Combination of the following criteria determines the interest rate of each customer. The lower the risk, the lower their interest rate. The following are major criteria:

• Down payment: Customer out-of-pocket payment as a percentage of price
• Credit score: Overall evaluation of financial status
• Employment history: Years of steady employment before applying
• Annual income
• Job category: (1, 2, 3, 4), 1 being the highest and 4 the lowest risk of unemployment
• Debt-to-income ratio: the lower the better

There are four rules to apply.

Rule 1: the applicant can borrow at 5.5% rate if credit score is above 620, has no down payment, job category is 1 or better, there is a steady employment at least during the past year, annual income is at least $15,000, and debt to income ratio is less than 10%.

Rule 2: applicant can borrow at 4.5% rate if credit score is above 680, ready for a down payment of at least 8%, job category is 2 or better, there is a steady employment at least for the past two years, annual income is at least $30,000, and debt to income ratio is less than 20%

Rule 3: applicant can borrow at 4% rate if credit score is above 730, ready for a down payment of at least 15%, job category is 2 or better, there is a steady employment for at least the past 3 years, annual income is at least $60,000, and debt to income ratio is less than 30%

Rule 4: applicant can borrow at 3.5% rate if credit score is above 780, ready for a down payment of at least 15%, job category is 3 or better, there is a steady employment at least for the past 5 years, annual income is at least $70,000, and debt to income ratio is less than 20%

Rule 5: applicant can borrow at 3% rate if has a credit score more than 800, job category is 4, and either has a steady employment history of at least 5 years or annual income of at least $80,000. There is no need to check for applicant down payment and debt ratio in this category.

• All numbers in your formulas should reference cells in this table. There should be no numbers in formulas.
• Use logical operators to find applicant qualifications for each rule.
• Use a function to show the best interest rate approved in column M. In case an applicant does not qualify for any of these rules the formula should return Not qualified.
• Apply conditional formatting to change the fill color of the entire row for customer information if "Not qualified".

Assignment 5 Rubric

Item	Points
Rule 1 formula is correct, all numbers are referenced	1
Rule 2 formula is correct, all numbers are referenced	1
Rule 3 formula is correct, all numbers are referenced	2
Rule 4 formula is correct, all numbers are referenced	2
Rule 5 formula is correct, all numbers are referenced	2
Max Loan amount calculated; all numbers are referenced. Rows of customer information who were not qualified are highlighted and display the text "Not qualified".	2
Total	10

Please tell me step how to do it and upload the result for this assignment

F G 1