1 Appendix
1.1
CRSA report
The Capital Requirements to the Standardised Approach report is still too large to place it
readable on one page. Therefore it was split up in two pages.
This sheet is used for 1 exposure class,within Basel II there are 15 classes. In the upper right
corner the capital requirements clearly appear. Also the off and on-balance sheet items can be
found in the sheet. .
1.2
IT requirements explained
This appendix gives insight in the consequences of the requirements set by stakeholders. It
primarily explains the information needed to fulfil the requirement. Some technical aspects are
described, but kept to a minimum. The tables in this appendix form one half of the GAP analysis
performed at the Information Architecture Chapter. When laying the requirements over the
provision a GAP could be found (TOGAF, 2007).
The tables show the desired uniform information. Thus the field names as they should be in the
whole TA and the description thus also counts for all systems in the TA. The field type in
combination with the valid values form the basis for the lexicon mentioned in the research
questions.
The numbers behind the headings refer at the number of the requirements in Error! Reference
source not found..
1.2.1 Executive board (EB) (7)
The EB, as head of the organization, carefully checks on the performance of the BU’s. The
information that the EB could use is:
• Total amount of offers accepted (this year)
• Total amount of valid offers sent
• The expected amount of offers accepted and paid out at the end of the year
• Margins on loans
To calculate the expected total amount of loans, the likelihood of success should be recorded. The
UK, NL and BE BU currently record the likelihood. Yet all in different formats, varying from
percentage to letters.
Field name Description Field type Valid values
Business Unit The abbreviation (first 2 letters) of the Business unit of the offer
Text (category) NL, BE, ES, UK (and in future DE)
Total amount of loan The total amount of loan: current account + loan account Currency Larger than 0
Likelihood of success
Likelihood of success that an offer will be accepted Percentage Between 0 and 100%
Currency The currency of the loan Text (category) EUR, DOLLAR, GBP
Status offer The status of the offer Text (category) Sent valid, sent invalid, agreed, paid, closed
Offer date
Date that the offer is sent to the customer
Date Later or equal to today (day information created)
Date agreed Date that the offer is returned with autograph of customer Date
Later than offer date and earlier than valid date
Date rejected Date that the customer rejects the offer Date
Later than offer date and earlier than valid date
Valid true date Date till offer gets invalid Date Later than offer date
Either an offer is rejected or it is accepted. The inclusion of both could lead to information on the
types of offers that are rejected or accepted. This could be done best when the additional
information underneath is added.
In addition the Triodos Bank specific sector and sub sector categories could be added to the
report, since that could compare the performance in the different markets.
Field name Description Field type Valid values
Sector The sector according to Triodos Bank standards Text (category) Max 1 symbol, only a letter
Subsector
The subsector according to Triodos Bank
standards Text (category)
Max 2 symbols, first always a letter second always a number, e.g. N1
A real nice to have could be the addition of Basel II risk exposure information to the report. This
could give insight in the possible capital requirements if offers would be accepted. This way
forecast can be done for the max growth of the portfolio.
Field name Description Field type Valid values Exposure class Basel II exposure class Numeric 1 to 15
Risk weight Risk weight in percentage according to the properties Percentage 0 to 200%
Credit rating The credit rating institution that rated the customer Text (category) Selected credit rating institutions according to Basel II Credit rating value the value according to the credit rating Text (category) Values for this credit rating
Duration of facility The duration of the facility in months Numeric At least higher than 0
The last nice to have would be the addition of margins on loans.
Field name Description Field type Valid values
Amount Current
Account The amount of current account loan Currency
larger or equal to 0, but always sum of Current Account and Loan Account is greater than 0
Amount Loan
Account The amount of current account loan Currency
larger or equal to 0, but always sum of Current Account and Loan Account is greater than 0
Suggested margin current account
The suggested margin according to the Excel spreadsheet model Decimal
between 0 and 1
Agreed margin
current account The agreed margin Decimal
between 0 and 1
Suggested margin credit loan
The suggested margin according to the Excel spreadsheet model Decimal
between 0 and 1
Agreed margin
credit loan the agreed margin Decimal
1.2.2 Auditable process (8)
The goal of the auditable process is to recover sources of errors when reporting goes wrong. If for
example an offer appears to have an abnormal interest rate, then auditability should lead to the
source of information creation, namely the loan manager or account manager.
In principle it is possible to log every modification, creation or deletion of information by every
user. The author believes this extensive way of logging has marginal added value. Traceability of
information to the client or account manager in the BU would sufficient. Therefore also the client
name should be recorded.
Field name Description Field type Valid values
Business Unit The abbreviation (first 2 letters) of the Business unit of the offer
Text (category) NL, BE, ES, UK (and in future DE)
Account manager Name of the account manager Text Free field to fill in name, could also be automatically stored when the application has login abilities.
Client name Name of the client (preferably like in Triton) Text Not null
1.2.3 Maturity report (11)
In the table underneath, the required information for the maturity report can be found. Important
addition compared to the other reports is that in this report the total amount at best should be split
up into Current Account (CA) and Loan Account (LA).
Field name Description Field type Valid values
Business Unit The abbreviation (first 2 letters) of the Business unit of the offer
Text (category) NL, BE, ES, UK (and in future DE)
Status offer the status of the offer Text (category) Sent valid, sent invalid, agreed, paid, closed
Offer date
Date that the offer is sent to the customer
Date Later or equal to today (day information created)
Date agreed Date that the offer is returned with autograph of customer Date
Later than offer date and earlier than valid date
Amount Current Account The amount of current account loan Currency
Larger or equal to 0, but always sum of Current Account and Loan Account is greater than 0
Amount Loan Account The amount of current account loan Currency
Larger or equal to 0, but always sum of Current Account and Loan Account is greater than 0
Duration of facility The duration of the facility in months Numeric Larger than 0 Type of repayment E.g. bullet / annuity Text (category) Bullet, Annuity
Interest renewal date The date that the interest for the loan is reviewed and renewed Date
Lager than offer date
If the split up of the loan is not possible the total amount of loan could also give insight.
Field name Description Field type Valid values
Total amount of loan The total amount of loan: current account + loan account
1.2.4 Margin report (12)
Also this report required a separation in current account and loan account. This report could be
drawn for both accepted as well as not accepted offers. This could reveal whether the margin has
influence on the acceptance.
Field name Description Field type Valid values
Business Unit The abbreviation (first 2 letters) of the Business unit of the offer
Text (category) NL, BE, ES, UK (and in future DE)
Status offer The status of the offer Text (category) Sent valid, sent invalid, agreed, paid, closed
Offer date
Date that the offer is sent to the customer
Date Later or equal to today (day information created)
Date agreed Date that the offer is returned with autograph of customer Date
Later than offer date and earlier than valid date
Date rejected Date that the customer rejects the offer Date
Later than offer date and earlier than valid date
Valid true date Date till offer gets invalid Date Later than offer date
Amount Current
Account The amount of current account loan Currency
Larger or equal to 0, but always sum of Current Account and Loan Account is greater than 0
Amount Loan Account The amount of current account loan Currency
Larger or equal to 0, but always sum of Current Account and Loan Account is greater than 0
Suggested margin current account
The suggested margin according to the Excel spreadsheet
model Decimal
Between 0 and 1
Agreed margin current
account The agreed margin Decimal
Between 0 and 1
Suggested margin credit loan
The suggested margin according to the Excel spreadsheet
model Decimal
Between 0 and 1
Agreed margin credit
loan the agreed margin Decimal
Between 0 and 1
Also for this report counts, that at best the loan could be split up but if this is not possible the
total loan should be sufficient.
Field name Description Field type Valid values
Total amount of loan The total amount of loan: current account + loan account Currency Larger than 0
Agreed margin The agreed margin Decimal Between 0 and 1
Suggested margin The suggested margin according to the Excel spreadsheet model Decimal
1.2.5 Large loans report (3)
The table underneath shows the requirements for the large loans report. This is to connect the
offers of the loans to the loans in the ERP system before aggregation can be done. Important to
mention is that this report contains primary keys from the ERP system in order to aggregate the
loans over groups of debtors including irrevocable credit facilities.
Field name Description Field type Valid values
Business Unit The abbreviation (first 2 letters) of the Business unit of the offer
Text (category) NL, BE, ES, UK (and in future DE)
Client name Name of the client (preferably like in Triton) Text Not null
Triton Debtor ID The Unique number of the debtor in the ERP system Numeric Only values valid that exist in the ERP system
Triton Relation ID The Unique number of the relation in the ERP system Numeric Only values valid that exist in the ERP system
Triton group ID The Unique number of the group of relations in the ERP system
Numeric Only values valid that exist in the ERP system
Status offer The status of the offer Text (category) Sent valid, sent invalid, agreed, paid, closed (only
Offer date
Date that the offer is sent to the customer
Date Later or equal to today (day information created)
Date agreed Date that the offer is returned with autograph of customer Date
Later than offer date and earlier than valid date
Valid true date Date till offer gets invalid Date Later than offer date Total amount of loan The total amount of loan: current account + loan account Currency Larger than 0
1.2.6 Basel II CRSA report information (1)
The following information is required from the pre-lending applications to compile the Basel II
CRSA report. Note that for the total CRSA report far more information is required. Important to
mention is that some Basel II specific information currently not exists in any system.
Field name Description Field type Valid values Business Unit The abbreviation (first 2 letters) of the Business unit
of the offer
Text (category) NL, BE, ES, UK (and in future DE)
Customer Name The name of the customer Text Max 50 symbols
Sector The sector according to Triodos Bank standards Text (category) Max 1 symbol, only a letter
Sub sector The sub sector according to Triodos Bank standards Text (category)
Max 2 symbols, first always a letter second always a number, e.g. N1
Total amount of loan The total amount of loan
Numeric, 2 decimal places
In case of the raise of a current loan, this amount only concerns the raise, not the full loan
Currency The currency corresponding with the amount of loan Text (category) EUR, GBP, DOLLAR,
Status offer The status of the offer Text (category) Sent valid, sent invalid, agreed, paid, closed
Offer date
Date that the offer is sent to the customer
Date Later or equal to today (day information created)
Date agreed
Date that the offer is returned with autograph of
customer Date
Later than offer date and earlier than valid date
Date rejected Date that the customer rejects the offer Date
Later than offer date and earlier than valid date
Valid true date Date till offer gets invalid Date Later than offer date Total amount of loan The total amount of loan: current account + loan
account
Currency Larger than 0
Exposure class Basel II exposure class Numeric 1 to 15
Risk weight Risk weight in percentage according to the properties Percentage 0 to 200%
Credit rating The credit rating institution that rated the customer Text
Selected credit rating institutions according to Basel II
Credit rating value the value according to the credit rating Text Values for this credit rating Duration of facility The duration of the facility in months Numeric At least higher than 0
It is possible that an offer is made to an existing customer; the offer could concern a raise in loan.
If the net value of the collaterals accepted for credit risk mitigation would exceed the amount of
on-balance exposure, the remaining part of the value of the collateral can be used for the offered
loan.
Due to the fact that there is (currently) no link between the systems these existing collaterals
cannot be taken into account at current offers, but it will be taken into account for the agreed
offers. This is acceptable as the validity of the offer is in most cases no more than 1 month, and
the risk weight for outstanding offers is lower than the risk weight for on-balance exposure (i.e.
50% vs. 100%).
1.2.7 Upcoming payouts (15)
The following information is needed to efficiently manage the payouts. The account manager and
client name are added to check on the payment and to enable faster handling.
Field name Description Field type Valid values
Business Unit The abbreviation (first 2 letters) of the Business unit of the offer
Text (category) NL, BE, ES, UK (and in future DE)
Account manager Name of the account manager Text Free field to fill in name, could also be automatically stored when the application has login abilities.
Client name Name of the client (preferably like in Triton) Text Not null
Status offer The status of the offer Text (category) Sent valid, agreed, paid Total amount of loan The total amount of loan: current account + loan account Currency Larger than 0
Currency The currency of the loan Text (category) EUR, DOLLAR, GBP
Amount paid out The total amount of the partial pay outs Currency
Smaller than total amount of loan
Amount partial pay out (1…n) The amount of an individual pay out (all of them to be stored) Currency
Larger than 0 smaller than total amount of loan
Date of partial pay out (1…n) The date that the payment is taking place Date
Later than yesterday (related to date entered)
Type of payment Whether the money is placed outside the bank Text (category)
Inbound, outbound
At best the Triton information should be added to the report in order to enable faster entry in
Triton.
Field name Description Field type Valid values
Triton Debtor ID The Unique number of the debtor in the ERP system
Numeric Only values valid that exist in the ERP system
Triton Relation ID The Unique number of the relation in the ERP system
Numeric Only values valid that exist in the ERP system
Triton group ID The Unique number of the group of relations in the ERP system
1.3
Information provision from the pre-lending systems
In this part of the appendix the information provision from the pre-lending systems is described.
The provision from the other systems are not described, since they are not pre-lending systems.
Only the front end information is shown, the back end information like primary keys to link
tables are not shown, since the author believes that first this has marginal added value and second
this does not belong to the aggregation level of Enterprise Architecture, but merely SysA.
1.3.1 Spanish pre-lending system
The ES BU simply sends its Application_Y, thus the output file is exactly similar to the
pre-lending system.
1.3.2 [This section has been left out in the public version of this research, due to confidentiality
issues.] UK pre-lending system
In the tables underneath the UK information provision is displayed. A loan manager manually
makes the output file. As mentioned in the research, the information integrity and validity of the
UK spreadsheet is higher than the ES spreadsheet, since the UK uses the 4-eye principle to check
on validity.
[This section has been left out in the public version of this research, due to confidentiality
issues.]
In the table underneath the different variables for the likelihood of success are explained. As can
be seen in both tables, the likelihood cannot be multiplied with the amount of loan since it is a
letter.
Likelihood of success
A - very likely, no obvious competition B - likely and probable
C - possible, some difficult issues to resolve, competition exists D - declined, not possible under any circumstances
