Docstoc

Purpose Of this ument

Document Sample
Purpose Of this ument Powered By Docstoc
					                                          REQUIREMENT DOCUMENT                   Credit Enh 15 - LOS Analytics
                                                                                       in Datawarehouse




1. Preparation by
This document is to be prepared by Business Solutions Group based on the concept note received and subsequent
meetings with and inputs from users, operations, vendors, etc. It is to be signed off by Application Owners at BSG and IT
and thereafter forwarded to the Concept Initiator for signoffs by Functional Head of Business/Operations.

2. Profile
 Project Id:              Credit Enh 15

 Project Name:           LOS Analytics in Datawarehouse

 Business Group:         Retail Assets

 Project Manager:        Zenita Ichhaporia



3. Introduction
Scope and Overview: Enter Scope of the project/product and Objectives here.

The retail credit Policy team and the LOS team generate various MIS reports from LOS &
LMS for their monthly reporting on Risk and loan application processing. The reporting
can be broadly segmented into three parts:

      1) Pre-Disbursement Analysis : Process Team
      2) Post Disbursement Analysis : Policy Team
      3) Risk Intelligence & Control : RIC Team

Currently, all these reports are generated thru backend queries from LOS and DWH (for
finnone data) and data massaging is done outside LOS & LMS. These MIS are very
crucial for both the teams in reviewing /revisiting/redesigning policies and processes for
the Retail Loan products on a periodic basis.


4. Functional Requirements
Capture all the requirements that are required to execute the project. Please also mention the details with regards to a/c
entries, reports, advices, MIS and validation required, wherever applicable.

Reporting Requirements
a. Pre-Disbursement Analytics : Process Team




 b.    Post Disbursement Analytics : Policy Team




 c.    RIC Team : For RIC Analytics




Requirement Document Template                            Page 1                    Version Date 01/06/2004
                                   REQUIREMENT DOCUMENT             Credit Enh 15 - LOS Analytics
                                                                          in Datawarehouse




Product Wise Report Description for Policy Reports

Reports for Product: Personal Loan & Business Loan (PL / BL)

#    Report Name                 Description
1    Promotion scheme wise       Ageing done for various promotion schemes. Where ENR is Sum of
     Delinquency                 Principal Outstanding Amount. And Contribution to ENR = [Per
                                 Promotion Scheme (ENR) / Total ENR] * 100
2    Promotion scheme wise       Month wise Ageing done for various promotion schemes.
     early mortality rate        Where mortality means Failures to pay back installments.
3    Source wise Scheme wise     DSA wise Ageing done for various promotion schemes.
     Delinquency                 Where Source is Direct Sales Agent.
4    Scheme wise Ticket Size     Ticket Amt Range wise Ageing done for various promotion schemes.
     wise Delinquency            Where Ticket Size means various Slabs (Range of Amount) for each
                                 Promotion Scheme.
5    Segment wise Income wise    Segment wise Income wise Ageing done for various segments.
     Delinquency                 Where Income band contains Slabs (Range of Amount).
6    Segment Wise Sourcing       Approval percentage for various segments.
                                 Here “No of Cases” means Total Count of Disbursed Loans.
7    Scheme wise Sourcing        Approval percentage for various Schemes.
                                 Here “No of Cases” means Total Count of Disbursed Loans.
8    Employer wise               Ageing done for various Employers. Where ENR is Sum of Principal
     Delinquency                 Outstanding Amount. And Contribution to ENR = [Per Employer (ENR)
                                 / Total ENR] * 100
9    Tier wise Delinquency       Ageing done for various Tiers. Where Tier is similar to Zone eg. East,
                                 West, etc.
10   Tier wise Cheque bounce     Percentage Cheque bounces out of Count of Cheques presented for
     %                           every Tier.
11   Tier Wise Nonstarter        Percentage “Nonstarters” out of “No Of Cases Disbursed” for every
                                 Tier.
                                 Where Nonstarters are those cases whose 1st Installment is unpaid till
                                 the month end.
12   Deviation Level wise        Ageing done for various Deviation Level. Where ENR is Sum of
     Delinquency                 Principal Outstanding Amount. And Contribution to ENR = [Per
                                 Deviation Level (ENR) / Total ENR] * 100
13   Deviation wise              Ageing done for various Deviation Code. Where ENR is Sum of
     Delinquency                 Principal Outstanding Amount. And Contribution to ENR = [Per
                                 Deviation Code (ENR) / Total ENR] * 100
14   DSCR wise delinquency       Ageing done for various DSCR (Range of Ratios). Where ENR is Sum
                                 of Principal Outstanding Amount. And Contribution to ENR = [Per
                                 DSCR (ENR) / Total ENR] * 100.
                                 Also DSCR mean Dead Service Coverage Ratio.
15   FOIR wise delinquency       Ageing done for various FOIR (Range of Percentage). Where ENR is
                                 Sum of Principal Outstanding Amount. And Contribution to ENR = [Per
                                 FOIR (ENR) / Total ENR] * 100.
                                 Also FOIR mean Fixed Obligation into Income Ratio.
16   a) Turnover wise            Ageing done for Turnover (range). Where ENR is Sum of Principal
     Delinquency                 Outstanding Amount. And Contribution to ENR = [Per Turnover (ENR)
                                 / Total ENR] * 100.

     b) Ticket size wise         Ageing done for various Ticket Size (Range of size). Where POS is Sum

Requirement Document Template                  Page 2                 Version Date 01/06/2004
                                    REQUIREMENT DOCUMENT               Credit Enh 15 - LOS Analytics
                                                                             in Datawarehouse




     Delinquency                  of Principal Outstanding Amount for Open Account. And ENR Cont =
                                  Count of POS.
17   Underwriter wise             Region wise Ageing of Value and Units for all Underwriters in each
     delinquency                  region.
                                  Underwriters are the Checkers in Maker / Checker process.
18   ITR income wise              Segment wise Ageing done against range of Incomes of data regarding
     delinquency with city &      Income Tax Returns (ITR).
     time dimension               Where Income band contains Slabs (Range of Amount).
19   Promotion scheme             Region wise Early Mortality % and Disbursement Count generated for
     wise/region wise early       various IT promotion schemes.
     mortality rate               Where mortality means Failures to pay back installments.
20   Program wise early           Program wise Early Mortality % and Disbursement Count for each
     mortality rate               program. Where mortality means Failures to pay back installments.
21   Program wise monthly         Program wise Early Mortality Amt for each month. Where mortality
     comparison for early         means Failures to pay back installments.
     mortality
22   Program wise Sourcing        Approval percentage for various Programs.
                                  Here “No of Cases” means Total Count of Disbursed Loans.
23   Scheme wise Sourcing         Approval percentage for various Schemes.
                                  Here “No of Cases” means Total Count of Disbursed Loans.
24   Nonstarters Internal vs      Percentage “Nonstarters” out of “No Of Cases Disbursed” for every
     External                     Tier. Data with break up between Internal and External.
                                  Where Nonstarters are those cases whose 1st Installment is unpaid till
                                  the month end.
25   Scheme wise Nonstarters      Percentage “Nonstarters” out of “No Of Cases Disbursed” displayed for
                                  every Scheme.
                                  Where Nonstarters are those cases whose 1st Installment is unpaid till
                                  the month end.
26   Deviation wise Nonstarters   Percentage “Nonstarters” out of “No Of Cases Disbursed” displayed for
                                  every Deviation code.
                                  Where Nonstarters are those cases whose 1st Installment is unpaid till
                                  the month end.
27   Tier wise cheque bounce%     Percentage Cheque bounces out of Count of Cheques presented for
                                  every Tier.
28   Scheme wise Cheque           Percentage Cheque bounces out of Count of Cheques presented for
     bounce%                      every Scheme.
29   Ticker size wise Cheque      Percentage Cheque bounces out of Count of Cheques presented for
     bounce %                     every Ticket size.
30   Cheque bounce % based        Percentage Cheque bounces out of Count of Cheques presented for
     on reason ( ECS Not Hit      every Reason.
     etc..)
31   Reason wise/State wise       Region wise Reason data displayed against various Programs. Also
     scheme wise disbursement     displaying totals at various levels.
32   City wise /product wise      Month wise Ageing done on No. of Disbursed cases for the selected City
     Life Table (30+              and Product.
     delinquency)
33   CPV Wastage Report           Contact Point Verification (CPV) is done for the given month and
                                  Product for Delhi, Mumbai, Calcutta and All India (others) locations.
                                  Where,
                                  No of CPV fired = Count of Verified cases
                                  No Cases Disbursed = Count of successfully verification
                                  CPV Wastage = No of CPV fired minus No Cases Disbursed
                                  CPV Wastage % = (CPV Wastage / No of CPV fired) * 100
34   Approval to disbursement     Approval to disbursement ratio is calculated for the given month and
     ratio                        Product for Delhi, Mumbai, Calcutta and All India (others) locations.


Requirement Document Template                    Page 3                 Version Date 01/06/2004
                                      REQUIREMENT DOCUMENT             Credit Enh 15 - LOS Analytics
                                                                             in Datawarehouse




                                    Where,
                                    No of cases approved = Count of Approved Cases
                                    No Cases Disbursed = Count of successfully Disbursements
                                    Approved but not disbursed = Count of cases not disbursed.
                                    Approval/Disbursement ratio = (Approved but not disbursed / No of
                                    cases approved) * 100

Reports for Product: Small Ticket Personal Loan (STPL)

#    Report Name                          Description
1    Promotion scheme wise                Ageing done for various promotion schemes. Where ENR is Sum
     Delinquency                          of Principal Outstanding Amount. And Contribution to ENR =
                                          [Per Promotion Scheme (ENR) / Total ENR] * 100
2    Program Wise Delinquency             Ageing done for various Programs. Where ENR is Sum of
                                          Principal Outstanding Amount. And Contribution to ENR = [Per
                                          Program (ENR) / Total ENR] * 100
3    Ticket size wise Delinquency         Ageing done for various Ticket Size (Range of size). Where POS
                                          is Sum of Principal Outstanding Amount for Open Account. And
                                          ENR Cont = Count of POS.
4    Constitution wise Delinquency        Ageing done for various Constitutions (eg. Self Employed,
                                          Salaried, etc). Where POS is Sum of Principal Outstanding
                                          Amount for Open Account. And ENR Cont = Count of POS.
5    Promotion scheme wise/region         Region wise Early Mortality % and Disbursement Count
     wise early mortality rate            generated for various IT promotion schemes.
                                          Where mortality means Failures to pay back installments.
6    Program wise early mortality rate    Program wise Early Mortality % and Disbursement Count for
                                          each program. Where mortality means Failures to pay back
                                          installments.
7    Program wise monthly                 Program wise Early Mortality Amt for each month. Where
     comparison for early mortality       mortality means Failures to pay back installments.
8    Source wise Scheme wise              DSA wise Ageing done for various promotion schemes.
     Delinquency                          Where Source is Direct Sales Agent.
9    Scheme wise Ticket Size wise         Ticket Amt Range wise Ageing done for various promotion
     Delinquency                          schemes.
                                          Where Ticket Size means various Slabs (Range of Amount) for
                                          each Promotion Scheme.
10   Promo scheme wise Income wise        Segment wise Income wise Ageing done for various segments of
     Delinquency                          Promotion Scheme.
                                          Where Income band contains Slabs (Range of Amount).
11   Program wise Sourcing                Approval percentage for various Programs.
                                          Here “No of Cases” means Total Count of Disbursed Loans.
12   Scheme wise Sourcing                 Approval percentage for various Schemes.
                                          Here “No of Cases” means Total Count of Disbursed Loans.
13   Region wise Delinquency              Report not required
14   Region wise Cheque bounce %          Report not required
15   Region Wise & City wise              Percentage “Nonstarters” out of “No Of Cases Disbursed” for
     Nonstarter                           each Region or City. Where Nonstarters are those cases whose 1st
                                          Installment is unpaid till the month end.
16   Deviation Level wise                 Ageing done for various Deviation Level. Where ENR is Sum of
     Delinquency and LOCATION             Principal Outstanding Amount. And Contribution to ENR = [Per
     WISE                                 Deviation Level (ENR) / Total ENR] * 100
17   Deviation wise Delinquency           Ageing done for various Deviation Code. Where ENR is Sum of
                                          Principal Outstanding Amount. And Contribution to ENR = [Per
                                          Deviation Code (ENR) / Total ENR] * 100
18   Program wise Deviations              Deviations listed against various programs along with Grand

Requirement Document Template                     Page 4                 Version Date 01/06/2004
                                        REQUIREMENT DOCUMENT             Credit Enh 15 - LOS Analytics
                                                                               in Datawarehouse




                                           Total.
19   Program wise Status                   List of Status against various Programs along with Grand Total.
20   Program wise Credit Reject break      Break Up of Credit Reject displayed against various Programs
     up                                    along with Grand Total.
21   Program wise Deviation Break up       Break Up of Deviations displayed against various Programs along
                                           with Grand Total.
22   Pan India Program wise status         Region wise PAN data displayed against various Programs. Also
                                           displaying totals at various levels.
23   Pan India, region, state, city and    Region / State / City / Branch wise PAN data displayed against
     branch wise under process status      various Programs. Also displaying totals at various levels.
     program wise
24   Pan India , region, state, city and   Region / State / City / Branch wise PAN data displayed against
     br wise purchase pending              various Programs. Also displaying totals at various levels.
     program wise
25   Weekly report                         Pan India data wise report for Current Month, Last Month and As
                                           On Date is displayed with break up for each. The report will
                                           further break up into Regions.
26   Cost MIS – Branch and region          Region wise / Branch wise / Branch Credit Manager wise report
     wise                                  displayed with counts of various files and break up of various
                                           costs.
27   DEDUPE HIT RATIO MIS                  Region wise counts displayed against various Dedupe Hit for
                                           parameters (Fired / Good match / bad match). Report will also
                                           display Dedupe Savings.
28   Dashboards                            Breakup of Data Heads month wise.
29   Underwriter wise delinquency          Region wise Ageing of Value and Units for all Underwriters in
                                           each region.
                                           Underwriters are the Checkers in Maker / Checker process.
30   Risk Reward                           Branch / Location / DSA wise risk reward amt displayed with
                                           more parameters (eg. Category).
31   Employer wise Delinquency             Ageing done for various Employers. Where ENR is Sum of
                                           Principal Outstanding Amount. And Contribution to ENR = [Per
                                           Employer (ENR) / Total ENR] * 100
32   Tier wise Delinquency                 Ageing done for various Tiers. Where Tier is similar to Zone eg.
                                           East, West, etc.
33   Tier wise Cheque bounce %             Percentage Cheque bounces out of Count of Cheques presented
                                           for every Tier.
34   Tier Wise Nonstarter                  Percentage “Nonstarters” out of “No Of Cases Disbursed” for
                                           every Tier.
                                           Where Nonstarters are those cases whose 1st Installment is
                                           unpaid till the month end.
35   Nonstarters Internal vs External      Percentage “Nonstarters” out of “No Of Cases Disbursed” for
                                           every Tier. Data with break up between Internal and External.
                                           Where Nonstarters are those cases whose 1st Installment is
                                           unpaid till the month end.
36   Scheme wise Nonstarters               Percentage “Count of Nonstarters” out of “No Of Cases
                                           Disbursed” displayed for every Scheme.
                                           Where Nonstarters are those cases whose 1st Installment is
                                           unpaid till the month end.
37   Deviation wise Nonstarters            Percentage “Nonstarters” out of “No Of Cases Disbursed”
                                           displayed for every Deviation code.
                                           Where Nonstarters are those cases whose 1st Installment is
                                           unpaid till the month end.
38   Tier wise Cheque bounce%              Percentage Cheque bounces out of Count of Cheques presented
                                           for every Tier.
39   Scheme wise Cheque bounce%            Percentage Cheque bounces out of Count of Cheques presented

Requirement Document Template                      Page 5                 Version Date 01/06/2004
                                      REQUIREMENT DOCUMENT              Credit Enh 15 - LOS Analytics
                                                                              in Datawarehouse




                                          for every Scheme.
40   Ticker size wise Cheque bounce       Percentage Cheque bounces out of Count of Cheques presented
     %                                    for every Ticket size.
41   Cheque bounce % based on             Percentage Cheque bounces out of Count of Cheques presented
     reason ( ECS Not Hit etc..)          for every Reason.
42   City wise /product wise Life         Month wise Ageing done on No. of Disbursed cases for the
     Table (30+ delinquency)              selected City and Product.

General Explanation to some group of Policy Reports:

     1. Early Mortality Rate Group Reports:
         Early mortality for Jan 2008 MIS Report will be the no. of cases which have become NPA and
         disbursed in the month of Dec 2006. Similarly early mortality for Feb 2008 MIS report will be No.
         of cases which have become NPA as of Feb 2008 and disbursed in Jan 2007.

     2. Fixed Obligation into Income Ratio (FOIR) Report:
         Computation logic of FOIR = (Total Obligations + First EMI) / Income

         Where First EMI will flow from Data Warehouse, Obligation & Income is a readymade field from
         LOS.

     3. Life Table Group Reports:
         There will be 2 types of Life Tables (30+ cases and 90+ Cases). At any point of time the report
         will show 24 months data set and it is not a LTD report.

         Case:
          Age On Book           Jan'05           Feb'05




          No. Of Cases
          Disbursed in
          respective
          months and live
          as of March 08                  100                     120
                          0              0.00%                  0.00%
                          8              1.60%                  0.00%
                          9              1.60%                  0.56%
                         10              1.60%                  0.56%
                         11              3.19%                  2.78%
                         12              2.13%                  3.89%



         Description of comments in table:
         #: Out of 120 cases disbursed in Feb 2005, 2.78% cases have exceeded 30 DPD in the 11th month
         from Feb 2005. This is for Dec 2005.

         $: Out of 100 cases disbursed in Jan 2005, 2.13% cases have exceeded 30 DPD in the 12th month
         from Jan 2005. This is for Dec 2005


Requirement Document Template                     Page 6                  Version Date 01/06/2004
                                    REQUIREMENT DOCUMENT               Credit Enh 15 - LOS Analytics
                                                                             in Datawarehouse




Product Wise Report Description for Process Reports

Reports for Product: Unsecured Loan and Secured Loan

#   Report Name        Description
1   CR MIS -           It consists of Region & Location wise Unsecured Loan Counts for components
    Unsecured Loans    like:
                       [A] Approval MIS
                       [B] Decline MIS
                       [C] Deviation MIS (Level wise)

                       The above components are classified into Salaried and Professional and further
                       classified into Internal and External for each Unsecured Loan Product for e.g. PL,
                       STPL, etc.

                       Where
                       No. of Verifications = sum of CPV initiated and RECPV initiated (Possible values
                       for a LOS account is 0, 1 or 2). Identified basis CPV verification date or re-
                       verification date
                       No. of deviations approved = One deviation per account LOS extract will prioritize
                       and provide it basis a priority matrix. (Severity of Deviation basis approved cases)
                       Approved = Count where status is approved (field to be referred is final status -
                       decision)
                       Declined = Count where status is Declined
                       Total Processed = SUM (approved + declined)
                       WIP Cases = Exclude ( Blank , approved ,declined) status for WIP )
2   CR MIS -           It consists of Region & Location wise Secured Loan Counts for components like:
    Secured Loans      [A] Approval MIS
                       [B] Decline MIS
                       [C] Deviation MIS (Level wise)

                       The above components are only classified into Internal and External for each
                       Secured Loan Product for e.g. PL, STPL, etc.

                       Where
                       No. of Verifications = sum of CPV initiated and RECPV initiated (Possible values
                       for a LOS account is 0, 1 or 2). Identified basis CPV verification date or re-
                       verification date
                       No. of deviations approved = One deviation per account LOS extract will prioritize
                       and provide it basis a priority matrix. (Severity of Deviation basis approved cases)
                       Approved = Count where status is approved (field to be referred is final status -
                       decision)
                       Declined = Count where status is Declined
                       Total Processed = SUM (approved + declined)
                       WIP Cases = Exclude ( Blank , approved ,declined) status for WIP )




Requirement Document Template                    Page 7                 Version Date 01/06/2004
                                    REQUIREMENT DOCUMENT             Credit Enh 15 - LOS Analytics
                                                                           in Datawarehouse




Product Wise Report Description for Risk Intelligence & Control
Reports

Reports for Product: All Products

#   Report         Description
    Name
1   RIC1           This report displays Product Wise and Location Wise Risk Count cases for following
                   classified Risks:
                         Document
                         Profile
                         Others

                   Where
                   Login = Count of total LOS login which are in DDE stage
                   RIC Sampled # cases = Count of cases where RIC initiated
                   RIC sampling % = (RIC Sampled # cases / Login) * 100
                   Fraud Document # cases = Count of cases with status "FD"
                   Negative document # cases = Count of cases with status "ND"
                   Document Hit Rate % = (Fraud Document # cases / RIC Sampled # cases) * 100
                   FES - FD only = Summation of loan amt of all Fraud Document cases
                   Fraud Profile # cases = Count of cases with status "FP"
                   Negative Profile # cases = Count of cases with status "NP"
                   Profile Hit Rate % = (Fraud Document # cases / RIC Sampled # cases) * 100
                   FES - FP only = Summation of loan amt of all Fraud Profile cases
                   Cannot be verified # cases = Count of cases with status "Cannot be verified"
                   Initiated # cases = Count of cases with status "Initiated"
                   Positive # cases = Count of cases with status "Positive"
                   Total Fraud = Fraud Document # cases + Fraud Profile # cases
                   Total FES = FES FD only + FES FP only
2   RIC2           This report displays DSA wise counts of Approved and Rejected Loans.
                   Where,
                   No. of Logins = Count of total LOS login which are in DDE stage
                   Approved = Count of Approved Loans which are approved but not disbursed.
                   Approved % = (Approved / No. of Logins) * 100
                   Rejected = Count of Rejected Loans.
                   Rejected % = (Rejected / No. of Logins) * 100
                   FD = Count of cases with status "FD" (i.e. Fraud Document)
                   FD % = (FD / No. of Logins) * 100
                   FP = Count of cases with status "FP" (i.e. Fraud Profile)
                   FP % = (FP / No. of Logins) * 100
                   ND = Count of cases with status "ND" (i.e. Negative Document)
                   ND % = (ND / No. of Logins) * 100
                   NP = Count of cases with status "NP" (i.e. Negative Profile)
                   NP % = (NP / No. of Logins) * 100




Requirement Document Template                   Page 8                 Version Date 01/06/2004
                                    REQUIREMENT DOCUMENT                Credit Enh 15 - LOS Analytics
                                                                              in Datawarehouse




Masters maintained in Data warehouse explicitly


         Master maintenance will be done by Central Risk Unit, which comprises of
          Policy, Process and RIC Team on regular basis.
         CRU will have only Add and Update rights to masters.

Following masters need to be maintained in Data Warehouse explicitly:

             1.      Holiday Master




             2.      Deviation Master




             3.      Rejection Master




             4.      Region Master (Product wise)




Report Retrieval Requirements:

         Report Retrieval will be done by Central Risk Unit, which comprises of Policy,
          Process and RIC Team.

         Reports will be generated exactly on 6th of every month. All reports will
          contain current month data plus history data, where History data will be for
          past 15 months. Except for two Policy Reports, Early Mortality Report and Life
          Table (for 30+ and 90 + Cases) Report will have history data for past 24
          months.

         Data will be retrieved using Revelous Cubes and/or Report Writer.

Requirement Document Template                   Page 9                   Version Date 01/06/2004
                                           REQUIREMENT DOCUMENT                  Credit Enh 15 - LOS Analytics
                                                                                       in Datawarehouse




  Data Purging Requirements:

            In Data Warehouse, data of past 24 months will be stored, rest all data will be
             purged. And Data Warehouse will use its current achieve policy.

  FTP Requirements:

            If there are any FTP requirements, specification will be provided by IT
             Technical Team.

  Error Handling in case of error in Source Data:

               In case of Source Data Error, Data Warehouse team will keep manual checks
                to detect error and then co-ordinate with LOS team for rectification.



  5. Operational Concepts and Scenarios
  Please detail any operation/conceptual aspects of the system that may be vital to the system execution and support once
  the system has been implemented. These requirements typically may be provided by the operations here.

LOS Extraction Flow

1) One time extract (Only for Policy Reports)
   As a one time extract, historical data will be extracted from the LOS archival database
   from April –05 onwards till date. This data is required for the post disbursement analysis
   by the policy team. This data should be only for disbursed loans in LOS which have
   subsequently moved into Finnone

2) Monthly Extracts
   All loans processed in LOS during a particular month will be downloaded into DWH on a
   daily basis. This will include loans, which are lying in different stages and are
   modified/appended/sourced etc during the month of extractions.
   Like Finnone, the LOS extraction can be done from a backup database, which is
   restored immediately after the completion of the month in LOS. As there is no month-
   end processing in LOS, the backup and restoration can happen immediately after the
   month-end cut over time

The data elements for extraction are included in the excel sheet attached containing the
report formats

  Report Requirements - Date
  The users will require the Policy, Process & RIC reports by 6th of the following Month.

  Report Extraction Process:

            On daily basis data will be extracted from LOS and kept in temporary storage
             area. Then on monthly basis this data will be used to generate reports using
             Revelous Cubes and/or Report Writer.


  Requirement Document Template                          Page 10                   Version Date 01/06/2004
                                REQUIREMENT DOCUMENT         Credit Enh 15 - LOS Analytics
                                                                   in Datawarehouse




Data Processing Center Requirements:

         Data Processing Center will handle daily Data Transfer from LOS to Data
          Warehouse.

Process on Error in Reports:

        1. Users will identify the problem to Data Warehouse via email.
        2. If the problem is found at Data Warehouse end then SFR will be raised by
           DWH
        3. If the problem is found at LOS end then LOS will raise SFR.
        4. Defect will be raised with respective vendors.

LOS Data details:
         The historical and the current records present in the LOS tables would be
          downloaded into the DWH on Day 1.
         The master data downloaded from LOS would need additional
          Summarizations so as to meet the respective Product needs. These
          Summarizations would be done by the Resources in the CRU team.
         The masters maintained in LOS system are product-wise: for e.g. : if „High
          Risk profile„ is the deviation for three products (AL, PL and TW) , it will
          necessarily be maintained thrice in the LOS Deviation master with different
          deviation codes. These Deviation codes are numbers. So the Deviation
          master would contain two additional columns in which the CRU team would
          input data :
               Deviation group 1 : The Value in this field will be *(X1, X2, X3 and ,, Xn)
               Deviation Group 1 desc: The description corresponding to value ( X1,
                  X2, ,,Xn).
               Deviation codes not grouped and appearing against the agreements for
                product would be clubbed under a catch all bucket called „Others‟.
         The product master to contain an additional grouping for Secured products
          and unsecured products. This grouping will be done by CRU users after
          downloading the product master from LOS. This product master would be
          common for Policy, Process and RIC reports.
         The HUB_Spoke master would be downloaded from LOS: The following
          needs to be maintained by the CRU user and additional columns would be
          provided in DWH for the same. Again the Tier and Hub would be different for
          the same branch across products .So again this is a product wise master.
              o Region
              o State
              o Tier
              o HUB
         Again the Reject Reason Master which gets downloaded from LOS would
          need additional summarizations as to meet respective product needs. These
          Summarizations would be done by Resources in CRU team.
         There is one more master required from finnone which is the Credit
          Promotion scheme master.




Requirement Document Template             Page 11             Version Date 01/06/2004
                                 REQUIREMENT DOCUMENT        Credit Enh 15 - LOS Analytics
                                                                   in Datawarehouse




Process Report details:
         Approval /Decline/Deviation MIS is the only process report required. However
          this needs to be generated for all the finnone products present .Also there
          should be a provision to group some of the finnone products together like
          („A‟,‟SA‟) since „A‟ is Auto loan and „SA‟ is securitized autoloan.
         The Report needs to be generated for the Salaried personnel and the non
          Salaried personnel.
         The Report consists of three subsections :
               o Approval MIS :
               o Decline MIS:
               o Deviation MIS:
         Approval MIS gives an overall view of the cases processed in that month .It
          consists of the following subsections:

                o No of verifications initiated : All those cases whose
                  „QDE_Complete_D‟ date falls in the month should form part of this
                  report/
             o No of files decisioned : All those cases which are in „Underwriter stage
                  „ as denoted by „LAS_Activity_ID‟ and Las_Status_c in ( Pending,
                  Forward , start,Approved and Rejected ) and
                  „DDE_doc_Complete_date‟ in the month are to be considered.
         No of files Decisioned can be further segregated into three categories:

                o    No of files approved: All those cases which are in „Underwriter stage
                     „as denoted by „LAS_Activity_ID‟ and Las_Status_c in (Approved)
                     and „DDE_doc_Complete_date‟ in the month are to be considered.
                o     No of files Declined: All those cases which are in „Underwriter stage
                     „ as denoted by „LAS_Activity_ID‟ and Las_Status_c in (Rejected )
                     and „DDE_doc_Complete_date‟ in the month are to be considered.
                o    WIP cases: All those cases which are in „Underwriter stage „ as
                     denoted by „LAS_Activity_ID‟ and Las_Status_c in ( Pending,
                     Forward , start) and „DDE_doc_Complete_date‟ in the month are to be
                     considered.

         Decline MIS provides a breakup of the LOS Cases rejected in the month
          along their decline reasons. Since the Decline reasons can be multiple for an
          LOS agreement id, the latest should be selected as per a logic .The logic can
          be derived basis the last reject reasons for that agreement.
         Deviation MIS provide two level of breakup for the LOS cases approved for
          that month :
              o Breakup along deviation levels: The Deviations levels are prioritized
                 basis Severity and today there are 4 levels. The Level 1 is the most
                 severe and Level 4 is the least severe.
              o For those LOS cases which falls into multiple deviation levels, the
                 deviation level with the higher severity to be chosen.
              o Breakup along deviation codes: Under the Deviation levels , one
                 has the deviation codes .The deviation codes will be grouped
                 together under business descriptions and codes in the deviation
                 master as outlined in the earlier section.
              o Total Deviation: are the total of all the deviations.

Requirement Document Template              Page 12             Version Date 01/06/2004
                                 REQUIREMENT DOCUMENT       Credit Enh 15 - LOS Analytics
                                                                  in Datawarehouse




         The entire report section and subsections needs to be split along Internal
          /External customers and should be Hub –wise as defined in the Hub-Spoke
          Master.
         The process reports need to be provided on a prospective basis from
          GOLIVE month.
         The process reports needs to be available by the 6th of the every month.

Policy Report details:
         There are 2 reports wherein history needs to be built up for last 24 months
          and provided to the user:
             o Early mortality reports
             o Life Table (for 30+ and 90 + Cases).
             o The history build-up needs to be for the last 24 months.
             o The rest of the policy reports can be provided on a prospective basis
                 from Golive month.
             o The policy reports needs to be available by the 6th of every month.

         Additional measures which needs to be provided are :

                o    ENR : (Sum of total POS) – (180+ POS)
                o    30+ POS : Sum of all POS for cases greater than 30 DPD but less
                     than 180 DPD
                o    60+ POS : Sum of all POS for cases greater than 60 DPD but less
                     than 180 DPD
                o    90+ POS: Sum of all POS for cases greater than 90 DPD but less than
                     180 DPD.
                o    ANR: Simple Average of last twelve months ENR. ie (Sum OF ENR
                     for last twelve months) / 12.
                o    Weighted IRR: Weighted IRR = (agreemt1 * Business IRR) +
                     (agreemt2 * Business IRR) + (Agreement 3 * Business IRR +
                     Agreement 4 * Buss IRR)/ Total POS of Agreement 1 to Agreement 4
                o    Nonstarter
                o    Credit Promotion scheme from Finnone is required.
                o    The Credit promotion scheme is required as an Master from Finnone
                     Also the master needs to have the facility for the user to specify a
                     higher level of grouping as is the case with Deviation master and
                     reject reason master.
                o    All the CRR reports have been raised as an enhancement to the
                     existing CRR cubes for e.g.: Age on book analysis , Cheque bounce
                     analysis , Retail concentration and New risk contracts analysis and
                     EOP delinquency analysis. We need to work out an optimal solution
                     basis the user inputs.

Operational Process Document Matrix:




Requirement Document Template             Page 13            Version Date 01/06/2004
                                          REQUIREMENT DOCUMENT                    Credit Enh 15 - LOS Analytics
                                                                                        in Datawarehouse




6. Target Environment
Please Specify the Target Environment for the system to be developed here. The details with regards to hardware
Servers/workstations, OS ver, Web-server/Application server/ ver, compilers/Interpreters/ ver, tools/ ver that are required
to run on this software and also geographical location for the deployments/installations if applicable. The Target
environment may Include items that are non vendor dependent/influenced but may impact the system design, building and
execution.
NOTE: PLEASE ALSO MENTION IF THERE ARE ANY KNOWN SYSTEM CONSTRAINTS

7. Interface Requirements
   a. Graphical User Interface
Please detail comprehensively the Graphical User Interface. Determine the look and feel of the Interface along with the,
navigational flow that the end user is going to use. Please mention any preexisting standards if applicable to the system
input/output from the user‟s perspective. The validations for data that will be input/output on the screen/output can be
defined here.
For example, for showing the a/c balance on the Internet, there will be a validation screen accepting the “Customer Id” and
the Net Banking “Password” and a “Submit/OK” button that will take the users of the Main screen where the “Account
number” and “Account type” are the mandatory fields and a “Get Balance” button to fetch the account details.
NOTE : IF LEFT BLANK IT IS ASSUMED THAT VENDOR IS FREE TO DEFINE THIS SECTION.
     b.    Hardware Interface
Clearly Indicate the h/w on which the desired system will be resting here. If there are any OS
command/executables/features/tools that the proposed system is may be required to use then these need to be
mentioned here.
NOTE : IF LEFT BLANK IT IS ASSUMED THAT VENDOR IS FREE TO DEFINE THIS SECTION.
     c. Communication interface
Clearly indicate the communication Interface that the proposed system will be having with the external world. An example
of this can be the SMPT configuration that will be required for defining alerts/reminders for the system. Addition this can
also include the file format that may be required for communication and handshake with the other systems and the
environment in which the proposed system will rest. You may also specify any data communication, client or inter-process
communication here.
NOTE : IF LEFT BLANK IT IS ASSUMED THAT VENDOR IS FREE TO DEFINE THIS SECTION.




Requirement Document Template                           Page 14                     Version Date 01/06/2004
                                          REQUIREMENT DOCUMENT                    Credit Enh 15 - LOS Analytics
                                                                                        in Datawarehouse




Data Flow Analysis



                                                Start



                                     Loan Origination System
                                     (LOS)




                                            FINONE
                                     (Loan Management                                 Finware [System]
                                     System)                                          (Branch users)




                                     Data Warehouse




                                           Report
                                           Processing




                                                End



     d.    Software Interface
Indicate the communication Interface that the proposed system will be having with the external world. An example of this
can be the SMPT configuration that will be required for defining alerts/reminders for the system. Addition this can also
include the file format that may be required for communication and handshake with the other systems and the environment
in which the proposed system will rest. You may also specify any data communication, client or inter-process
communication here.
NOTE : IF LEFT BLANK IT IS ASSUMED THAT VENDOR IS FREE TO DEFINE THIS SECTION.


8. Migration/Transition Plan (if applicable)
Please mention any data/system migration details that may be applicable here. This section is relevant if the data from the
earlier application database needs to be ported over to the new application database.

Data is ported from LOS to Data Warehouse. The data flow details are mentioned in
below document.




Requirement Document Template                           Page 15                     Version Date 01/06/2004
                                          REQUIREMENT DOCUMENT                    Credit Enh 15 - LOS Analytics
                                                                                        in Datawarehouse




9. Non-functional Specific/Additional Requirements
Please mention any non-functional specific or additional technical information, which may be critical for
designing/building/execution of the system here. Please validate if the information that is being provided here needs to be
also mentioned in the Function Specification section of this document. Only details that are out of the scope of the system
design as part of vendors system development but key from the execution/implementation/environmental aspects need to
be mentioned here.
NOTE : IF LEFT BLANK IT IS ASSUMED THAT VENDOR IS FREE TO DEFINE THIS SECTION.

Dimensions absent in Data Warehouse for Policy Reports

Sr. No.         Field Name              DESCRIPTION
   1 Cheque Bounce_Reson   Cheque Bounce reason
   2 Approved code         Emp code of approval authority
   3 ADDYEAR               Residence stability
   4 DME                   DME Code
   5 NBFC_CUSTOMER_M       Customer Status
   6 NBFC_CUSTOMER_M       Net Monthly Income
   7 Decision              De-dupe result wise delinquency
   8 LOS_WFL_DEVIATION_DTL Deviation code
   9 LOS_APP_APPLICATIONS Sourcing Branch-Finware Br. Code
  10 NBFC_CUSTOMER_M       Customer Religion
  11 NBFC_CUSTOMER_M       Educational Qualification
  12 NBFC_CUSTOMER_M       Years In current business/job
  13 NBFC_CUSTOMER_M       Total work experience
  14 NBFC_CUSTOMER_M       Business Turnover
  15 NBFC_CUSTOMER_M       Depreciation
  16 NBFC_CUSTOMER_M       dscr Ratio
  17 CR_BANK_M             ABB to EMI ratio
  18 NBFC_OBLIGATION_DTL Sum of Total obligation
  19 FOIR                  FOIR wise delinquency


Dimensions Present in Data Warehouse for Policy Reports

Sr. No. Field Name          DESCRIPTION
   1 BRANCH        BRANCH NAME
   2 BRANCHCODE BRANCHCODE
   3 BUCKET        BUCKET
   4 CBI_FLAG      CBI FLAG
   5 CHANNEL       SOURCE CHANNEL
   6 CHQR_BAND CHQ RETURN BAND
   7 CLOSED        CLOSED INDICATOR
   8 CURRENT_AC CURRENT A/C HOLDING
   9 DEMAT         DEMAT HOLDING
  10 DEPOSIT       DEPOSIT HOLDING
  11 EDUCATION EDUCATION OF THE CUSTOMER
  12 GENDER        GENDER
  13 HUB           HUB
  14 LOAN_BAND LOAN AMOUNT BAND
  15 LOCATION      LOCATION

Requirement Document Template                           Page 16                     Version Date 01/06/2004
                                         REQUIREMENT DOCUMENT                 Credit Enh 15 - LOS Analytics
                                                                                    in Datawarehouse




  16   LTV_BAND   LOAN TO VALUE BAND
  17   MFG_NAME ManufacturerNAME
  18   MODEL      MODEL NO
  19   MRTL_STATU MARITAL STATUS
  20   NEW_IND    NEW INDICATOR
  21   OD         OD HOLDING
  22   PROD_CODE PRODUCT CODE
  23   PROD_HLDG PRODUCT HOLDING
  24   PROFESSION CUSTOMER PROFESSION
  25   PROMO_SCHE PROMO SCHEME
  26   REGION     REGION
  27   RESI_CITY  RESIDENCE CITY
  28   RESI_STATE RESIDENCE STATE
  29   RESI_TYPE RESIDENCE TYPE
  30   SAVINGS    SAVINGS A/C HOLDING
  31   SPOKE      SPOKE
  32   STATE      STATE
  33   TPP        TPP HOLDING
  34   MIS_DATE   MONTHEND DATE
  35   BANK_BAND BANK BANDING
  36   DSA_CODE DSA CODE
  37   INTRATE    INTEREST RATE
  38   MOB_MON    LOAN DISBURSAL MONTH
  39   MOB_YEAR LOAN DISBURSAL YEAR
  40   PROD_SCHEM PRODUCT SCHEME
  41   TENURE_MTH TENURE
  42   VINTAGE    VINTAGE OF THE CUSTOMER


User Access

          Maximum 10 users will be working on system concurrently.
          All users are Intranet users located at Mumbai, Pune, Chennai and Delhi.
          Expected growth of users is as follows:

   Period             Total Users LAN Users           Intranet Users External Users           Concurrent Users
   End of Year1       50                              50                                      10
   End of Year2       70                              70                                      15
   End of Year3       80                              80                                      20



10. Assumptions/Dependencies/Limitations
Clearly mention all Assumption/Dependencies and Limitation that are identified for this system. The Limitations and
Assumptions that are made as part of the concept note will be inherited in this section. Additionally if there are any
ambiguities with the system design, then these need to be mentioned here.
NOTE: IF LEFT BLANK IT IS ASSUMED THAT THE SYSTEM ASSUMPTION / DEPENDENCIES / LIMITATIONS ARE
NONE.

          There is no requirement of moving LOS data into Finnone
          There is no change to the existing Finnone extraction into DWH

Requirement Document Template                         Page 17                   Version Date 01/06/2004
                                          REQUIREMENT DOCUMENT                      Credit Enh 15 - LOS Analytics
                                                                                          in Datawarehouse




           The linkage of Finnone and LOS will be done in DWH basis the loan number
            which is common in both systems
           LOS will extract raw data. All computations/derivations will be done in DWH
           A loan disbursed in LOS can be cancelled in Finnone post disbursement in
            LOS. The cancellation can be initiated by the customer or could be a case of
            cancelled and rebooked. For all such cases, the finnone loan no will not be
            the same as LOS loan no
           The are some data elements in LOS which are currently moving into finnone
            and are part of DWH extract from Finnone . However, it has been decided to
            extract these data elements also from LOS on the following grounds:
                1. All Loans disbursed in LOS during the month would not get authorized
                    in Finnone before the month-end. Hence there would be a mismatch
                    between LOS data and Finnone data leading to incomplete/incorrect
                    MIS. The impact will be more on month-ends.
                2. There could be instances where a loan is disbursed in LOS but
                    cancelled in Finnone . The LOS MIS will get skewed and may not
                    reflect the correct data


11. Systems Impacted (if any) :
Clearly indicate in detail the systems that may be impacted once this system will come live. There may be other specific
requirements that may be required to be specified in section 7d. The other systems that may be impacted are live
functional systems that are currently running in the bank.
NOTE : IF LEFT BLANK IT IS ASSUMED THAT VENDOR MAY OVERLOOK ANY SUCH SPECIFIC
NEEDS/CONSTRAINTS.
12. Screen Shots (if any)
Clearly Indicate in detail the systems that may be impacted once this system will come live. There may be other specific
requirements that may be required to be specified in section 7d. The other systems that may be impacted are live
functional systems that are currently running in the bank.
NOTE : IF LEFT BLANK IT IS ASSUMED THAT VENDOR MAY OVERLOOK ANY SUCH SPECIFIC
NEEDS/CONSTRAINTS.
13. Acceptance Criteria
Please specify the acceptance criteria here e.g.
The acceptance criteria can be defined as follows:
        The specification / design documents address the required functionality.

Following Policy Reports must be generated and data must match accurately:

Sr. No. Reports                                                             Frequency
   1 Promotion scheme wise Delinquency                                      Monthly
   2 Promotion scheme wise early mortality rate                             Monthly
   3 Source wise Scheme wise Delinquency                                    Monthly
   4 Scheme wise Ticket Size wise Delinquency                               Monthly
   5 Segment wise Income wise Delinquency                                   Monthly
   6 Segment Wise Sourcing                                                  Monthly
   7 Scheme wise Sourcing                                                   Monthly
   8 Employer wise Delinquency                                              Monthly
   9 Tier wise Delinquency                                                  Monthly
  10 Tier wise Cheque bounce %                                              Monthly
  11 Tier Wise Nonstarter                                                   Monthly
  12 DSCR & Ticket size wise Delinquency with location & city dimension Monthly
  13 Turnover & ticket size wise Delinquency with location & time dimension Monthly
  14 ITR income wise delinquency with city & time dimension                 Monthly
  15 FOIR wise Delinquency with location & time dimension                   Monthly

Requirement Document Template                           Page 18                      Version Date 01/06/2004
                                    REQUIREMENT DOCUMENT                Credit Enh 15 - LOS Analytics
                                                                              in Datawarehouse




  16   City wise /product wise life table                                              Monthly
  17   Constitution wise Delinquency                                                   Monthly
  18   Promotion scheme wise/region wise early mortality rate                          Monthly
  19   Program wise early mortality rate with location Dimension                       Monthly
  20   Program wise monthly comparision for early mortality                            Monthly
  21   Promo scheme wise Income wise Delinquency                                       Monthly
  22   Program wise Sourcing                                                           Monthly
  23   Scheme wise Sourcing                                                            Monthly
  24   Region Wise & City wise Nonstarter                                              Monthly
  25   Deviation Level wise Delinquency with location & time                           Monthly
  26   Deviation code wise Delinquency with location & time                            Monthly
  27   Program wise Deviations                                                         Monthly
  28   Program wise Status                                                             Monthly
  29   Program wise Credit Rj break up                                                 Monthly
  30   Program wise Deviation Break up                                                 Monthly
  31   Nonstarters Internal vs External                                                Monthly
  32   Scheme wise Nonstarters                                                         Monthly
  33   Deviation wise Nonstarters                                                      Monthly
  34   Tier wise Cheque bounce%                                                        Monthly
  35   Scheme wise Cheque bounce%                                                      Monthly
  36   Ticker size wise Cheque bounce %                                                Monthly
  37   Cheque bounce % based on reason ( ECS Not Hit etc..)                            Monthly
  38   Reason wise/State wise scheme wise disbursement                                 Monthly
  39   CPV wastages- CPV fired vs cases disbursed                                      Monthly
  40   Approval to disbursement ration                                                 Monthly
  41   Approver wise disbursement {Report Dropped by User}                             Monthly
  42   Pan India Program wise status                                                   Monthly
  43   Pan India , region, state, city and br wise under process status program wise   Monthly
       Pan India , region, state, city and br wise purchase pending program wise       Monthly
  44   ( same as above)
  45   Weekly report                                                                   Monthly
  46   Cost Mis - Br and region wise                                                   Monthly
  47   DEDUPE HIT RATIO MIS                                                            Monthly
  48   Dashboards                                                                      Monthly
  49   Underwriter wise delinquency                                                    Monthly
  50   Risk Reward                                                                     Monthly




Following Process Reports must be generated and data must match accurately:

Sr. No. Reports                   Frequency
   1 Credit MIS – Unsecured Loans Monthly
   2 Credit MIS - Secured Loans Monthly


Following Process Reports must be generated and data must match accurately:



Requirement Document Template                    Page 19                 Version Date 01/06/2004
                                          REQUIREMENT DOCUMENT           Credit Enh 15 - LOS Analytics
                                                                               in Datawarehouse




Sr. No. Reports Frequency
   1 RIC1       Monthly
   2 RIC2       Monthly

14. Additional Information
Please mention any additional relevant information here.


Refer to understanding document for more detailed clarification.




          Prepared by / Date                                              Reviewed by / Date
           (BSG Manager)                                                    (IT Manager)




         Approved by / Date                                               Approved by / Date
      (BSG Application Owner)                                           (IT Application Owner)




         Reviewed by / Date                                              Concurred by / Date
              (BPRG)                                                 (Operations Head, if required)




Requirement Document Template                              Page 20        Version Date 01/06/2004

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:12
posted:9/3/2011
language:Vietnamese
pages:20