Be on schedule.Score better.

EN

Section A Input Data

Section A

Input Data
EduCredit is the education loan division of a bank. It is considering a batch of applications for student loans repayable in one year. EduCredit charges each loan applicants an annual interest rate that depends on the band of their credit scores (A is the highest)
Loan Applicant Maximum Loan requested Credit Band Predicted Default Probability
Applicant 1 $10,000 A 1.0%
Applicant 2 $15,000 C 5.8%
Applicant 3 $20,000 B 2.0%
Applicant 4 $8,000 A 1.5%
Applicant 5 $13,000 C 11.0%
Credit Band Interest Rates The current batch of applications come from five applicants, each of whom has requested a different loan amount. EduCredit can choose to offer any loan amount up to the maximum amount requested (including zero). Besides the credit scores, EduCredit uses a proprietary predictive analytics model to predict the default probability for each loan applicant based on their previous business history with the bank. All of the above information for the five applicants are listed
A 3.0%
B 4.5%
C 7.0%
D 10.0%
Total Budget $35,000 Q3-15
$60,000 Q2
Q1 If EduCredit wants to decide optimally the best amount of loans to offer to each applicant, what should be a reasonable objective to consider?
To maximize interest revenue minus expected defaulted loan amounts
To minimize expected defaulted loan amounts
To maximize interest revenue
To maximize total dollar amount of loans approved
Q2 If EduCredit has a total budget of $60,000 that it could offer to this batch of applicants, what is optimal decision in terms of the loan amounts to approve for each applicant? Explain briefly [in 30 words or so] how you can find this optimal solution even *without* using Solver.
Q3 Suppose EduCredit only has a budget of $35,000 for this batch of applications. Formulate the optimization problem on a spreadsheet and solve for the optimal decisions using Solver. Solve for the optimal solution and generate the sensitivity report (for later questions). In Questions 3-7, you will input the optimal loan amounts for each applicant.

What is the optimal loan amount to approve for Applicant 1? Enter only the numerical value without dollar sign.

Q4 What is the optimal loan amount to approve for Applicant 2? Enter only the numerical value without dollar sign.
Q5 What is the optimal loan amount to approve for Applicant 3? Enter only the numerical value without dollar sign.
Q6 What is the optimal loan amount to approve for Applicant 4? Enter only the numerical value without dollar sign.
Q7 What is the optimal loan amount to approve for Applicant 5? Enter only the numerical value without dollar sign.
Q8 From the sensitivity report generated from Question 3, how much do we predict the optimal objective value to change if Applicant 4’s requested loan amount is decreased from currently $8000 to $4000. Enter the net change (i.e., include the negative sign if the objective value decreases) without the dollar sign. If we cannot predict based on the sensitivity report, enter -99999.
Q9 From the sensitivity report generated from Question 3, find the shadow price for the budget constraint. Explain [in about 20-30 words] why the shadow price is equal to this value and give an interpretation based on the values of the input data.
EduCredit would like to add the following requirement on the approval decisions: the fraction of loans provided to applicants with credit bands B or below cannot be more than 20% of all loans approved (e.g., if $10000, $4000, $5000 and $6000 are loaned to applicants of bands A, B, C and D, respectively, then the fraction loaned to bands B or below would be (4000+5000+6000)/(10000+4000+5000+6000) = 60%, which violates the requirement).

Implement this constraint in your Excel model and re-run Solver.

Q10 What is the optimal loan amount to approve for Applicant 1 after the new constraint? Enter only the numerical value without dollar sign.
Q11 What is the optimal loan amount to approve for Applicant 2 after the new constraint? Enter only the numerical value without dollar sign.
Q12 What is the optimal loan amount to approve for Applicant 3 after the new constraint? Enter only the numerical value without dollar sign.
Q13 What is the optimal loan amount to approve for Applicant 4 after the new constraint? Enter only the numerical value without dollar sign.
Q14 What is the optimal loan amount to approve for Applicant 5 after the new constraint? Enter only the numerical value without dollar sign.
Q15 We have so far assumed that EduCredit can approve any dollar amount up to the maximum amount requested. For example, we can offer Applicant 1 (who applied for $10,000) any amount from $0 to $10,000.

For the last question for Section A, suppose EduCredit can only make approve/reject decisions, i.e., if an application is approved, it must be approved in full. For example, Applicant 1 can only be offered $0 or $10,000, but not (say) $5,000.

What would happen as we formulate this as an optimization problem and solve it with Solver? Answer *without* implementing the model. [Select all that apply.]

We should add integer constraints.
We should define decision variables that only take values of one or zero
We should use roundup functions in our decision cells to ensure that, if we approve part of a loan, the full amount must be approved
We should use the nonlinear solving method
After Solver solves the problem, it will not generate a sensitivity report

Table of Contents

[display_short_calculator]

Latest Reviews

Impressed with the sample above? Wait there is more

Related Questions

New questions

Don't Let Questions or Concerns Hold You Back - Make a Free Inquiry Now!