MIS Project

KATHMANDU KA THMANDU UNIVERSITY SCHOOL OF MANAGEMENT

PROJECT REPOR REPORT T: MIS MI S MANAGEMENT INFORMATION SYSTEM

Submitted on: 27 th December, 2009

Submitted by: Mr. Bimal Daga Mr. Dharendra Rayamajhi Mr. Govinda Gyawali Mr. Sandip Timsina Mr. Shushant Limbu Mr. Tribikram Dhungana EMBA -2009 Submitted to: Mr. Sudarshan Raj Sharma Course instructor Management Information System

SIGNATURE PAGE I/we certify that I/we have read this document and, in my/our opinion, it is satisfactory in scope and quality as a project report in partial fulfillment for the graduate course of Management Information System held at the School of Management of Kathmandu University.

Mr. Bimal Daga

_______________________

_______________________

_____ Mr. Sandip Timsina

______________________

Mr. Dharendra Rayamajhi

Mr. Sushant Limbu

_______________________ Mr. Govinda Gyawali

_______________________ Mr. Tribikram Dhungana

COPYRIGHT & DISCLAIMER 3 Copyright @ 2007, By the authors All rights reserved. Reproduction or translation of any part of this work beyond that permitted by the acts of Nepal without the permission of the copyright owner is unlawful. Request for the permission or further information should be addressed to the authors.

DISCLAIMER The The obje object ct of this this proj projec ectt work work was was to stud study y the the MIS MIS requ requir irem emen entt in the the cont contex extt of Nepa Nepale lese se Organization. The authors are confident that the results of analysis and the results presented in this report will be taken as a guide for a more comprehensive study at a future date. The authors are not responsible or liable legally and morally against the results and consequent decisions based on the project report. The project shall only serve the academic purpose. The views expressed in this report are those of the project team members only.

ACKNOWLEDGEMENT We woul would d like like to expr expres ess s our our grat gratit itud ude e to Kath Kathma mand ndu u Univ Univer ersi sity ty Scho School ol of Management for giving us the opportunity to conduct a project work on a topic that helped helped us not only to asses assess s the the MIS require requiremen mentt of a unit unit of an establ establish ished ed organization, but the project also helped us learn to do research and enrich our knowledge. We are very grateful to our instructor Mr. Sudarshan Raj Sharma for his guidance, suggestions and resource materials without which the project work would not have been possible. Last but not the least we would like to express our sincere thanks to employees at Employees Provident Fund for letting us conduct a project task on one of their crucial unit.

Executive Summary Employees Provident Fund is a government owned fund management organization. It collects the contribution deduction from various organizations (government, nongovernment, police, and army) and maintains account for each employee. It then invests the fund and distributes the interest. There are various functions from fund collection to investment that are core to Employees Provident Fund’s operation. There are various departments and sections each dedicated to carry out specific operation and one of them is agency. The primary function of agency section/unit is to collect collect informa information tion from offices offices and statement statements s from banks and to reconcile reconcile these statements. The agency section is fully computerized however the agency section at Thamel branch and Pulchowk branch use different software to carry out their operation. These independent systems are incompatible. Though there is no requirement for the Pulchowk’s agency section to work on Thamel ‘s data it is Thamel’s agency section that has to carry out the entire reconciliation process. In this project the team proposes a single system that carries out the entire agency functionalities that includes from statements entry, to batch import from schedule collection, to reconciliation to ledger file generation at the end of every fiscal year. The project will also study the need for business process re-engineering as well as implemen implementati tation on of technolo technology gy (softwar (software, e, hardware hardware,, and network) network) to enable enable the process. The system will support multiple users, will be able to import data from files with various formats, generate MIS reports and automate most of the agency process. The system will also generate the year end ledger files.

Contents SIGNATURE PAGE........ ............... ............... ................ ................ ............... ............... ................ ............... ............... ................ ................ ............... ........... .... 1 COPYRIGHT & DISCLAIMER .................................................................................. ........................................................................................ ...... 2 ACKNOWLEDGEMENT ACKNOWLEDGEM ENT....... ............... ................ ................ ............... ............... ................ ............... .............. ........... ........ ........ ........ ........ ........ ........ ......3 Executive Summary....... ............... ................ ................ ............... ............... ................ ............... ............... ................ ................ ............... ............ ........ ... 4 Contents ........ ............... ............... ................ ................ ............... ............... ................ ............... ............... ................ ................ ............. ......... ........ ........ ........ ....... ... 5 Organization Organizatio n....... ............... ................ ............... ............... ................ ............... ............... ................ ................ ............... .............. ........... ........ ........ ........ ........ .... 1 History ........ ............... ............... ................ ............... ............... ................ ................ ............... ............... ................ ............... ............... ............. ......... ........ ........ .... 1 Objective............................................................ ....................................................................................... ....................................... ........................ .............. ..1 Statistics ........ ................ ............... ............... ................ ............... ............... ................ ................ ............... ............... ................ ............... .............. ........... ....... ... 2 ICT Usage in the organization ....... ............... ................ ................ ............... ............... ................ ............... ............... ................ ............ ......3 Hardware infrastructure ....... ............... ................ ............... ............... ................ ............... ............. .......... ........ ........ ........ ........ ........ ...... .. 3 Network infrastru infrastructure cture........................... .......................................................................... .......................................................... ............. ..3 Existing Database ........ ................ ............... ............... ................ ................ ............... ............... ................ ............... ............... .............. .......... .... 4 Existing Software ........ ................ ............... ............... ................ ............... ............... ................ ................ ............... ............... .............. .......... ......5 ICT strategies at EPF......................... .................................................... ................................................ ................................ ...................... ........... 7 AGENCY SECTION........................... ...................................................... .............................................................. ............................................... ............ 10 Organizational Organizati onal structure of the Agency Section ........ ............... ............. .......... ........ ........ ........ ........ ........ ...... ..10 ................................................... ........................ ...................................................... ................................................................... ........................................... ... 11 Workflow at agency system....... ............... ................ ............... ............... ................ ................ ............... ............... ............... ......... ..12 Information Requirement ....... ............... ................ ............... ............... ................ ............. ......... ........ ........ ........ ........ ........ ........ ...... .. 13 Position ........ ................ ............... ............... ................ ............... ............... ................ ................ ............... ............... ................ ............... ........... ........ ......13 Activities Activiti es and Requirement ....... ............... ................ ................ ............... ............... ............ ........ ........ ........ ........ ........ ........ ........ .... 13 Department Head....... ............... ............... ............... ................ ................ ............... ............... ................ ............... ............ ......... ........ ........ .... 13

Manager.................................................. .................................................................................................. ......................................................... ......... 13 Section Chief.......................... ..................................................... ............................................................. .............................................. ..............13 Section Officer ........ ............... ............... ................ ............... ............... ................ ................ ............... ............... ............... ........... ........ ........ ...... .. 13 Supervisor ........ ............... ............... ................ ............... ............... ................ ................ ............... .............. ........... ........ ........ ........ ........ ........ ........ .... 13 Data entry operators ........ ............... ............... ................ ............... ............... ................ ................ ............. ......... ........ ........ ........ ........ ......13 Revised Section structure stru cture of Agency section ........ ................ ............... ............... ................ ................ .............. .......... .... 14 ..................................................... .......................... ...................................................... ......................................................................... .............................................. 14 Data Access Policy ........ ................ ............... ............... ................ ................ ............... ............... ................ ............... ............... ................ ............ .... 15 Data entry operators ........ ............... ............... ................ ............... ............... ................ ................ ............. ......... ........ ........ ........ ........ ......15 Data Importers ........ ................ ................ ............... ............... ................ ................ ............... .............. ........... ........ ........ ........ ........ ........ ........ .... 16 Supervisors ........ ................ ............... ............... ................ ................ ............... ............... ................ ............... ............... ................ ............. ......... ......18 Section Officer ........ ............... ............... ................ ............... ............... ................ ................ ............... ............... ............... ........... ........ ........ ...... .. 19 Proposed System........ ................ ............... ............... ................ ................ ............... ............... ................ ............... ............... .............. .......... ........ .... 23 Hardware Requirement ....... ............... ................ ............... ............... ................ .............. .......... ........ ........ ........ ........ ........ ........ ........ .... 23 Software Requirement ....... ............... ............... ............... ................ ................ ............... ............... ................ ............... ............ ......... ......24 Gap Analysis....... ............... ................ ................ ............... ............... ................ ............... ............... ................ ................ ............... ............. .......... ........ .... 24 Technological Technolo gical gap....... ............... ................ ............... ............... ................ ............... ............... ................ ................ ............... ............... ..........24 Entity relationshi relationship p diagram........ ............... ............... ................ ............... ............... ................ ............ ........ ........ ........ ........ ........ ........ ........ .... 27 Physical Architecture ........ ................ ............... ............... ................ ................ ............... ............... ................ ............... ........... ........ ........ ........ ....... ... 28 Application Architecture ....... ............... ............... ............... ................ ................ ............... ............... ............. ......... ........ ........ ........ ........ ........ ......29 Conclusion and Recommendatio Recommendation n........ ................ ............... ............... ................ .............. .......... ........ ........ ........ ........ ........ ........ ....... ... 30 References ....... ............... ................ ............... ............... ................ ................ ............... ............... ................ ............... ............... ................ ................ ............ .... 31

Organization History In Nepal, the history of Provident Fund (PF) dates back to 1934 when the PF scheme came into existence with the establishment of Sainik Drabya Kosh (Army Provident Fund) during the Rana Regime. The scheme was initiated with the intentions of removing financial hardships to the army personnel after their retirement. Under the scheme, the army staffs were required to contribute a specific percentage of their salary to their provident fund (PF) account in Sainik Drabya Kosh. A decade later the scheme was broadened to cover the employees of civil services as well. A separate organization named Nijamati Provident Fund was established in 1944 to manage the scheme for civil servants working within Kathmandu. Kathmandu. In 1948 the coverage of the scheme was extended to provide coverage to the entire civil servants working throughout the kingdom of Nepal. In 1959, Employees' Provident Fund Department was established under the Ministry of Fina Financ nce e and and Econ Econom omic ic Affa Affair irs. s. This This depa depart rtme ment nt was was entr entrus uste ted d with with the the management of both Sainik Drabya Kosh and Nijamati Provident Fund. With this, the scope of the scheme was extended to cover all government employees including the police. Three years after the establishment of Employees Provident Fund Department, a special Act called "Karmachari Sanchaya Kosh (or Employee's Provident Fund) Act" was legislated in the year 1962. The same year the present Karmachari Sanchaya Kosh (KSK), or Employees Provident Fund (EPF) in English, was established under the act as an autonomous provident fund organization. After the establishment of EPF the erstwhile Sainik Drabya Kosh, Nijamati Provident Fund and Provident Fund Department were merged into the EPF. Since then EPF has grown by leaps and bounds and today it stands as a strong social security providing organization in Nepal.

Objective The primary objective of the Karmachari Sanchaya Kosh (KSK) or the Employees Provident Fund (EPF) in English is to manage the provident fund of the government, public and private sector employees and to help them financially on retirement or separation from their jobs.

Related other objectives of KSK are: 1|Page

•

•

•

•

•

To mobilize the savings received in KSK through the compulsory provident fund contributions on the part of employees and employer. To extend the KSK coverage so that a larger section of the organized sector employees, who have yet not been covered, can be brought under the KSK umbrella with a view to benefit them. To generate maximum return on the investment of KSK for the benefits of the members. To undertake activities that can provide social security to the members of KSK. To conduct research activities and to explore welfare schemes, this benefits the KSK members at large.

Statistics Total Contributors Contributors Total Contributing Offices Provident Fund Loan to Con Contrib tribut uto ors Reserve Fund (est) Investment Fixe Fixed d Dep Depos osit it iin n Ban Banks ks Government Bond Investment in Buildings Inves nvestm tmen entt in in Sh Shares ares Investment in Fixed Assets Other Funds

2|Page

4 Lakh 40 Thousand Total 28 Thousand NRs. 7266.98 Crores NRs NRs. 35 3557 57.0 .09 9 Cro Crores NRs. 437.80 Crores NRs. 354.31 Crores NRs. NRs. 25 2578 78 Cror Crores es NRs. 664.91 Crores NRs. 38.05 Crores NRs NRs. 91 91.80 .80 Cr Crores NRs. 45.37 Crores NRs. 585.56 Crores

ICT Usage in the organization EPF is one of the very few organizations in the country that uses ICT at maximum from from acco accoun unti ting ng to tran transa sact ctio ion n manag anagem emen entt to emp employe loyees es’’ info inform rmat atio ion n management. It has its own website, email system, and well managed network.

Hardware infrastructure No of Servers (High End): Database server :1 Application Server : 1 Backup Server :1 Remote Server :1 • • • •

Low-end Servers: DNS Server : 1 Web Serve erverr :1 Database Server (Web) : 1 Internal Web Server :1 • • • •

Router: Fortinet Routers: 8 Printers: Large Laser Printers: 2 Small Laser Printers: 45 Line Printers: 25 Others (Dot Matrix, Cheque Printers etc): 50 Client Computers:

330

UPS: 200 Scanners: 45

Network infrastructure The computers at EPF Thamel branch are all in a network and are capable of sharing files, files, print printers ers,, scanne scanners rs and and hardwa hardware. re. The The use of networ network k has enable enabled d EPF to maximize the utilization of resources by sharing it, for example every section has not more than 5 printers that are simultaneously used by around 30 users. There are scanners that are also shared due to which there is no requirement to have a separate printer for individual teller. Pulchowk branch is semi-networked with more than 70% of the computers in a network, the network of the Pulchowk branch is also managed by the Computer Management Department at Thamel. 3|Page

Outs Outsid ide e bran branch ches es like like Heta Hetaud uda, a, Bira Biratn tnag agar ar,, Pokh Pokhar ara a and and Butu Butuwa wall are are full fully y networked. These networks are in turn connected to the Thamel LAN with lease line and and thus thus maki making ng a VL VLAN AN.. All All thes these e netw networ orks ks are are mana manage ged d by the the Comp Comput uter er Management Department at Thamel. In branches the support is provided by the computer operators or the local technicians.

Router: Fortinet Routers: 8 Software Windows Server 2003 (internal network management) Linux Enterprise Edition – 2005 (web management) Network Analyzer: 1

Existing Database EPF has Oracle 9i Release 2.0 RDBMS database. Before that it had foxpro database system.

4|Page

Existing Software The major software systems are listed below

CMS EPF was one of the pioneer organizations that adopted computer system over the manual system. EPF had its first computer in the mid 80s for the accounting purpose. Later it built a Fox-pro based Contribution Contribution management system in house and remained as its core system till early 2000. Later the need for a centralized contribution management management system became inevitable and EPF decided to build a new centralized system in-house with partnership with some other software company. It called for proposal and IT-Nepal got the contract and the project CMS started, the whole system was migrated into the new CMS system and it was in the year 2005 the fiscal year ledger processing was done in the CMS system and CMS system officially became the main Transaction Transaction processing system in the organization. organization. Following are the major characteristics of the CMS system 1. 2. 3. 4. 5. 6.

CMS is a centra centralized lized Contr Contribut ibution ion Managem Management ent System System CMS is a multi multiuse userr syst system. em. CMS has has Oracle Oracle Datab Database, ase, Oracl Oracle e 9i relea release se 2. CMS was built built in Oracle Oracle 6i 6i form and and reports reports develope developer. r. CMS ownersh ownership ip lies at the the hand of Employee Employees s Provident Provident Fund. Fund. CMS is basically a transaction transaction processing processing system system but but has now been expanded to cover accounting, investment investment and MIS reporting. 7. CMS is a window window based based software software solution solution..

Website EPF with its purpose of information information dissemination launched its website in early 2000 but but the the webweb-si site te was was reva revamp mped ed to make make it serv servic ice e orie orient nted ed in the the year year 20 2007 07 allowing the users to log-in into the system and check the status of their accounts. The website of EPF can be visited at http://www.epfnepal.com.np http://www.epfnepal.com.np.. The website was also built in-house and is managed and hosted by Employees Provident Fund itself. One One can can find find most most of the the neces ecessa sary ry info inform rmat atio ion n abou aboutt EP EPF, F, its its serv servic ices es,, organizational structure, tender notices, vacancies and statistics in its website. In order to simplify the process of service delivery EPF has also made various forms e.g. loan form, payment forms, contribution deduction forms in the internet. The contributors can simply download these forms print them and then submit it to the EPF offices. It is from the web-site that the contributors and their offices can get secure access to their account related information.

Email EPF has also an email system of its own and this email system is also managed by EPF itself. Recently EPF has also been providing services to its contributors and to 5|Page

other information seekers via notice board services. It provides basic information to its contributors on the notice-board. All the departments, sections, branches have thei theirr own own emai emaill ids ids and and the the comm commun unic icat atio ion n can can be done done with with emai email. l. With With impl implem emen enta tati tion on of emai emaill ther there e has has been been a dras drasti tic c drop drop in the the numb number er of the the traditional mail handling and EPF is aggressively looking for reducing the number of traditional mail handling by 50%.

Chat System To facilitate the internal communication, EPF has an internal chat system where the employees can exchange information in real time. This chat server is managed by the computer Management Department and is a free source software system.

IMS system EPF also has an internal web-based system that is to get into the shape of the core MIS system of the organization. IMS (Information Management System) currently serves circulars, inventory reports, software related issues and transaction reports or previous fiscal year.

EMS system Emplo Employee yees s Manage Managemen mentt System System (EMS) (EMS) is inten intended ded to be the Human Human Resour Resource ce Informat Information ion System. System. It has the records records of employees employees like their personal personal record, record, educational record, professional professional experience, training and HR inventory report.

6|Page

ICT strategies at EPF As stated in earlier paragraphs Employees Provident Fund is one of the pioneer organizations as far as adoption of the computer system is concerned. From a one compu computer ter,, non-ne non-netwo twork rk system system organ organiza izatio tion n it has grown grown up into into a comple completel tely y compu computer terize ized d organi organizat zation ion and there there are no manua manuall proces processes ses in EP EPF. F. EPF has always realized the importance of the ICT in terms of information management, accuracy accuracy and reliabil reliability. ity. It has a policy policy of one man one computer computer and ubiquitous ubiquitous role based information access policy. It has eight branches and a central office and all these branches are linked with the servers stationed at the Computer Management Department at Thamel branch. Currently only 3 branches are linked with optical fiber connection with the Thamel branch, however there has been a strategy to link all the branches via optical fiber and have a virtual Local Access Network (vLAN) so that the systems across all the branches can communicate seamlessly. EPF is also looking toward the expansion of its web-services to incorporate the capability to accept the contribution schedules from various offices. In the mean time time to make make its its tend tender er and and bidd biddin ing g proc proces ess s tran transp spar aren entt it has has stra strate tegy gy to implement the e-bidding system. It also has plans to support the account unification and ledger distribution process via its website that will not only facilitate the contributors but also reduce the work load for the employees. Since all the computers in the EPF offices (8 braches) are in the network, they can share information and despite the geographical distance the people are brought near especially at the click of the mouse. EPF also EPF also has has plan plans s to have have an emai emaill id for for all all its its staf staffs fs to faci facili lita tate te thei theirr communication communication requirement. EPF is also looking toward the implementation of digital signature to authenticate its digital transactions. It has few other plans to maximize its reach to the customers and these plans include following: i) SMS SMS ba based servi ervice ce Under Under this this system system,, the contri contribut butor ors s havin having g a mobil mobile e and wanti wanting ng to subscribe to the SMS service will be given a pin-code and on the basis of this pin code they will be able to check the status of their contribution, loan and other services that they are eligible to receive. ii) ii) Kios Kiosk k syst system ems s EPF also also aims aims to stall stall few kiosk kiosk system systems s (self (self inter interact active ive syste systems ms)) at different places especially in its own premises and industrial areas so that the contributors will be able to access the services of the EPF on their own. This system will be similar to the ATM systems however they will not allow transactions. transactions. iii) iii) IVR syste system m 7|Page

EPF also plans to provide services via Interactive Voice Response where toll-free number will be dedicated that will be linked with the Database system at EPF and which upon user query will retrieve user information based on the pin-code provided by the user.

8|Page

Work-flow Start

Offices deposit the PF deductions and receive a voucher from bank

Agency Section Agency collects statements from banks

Offices send the schedules with the bank voucher attached to KSK The Collection section receives the schedule and makes batch entry

Bank sends the draft to the pulchowk’s investment department

Investment department maintains records of bank drafts

The entry made by collection is available to agency, account and sections

Contribution entry into individual account of employees Year end ledger processing (passing interest and generate opening balance for next fiscal year)

Ledger file

End

9|Page

Ledger file

Special Loan/Full Payment

AGENCY SECTION The main function of the Agency section is the collection of the bank statements from from variou various s banks banks where where the contri contribut buting ing office offices s depos deposit it the the provid provident ent fund fund deduction of their employees. The agency function collects these statements from all over Nepal and the data entry operators post these individual transactions into the computer. On the other hand the Collection section receives the contribution schedule from the contributing offices and makes the batch entries based on the bank voucher submitted to it. Among the various banks, the current practice is to receive the bank statements from the private and joint venture banks from the Investment Department located at Pulchowk i.e. instead of Agency section directly receiving the statements from private and joint venture banks these are received by the investment department and they send it to the Agency section. At the end of a fiscal year, Agency section reconciles the bank statements that have been entered by the data entry operators with the entries made in the ‘Collection’ section. After the reconciliation process the statements that are not reconciled are put on the pending files for reconciliation later. So, in a given fiscal year the Agency section can can recon reconcil cile e the the pendin pending g statem statement ents s from from curren currentt fisca fiscall year year and other other years years which statements have not been reconciled.

Organizational structure of the Agency Section

10 | P a g e

Department Head

Manager

Section Chief

Senior Officer

Supervisor

Data Entry

11 | P a g e

Data Entry

Data Entry

Data Entry

Data Entry

Data Entry

Data

Workflow at agency system 1.

Deposit PF

Contributi ng Offices

Bank

2. Receive voucher 3. Send schedule with voucher

COLLECTION SECTION

3. Send statements of transactions

AGENCY SECTION

2.

schedule

1.

statement

RECONCILIATION PROCESS

Reconciliati on Statements

12 | P a g e

Pending statements

Information Requirement Position

Activities and Requirement See the status of the pending statements Check the status of the reconciliation reconciliation process Check the pending amounts bank wise Submit the status of agency to the management committee Compare summaries of collection and agency information. Check consolidated report of the collection and agency section. Check the status of pending and reconciled statements. Compare the pending amount fiscal year wise. Verify the transaction with the collection section, investment department. Check the summary of the transactions. Prepare various kinds of reports to be submitted to upper level. Report the status of pending statement to senior level. Check the summary of the transactions. Prepare various kinds of reports to be submitted to upper level. Report the status of pending statement to senior level. Approve the data entries made by the operators. Check the list of approved transactions Check the list of total entries made on the basis of date range, entry operator, banks. Make the data entries into the system. Check the approved transaction. Check the rejected entries. Check the status report of entries in terms of number of entries and dates. •

Department Head

• • •

•

Manager

•

•

•

Section Chief

•

• •

•

•

Section Officer

•

•

•

Supervisor

• •

•

Data entry operators

• • •

13 | P a g e

Revised Section structure of Agency section Department Head

Manager

Section Chief

Senior Officer

Supervisor

Data Entry

14 | P a g e

Data Entry

Data Entry

Supervisor

Data Entry

Data Im orter orters s

Data I m orters

Dat a I

Data Access Policy There is no requirement for a separate sever just for the agency section. Agency databa database se shall shall be a part part of the the entire entire CMS (Contr (Contribu ibutio tion n Manage Managemen mentt System System)) database. We do not even recommend a separate schema for the agency. However each table, views, procedures and other related objects should be highly secure. There should be a separate access control list for the agency section. The access policy should be based on the duties and responsibilities of the employee. There can be following users

Data entry operators It will take a long time for the agency section to receive all the statements from the various banks in a digital format so we cannot avoid the data entry operators. These operators will post the transaction statements that EPF receives in paper into the computer system. These users shall have following rights Forms Pas Passwor word chang hange/ e/m modif odific icat atio ion n form orm This ffor orm m will will all allo ow the the user users s to ch change ange password password when they want it to change or when the password expires. Data entry form

It is in this form that the data entry oper operat ator ors s shal shalll post post the the tran transa sact ctio ion n statements into the CMS system.

Data submit form

After the data entry operators finish their tasks, they will make a final check and submit the records for release/approval to the supervisors.

Reconciliation form

This fo form wi will al allow th the op operators tto o reconcile reconcile the bank statements statements and the batch entries.

Message form

It is via this form the operators should be able to send messages to their supervisors or receive/read their messages and replies

Reports Data entry report

The operators shall be able to see the entri entries es they they have have made. made. There There can can be three types of such reports and they are i) ii)

15 | P a g e

Data Data entr entry y rep repor ortt bef befor ore e sub submi miss ssio ion n Data entry report of submitted transactions.

Activity report

Reconciliation report

iii) Data entry report of approved entries. iv) Data Data entry entry report report of rejec rejected ted entri entries es or entries with notes specified. This re r eport wi w ill di d isplay th t he various activities that the operators have carried out for e.g. total number of entries, total vouche voucherr pass passed, ed, numbe numberr of log in into into the system, number of approved transactions transactions etc. The da data im import op operators wi will al also be be able to see the reconciliation status of the banks that they have reconciled.

Database area The entries are not posted directly into the master table but into the online transaction table. The operators will have full access to the subset of the online transaction table so that they will be allowed to enter records, update and delete the records that they have entered. The operators will have read-only access to the master table and only in the subset of the data i.e. they will be allowed to read only those data that they have entered. They will have read only access to the user credentials table but will be allowed to update their passwords. •

•

•

•

Data Importers The main job of the data importers is to import the digital data received by the Agency section into the CMS. As per the current practice the data are received in excel files however the format varies between various banks. The role of the data importers is to convert the excel files in the required format and to import these converted format in the CMS system. These users will have following rights

Forms Excel data import form

With th this fo form th the da data im importers wi will import excel files into the CMS.

Import-edit form

In order to check and edit the mistakes that that coul could d have have rema remain in unse unseen en and and unsolved in excel files can be edited and updated in this form.

16 | P a g e

Data import from Collection

The data importers will also be responsible for process of importing the batc batch h entr entrie ies s made made by the the coll collec ecti tion on section.

Data submit form

After the data entry operators finish their tasks, they will make a final check and submit the records for release/approval to the supervisors.

Reconciliation form

This fo form wi will al allow th the op operators tto o reconcile reconcile the bank statements statements and the batch entries.

Message form

It is via this form the importers should be able to send messages to their supervisors or receive/read their messages and replies

Reports Data import report

The operators shall be able to see the entri entries es they they have have made. made. There There can can be three types of such reports and they are i)


Activity report

17 | P a g e

Data import report before submission ii) Data import report of submitted transactions. iii) Data import report of approved entries. iv) Data import report of rejected imports or imports with notes specified. The da data im import op operators wi will al also be be able to see the reconciliation status of the banks that they have reconciled. This re r eport wi w ill di d isplay th t he various activities that the importers have carried out for e.g. total number of imports, total vouche voucherr pass passed, ed, numbe numberr of log in into into the system, number of approved transactions transactions etc.

Supervisors The main responsibilities will be to evaluate the entries and imports done by the operators and the importers. They will be responsible for the approval process of the transaction. Beside they will also validate the data fetched from the collection section. The entries and imports will be posted into the main/master file only after the approval of the supervisors.

Forms Transactions viewing form

Transactions approval form

Batch import form

This form will let the supervisors to chec check k the the tran transa sact ctio ions ns,, make make mino minorr changes when necessary and write note in the transaction. The ma main ta task of o f th the su supervisors iis s tto o valida validate te the entri entries es carrie carried d out by the data entry operator and verify the data imports carried out by the data importer rters s. So, So, this for form let lets them them validate the transaction and approve the data data to be writ writte ten n into into the the base base file file.. They will also be able to invalidate the data. The other activity that th the supervisors need to do is to import the entries made in the collection section. Batch import is not something that is regularly done and it involves various manual processes like gett gettin ing g appr approv oval al from from the the mana manage gers rs,, section chief so this task is that of the supervisors.

Reports Pending transactions

Approved transactions Status of reconciliation

Ledger reports 18 | P a g e

With this report the supervisors will be able to see the transactions waiting for appr approv oval al or rele releas ase. e. This This tran transa sact ctio ion n repo report rt can can be view viewed ed on the the basi basis s of bank code, user, and date range. This report displays the transactions approved by the supervisors. This report gives the amount of pending transactions transactions i.e. the transactions that are yet to be reconciled and the transactions that have already been reconciled. The supervisors will also be allowed to

view view the the ledg ledger er stat status us repo report rts s of the the various fiscal years.

Section Officer The main responsibilities of the section officer are to ensure the smooth operation of the section, the entries are being made, the work is properly distributed and the reconciliation process is in progress. He needs to report the status of the work basically the pending statements and reconciliation to the section chief and has to supervise the staffs at the lower level of hierarchy.

Forms Data display form

In this form the reports of the data that have been released or approved by the supervisor is displayed. In this form the offi office cerr will will be allo allowe wed d to dril drilll-do down wn further, make notes.

Setup forms

These forms will be used to add/upda add/update/de te/delete lete new master master records records like banks, offices, offices, agencies agencies and other other financial statements.

Reports Reports of pending statements

Number and amount of the pending statements and ledger.


This report will display the state of the reconciled statements.

Activity report

With this report the section officer will be able to view the number of entries made by data entry operators, data importers and the approvals.

Section Chief/Manager The main responsibility of the section chief and the manager is to formulate the work workin ing g plan plan,, comm commun unic icat ate e with with bank banks, s, inve invest stme ment nt depa depart rtme ment nt in orde orderr to smoo smoothe then n the reconc reconcili iliati ation on proces process. s. They They are are also also respo responsi nsible ble identi identifyi fying ng the stat status us of the the bank bank acco accoun unts ts and and reco recomm mmen end d the the open openin ing g and and clos closin ing g of the the accounts to the department chief. 19 | P a g e

Reports Reports of pending statements



This report will display the status of the reconciled statements.

Activity report


Bank wise pending reports

With this report the section chief will be able to identify the banks with whom the volume volume of pending pending transactions transactions is high and needs communication.

Comparative pending reports fiscal year This will be a graphical report displaying wise the comparative reports on the pending statements/ledgers.

Department head The main responsibility regarding the agency section is to ensure that the offices are routinely depositing contribution deduction, the tax, interest, commission are prop proper erly ly acco accoun unte ted. d. The The depa depart rtme ment nt head head only only requ requir ires es view viewin ing g repo report rts s (consolidated) (consolidated) on the status of pending and reconciled statements. Reports Reports of pending statements



This report will display the status of the reconciled statements.

Activity report*


Bank wise pending reports


Comparative pending reports fiscal year This will be a graphical report displaying wise the comparative reports on the pending 20 | P a g e

statements/ledgers. * The managers will not require this report.

21 | P a g e

Management Management refers to the chief officer, administrator and members of board of directors who have to formulate various policies and strategies that can affect the activities of Agency section of EPF. Bank wise pending reports


Comparative pending reports fiscal year This will be a graphical report displaying wise the comparative reports on the pending statements/ledgers.

22 | P a g e

Proposed System The proposed system will not be a different system but will be modification to the existing system. Few of the features of the proposed system are listed below It will be a web-based system with interface to accommodate requirement of the external parties/partners. parties/partners. It will be a server based system with thin-client architecture and most of the processing will be handled by server for e.g. the reconciliation process. It will incorporate the information requirement at all levels of management and cater their needs. The communication system will be taken special care with email system built as a part of the system to communicate with the banks and with the system being capable to send email to banks when the delay in statement receipt is noticed. It will have a powerful error tracking feature, user alert features and reconciliation messages. A smart module will be designed that will not only accelerate and enhance the reconciliation process but will also inform user on possible measures that can be carried out to enhance reconciliation reconciliation for e.g. it will compare the collection entries with the statement entries to check the level of similarities of information. Added information like Cheque number, contribution-deduction contribution-deduction month and office code will be made mandatory. The same system will include the data processing processing at the Pulchowk branch so that the system will automatically receive the data from the investment department. A powerful multi-format supporting supporting module will be built to make it possible to import excel files provided in different format. •

•

•

•

•

•

•

•

•

Hardware Requirement •

• •

1 High-end server (Shared server, no dedicated server requirement for Agency section alone) 1 Back-up server 9 desktop computers at client end (on the basis of one man one computer policy, currently there are 9 staffs at the agency section) Computers that shall be used for the reconciliation reconciliation Monitor 17” flatron CPU Minimum 3 GHz Hard Disk Minimum 180 GB RAM 1 GB of RAM CD-DVD ROM (RW) latest Computers that shall be used for data entry and import Monitor 17” flatron CPU Minimum 1 GHz

23 | P a g e

Hard Disk RAM CD-DVD ROM (RW) •

Minimum 40 GB 256 MB of RAM latest

Centralized UPS for Agency section (the UPS should support minimum 50% of the computers to run 4 hours without need for shutdown)

Software Requirement As with with the hardwa hardware re system system there will will not be any indep independ endent ent and differe different nt software system for the Agency section. The software system shall be a part of the entire CMS module. However unlike the current system where there are different redundant systems that often generate incompatible data formats there shall be a single system that will be implemented in investment department, branches and the the Tham Thamel el Ag Agen ency cy sect sectio ion. n. The The syst system em sh shal alll su supp ppor ortt data data sh shar arin ing g and and comm commun unic icat atio ion n in real real time time amon among g multi ultip ple us user ers s and and the the sect sectio ions ns.. The communication communication system will enable the Agency section to receive digital statements, notifications notifications from banks and other agencies. The major software requirements are listed below: i) ii) iii) iv) v) vi)

Oracle Oracle Relation Relational al Databas Database e managemen managementt system system (Releas (Release e 9i or later) later) Windows Windows operati operating ng system system (Windows (Windows Server Server 2003 2003 or later) later) Windows Operating System (Windows XP) Internet Internet Explorer Explorer 5 or greater greater Antiv ntivir irus us Security Certificate Certificate for communication communication with the third party party like banks

Gap Analysis Technological gap T The he curr curren entt syst system em does does have have a one one user user one one comp comput uter er poli policy cy howe howeve verr the the computers are very old and hence slow. This hampers the reconciliation process as it takes more than hour to reconcile small banks which shouldn’t have otherwise taken less than half hour. The RAM in these computers has little data capability with few only 32 MB. Most of the reconciliation process is carried out in the client system due to which the network traffic rise unnecessarily and slows the other programs as well. Again the client computers are not high end and they take comparatively longer time to execute execute the reconciliat reconciliation ion process. process. So, there is a requirem requirement ent to carry carry out this reco reconc ncil ilia iati tion on at the the serv server er end. end. This This can can be done done by writ writin ing g modu module les s and and proced procedur ures es (data (data base) base) which which will will cut down down the networ network k traffi traffic, c, smoot smoothen hen the the reconc reconcili iliati ation on proces process s and ultima ultimatel tely y lead lead to timely timely comple completio tion n of the ledger ledger processing task at the end of the fiscal year. 24 | P a g e

The other point that will help the reconciliation process is making the entry of cheque no mandatory during the statement entry process. The current practice has been the ‘Collection’ section does not enters the cheque number otherwise the reconciliation process would have been far more effective and lesser statements would be pending.

25 | P a g e

Non-technological gap Agency section handles one of the most crucial tasks of EPF but there has not been proper policy, strategy and concern in the Agency section. In the first place the work work done done at Ag Agen ency cy sect sectio ion n is a very very rigo rigoro rous us proc proces ess s and and also also requ requir ires es a comparatively greater number of staffs to complete the tasks. However currently the staffs in Agency section have been the ones with history of absenteeism and they also lack proper IT skills which makes the entry process prone to error. The entry operators have no motivation for making entries so it is very important to give them some cash benefit on the basis of number of entries. On the other hand it is also important to take disciplinary actions against the employees who remain absent for a long time and who avoid work by dedicating most of their time in union related activities. It is also important for the entire management to consider the bank transactions with much care and attention, the attitude of “bank will keep records” does not help now and the amount that is not reconciled might be subject to embezzlement and misuse. One of the major problems is despite being able to do its tasks independently; the Agency section has to depend on other sections and departments for data e.g. it has to constantly ask the “Investment Department” to provide it the statements of the private banks. The other problem of the Agency section is that the number of banks is more than necessary. The main problems are with the government owned banks like Nepal bank Limited, Rastriya Banijya Bank, Agriculture Development Bank as they have very poor accounting practices and they do not provide the statements timely. In many places there are no options but to rely on these banks but on others there are competitive private banks who are far more effective in management of accounts and sending the statements to EPF. On the other hands there is more than one account in a particular bank and these accounts are very redundant and make the account management so the redundant bank accounts should be closed.

26 | P a g e

Entity relationship diagram

27 | P a g e

Physical Architecture The physical architecture consists of the main server (database, middleware server, webserver) as the source of information. The client computers will be connected with the server via switches and hubs and each section shall be in a different subnet. There will be a firewall that will separate the external network (internet and partners network) and the internal system shall be connected with the external networ network k with with route router. r. Follow Following ing is the pictor pictorial ial repres represent entati ation on of the propos proposed ed physical mode. The branches can be inside the firewall and will seamlessly work as the part of the LAN of EPF.

28 | P a g e

Application Architecture The application architecture consists of three tier module consisting of the database server server,, the busine business ss logic logic server server and the the web-se web-serve rver. r. The client clients s will will pass pass the the request information to the web-server which in turn will demand the credentials from the clients and when supplied with credentials will forward the request to the business logic server. The business logic server will check the credentials; get the access rights list to the web server. The web-server will in turn provide a ticket (sess (session ion-ti -ticke cket) t) to the client clients. s. The The client clients s have have to pass pass this this ticket ticket with with every every request. The database server will perform very few logical operations apart from supplying supplying informat information. ion. The informa information tion supplied supplied will be tested tested against against business business logics of EPF by the Business Logic Server and will pass the reworked data to the web-server. The web-server will format format the data it receives in the readable form and supplies them back to the clients and the clients will do their tasks. So, this model has following major components: • • • •

Database server/layer Business Logic server/layer Web-server/Application Web-server/Application layer Clients

The The model model is based based on thick thick server server-th -thin in client client model model.. Except Except for simpl simple e data data validation, no checking and control will be done at the clients. More than 90% of the data processing will be done at the server end.

29 | P a g e

Conclusion and Recommendation Our team proposes to develop a web-based system for the Agency section with proper security features allowing the seamless communication between the Agency section, Investment department and the external agencies like bank. We propose the web-ba web-based sed system system becaus because e web-ba web-based sed system system needs needs no client client softw software are management though it is based in the client server architecture; it is light and properly manageable. We also recommend that important processes be written into the database in the forms of procedures, views, triggers and packages however we recommend the proper use of business layer system or the middleware technology that shall allow the change in business process without actually changing the other part of the system. It is also advised to try to make system that is less tied to the database or the web-s web-serv erver, er, so that that if there there is change change is the databa database se softw softwar are, e, the the same same business logic shall be compatible with minimal adjustments. A speci special al care care should should be given given to the securit security y as web-ba web-based sed system system is more more vulnerable to security threats unlike windows based system. Highly capable firewall technology should be implemented that shall pose very little threat. On the non-technical front it is advised to build a proper training package for the staffs. The architecture of the agency section also needs adjustment making it more independent and each staff with a clear set of task and responsibilities.

30 | P a g e

References

31 | P a g e

MIS Project

Recommend Documents