Assignment 2 relates to the specific course learning objectives 1, 2, 4 and 6 and associated
MBA program learning goals and skills: Global Content, Problem solving, Critical thinking,
and Written Communication at level 3:
1.demonstrate applied knowledge of people, markets, finances, technology and management in a
global context based on data warehouse design, the CRISP data mining process, data visualisation
and performance management
2.identify and solve complex organisational problems creatively and practically such as the design,
implementation and practical use of business intelligence systems tools and applications to increase
organisational performance through better alignment of operational activities with strategic goals
4.evaluate, synthesise and critically review current literature on business intelligence systems
(Evidence based decision making, data warehousing design, data mining, data visualisation, business
performance management, and overarching business intelligence systems implementation and
utilisation) to show how these enable evidence based decision making and sustainable business
6.demonstrate the ability to communicate effectively in a clear and concise manner in written report
style for senior management with correct and appropriate acknowledgment of the main ideas
presented and discussed.
Assignment 2 consists of two main tasks and a number of sub tasks
Task 1 (Worth 60 marks) consists of the following sub tasks
A) Assignment 2 requires that you research and critically evaluate literature surrounding the
problem of effectively assessing loan applications for credit worthiness. Credit worthiness
assessment reduces the risks associated with lending by determining which potential loan
applications are considered to be good, or alternatively a poor, credit risk and should on that
basis be approved or rejected. Good risk management of loan applications can significantly
improve the bottom line of financial institutions such as banks, building societies and credit
unions. This research will inform your assessment of the credit data set which is provided for
Assignment 2 and Assignment 3. (About 1000 words)
B) Conduct an exploratory analysis of the creditdata.csv data set which is provided on the
CIS8008 S1 2012 course study desk to identify what you consider to be top five key
variables which contribute to determining whether a potential loan applicant is a good credit
risk or a bad credit risk. Note you should also refer to the data dictionary provided with the
creditdata.csv file as this document defines each of the variables and their range of values
Discuss each of your five top variables in about 200 words in terms of the results of your
exploratory data analysis using Rattle data mining tool and the relevant supporting literature
on credit assessment. Your discussion should also include appropriate statistical analysis
results including graph and tables from the exploratory data analysis tab in Rattle data
mining tool. (about 1000 words)
C) Building on your answers to the two previous questions discuss how improved decision
making through the capture and utilisation of critical information about customers
(segmentation and behavioural variables of customers and their propensity to default on a
loan) can be incorporated and enabled in the design of a data warehouse architecture (500
Task 2 (Worth 30 marks) consists of the following sub tasks
With the following excel file sales provided on the CIS8008 S1 2012 course study desk
produce the four following reports with appropriate accompanying graphs based on a pivot
table for each report and briefly comment on each report in about 125 words in terms of what
trends and patterns are apparent in each report.
The sales.xlsx file contains the following dimensions and information:
5. Sales by month
A) Create a report and accompanying graph using a pivot table that lists product by region in
order of the top selling product for the year 2008 and comment on key trends and patterns in
this report (125 words approx)
B) Create a report and accompanying graph using a pivot table that lists by region in order of
the top performing sales person for the year 2009 and comment on key trends and patterns
in this report (125 words approx)
C) Create a report and accompanying graph using a pivot table that lists by product in the
order of the top customer for years 2008 and 2009 and comment on key trends and patterns
in this report (125 words approx)
D) Create a report and accompanying graph using a pivot table that lists the top five selling
products by region and by sales person and comment on key trends and patterns in this
report (125 words approx)
Your assignment 2 report must be structured as follows, which is similar to the report
structure detailed in Summers & Smith 2010:
1. Cover page for assignment 2 report
2. Introduction (250 words)
3. Body of report – main sections and subsections for assignment 2 task and sub tasks so
Task 1 will be a main heading with appropriate sub headings etc….for each sub task etc..
4. Conclusion (250 words)
5. List of References
Harvard referencing resources
Install a reference tool (example Endnote) which integrates with your word processor. These
tools are a great help for referencing and citing sources in your assignments. For more
information on how to get Endnote you may visit the following webpage:
Originality of submission
This assignment must be the expression of your own work. It is acceptable to discuss
course content with others to improve your understanding and clarify requirements,
but solutions to assignment questions must be done on your own. This also means that
it is not sufficient to merely paraphrase the entire assignment content from a textbook
or other source. Your assignment answers need be a reflection and synthesis of your
research of the associated topics. This should be evident in your annotated
You need to demonstrate your understanding of associated topics for each assignment.
You must not copy from anyone, including tutors and fellow students, nor provide
copies of your work to others. Assignments that do not adhere to this requirement will
be deemed as being the result of collusion or plagiarism. This may lead to severe
academic penalties as outlined in Academic Regulation 5.10 of the USQ Handbook. It
is your own responsibility to ensure the integrity of your work. Refer to the Faculty of
Business policy for further details.
An indiscriminate overuse of incorrectly referenced or cited web pages in your
assignment will result in poor marks.
Late submission of assignment work
Late assignments will be penalised unless permission has been granted by the course
leader BEFORE the assignment due date. Late assignments may be penalised by
reducing the allocated mark by 5% per day late.
a) Page size should be A4. Allow for TOP MARGIN – 2.5 cm; BOTTOM MARGIN –
2.5 cm; SIDE MARGINS – 2.5 cm
b) Major headings should be in upper case, 14 point Times New Roman Bold
c) Minor headings should be in lower case, 12 point Times New Roman Bold
d) Body text should be formatted to 12 point, Times New Roman
e) Use single or one and half line spacing
f) Assignments should be in one of the following formats:
(1) Microsoft Word (2003)
(2) Microsoft Word (2007)
(3) Rich Text Format (RTF)
(4) Portable Document Format (PDF)
2) The file naming guidelines are as follows:
a) It is important that you use this convention to ensure that assignments can be tracked:
b) Replace ‘familyname’ with YOUR family or surname
c) Replace ‘studentnumber’ with YOUR student number (which starts with ‘00’)
3) BEFORE submitting your FINAL version of the assignment to the online submission
system (EASE) you must upload your draft copies to the online TURNITIN system (see
the course Study Desk for details on accessing and using the TURNITIN system).
a) TURNITIN produces an Originality Report that shows your overall similarity index.
If the index is less than 25 percent, your assignment is considered to be plagiarism free.
If not, you will have to rework your assignment, using the feedback in the
Originality Report as a guide, and then resubmit it to TURNITIN for another round of
4) Once you have obtained a plagiarism-free assignment you should upload your Originality Report along with the FINAL version of your assignment through the online assignment
submission system (EASE). No hardcopy submissions will be accepted. See
the course Study Desk for instructions on uploading your assignment
Recent financial crises in the United States and the European Debt crisis have created doubts in the financial systems and financial services industry has grown far more risk averse than before. The risk identification, risk mitigation and risk monitoring procedures have been modified and the whole industry has been rebuilt. The main reason for the crisis was exposure to market risk and credit risk, and operational risk to a certain extent. The financial institutions failed to identify and more importantly measure the risk they are exposed to, as a result of which most of the industries across the world have been badly affected.
Therefore, it is necessary for a financial institution to have a rigid risk management policy so as to minimize the losses from the risks that are inherent in their business. This report aims to highlight the challenges faced by the institutions in assessing the credit worthiness of the borrower and reviews the literature associated with the credit risk and credit appraisal techniques. Further statistical analysis is performed over a data set obtained in a survey based on the credit rating and loan application details for retail customers. The report then focuses on the data warehouse architecture which essential for the day to day operations of the financial institutions.
Challenges faced in determining credit worthiness of a loan applicant
The business model of most of the financial intermediaries is to borrow from those who have surplus funds and to lend to those who are in deficit of funds. Such business model is exposed to different kinds of risks like, credit risk, maturity mismatch, market risk etc. However, our focus for this report is the inherent credit risk and in order to minimize the risk the financial institutions need to have a rigid process of scanning the applications and analyzing the credit risks associated with the applicant. One with a lower credit risk is preferred as there would be less amount of loss for the lending institutions in case of default (Limsombunchai V, Gan C, and Lee M). Credit worthiness of a borrower is determined by the credit appraisal process through which all the risks associated with the borrower are identified. Through this process every borrower is assigned a credit rating which also helps the institution in pricing the loan. In the industry there are different credit rating agencies that provide ratings to all kind of borrowers, mainly institutional. However, for retail borrowers the financial institutions have to provide a rating through internal rating methods.
Credit appraisal helps us in quantifying the risk associated with the borrower. The credit appraisal process for a retail borrower is different from that of an institutional borrower. It must be noted that in case of retail borrower the information available with the lending institution is basically the sources of income and ability/willingness of the borrower to repay the loan. On the other hand in case of an institution a step wise process is followed in order to provide a rating which then is used in pricing the loan. The Basel Committee of Bank of International Settlements has provided guidelines for risk management which focuses mainly on the capital adequacy of the banks. It came into existence in 1974 by the central-bank governors of ten countries (Bank of International Settlements). Its main focus has been to develop standards which form the base of evaluation of a bank’s performance. Its standards have been regularly modified in order to address the new risks and concerns arising in the market. The capital adequacy framework designed by the committee aims at reducing the loss for a bank in case of default and ensures that there is adequate capital to return the money aken from the depositors.
The Basel II accord is a modified set of standards which consists of three main pillars that govern the framework, namely, capital requirements, centralized supervision and market discipline (Decamps Jp, Rochet Jc, Roger B, 2002). Our focus is towards the first pillar which deals with the capital adequacy requirements. Capital Adequacy ratio (CAR) is given as (maxi-pedia):
CAR=(Tier 1 Capital+Tier 2 Capital)/(Risk Weighted Assets×8%)
Where, Tier 1 capital comprises of the bank’s core capital, Tier 2 capital comprises of supplementary capital like, revaluation reserves, hybrid debt instruments etc. and The Risk Weighted Assets represents the total assets that the bank owns weighted according to the risk they are exposed to. In order to calculate the risk weighted assets the committee has classified assets into 12 different classes like claims on retail, coprorate, SME, sovereign etc. Each class has different risk weights which are assigned to them with respect to the risk they are exposed to.
For any financial asset, the credit risk is determined by the three main variables (Altman EI):
Probability of Default (PD
Loss Given Default (LGD) : calculated as (1- Recovery Rate (RR) )
Exposure at Default
Figure 1: One Year Transition Probability matrix of Moody’s(94) and S&P’s (95) (Elton EJ)
The above figure shows the transition matrix of two rating agencies Moody’s and Standard and Poor’s, which indicates the probability of transition of a borrower from one rating to other. Thus, it shows whether the credit quality of a borrower has improved or declined. The last column shows the percentage of borrowers of a particular rating have defaulted that year. This is a very useful matrix as the value of PD from the table can be used to calculate the expected loss (EL) for the bank.
This Expected loss helps the bank to identify how much additional capital it needs in order to cover the loss.
As stated earlier the credit ratings are normally awarded by external agencies like Standard & Poor’s. However, an effective internal rating model is needed for financial institutions to process loan applications. Most of the corporate avail loan for financing their projects, and in such cases it is not only important to assess the financial performance of the company but also to analyze the project and check whether the revenue generated from the project will be able to service the debt. Credit risk in such cases can be minimized by negotiating with the borrower in terms of payment period or the rate of interest.
After assessing the credit worthiness of the borrower and once the loan is sanctioned it is necessary for the institution to recover the amount. The total amount of defaults is reflected by the Non-Performing Assets of the lending institutions, in order to prevent losses the institutions make some provisions (i.e. keep some reserve capital as a backup). However, the risk weight associated with the asset increase subsequently as a result of which higher capital is required to cover the losses.
Hence, the process of assessing the credit quality of a borrower is one of the main factors in the process of accepting a loan approval. It is important for the institution to identify, mitigate and monitor the credit risk that the business is exposed to.
In order to determine the main variables those are required to evaluate the credit quality of a borrower the Rattle tool was used in order to perform an exploratory analysis using the given dataset. Principal component analysis was done in order to find the major variables that determine the nature of the credit risk. Out of the 22 variables only 12 variables were used in the analysis, out of which only 5 were chosen, they are variable 4-history, variable 5- purpose, variable 6-amount, variable 13-property, variable 18- job. These variables were chosen as they contributed to the explanation of the first few principal components as shown in the table below:
PC1 PC2 PC3 PC4 PC5 PC6 PC7 PC8 PC9 PC10 PC11 PC12
history 3% -55% 39% -8% 9% 12% -5% 15% -11% -13% -21% 64%
purpose 4% 1% -25% -72% 3% -36% 14% 29% 34% -19% 8% 13%
amount 46% 16% 5% -5% 31% 9% -36% 9% 36% 53% -33% 4%
employed 13% -38% -7% 9% 15% -38% 49% -53% 14% 34% 2% 2%
instalp -1% -24% -29% 34% 7% -62% -42% 33% -24% 7% 8% 1%
coapp -18% 0% -32% 16% 75% 31% 26% 26% -3% -1% 21% 9%
property 58% 9% 0% 6% -19% 11% 6% 6% -11% 8% 70% 29%
other -15% 2% 54% 34% -2% -19% 19% 37% 54% -1% 22% -14%
Housing 41% -20% -18% 14% -23% 12% 45% 45% -12% -7% -40% -28%
excred 5% -57% 14% -30% 11% 22% -25% 2% -3% 8% 31% -58%
job 45% 12% 21% 8% 40% -21% -8% -24% -4% -66% -8% -17%
depends 3% -28% -45% 28% -21% 25% -23% -17% 58% -32% 1% 10%
The importance of each principal component extracted is shown in the figure below:
Variable 4- History:
One of the main procedures in the screening process of loan applications is to check the past credit information of the borrower which will show a clear picture regarding the instances where the borrower has defaulted. A borrower with numerous defaults in his past is considered as highly risky borrower, on the other hand one with less or no defaults is considered to be a low credit risk exposure for a lending institution. In cases where the borrower delays the payments, for example, a payment is due in April but the borrower pays a month after the due date., he/she will not be termed as a defaulter as he has paid the installment, however, it will be indicated as a late payment which has an adverse effect on one’s credit rating. The data set provided consists of participants, most of them who have duly paid their existing credits. The histogram below shows the distribution of the variable among the participants:
However, where there is no credit history, payments of bills are used as an alternative parameter (valoans).
Variable 5- Purpose
Loans are sanctioned to a borrower only for specific purposes, as can be seen in the creditdata-dictionary document, there are only few purposes listed for this study. Therefore, it is clear that financial institutions do not lend for any random purpose even though the borrower has a high credit rating. For instance, loans are not given for gambling or for speculation or investing in stock markets, as such investments are highly risky and do not guarantee any kind return for the borrower, but in case of home loans, the borrower can build a house and generate income from it. Most of the loans for retail customers are classified as Personal Loans, Housing loans or Educational Loans for students. However, in case of corporate bodies, loans are provided mainly for projects mostly related to manufacturing sector. The importance of the purpose of the loan for credit quality of a borrower is that, it helps the lender the nature of the loan, as in is it a working capital loan or a long term loan. Depending on this the pricing of the loan and the repayment structure is designed. Moreover, the credit risk involved with a short term loan is lesser than that involved with a long term loan.
Variable 6- Amount
Another major factor that affects the credit quality is the amount of the loan sanctioned. It is not always possible for bank or any other lending institution to sanction the whole amount one applies for. Every loan amount has some part of margin money contributed by the borrower and the rest is sanctioned in phases as and when needed by the borrower. The amount sanctioned to a borrower is decided depending on various factors which also determine the credit rating of the borrower. The amount and the term of the loan decide the repayment schedules for the borrower. For a bank every loan sanctioned consists part of the bank’s capital and the depositor’s money. It is important for the bank to sanction an amount which the borrower has the ability to repay and has collateral to provide as a backup in case of default. The amount also signifies the amount of exposure that the bank will have and proportionately the expected loss will increase result in higher capital required.
Variable 13- Property
Every loan needs to be supported with some kind of collateral as it serves as a backup for the bank in case of default. The property that the borrower owns also helps in determining the amount that can be sanctioned. The collateral value is calculated and used to assign Risk Weight to the asset and indirectly affects the capital that is required from the bank to pump in. Credit quality of a borrower is determined by the value of property one owns. In order to recover the sanction amount in case of a default the bank has to calculate the value of the collateral which would be covering only a part of the total amount. The credit risk exposure and the collateral are inversely related to each other as more is the value of the collateral; less is the exposure for the bank.
Variable 18- Job:
Out of all the variables, Job is the main parameter that affects the credit quality as it is the main source of income for most of the households. Source of income for an individual is similar to the revenues generated by an organization, as it is from this income that the individual will meet all his expenditure. A constant source of income is preferred as it indicates that there is a very probability of default for that individual. In cases of small case businessmen, where the source of income is not of robust nature, it becomes difficult for the bank to sanction large amount of loans as it will lead to a higher credit exposure. Credit exposure can be limited by properly assessing the income and expenditures of the borrower and the income left will be the indicator of the debt servicing ability.
Data Warehouse Architecture
It is necessary to have a sound infrastructure in a financial institution as lot of data is exchanged across the branches of the banks as well as other institutions. The effects of financial markets need to be reflected in the systems of the institutions, any decision like interest rate cuts should be incorporated immediately and the systems should be able to reflect the change. Financial Institutions deal with a large chunk of data, and in order to ensure proper storage and retrieval, it is necessary to have a data management system installed in the system. One such solution to the data management is to create a Data Warehouse whose work would mainly revolve around collecting and storing data and to distribute it as and when the request arises (Oracle).
On a daily basis large amount of data is pumped in and out of the system in a financial institution and most of the times analytical process are run in the system. As discussed in the previous sections, the credit appraisal process requires lot of calculations and projections. Mostly, it deals with lots of data transmission in both the direction with various parties. Oracle has done a similar study in order to provide a better data warehouse system for financial institutions. In order to incorporate the improved data capturing of the critical information the architecture must include the following points:
A well developed Data model is necessary to accumulate different types of data for different requirements of the organization. This can be enabled designing efficient data sourcing models such that the ETL process can be conducted smoothly. Moreover, the data model should also be able to incorporate proper reporting models such that the results of the analytical processes in the institutions are standardized.
The design of the data warehouse architecture should be such that the analytical processes are fully supported by the active data warehouse. Active data warehouse ensures the access of data in real time and also ensures effective control of data management systems in the organization. The focus here is to provide end-to-end support to the analytical procedures in the system.
One of the most important designing parameters of the data warehouse architecture is designing the capability of large storage volumes and ensuring higher scalability. Such architecture would help in increasing the efficiency of the data management system and would also decrease cost. Another benefit of such architecture is integration of various system functions with the data management system.
Therefore, the process of credit appraisal does not end with collecting important data, rather the most important work of analyzing the data and providing the borrower a particular rating is done with the help of the IT infrastructure in the organization, and it becomes important to have flexible, updated, active data warehouse installed in the system so as to ensure large amount of data processing and storage in the system.
Pivot Table Exercise
Sum of 2008 Column Labels
Row Labels Glue Guns Transponders Light Sabres Grand Total
South 120532 122228 112626 355386
East 129934 104517 87462 321913
North 99037 81302 84242 264581
West 87507 100204 60953 248664
Grand Total 437010 408251 345283 1190544
The above table and figure show the region wise sales for the three products. It is clearly visible from the graph that except for the west region, the product Glue guns has very hgh demand relative to other products. Out of all the regions, South contributed maximum to the sales (29.85% of the total sales). The demand for the products in the southern and eastern regions is very high as they collectively account for more than 55% of the total sales. The highest selling product Glue Guns contributes to almost 37% percent of the total sales. It is necessary to promote the products in the regions with low demand as it would benefit the bottom line of the company.
Sum of 2009.00 Column Labels
Row Labels Luke Skywalker Chewbacca James Kirk Hansolo Grand Total
South 174628 160543 166312 134701 636184
East 161051 126177 143460 152246 582934
North 80042 128453 125849 136157 470501
West 168125 106402 84776 86036 445339
Grand Total 583846 521575 520397 509140 2134958
Similar to the previous answer, the table and the graph in the picture represent the product sales by every employee for every region. The employees are arranged in a descending order, from left to right in the table. The graph clearly shows that in the north, the salesperson Luke was not able to sell the products at the same level as that of other regions contributing only about 14% of the total sales. It can be seen that there has been a close competition with almost everyone contributing to more than 24% of the total sales. As explained in the previous question there is comparatively less demand in the West
Sum of 2008 Sum of 2009.00
Row Labels Glue Guns Light Sabres Transponders Glue Guns Light Sabres Transponders
Enterprise 163133 146602 136756 284918 262634 245167
Galaxy 144049 107038 145061 266026 192202 262439
Planet 129828 91643 126434 227633 168577 225362
Grand Total 437010 345283 408251 778577 623413 732968
The above table and graph represent the sales of products by region with respect to the customer type. The sales of all the products combined have increased by 80% (approx.) from year 2008 to 2009. Product Glue Guns has been the top most selling product for both the years, however, the sales of Light sabres have increased at a slightly faster rate than others. Out of the three customer types, Enterprise customers have bought more than one-third of the total products sold and in all cases the purchase of the goods Glue Guns is the highest, and transponders being the least demand for the customers as a whole.
Sum of Total Sum Column Labels
Row Labels Glue Guns Transponders Light Sabres Grand Total
Luke Skywalker 367628 297185 242329 907142
Chewbacca 278153 354123 182445 814721
James Kirk 282138 261104 268902 812144
Hansolo 287668 228807 275020 791495
Grand Total 1215587 1141219 968696 3325502
The above table and graph represent the list of the highest selling products by region and by salesperson. It is difficult to show the graphs for all the combinations of regions and salesperson, therefore, only the graph with all the regions combined has been shown. The graph clearly shows that Glue guns is the highest selling product across the all the regions as well as for all the salesperson except Chewbacca who sold more of Transponders. As seen earlier Luke is the topmost salesperson.
The report has addressed many issues related to the credit risk of a financial institution. It is important to design a proper risk management policy such that the losses due to a crisis can be minimized. It is understood that banking and financial services is a such a type of industry which is exposed to all kinds of risks and, therefore, it becomes difficult to manage these risks. However, organizations across the world have started taking stringent measure in order to avoid yet another meltdown in the industry.
The statistical analysis performed on the data set helped us in identifying five main variables out of twenty-two used in the survey which affect the credit quality of a retail borrower. The variables are history, purpose, amount, property, and job, these five variables play a vital role in the credit appraisal process of a borrower. It must be noted that the list of variables affecting the credit rating is not an exhaustive list as there are many qualitative parameters which are difficult to quantify and cannot be incorporated in the model.
The data warehouse architecture designed specifically for financial institutions’ credit appraisal process should concentrate on providing faster and better models. The analytical procedures of the financial institutions require an active data warehouse which has the ability to increase scale as well as store large volume of data.
Bank of International Settlements, ‘ History of the Basel Committee and its Membership’, viewed 17 April 2012 <http://www.bis.org/bcbs/history.htm>
Decamps Jp, Rochet Jc, Roger B, 2002, ‘ The Three Pillars of Basel II: Optimizing the Mix in a Continuous-time Model’, viewed 17 April 2012, <http://www.bis.org/bcbs/events/b2earoc.pdf>
Limsombunchai V, Gan C, and Lee M, ‘ Lending Decision Model for Agricultural Sector in Thailand’, viewed 17 April 2012 <http://www.mssanz.org.au/modsim05/papers/limsombunchai.pdf>
Oracle Financial Services, 2011, ‘ Evolving the Data Warehouse: The Next Generation for Financial Services Institutions’, viewed 17 April, 2012 <http://www.oracle.com/us/industries/financial-services/evolving-datawarehouse-wp-400257.pdf>
Valoans, ‘VA Loan Guidelines’, viewed 17 April, 2012, <http://www.valoans.com/va_facts_credit.cfm>
Elton EJ, Figure 1: One Year Transition Probability matrix of Moody’s(94) and S&P’s (95), viewed 17 April 2012, <http://pages.stern.nyu.edu/~eelton/working_papers/corp%20bonds/all%20tables%20and%20figures%201.pdf>
Maxi-pedia, ‘Capital Adequacy Ratio (CAR)’ , viewed 17 April 2012 < http://www.maxi-pedia.com/capital+adequacy+ratio+CAR>
But you can order it from our service and receive complete high-quality custom paper. Our service offers Business essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”