Quantitative Methods

Q1)Goodlife Bank is formulating its loan policy for next year. The bank offers three types of one-year bridging loans: education loans, home loans and car loans. The interest rate and the bad-debt percentage for each loan type are as follows:

For example, if a borrower obtains an education loan of $100, he needs to repay $104.5 to the bank after one year. The bad debt percentage is the uncollectible loan amount divided by the total loan amount. For example, 2.1% of the education loan amount is not recoverable. In other words, every dollar lent out has a 2.1% probability of not returning to the bank.

(a) Identify which of these three types of loans has the highest net return.

(b) The bank decides to lend up to $9 million. To support the vision of a learning society, at least $3 million will be allocated for education loans. In order to have a diversified customer base, home loans need to account for at least 15% of total loans; car loans need to be at least 40% of home loans. To keep the bank running smoothly, the overall bad-debt percentage shall not exceed 1.25%. The bank wants to determine how much loan amount to allocate for each loan type in order to maximise the bank's net return. Develop the linear programming (LP) model for the problem. Please limit the answer to within one page.

(C) This part is a continuation of Part (b). Summarise how the bank should allocate funds for the three types of loans after solving the problem with Excel Solver. Please paste here three screenshots,

(C1) the problem worksheet just before clicking the Solver command: after developing the Excel Solver model, press Ctrl + ~ (tilde) to show formulas in cells, and then take a screenshot with row and column headings;

(C2) the Solver Parameters dialog just before clicking Solve; and

(C3) the Answer Report created by the Solver. Please limit the answer to within three pages.

Loan type Education Home Car Interest rate (per annum) 4.5% 1.6% 1.9% Bad-debt percentage 2.1% 0.7% 0.9% Loan type Education Home Car Interest rate (per annum) 4.5% 1.6% 1.9% Bad-debt percentage 2.1% 0.7% 0.9%