ADF Loan Application Module Development

ANNEX A

TECHNICAL REQUIREMENTS

Database Development

DRAFT

Program:Agricultural Credit Enhancement Program Phase II

Activity Name: Loan Application and Credit Analysis Model

IT project name: Developing a Database Solution for a new Loan Application model to be used by the Agricultural Development Fund (ADF)

Date:July 01, 2016

Location: Kabul, Afghanistan

Prepared by: ACE II (DAI)

Document status: Draft

Document control

Revision history

Revision / Date / Authors / Status / Comment
Version 1.0 / July 05, 2016 / G Norris, A Nazary / Draft - for review by COP
Version 2.0 / July 07, 2016 / G Norris, A Nazary / Proposed – submitted for review by DAI Home Office
Version 3.0 / July 14, 2016 / G Norris, A Nazary / Validated – by DAI Home Office
Version 4.0 / July 21, 2016 / G Norris, A Nazary / Approved – by USAID

ADF Loan Application Module Development

TABLE OF CONTENTS

1INTRODUCTION

1.1General Information

1.2Purpose

2SQL DATABASE Requirements

2.1Scope of the SQL based platform

2.2Overview of Requirements Development

2.3Assumptions and Constraints

2.3.1Assumptions

2.3.2Constraints and Dependencies

2.4Risks

2.5System Users

2.6Solution Architecture Diagram

2.7ADF Loan Application Module High Level Requirements

2.8Detailed Functional Requirements

2.8.1Workflow and Data Requirements

2.8.2Administrative Functions Including User Management Requirements

2.8.3Reporting Requirements

2.8.4Document and File Management Requirements

2.8.5Interface Requirements

2.9Operational Requirements

2.9.1Hardware Requirements

2.9.2Security Requirements

2.9.3Database Testing

ACE II users must be allowed to test the database functions and confirm that they are in line with confirmed and documented final requirements.

2.9.4Audit Trail

2.9.5Reliability

2.9.6Recoverability

2.9.7System Availability

2.9.8Performance

2.9.9Training

2.9.10Capacity

2.9.11Data Retention

3GENERAL

FIGURES

Figure 1- requirements gathering (required inputs) EXAMPLE

Figure 2 - 3-Tier Architecture

Figure 3 - requirements functional areas and processes - EXAMPLE

TABLES

Table 1 - high level requirements

ADF Loan Application Module Development

1INTRODUCTION

1.1General Information

This document is intended to guide the design and development of a database solution for a new Loan Application model (“the Project”) to be used by the Agricultural Development Fund (ADF). It will pass through the following stages before the Project commences:

Draft version:Specifies requirements, which arerecorded, classified, and prioritized.

Proposed: The draft document is then proposed as a potential requirements specification for the Project. The proposed document should be reviewed by DAI Home Office who may comment on any requirements and any priorities, either to agree, to disagree, or to identify missing requirements. This document may be amended and re-proposed several times before moving to the next stage.

Validated: Once ACE II and DAI Home Office have agreed to the requirements in the document, it is considered validated.

Approved: The validated document shall then be submitted to USAID for approval who may comment on any requirements and any priorities, either to agree, to disagree, or to identify missing requirements. This document may be amended and re-proposed several times before final approval.

1.2Purpose

The overall objective of the Project is to develop and pilot test a suitable SQL based database for a new Loan Application model for use by the ADF. This will facilitate an online application process involving key participants from the ADF’s Business Development, Credit and Risk management, and Technical Services units. The process incorporates revised credit metrics designed to improve portfolio performance by tightening qualification criteria. It will also eliminate duplicative committees and focus the decision making responsibility.

Primary objectives and functions of the Database Solution are:

To design and develop a database solution and platform for a new Loan Application model that will reduce duplicative processes, apply credit metrics to increase the focus on construction of cash flow projections and calculation of reimbursement capacity based on those projections. The database solution will enhance the primary objectives of the new model, which are to:

Introduce a standardized master template that is more user friendly;
Improve the quality of borrower information and pay more attention to borrower selection and character references;
More effectively inculcate technical and marketing information into the credit process and credit decisions;
Pay more attention to leveraging or mitigating opportunities / risks associated with different strength value chains;
Apply a credit methodology centered on debt repayment capacity, as opposed to basing credit decisions on a "credit need" approach;
Impose stricter information requirements to assess affordability.

2SQL DATABASERequirements

2.1Scope of the SQL based platform

To provide a centralizedlocation where ADF staff in 5different locations can store data simultaneously, and the preferred DBMS type is Microsoft SQL.
To provide a system where data can be extracted using queries.
To provide online availability and access over Internet browsers (Internet explorer, Mozilla Firefox, Google Chrome, and Safari): applicable for desktops, tablets, IOS and smartphones.
Which is accessible offline using an offline application,when there is no internet access (Windows, MAC, Android, IOS).
Which has the capability of syncing data using an offline application when the internet is not available
Has user and / or group rights management capability

2.2Overview of Requirements Development

Strict enforcement of Industry Graphical User Interface (GUI) standards.
The Database platform must have an appealing and professional design, easy to use interface and must be optimized for performance.
Must have clear Data Field Definitions for user to clarify Data Entry.
Must have Text Fields that expand as text is typed in select fields.
Must have Text Fields that limit the number of characters typed in select fields.
To include Drop-Down, Combo, and Check boxes.
Capability to automatically activate a specific work sheet when an option is selected in a Drop-Down box.
To include mathematic formulasfor automated financial calculations.
Capability to modify database in order to accommodate future and changing needs.
Must have ad-hoc reporting capabilities.
Standard reports should be transactional based on listing the information entered for that specialty or area.
Allow for the entering of data offline for future synchronization,when there is no access to the internet.
The database must be compatible with SQL Server
The database must be easy to use, manage and administer the database
The database must have keyword search capability
It must have capability to generate user reports and system reports
It must have capability to add filters for generating reports, for example to select and filter by regions, provinces, loan officers, clients etc.
Upon completion of the database development, at least three members of ACEII and ADF should be trained to administer and modify the database for continued maintenance and future needs.

Refer to Figure 1 in Annexure A for an illustration of the database requirements and inputs

ADF Loan Application Module Development

Figure 1- Flow Chart for Database Requirements and Inputs

ADF Loan Application Module Development

2.3Assumptions and Constraints

2.3.1Assumptions

An Excel based Loan Application Module has already been developed by ACE II.
The above Module shall provide the data points, inputs and fields for the database.
The above Module shall provide the basic layout and flow for the database, according to the spreadsheets in the Module
The currency to be used in the database shall be Afghani only

2.3.2Constraints and Dependencies

The following constraints apply:

The database must be ready for pilot testing within 14 days of the Project start date.
The Project budget under an approved STTA shall be strictly enforced.

The development of a database for the ADF Loan Application Module has the following dependencies:

It must be a on a MS SQL Server database platform.
It must have Web Access capability.
It must have an Offline Application

2.4Risks

The following risks must be taken into consideration:

Additional and / or new functional requirements or details not documented during the initiation and discovery phases.
Additional and / or new functional requirements or details identified and prioritized after pilot testing the database.

2.5System Users

The users of the ADF Loan Application Modulewill be restricted to ADF staff members from select departments.

Internal users will include the following persons:

ADF Loan Application Module Development

Chief Executive Officer
Senior Business Development Director;
Credit & Risk Director;
Technical Services Director;
Technical Services Officer (Kabul)

Market Information Manager
Credit Administration Officer;
Internal Audit Manager
Director Internal Audit
Credit Manage

ADF Loan Application Module Development

External users will include the following persons (note: External users are those personnel deployed in the field):

Loan Officers at Regional Offices
Technical Services Officers at Regional Offices

The overall expected number of users is up to 25.

The projected concurrent number of users is a maximum of 10.

The users will require different level of access depending on their responsibilities, authority and needs.

Based on the application components and recognized functions, the following user groups can be identified at this time:

Loan Origination user group.
Technical Services user group
Marketing Information user group
Credit and Risk user group
Internal Audit user group
Senior Management user group

2.6Solution Architecture Diagram

Figure 2 - 3-Tier Architecture

Client Tier is defined by presentation logic - the user interface (UI) which displays data to the user and accepts input from the user.

Application Tier is defined by business logic - handles data validation, business rules and task-specific behavior.

Data Tier is defined by data access logic - communicates with the database by constructing SQL queries and executing them via the relevant API.

The main advantages of the 3 Tier Architecture are:

Flexibility - By separating the business logic of an application from its presentation logic, a 3-Tier architecture makes the application much more flexible to changes.

Maintainability - Changes to the components in one layer should have no effect on any others layers. Also, if different layers require different skills (such as HTML/CSS is the presentation layer, PHP/Java in the business layer, SQL in the data access layer) then these can be managed by independent teams with skills in those specific areas.

Re-usability - Separating the application into multiple layers makes it easier to implement re-usable components. A single component in the business layer, for example, may be accessed by multiple components in the presentation layer, or even by several different presentation layers (such as desktop and the web) at the same time.

Scalability - A 3-Tier architecture allows distribution of application components across multiple servers thus making the system much more scalable.

Reliability - A 3-Tier architecture, if deployed on multiple servers, makes it easier to increase reliability of a system by implementing multiple levels of redundancy.

2.7ADF Loan Application Module High Level Requirements

The identified high level initial set of requirements during the assessment phase are the following:

High Level Requirements - General
1 / Must provide for an electronic on-line application process
2 / Must facilitate the recording of obligatory credit application data needed to support application screeningie, the application process is suspended until compulsory Data Fields are completed
3 / Must provide user access to staff participating in the screening and credit-assessment process. Specific user access and levels of authority must be accommodated
4 / Supportanalysis of the applicant’s eligibility in accordance with policy requirements
5 / Support the development of information to satisfy credit requirements
6 / Must be able to check system data files to determine whether
the applicant has submitted a duplicate application or has had a recent loan application
7 / Must be able to update an application information store to reflect the status of a loan application
8 / Must be able to record certain business type / value chain / risk categories associated with the loan
9 / Must be able to assign a unique reference number to the loan application that remains unchanged throughout the life of the loan
10 / Record time / dates when specific steps in the loan application model are initiated and completed
High Level Requirements – Internal Management Information
1 / The database must include a user friendly query tool
2 / Be able to create and maintain a system record of accepted, deferred and rejectedloan applications
3 / Must provideoverride exceptionsmanagement information. Theoverride exceptions summaryidentifies all loan applications where specific rules are overridden
4 / Must provide a detailed transaction history of loanorigination.The data summary will be used forcontrol and tracking, and as an audit trail
5 / Must provide exceptions managementinformation in loan origination processing, for example affordability and / or loan-to-value ratios exceeding allowable parameters

Based on the above, the requirements gathering and analysis initially focused on the Excel based model already prepared by ACE II.

2.8Detailed Functional Requirements

This section of the document lists specific requirements for this project as well as predicted and/or proposed processes (workflows). The main processes described by functional requirements are presented in the diagram below:

The main functional areas and processes to be supported by the solution which were identified during discovery phase are the following:

Workflow and Data

Workflow Management

Data Input

Applicant Information
Technical Assessment
Financial Analysis

Data Outputs

Financial Outcomes
Recommendation

Administrative Functions
Reporting
Document and File Management

2.8.1Workflow and Data Requirements

Workflow and Data Requirements / Priority
1.1 / A multi-user environment with security settings for various user types / Mandatory
I.2 / Workflow based design for loanmanagement. Supports an efficient loan application process / Mandatory
I.3 / Must allow input of data by authorized internal and external users / Mandatory
I.4 / Ability for authorized users to edit / update data. / Mandatory
1.5 / Automated transfer of data to linked worksheets / Mandatory
1.6 / Formularized for mathematical calculations / Mandatory
1.7 / All worksheet modules should be fully integrated / Mandatory
I.8 / Ability to suspend next steps in process until compulsory fields completed / Mandatory
1.9 / Ability to activate worksheet(s) on selecting drop-down option(s) / Mandatory
1.10 / Generation / transmission of emailalerts at variousstages of loan processing / Mandatory
1.11 / The database must be available in English / Mandatory
1.12 / Non-technical staff must be able to maintain (content manage) the database using a content management system / Mandatory
1.13 / Provide capabilities for authorized users to add new worksheets with ease; and manage the database primary menu and any other secondary / Mandatory

2.8.2Administrative Functions Including User Management Requirements

Administrative functions are related to specific capabilities of the application which require higher level of privileges including user management.

The administrator(s)must be able to manage user group’s access to different application functional areas
The administrators can view logs
Only Administrator may change / edit the worksheet design and framework

2.8.3Reporting Requirements

Reporting functions depend on the data structure and data availability as provided for in the Excel based Loan Application Module template. The Database must support a variety of reports without manual intervention. Reporting function will include standard reports; however, ad-hoc reporting will also be available. Standard reports will be identified from discussions with end-users and experts.

The required types of reports are:

Standard Reports – predefined

Ad-hoc reports
Reporting Other

Reporting requirements:

Reporting requirements / Priority
Reporting type: Standard
RI.1 / Must be tabular and graphical / Mandatory
RI.2 / Generate standard reports based on categories of data / Mandatory
RI.3 / Generate summary reports to reflect workflow results / progress / Mandatory
Reporting type: Ad-hoc
RS.1 / Must be tabular and graphical / Mandatory
RS.2 / Generate ad-hoc reports based on categories of data / Mandatory
Reporting Other
RO.1 / Supports appropriate printer interfaceswith outputs configurable in terms oflayouts as well as file / Mandatory
RO.2 / Export report to xls, csv, docx or pdf format / Mandatory

2.8.4Document and File ManagementRequirements

Provision for scanning and uploading documents
Has an inbuilt Document Management System for storage, retrieval, display and printing of documents / images relating to various product types / transactions – to include documents generated within the application and any thirdparty provided documents
Supports creation of documents /templates

2.8.5Interface Requirements

Interface requirements describe the look and feel of the user interface, how certain functions should be displayed and messaging requirements.

The user interface should be simple, intuitive and easy to navigate
Must be visually appealing and intuitive to use with an attractive mix of text and graphics
There must be consistent use of fonts and layouts through-out the worksheets
Site Navigation must be intuitive and consistent
All information must be presented in a logical manner and require easy to use “drill-down” for the user to find the desired information
Help files should be available for each user action (question mark available to click on next to a button or action, where clicking on it would invoke required help file either on the side of the screen or in a separate window).

2.9Operational Requirements

These requirements are not related to business process but to non-functional aspects for the solution.

2.9.1Hardware Requirements

Database Solution is platform agnostic – not dependent on a particular hardware setup

2.9.2Security Requirements

Robust confidentiality and security features to protect information flow between once worksheet and / or user to another.
Access to application and data should be in line with the proposed user groups and available roles. Security settings assigned to unique usernames and passwords to grant/restrict users access to certain functions.
Non-authenticated users will have no access to application and data.
User roles will control user access to data and system functions.

2.9.3Database Testing

ACE II users must be allowed to test the database functions and confirm that they are in line with confirmed and documented final requirements.

2.9.4Audit Trail

The Database solution supports audit trails at all levels with easy to use storage, retrieval, display and print of audit logs based on various parameters. The application should at a minimum log the following activity: