# Part 4: Modeling Profitability Instead of Default >> Week 6

## Part 4: Modeling Profitability Instead of Default >> Week 6 >> Mastering Data Analysis in Excel

1. Question 1 Modeling Profitability Instead of Default Modeling Profitability Level as a Continuous Output (Instead of Binary Classification Default/No Default) Introduction Both your own model and the forecast based on Eggertopia scores are binary classifications: they forecast one of just two outcomes: “Default” or “No Default.” Your boss is interested in the idea that it might be preferable instead to model and forecast profits and losses as continuous values, using a a multivariate linear regression model on the same six input variables. This idea has arisen because the bank has been reviewing individual profit and loss numbers for each customer over the three-year period and has made an interesting discovery: some defaulting customers carried so much debt for so long, and paid so much interest on it, that they were profitable for the bank even though they defaulted! Many customers who seem to have risky spending behaviors are also among the most profitable for a lending business. And, at the opposite extreme,customers who always paid off their cards in full each month never defaulted but were not very profitable: the bank barely broke even, or even lost money, on its“safest” borrowers. Your boss asks you to forecast each applicant’s expected profitability, in dollars,before deciding whether or not to issue them a credit card. He wants to know how reliable this type of forecast would be: what is the range above and below the point estimate that will be correct 90% of the time? Although it might be possible to combine the six inputs in other ways, in the interests of time and focusing on the key learning objectives, we will use only a simple linear combination of the six input variables for Part 4 of this Project. (You should not include the Eggertopia Scores as an input variable). Question 1 is about the coefficients or “betas” used to combine the standardized inputs to get the best-fit-line on standardized outputs on the Training Set. We then use those fixed betas to measure the observed residual error of the model on the Test Set. Questions 2 through 6 concern the forecasts on the Test Set. Questions 7 through 11 look at the Training Set results so that they can be compared (for possible over-fitting) against the Test Set Results. Questions 12 through 14 are about the uncertainty that remains in a new individual forecast of profitability. Use the Excel “Linest” function on the six inputs and profitability output on the 200 Training Set applicants to calculate the coefficients (the “betas”) that result in the best-fit line. Question: Do you feel prepared to take this quiz? 1 point Yes No 2. Question 2 Question: What are your values for each “beta” on the Training Set? Age Years at current employer Years at current address Income over the past year Current credit card debt Current automobile debt 1 point .01, .19, -.07, .64, -.06, 0 01, -.19, -.07, -.64, -.06, 0 .01, .19, .07, .64, .06, 0 3. Question 3 For this question, use the Liner Regression Forecasting explanation and Excel spreadsheet. Question: What is the root-mean-square residual (the standard deviation of model error) on Standardized output for the Test Set? 1 point .3250 0.6750 .5835 .6875 .8109 4. Question 4 For this question, use the Linear Regression Forecasting Explanation and Spreadsheet. Question: What is the observed correlation R on the Test Set? 1 point .8095 .7332 .7590 0.7378 5. Question 5 For this question, use the Linear Regression Forecasting explanation and Excel spreadsheet. Question: What is the Standard deviation of model error, in Dollars, for the Test Set? 1 point $3,885.14 $3,996.81 $3,379.36 $3,411.80 6. Question 6 For this question, use the Linear Regression Forecasting explanation and Excel spreadsheet: Question: What is the 90% confidence interval, in dollars, for the Test Set? 1 point $5,611.91 above the point estimate, and $5,611.91 below the point estimate $5,558.55 above the point estimate, and $5,558.55 below the point estimate $6,574.17 above the point estimate, and $6,574.17 below the point estimate $6,390.49 above the point estimate, and $6,390.49 below the point estimate 7. Question 7 What is the Percentage Information Gain (P.I.G.) on the Test Set? 1 point 37.2% 27.7% 26.4% 18.9% 8. Question 8 For this question, use the Linear Regression Forecasting explanation and Excel spreadsheet: Question: What is the Correlation, R, of your model on the Training Set? 1 point .8095 .7505 .7805 9. Question 9 For this question, use the Linear Regression Forecasting explanation and Excel spreadsheet: You need to quantify the uncertainty in a regression model forecast of applicants’ future profitability. Assume that both the forecast profits and the errors have a Gaussian distribution. You will calculate the standard deviation of model error on standardized data, the standard deviation in dollars of the model error, and the 90% confidence interval for profitability estimates. Question: What is the standard deviation of your model error on the standardized Training Set output? 1 point .587 .487 -.487 -.587 10. Question 10 For this question, use the Linear Regression Forecasting explanation and Excel spreadsheet. Question: What is the standard deviation of model error in dollars on the Training Set? **This may seem similar to question 5, but Q5 refers to the Test Set. 1 point $5,500.87 $3,379.36 $4,312.91 $4,379.36 11. Question 11 For this question, use the Linear Regression Forecasting explanation and Excel spreadsheet. Question: What is the 90% confidence interval, in dollars, on the Training Set? **This may seem similar to question 6, but Q6 refers to the Test Set. 1 point $5,558.55 $6,211.18 $7,128.55 $5,328.93 12. Question 12 For this question, use the Linear Regression Forecasting explanation and Excel spreadsheet. Question: What is the Percentage Information Gain (P.I.G.) on the Training Set? **This may seem similar to question 7, but Q7 refers to the Test Set. 1 point 32.4% 37.5% 41.4% 36.5% 13. Question 13 Questions 13 through 15 use the same example applicant. The following data are known about the sample applicant: Age: 42.00 Years at Employer: 12.44 Years at Address: 0.9 Income: $121,400 CC debt: -34,228 Auto debt: -23,411 To convert above inputs to standardized form, locate the Training Set Spreadsheet (first bottom tab of workbook) in the Data for Final Project Workbook. Data_for_Final_Project.xlsx Use the input means [Cells C207:H207] and standard deviations [Cells C209:H209]. Use the Training Set profitability mean [$1,905.51] and standard deviation [$5755.91] from the Profit and Loss (last bottom tab) Spreadsheet. Use the Test Set standard deviation of error on standardized outputs of .6750 Question: What is the point estimate of profitability, in dollars? 1 point $10,683.61 $11,109.61 $8,451.61 -$10,683.61 14. Question 14 The following data are known about the sample applicant: Age: 42.00 Years at Employer: 12.44 Years at Address: 0.9 Income: $121,400 CC debt: -34,228 Auto debt: -23,411 To convert above inputs to standardized form, locate the Training Set Spreadsheet (first bottom tab) in the Data for Final Project Workbook. Use those means [Cells C207:H207] and standard deviations [Cells C209:H209]. Use the Training Set profitability mean [$1,905.51] and standard deviation [$5755.91] from the Profit and Loss (last tab on bottom) Spreadsheet Use the Test Set standard deviation of error on standardized outputs of .6750 Question: With 50% confidence, what is the range of profitability? 1 point Range from $10,683.61 to – $2,278.99 Range from $11,823.28 to $9,543.94 Range from $13,304.16 to $8,063.06. Range from $12,962.61 to $10,683.61 15. Question 15 The following data are known about the sample applicant: Age: 42.00 Years at Employer: 12.44 Years at Address: 0.9 Income: $121,400 CC debt: -34,228 Auto debt: -23,411 To convert above inputs to standardized form, locate the Training Set Spreadsheet (bottom tab) in the Data for Final Project Workbook. Use those means [Cells C207:H207] and standard deviations [Cells C209:H209]. Use the Training Set profitability mean [$1,905.51] and standard deviation [$5755.91] from the Profit and Loss (bottom tab) Spreadsheet Use the Test Set standard deviation of error on standardized outputs of .6750 . Question: With 99% confidence, what is the range of profitability? 1 point Range from $16,388.27 to -$7,704.31 Range from $10,683.61 to -$8,704.31 Range from $19,388.27 to 10,683.61. Range from $20,691.32 to $675.90. 16. Question 16 Comparing Test Set and Training Set Performance Question 15: Between the Training Set and the Test Set, the dollar value of the standard deviation of model error… 1 point Increased by more than 50%, which leads to the conclusion of model over-fitting. Increased by more than 25%, which suggests possible model over-fitting. Decreased by about 15%, which suggests a very strong model on Test Set data. Increased by less than 20%, which suggests minimal model over-fitting.

*Please Wait 15 Seconds To Get The Pdf Loaded