Management - Certified Expert in Risk Management - Case Study Assessment Answer

Assessment Task:

This Case Study is a mandatory assignment in the Certified Expert in Risk Management e-learning course. You need to pass the assignment in order to be eligible for the final exam. 

In the file CERM14_Case1.xlsx, you will find the loan portfolio data for this case. There are 40,000 Micro and Small Business loans disbursed between August 2017 and May 2019. We are now at the end of September 2019 and we have arrears and balance observations for these loans between June 2019 and September 2019.

Let’s assume you are an analyst at an MSME investment fund and have received this data file as part of your monthly reporting from Business Bank in Kenya. Note that this portfolio does not represent the entire lending activity of Business Bank, it represents only the special MSME loans that the bank funds by way of borrowing USD from international MSME investment vehicles. In this market segment, Business Bank lends both in Kenyan Shilling (KES) and in US-dollar (USD). All loans are amortizing and carry fixed monthly (annuity-style) instalments. All currency amounts are reported to you in USD equivalent. All micro and small business loans are evaluated with an expert scoring model at disbursement. The scoring grades 1-6 are defined as follows: 1 – Excellent, 2 - Good, 3 – Average, 4 – Below Average, 5 – Marginal, 6 – Reject.

Here is the minimum scope of analysis that your Investment Committee expects from you, any additional analysis and findings would be welcome, of course. You may use manual filters to explore the data initially, but please use concise formula statements and analytical functions whenever possible to answer the questions below. Useful Excel functions for this case include: Pivot Tables, VLOOKUP(), HLOOKUP(), IF(), SUMIF(), COUNTIF(), MMULT() etc. Please work smarter, not harder!

1. 25% - Analyze the portfolio balance and disbursement trends: (a) Make a table and /or chart with a time series of the disbursed loan amounts by calendar month. (b) Add the weighted average disbursed scoring grade by calendar month to the table or chart under (a). (c) Estimate what the outstanding balance under this portfolio would have been as of 31-Dec-2018, if all clients had paid perfectly as per the contract and there were no arrears as of December 2018 and compare to the total balances outstanding in Jun-Sep 2019. Comment on your findings.

2. 20% - Default Rate and Scoring Test: (a) Using the limited arrears observations available, obtain an estimate default rate for loans disbursed by Business Bank up until 31-Aug-2018. Differentiate the default rate by disbursement year (2017, 2018) and by

Contractual currency USD, KES). A default is deemed to have occurred when a loan has reached >90 days in arrears at any time during Jun-Sep 2019. You should assume that no defaulted loans have been written off from this portfolio. (b) Separately find the default rates for all loans disbursed up until 31-Aug-2018 by scoring grade at disbursement. What does the result tell you about the predictive performance of the scoring model?

3. 25% - Gini Concentration Coefficient: You are concerned about concentration risk in the MSME portfolio of Business Bank. The Bank has a policy of not allowing multiple concurrent loans to the same client in this MSME development portfolio. You suspect that this policy may not always be followed strictly. (a) Check whether as of June 2019, there were any active concurrent loans to the same borrower in this portfolio. If so, prepare a table highlighting the number of client instances with concurrent active loans as of June 2019 and the portfolio balances impacted by this policy violation. (b) Calculate the Gini concentration coefficient across the entire portfolio on 30-Jun-2019. Be sure to exclude accounts with zero balances from the analysis. Prepare the Gini calculation once by account balances and then separately after accumulating separate account balances that are owed by the same client.

4. 30% - Verify the Sep 2019 Loan Loss Reserves: Calculate the necessary loan loss reserves under the national accounting rules that BusinessBank should have accrued against this Micro and Small Business portfolio as of 30 Sep 2019, using the following provisioning rate table prescribed by the Central Bank.

5. Bonus Question (20%) - Transition Matrix: Calculate three monthly transition matrixes between Jun 2019 and Sep 2019. Use the enhanced transition matrix method including principal paydown as described at the end of Chapter 4.3 in Unit 4.1. Carry out the transition matrix to 331-360+ days in 30-day arrears increments. Any arrear balances above 360 days should be summarized into the 331-360+ category.

Obtain the average of the three transition matrixes above and take the average through the matrix exponential calculation up to 12 months. In the matrix, exponential find the conditional probabilities of balances reaching 91+ days after 12 months depending on their initial levels of arrears.

What does the transition matrix exponential calculation tell you about the size of the IFRS 9 expected credit loss provision for loans currently not in arrears (Stage 1)?

This Management Assessment has been solved by our Management Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.