Provide clear steps to complete the process in the excel sheet provided.

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. A 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

You have a list of loan applicants Assignment$5$ DATAFall$24$ darr and should decide about the loan amount they can receive. There are five rules to apply.

Rule $1$: the applicant can borrow at a $5\mathrm{.}0\%$ rate if the credit score is above $620,$ has no down payment, job category is $1$ or better, there is steady

employment at least during the past year, and annual income is at least $$20,000,$ and debt to income ratio is less than $10\%.$

Rule $2$: the applicant can borrow at a $4\mathrm{.}5\%$ rate if the credit score is above $680,$ ready for a down payment of at least $8\%,$ job category is $2$ or better,

there is 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$: the applicant can borrow at $4\%$ rate if the credit score is above $730,$ ready for a down payment of at least $15\%,$ job category is $2$ or better, there

is steady employment for at least the past $3$ years, annual income is at least $$50,000,$ and debt to income ratio is less than $30\%$

Rule $4$: the applicant can borrow at $3\mathrm{.}5\%$ rate if the credit score is above $780,$ ready for a down payment of at least $15\%,$ job category is $3$ or better,

there is 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$: the applicant can borrow at a $3\%$ rate if has a credit score of more than $800,$ the job category is $4,$ and either has a steady employment history

of at least $5$ years or an annual income of at least $$90,000.$ There is no need to check for applicant down payment and debt ratio in this category.

Use logical operators to find applicant qualifications for each rule.

All numbers in your formulas should reference cells in this table. There should be no numbers in formulas.

Use the nested IF 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 for customer information if not qualified.