(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 valid 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 input.

b. Run a PivotTable analysis that shows the number of loans in each DTI bucket.

Which DTI bucket had the highest and lowest grouping for this rejected Loans dataset? Any interpretation of why these loans were rejected based on debt-toincome ratios?