ADF Loan Application Module Development

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

1

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

1

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

1

ADF Loan Application Module Development

Figure 1- Flow Chart for Database Requirements and Inputs

1

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:

1

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

1

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

1

1

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:

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:

1. Workflow and Data

Workflow Management

Data Input

• Applicant Information
• Technical Assessment
• Financial Analysis

Data Outputs

• Financial Outcomes
• Recommendation

1. Administrative Functions
2. Reporting
3. Document and File Management

2.8.1Workflow and Data Requirements

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:

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: