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

<t

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.

Related Posts

All Rights Reserved Terms and Conditions
College pals.com Privacy Policy 2010-2018

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