28 Mar You recently joined a boutique consultancy that
I have attached the project details. The excel has the data set and the word doc has the assignment questions and I added some general guidelines. I have also attached the answer to question 1 so the regression should match this (unless you can make it better).
—
This email is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or action its contents by anyone other than the intended recipient(s) is strictly prohibited. If you have received this email in error, please contact the sender and delete its contents from any computer, file, account or other storage device.
Stats Project.docx
Task:
Use the Data Set “Loan Pricing Data Set.xlsx”
You recently joined a boutique consultancy that specializes in providing data-driven insights and decision support to the financial sector.
For your first assignment, Isabella Euler, one of the firm’s thought leaders, assigns you to a client who wants to improve the procedures of its Auto Financing Group (AFG). The client’s newly appointed VP believes that the existing procedures for pricing car loans are outdated and adversely affect profitability. This state of affairs is the result of organizational challenges, as the division has been cobbled together from multiple groups over the years. The ultimate ambition is to introduce sophisticated price optimization models. However, for now the VP would like you to study a sample of recently approved car loan applications to provide clarity on the AFG’s current approaches and results.
Specifically, you are to provide guidance on the following questions:
1) What is the relationship between the annual percentage rate (APR) that is offered to a prospective customer whose loan application has been approved, and the characteristics of that customer’s loan application? The AFG makes each approved customer a take-it-or leave-it APR offer, but the process for determining the quoted APR is currently not standardized. It is therefore not clear exactly how the quoted APRs are determined.
2) What factors are related to a customer’s loan offer acceptance? The VP of the AFG would both like to have a way to predict how a given applicant pool will respond to offers under the current APR quotation approach, and also understand which factors are related to these responses.
3) Relatedly, which factors are relevant for predicting the profitability of a given applicant pool under the current APR quotation approach?
Overall, record your approach for each step; why you did what you did. And summarize/infer the data results for each question.
Concepts: Confidence Intervals, Hypothesis Testing, Multiple regression, Regression for prediction, Regression for prescription, Linear regression assumptions, Collinearity, Binary Dependent Variables: Linear & logistic regression
Rough Steps/Guideline:
1)
-0.867 adjusted R squared (expected answer) -Independent variables used: credit score, amount, term, competitor, prime, internal_risk = high, internal_risk = very high, internal_risk = very low, loan_type = refinance -If internal risk is very high it’s 1 and the other 2 are 0 and vise versa. -Use Interaction terms? -If you don’t use interaction terms, run the correlation matrix in Stattools (Summary statistic, correlation and covariance) to see how highly correlated they are. Then do a linearity test and then run the regression to see which gives best sample fit, who has the highest adjusted R squared, then check linearity again -Suspected Answer: If internal risk is high or very high than the interest rate is high -Explanatory not prediction for Q1 -Do not use just holdout sample, run regression on entire data set. Holdout is “1” and “0” is main sample data.
2) -Do a predictive? analysis to see whether they will accept the offer rate or not -Holdout sample column added using 15%. If it is “1” then it is holdout sample, if it is “0” then it is main sample data.
3)
-Profitability we will have to run it with an interaction variable. -Profitability is assumed to be acceptance rate – prime which gives us the spread. Create a column for this as the dependent variable? -Internal risk: make dummy variables (4 for each type). Regression take 3, leave one out -Refinance column: put 1 and 0 and see how the coefficient looks like -What interaction terms should we create? -Holdout sample column added using 15%. If it is “1” then it is holdout sample, if it is “0” then it is main sample data. -Refinance column: put 1 and 0 and see how the coefficient looks like
Loan Pricing Data Set.xlsx
DataKey
Variable Name | Variable Description |
Customer_ID | Unique customer identifier |
Acquisition | Describes how the loan was acquired, one of four categories: own, insurance partner, auto partner, intermediary |
Internal_Risk | Internal risk classification, one of four categories: very low, low, high, very high |
Credit_Score | External risk score determined by consumer credit reporting agency |
Approved_Date | Date the customer's application was approved |
Loan_Type | One of three categories: new car, used car, refinance |
Amount | Loan amount |
Term | Loan term in months |
Offered_Rate | Annual percentage rate (APR) offered to customer |
Refinance | Previous APR for a refinanced car (blank if not a refinance) |
Competitor | APR published by competitor |
Prime | Prime rate |
Accepted | 1 if loan was funded, 0 if customer rejects the loan offer |
Funded_Date | Date the customer's loan was funded (blank if applicant rejects the loan offer) |
Data
Customer_ID | Acquisition | Internal_Risk | Credit_Score | Approved_Date | Loan_Type | Amount | Term | Offered_Rate | Refinance | Competitor | Prime | Accepted | Funded_Date | Random | Holdout |
3053 | Insurance Partner | Very Low | 717 | 9/12/2017 | New Car | $45,000 | 60 | 5.49 | 5.59 | 2.625 | 0 | 0.999990797 | 1 | ||
5417 | Own | Very Low | 742 | 9/21/2017 | Used Car | $24,750 | 72 | 6.75 | 6.75 | 2.686 | 0 | 0.999583455 | 1 | ||
2007 | Intermediary | Low | 708 | 9/9/2017 | Used Car | $14,850 | 48 | 7.44 | 6.15 | 2.61 | 0 | 0.999426916 | 1 | ||
89 | Intermediary | Very High | 651 | 9/1/2017 | New Car | $17,000 | 60 | 11.85 | 5.59 | 2.6 | 0 | 0.999315823 | 1 | ||
1810 | Insurance Partner | Low | 715 | 9/8/2017 | Used Car | $34,752 | 72 | 8.09 | 6.89 | 2.61 | 0 | 0.999249683 | 1 | ||
2086 | Insurance Partner | Very High | 640 | 9/11/2017 | Used Car | $16,830 | 60 | 11.95 | 6.15 | 2.615 | 0 | 0.999147985 | 1 | ||
5546 | Own | Very High | 647 | 9/22/2017 | Refinance | $20,790 | 72 | 12.35 | 7.9 | 7.29 | 2.696 | 0 | 0.999105034 | 1 | |
6012 | Own | Low | 707 | 9/26/2017 | New Car | $31,000 | 72 | 7.79 | 6.19 | 2.738 | 0 | 0.999043575 | 1 | ||
1682 | Intermediary | Very Low | 768 | 9/7/2017 | Used Car | $17,325 | 36 | 5.65 | 5.65 | 2.61 | 0 | 0.999006085 | 1 | ||
4589 | Own | Very Low | 809 | 9/18/2017 | New Car | $20,000 | 36 | 3.99 | 4.75 | 2.65 | 1 | 10/23/2017 | 0.998904108 | 1 | |
4363 | Own | Low | 703 | 9/18/2017 | Used Car | $25,245 | 60 | 7.44 | 5.89 | 2.65 | 0 | 0.998796731 | 1 | ||
477 | Own | Very High | 639 | 9/4/2017 | Used Car | $16,341 | 36 | 11.95 | 5.65 | 2.6 | 1 | 9/22/2017 | 0.998551352 | 1 | |
272 | Insurance Partner | High | 679 | 9/2/2017 | Used Car | $9,900 | 60 | 9.65 | 6.15 | 2.6 | 0 | 0.998143286 | 1 | ||
1966 | Insurance Partner | Low | 708 | 9/8/2017 | New Car | $25,000 | 60 | 7.39 | 5.59 | 2.61 | 0 | 0.997992168 | 1 | ||
5025 | Own | Very High | 670 | 9/20/2017 | Used Car | $17,820 | 48 | 11.95 | 5.89 | 2.67 | 0 | 0.997948124 | 1 | ||
5365 | Own | High | 699 | 9/21/2017 | New Car | $30,000 | 60 | 9.55 | 5.49 | 2.686 | 0 | 0.997825786 | 1 | ||
3589 | Intermediary | Very Low | 789 | 9/14/2017 | New Car | $10,000 | 36 | 3.99 | 3.99 | 2.64 | 1 | 10/12/2017 | 0.997822343 | 1 | |
5890 | Own | Very Low | 766 | 9/24/2017 | New Car | $35,000 | 60 | 5.49 | 5.49 | 2.696 | 0 | 0.997640979 | 1 | ||
1894 | Insurance Partner | Very Low | 741 | 9/8/2017 | New Car | $39,999 | 60 | 5.49 | 5.59 | 2.61 | 0 | 0.997634649 | 1 | ||
833 | Insurance Partner | Very High | 661 | 9/5/2017 | New Car | $32,000 | 66 | 12.15 | 6.39 | 2.6 | 0 | 0.997567636 | 1 | ||
4950 | Insurance Partner | Low | 704 | 9/19/2017 | New Car | $17,500 | 60 | 7.39 | 5.49 | 2.67 | 0 | 0.99747045 | 1 | ||
1341 | Auto Partner | Low | 713 | 9/6/2017 | Refinance | $25,740 | 60 | 8.09 | 4.9 | 6.49 | 2.6 | 0 | 0.997451956 | 1 | |
3818 | Intermediary | Very Low | 760 | 9/15/2017 | Refinance | $17,202 | 60 | 5.75 | 11 | 6.49 | 2.65 | 0 | 0.997408618 | 1 | |
5110 | Own | Very Low | 759 | 9/20/2017 | Refinance | $10,569 | 72 | 6.28 | 7.75 | 7.29 | 2.67 | 1 | 10/5/2017 | 0.997388049 | 1 |
5927 | Auto Partner | Very Low | 751 | 9/24/2017 | Refinance | $12,078 | 48 | 5.75 | 6.5 | 6.49 | 2.696 | 0 | 0.997360153 | 1 | |
4227 | Insurance Partner | Very Low | 780 | 9/17/2017 | New Car | $30,000 | 36 | 3.99 | 4.75 | 2.65 | 0 | 0.997320874 | 1 | ||
142 | Own | Low | 689 | 9/2/2017 | Used Car | $34,650 | 60 | 7.44 | 6.15 | 2.6 | 0 | 0.997263583 | 1 | ||
4605 | Own | Very Low | 833 | 9/18/2017 | New Car | $26,000 | 36 | 3.99 | 4.75 | 2.65 | 0 | 0.996944336 | 1 | ||
4187 | Own | Very Low | 826 | 9/17/2017 | New Car | $33,000 | 60 | 5.49 | 5.49 | 2.65 | 0 | 0.996867151 | 1 | ||
952 | Own | Very High | 661 | 9/6/2017 | Refinance | $16,954 | 66 | 8.42 | 9.89 | 7.29 | 2.6 | 0 | 0.996848452 | 1 | |
5658 | Own | Very Low | 796 | 9/23/2017 | Used Car | $17,820 | 72 | 6.75 | 6.75 | 2.696 | 1 | 9/29/2017 | 0.996771484 | 1 | |
4145 | Intermediary | Very Low | 757 | 9/16/2017 | New Car | $40,000 | 66 | 6.39 | 6.39 | 2.65 | 0 | 0.996736128 | 1 | ||
1697 | Intermediary | High | 669 | 9/7/2017 | Used Car | $14,849 | 60 | 9.65 | 6.15 | 2.61 | 0 | 0.996594655 | 1 | ||
7069 | Insurance Partner | Very Low | 828 | 9/29/2017 | New Car | $27,776 | 60 | 5.49 | 5.49 | 2.757 | 0 | 0.996591684 | 1 | ||
177 | Insurance Partner | Low | 716 | 9/2/2017 | Used Car | $21,780 | 72 | 8.09 | 6.89 | 2.6 | 0 | 0.996560869 | 1 | ||
6761 | Own | Very Low | 765 | 9/28/2017 | Refinance | $27,216 | 72 | 7.29 | 9 | 7.29 | 2.75 | 0 | 0.996433222 | 1 | |
3576 | Own | Low | 719 | 9/14/2017 | New Car | $22,520 | 60 | 7.39 | 5.59 | 2.64 | 0 | 0.996368412 | 1 | ||
1187 | Intermediary | Low | 727 | 9/6/2017 | Refinance | $12,428 | 36 | 7.89 | 7.15 | 6.05 | 2.6 | 1 | 9/28/2017 | 0.996295022 | 1 |
2201 | Intermediary | Very Low | 800 | 9/9/2017 | New Car | $45,000 | 60 | 5.49 | 5.59 | 2.61 | 0 | 0.996246487 | 1 | ||
2718 | Insurance Partner | Very Low | 806 | 9/11/2017 | Used Car | $7,425 | 36 | 5.65 | 5.65 | 2.615 | 1 | 9/27/2017 | 0.99619279 | 1 | |
3494 | Own | Very Low | 768 | 9/14/2017 | Used Car | $30,442 | 60 | 6.05 | 6.15 | 2.64 | 1 | 9/21/2017 | 0.996052577 | 1 | |
943 | Intermediary | Very High | 656 | 9/5/2017 | New Car | $12,000 | 60 | 11.85 | 5.59 | 2.6 | 0 | 0.995903595 | 1 | ||
155 | Intermediary | Low | 707 | 9/2/2017 | New Car | $20,000 | 60 | 7.39 | 5.59 | 2.6 | 0 | 0.995867114 | 1 | ||
2829 | Own | Very High | 646 | 9/12/2017 | New Car | $14,127 | 72 | 12.15 | 6.39 | 2.625 | 1 | 9/18/2017 | 0.99578383 | 1 | |
2751 | Own | Very Low | 746 | 9/12/2017 | Used Car | $11,899 | 36 | 5.65 | 5.65 | 2.625 | 1 | 9/21/2017 | 0.995651584 | 1 | |
2692 | Own | High | 663 | 9/11/2017 | Refinance | $21,047 | 48 | 8.53 | 10 | 6.49 | 2.615 | 0 | 0.995374658 | 1 | |
1490 | Intermediary | Very Low | 761 | 9/7/2017 | New Car | $19,999 | 48 | 5.09 | 5.59 | 2.61 | 0 | 0.995327218 | 1 | ||
4464 | Own | Very Low | 767 | 9/18/2017 | New Car | $55,000 | 48 | 5.59 | 5.49 | 2.65 | 0 | 0.995010453 | 1 | ||
421 | Intermediary | Very Low | 822 | 9/4/2017 | New Car | $34,999 | 60 | 5.49 | 5.59 | 2.6 | 0 | 0.994928298 | 1 | ||
4201 | Insurance Partner | Very Low | 749 | 9/17/2017 | Used Car | $29,700 | 60 | 6.05 | 5.89 | 2.65 | 0 | 0.99488342 | 1 | ||
2597 | Auto Partner | Low | 726 | 9/11/2017 | Refinance | $21,285 | 48 | 8.09 | 9.9 | 6.49 | 2.615 | 0 | 0.994611698 | 1 | |
2979 | Own | Very Low | 750 | 9/12/2017 | Refinance | $17,282 | 48 | 6.49 | 8.29 | 6.49 | 2.625 | 1 | 9/27/2017 | 0.993992989 | 1 |
1124 | Intermediary | High | 690 | 9/6/2017 | Used Car | $17,717 | 48 | 9.65 | 6.15 | 2.6 | 0 | 0.993959119 | 1 | ||
977 | Own | High | 724 | 9/5/2017 | Refinance | $13,365 | 60 | 8.09 | 6.39 | 6.49 | 2.6 | 0 | 0.99395512 | 1 | |
7174 | Insurance Partner | Very Low | 783 | 9/29/2017 | New Car | $20,000 | 36 | 4.75 | 4.75 | 2.757 | 0 | 0.993858569 | 1 | ||
1437 | Intermediary | High | 682 | 9/7/2017 | Used Car | $25,740 | 60 | 9.65 | 6.15 | 2.61 | 0 | 0.993788207 | 1 | ||
6588 | Insurance Partner | High | 688 | 9/27/2017 | Refinance | $6,379 | 36 | 7.72 | 9.19 | 5.99 | 2.74 | 1 | 11/13/2017 | 0.993694324 | 1 |
2678 | Insurance Partner | Very Low | 760 | 9/11/2017 | New Car | $50,000 | 60 | 5.49 | 5.59 | 2.615 | 0 | 0.993688619 | 1 | ||
1846 | Insurance Partner | Low | 715 | 9/8/2017 | Refinance | $14,453 | 60 | 7.59 | 11 | 6.49 | 2.61 | 0 | 0.993494526 | 1 | |
1093 | Auto Partner | Very Low | 798 | 9/6/2017 | Refinance | $42,174 | 72 | 6.67 | 8.14 | 7.29 | 2.6 | 0 | 0.993491837 | 1 | |
1315 | Insurance Partner | Very Low | 717 | 9/6/2017 | New Car | $18,383 | 60 | 5.09 | 5.59 | 2.6 | 1 | 10/19/2017 | 0.993380637 | 1 | |
5689 | Insurance Partner | Very Low | 801 | 9/23/2017 | New Car | $30,000 | 60 | 5.49 | 5.49 | 2.696 | 0 | 0.99330423 | 1 | ||
4949 | Own | Very Low | 765 | 9/19/2017 | New Car | $20,000 | 48 | 5.49 | 5.49 | 2.67 | 0 | 0.993257216 | 1 | ||
7061 | Intermediary | Very Low | 744 | 9/29/2017 | Refinance | $12,404 | 72 | 5.75 | 6.89 | 7.29 | 2.757 | 0 | 0.993209055 | 1 | |
4455 | Own | Very High | 649 | 9/18/2017 | Used Car | $23,760 | 60 | 11.95 | 5.89 | 2.65 | 0 | 0.993189949 | 1 | ||
2080 | Own | Very High | 660 | 9/9/2017 | New Car | $15,000 | 72 | 12.15 | 6.39 | 2.61 | 0 | 0.993037155 | 1 | ||
2013 | Intermediary | Low | 714 | 9/9/2017 | New Car | $17,500 | 36 | 7.34 | 3.99 | 2.61 | 0 | 0.993034569 | 1 | ||
2466 | Own | Very High | 652 | 9/11/2017 | Used Car | $8,909 | 60 | 11.95 | 6.15 | 2.615 | 0 | 0.99303395 | 1 | ||
4230 | Intermediary | Very High | 647 | 9/17/2017 | Refinance | $12,815 | 72 | 12.35 | 19.6 | 7.29 | 2.65 | 1 | 10/5/2017 | 0.99291209 | 1 |
1999 | Insurance Partner | High | 696 | 9/9/2017 | Used Car | $36,630 | 60 | 9.65 | 6.15 | 2.61 | 0 | 0.992704705 | 1 | ||
5126 | Intermediary | High | 694 | 9/20/2017 | Used Car | $24,750 | 60 | 9.65 | 5.89 | 2.67 | 0 | 0.992641051 | 1 | ||
2134 | Own | Very High | 655 | 9/10/2017 | New Car | $36,223 | 60 | 11.85 | 5.59 | 2.61 | 0 | 0.992558009 | 1 | ||
3836 | Insurance Partner | High | 672 | 9/15/2017 | New Car | $25,000 | 60 | 9.55 | 5.59 | 2.65 | 0 | 0.992542436 | 1 | ||
2637 | Own | Very Low | 746 | 9/11/2017 | New Car | $30,933 | 60 | 5.49 | 5.59 | 2.615 | 1 | 9/22/2017 | 0.992394446 | 1 | |
7154 | Own | Very Low | 806 | 9/29/2017 | New Car | $19,999 | 36 | 4.75 | 4.75 | 2.757 | 0 | 0.99232408 | 1 | ||
4552 | Insurance Partner | Very Low | 741 | 9/19/2017 | New Car | $10,001 | 36 | 3.99 | 4.75 | 2.67 | 0 | 0.992271042 | 1 | ||
3213 | Intermediary | Low | 728 | 9/13/2017 | Used Car | $5,940 | 36 | 7.39 | 5.65 | 2.63 | 1 | 9/19/2017 | 0.992185303 | 1 | |
4783 | Insurance Partner | Very Low | 740 | 9/19/2017 | Used Car | $39,600 | 72 | 6.75 | 6.75 | 2.67 | 0 | 0.992025631 | 1 | ||
116 | Own | Very High | 655 | 9/2/2017 | New Car | $12,000 | 72 | 12.15 | 6.39 | 2.6 | 0 | 0.991956064 | 1 | ||
5673 | Intermediary | Very High | 641 | 9/23/2017 | New Car | $31,000 | 72 | 12.15 | 6.19 | 2.696 | 0 | 0.991843135 | 1 | ||
4132 | Own | Very Low | 796 | 9/17/2017 | Refinance | $36,630 | 60 | 6.49 | 7.99 | 6.49 | 2.65 | 1 | 10/6/2017 | 0.991270704 | 1 |
921 | Own | Low | 705 | 9/5/2017 | New Car | $35,000 | 60 | 7.39 | 5.59 | 2.6 | 0 | 0.991057356 | 1 | ||
294 | Intermediary | Very Low | 753 | 9/4/2017 | Used Car | $18,810 | 60 | 6.05 | 6.15 | 2.6 | 0 | 0.990776881 | 1 | ||
4772 | Own | Very Low | 743 | 9/19/2017 | New Car | $39,000 | 48 | 5.49 | 5.49 | 2.67 | 0 | 0.990748546 | 1 | ||
5022 | Own | Very Low | 733 | 9/20/2017 | New Car | $35,000 | 60 | 5.49 | 5.49 | 2.67 | 1 | 9/29/2017 | 0.990486968 | 1 | |
6944 | Intermediary | Very Low | 786 | 9/28/2017 | Refinance | $10,296 | 36 | 5.59 | 6.6 | 5.99 | 2.75 | 0 | 0.990269589 | 1 | |
2586 | Insurance Partner | Very High | 665 | 9/11/2017 | Refinance | $7,227 | 36 | 11.95 | 13.5 | 6.05 | 2.615 | 0 | 0.990113535 | 1 | |
5959 | Own | Very Low | 735 | 9/25/2017 | Used Car | $17,820 | 48 | 5.89 | 5.89 | 2.723 | 0 | 0.989712017 | 1 | ||
6312 | Insurance Partner | Very Low | 753 | 9/26/2017 | Used Car | $4,950 | 36 | 5.39 | 5.39 | 2.738 | 0 | 0.989710768 | 1 | ||
1590 | Own | High | 685 | 9/7/2017 | Used Car | $25,740 | 60 | 9.65 | 6.15 | 2.61 | 0 | 0.989556145 | 1 | ||
1255 | Own | High | 675 | 9/6/2017 | Used Car | $15,868 | 60 | 9.65 | 6.15 | 2.6 | 1 | 9/21/2017 | 0.989444448 | 1 | |
3780 | Own | Very Low | 767 | 9/15/2017 | Used Car | $34,650 | 36 | 5.65 | 5.65 | 2.65 | 0 | 0.989025309 | 1 | ||
989 | Insurance Partner | High | 687 | 9/5/2017 | Used Car | $44,550 | 60 | 9.65 | 6.15 | 2.6 | 0 | 0.988792797 | 1 | ||
5007 | Own | Very Low | 764 | 9/20/2017 | Used Car | $10,249 | 36 | 5.39 | 5.39 | 2.67 | 1 | 10/17/2017 | 0.988497102 | 1 | |
5393 | Auto Partner | Very Low | 771 | 9/21/2017 | Refinance | $24,594 | 60 | 5.98 | 7.45 | 6.49 | 2.686 | 0 | 0.988473698 | 1 | |
3695 | Intermediary | High | 699 | 9/15/2017 | Used Car | $21,772 | 60 | 9.65 | 6.15 | 2.65 | 0 | 0.988253348 | 1 | ||
1306 | Insurance Partner | Very Low | 793 | 9/6/2017 | Used Car | $17,819 | 60 | 6.05 | 6.15 | 2.6 | 0 | 0.987965384 | 1 | ||
7097 | Own | Very Low | 800 | 9/29/2017 | New Car | $18,000 | 60 | 5.49 | 5.49 | 2.757 | 1 | 10/12/2017 | 0.987913484 | 1 | |
462 | Own | Low | 725 | 9/3/2017 | Used Car | $25,938 | 60 | 7.44 | 6.15 | 2.6 | 1 | 9/21/2017 | 0.987780651 | 1 | |
5877 | Own | Very High | 647 | 9/24/2017 | New Car | $28,000 | 66 | 12.15 | 6.19 |