(LO 1-6) Consider the 2013 rejected loan data from LendingClub titled “DAA Chapter 1-2 Data.” Browse the file in Excel to ensure there are no missing data.

Because our analysis requires risk scores, debt-to-income data, and employment length, we need to make sure each of them has valid data. There should be 669,993 observations.

a. Assign each risk score to a risk score bucket similar to the chapter. That is, classify the sample according to this breakdown into excellent, very good, good, fair, poor, and very bad credit according to their credit score noted in Chapter 1.

Classify those with a score greater than 850 as “Excellent.” Consider using nested if-then statements to complete this. Or sort by risk score and manually input into appropriate risk score buckets (similar to Problem 9).

b. Assign each debt-to-income ratio into three buckets (labeled DTI bucket) by classifying each debt-to-income ratio into high (>20.0 percent), medium (10.0–20.0 percent), and low (<10.0 percent) buckets. Consider using nested if-then statements to complete this. Or sort the row and manually classify into the appropriate bucket.

c. Run a PivotTable analysis to show the number of excellent risk scores but high DTI bucket loans in each employment year bucket.

Which employment length group had the most observations to go along with excellent risk scores but high debt-to-income? Which employment year group had the least observations to go along with excellent risk scores but high debt-toincome?

Analysis: Any interpretation of why these loans were rejected?