Question

1.

Customer	Debt to Income Ratio (DTI)	Annual Income	Credit Score	Extend Credit?
Alice A. Hall	0.49	$50,325.00	4.1
Bruce A. Farrell	0.18	$58,113.00	1.4
Kathleen U. Lucas	0.22	$63,241.00	1.7
Amy O. Norman	0.48	$14,347.00	1.1
Ronnie T. Atkins	0.33	$11,698.00	3.6
Martha O. Monroe	0.03	$28,112.00	3
Lynn O. Robertson	0.51	$108,420.00	3.3
Jose Y. Sykes	0.32	$88,224.00	1.1
Robert E. Reid	0.05	$39,103.00	1.4
Pauline H. Chandler	0.31	$34,964.00	3.1
Stephen I. Finch	0.43	$40,079.00	2.5
Peggy O. Hobbs	0.11	$99,100.00	4.8
Donna D. Adkins	0.29	$97,847.00	1.4
Doris I. Kinney	0.29	$40,437.00	2.3
Ben H. Whitaker	0.39	$129,588.00	2.6
Kristin L. Alexander	0.31	$69,515.00	4.8
Ryan O. Conner	0.47	$42,391.00	3.6
Tracey A. Waters	0.05	$52,559.00	4.9
Mark E. Becker	0.01	$33,307.00	2.5
Louis O. Rollins	0.38	$18,664.00	1

Criteria
DTI	0.3
Annual Income	$        37,000
Credit Score	4

2. Create a formula that will return "Extend Credit" or "No Credit" in column F. In order to be receive credit, the customers must have DTI that is lower than .30 (30%) and annual income of at least $37,000 or have a credit score higher than 4.0

Sales	Sales	%
0 >= Your Sales < 5000	$          -	0.00%
5000 >= Your Sales < 10000	$   5,000	2.00%
Your Sales > = 10000	$ 10,000	4.00%

Sales	Bonus $
$6,000.00

Enter an IF statement in G4 that will return the appropriate bonus for the sales recorded in F4. The actual bonus rate depend on the amount sold. The table B2:D5 show the bonus rate for different sales levels. This assignment will require a statement with multiple if's.

Please mention the excel formula used here.

Answer 1

1.

Create a formula that will return "Extend Credit" or "No Credit" in column F. In order to be receive credit, the customers must have DTI that is lower than .30 (30%) and annual income of at least $37,000 or have a credit score higher than 4.0

The cells in Column F will have the entry of "Extend Credit" if DTI (values in Column C ) < 0.3 ( Value in Cell C24) and Annual Income (values in Column D) >= 37,000 (Value in Cell C25) or Credit Score (Values in Column E) >= 4 (Value in Cell C26)

In the above query, relative references are made to Columns C, D and E and absolute references are made to cells C24, C25 and C26. IF statements are used for comparing the values.

The formula used to fill the entries in Extend Credit Column in given as below:

The formula used in Cell F2 :

F2 =IF(OR(AND(C2<$C$24,D2>=$C$25),E2>$C$26),"Extend Credit","No Credit")

In the above formula, relative references are made to Cell C2 , D2 and E2 and absolute references are made to Cell C24, C25 and C26. The above Formula is dragged along the Column F to calculate the values for the rest of the cells in Column F as shown below:

2.

Enter an IF statement in G4 that will return the appropriate bonus for the sales recorded in F4. The actual bonus rate depend on the amount sold. The table B2:D5 show the bonus rate for different sales levels. This assignment will require a statement with multiple if's.

To Calculate the Bonus amount in Cell G4, we need to use nested IF statements with absolute references to Cells C4, C5, D3,D4,D5 and relative reference to Cell F4, We use the formula as given below in cell F4:

The nested IF statements to use is:

if(sales >=0 and sales <5000) then

bonus = 0*sales

else

if(sales >=5000 and sales <10000) then

bonus = (sales-5000)*0.02

else

if(sales >=10000) then

bonus = (sales-10000)*0.04

else

bonus = 0

end if

end if

end if

F4=IF(AND(F4>=0,F4<5000),F4*$D$3,IF(AND(F4>=5000,F4<10000),(F4-$C$4)*$D$4,IF(F4>=10000,(F4-$C$5)*$D$5,0)))