1. In column E, use an =IF function to automatically assign the applicant to a Risk Score Bucket based on the Risk Scores, according to the chart below (i.e. A risk score of 800 or more should automatically display the word “Excellent,” 750-799 should display the words “Very Good,” and so on).
a. Run analysis with a PivotTable to show the count of loan titles in each Risk Score Bucket and answer the following questions:
i. Which Risk Score Bucket had the most rejected loans (most observations)?
ii. Which risk score bucket had the least rejected loans (least observations)?
2. Assign each debt-to-income (DTI) ratio into one of three DTI Buckets, High (for >20% DTI), Medium (for 10-19.99%), or Low (for <10%.
a. Run analysis with a PivotTable to show the count of amount requested in each DTI Bucket
i. Which DTI Bucket had the most rejected loans (most observations)?
ii. Which DTI Bucket had the least rejected loans (least observations)?
iii. What is your interpretation of why these loans were rejected based on DTI Ratio?
