Data automation, security and accuracy

Technology Partnership Group, Inc. (TechPG) developed a custom Web application for the Michigan Department of Treasury, Bureau of Bond Finance (US).

The Michigan Department of Treasury, Bureau of Bond Finance needed to develop a Loan and Bond Management System (LBMS) with the ability to track, process and report on State of Michigan-issued bonds and their corresponding loan programs.

In 2006 an assessment was conducted by the State to identify business processes, determine technology needs, and study the feasibility of replacing the systems used by the agency for bond and loan management. The systems consisted of two desktop applications and numerous, independently-created Excel spreadsheets with no integration between them.

The findings revealed a significant duplication of processes and data across the Bureau. The analysis also revealed that due to the similarity of the processes, all the loans, bonds and state debt across the Bureau could be integrated into a core Loan and Bond Management System, incorporating all the current systems in a phased approach, beginning with the School Loan Revolving Fund. TechPG was chosen to develop the system and implement the first phase.

TechPG followed the State’s structured software development and project management methodologies, policies, standards and guidelines and the application was designed to comply with all applicable State IT standards.

TechPG used the GeneXus IDE to prototype a system that integrated the functions and operations of the two independent systems and multiple spreadsheets and interfaced with outside data sources. By using a prototype approach and incremental changes, the Bureau staff was able to understand the application’s processing and how it would be used to meet their needs.


The GeneXus-developed system allowed us to use valuable business analysts with technical skills as opposed to “heavy” technical resources, which eliminates the task of translating user needs to technical staff. According to TechPG: "Using technically-savvy business analysts to program in GeneXus and given the amount of “heavy lifting” GeneXus does through code generation, we estimate that GeneXus saves us more than 70 percent of the normal development and testing effort experienced on other government systems development projects."

The ability to quickly prototype and make incremental changes was another GeneXus feature that enabled the quick launch of the project.

Developing with GeneXus also provided environmental independence for the online and database environments. In addition, the use of patterns allowed for a consistent user interface.

Description of the Solution

To address the information management and financial record-keeping needs of the Michigan Department of Treasury, Bureau of Bond Finance (Bureau), TechPG developed a unique software solution that integrates the processing, tracking and reporting of bonds and their corresponding loan programs.

The solution, LBMS, can be accessed through a browser and was engineered to provide a highly intuitive and user-friendly online interface in a web environment.

LBMS is user-configurable, features simple menu navigation, provides strong security, presents multiple reporting options, and has integrated online help.

LBMS was implemented in a .Net web environment using SQL Server. Data access by authorized users is typically “one click away” and basic training on the system is accomplished in under one day.

LBMS calculates and tracks all debt payments due for the bonds and has extensive reporting and notification services. Bond data entry utilizes a wizard-based interface where data is collected in groups prior to performing bond debt calculations. Loan tracking and processing utilizes integrated email capabilities which generate detailed documents and Excel spreadsheets and automatically emails the required forms to borrowers for periodic processing.

LBMS integrates with existing legacy systems for data transfers and supports ad-hoc reporting and financial approval workflows.

The primary processing of the loan and bond records was implemented through “one-button” processing where each process was automated so that users only have to press one button to perform the function. An example would be the semi-annual loan process: pressing the button creates a customized email to each client with customized Word and Excel documents attached and tracking records created in the database to receive the data that is returned by the client.

The system provides:

• Tab-based access to related functionality
• Reporting through standard and ad hoc reports
• User-maintainable look up tables and system configurations
• Common functions include:
- Blog-based notes
- The ability to attach documents and other files to data records
- Online help
- Automatic document generation 
- Integrated searching
• Integrated contact management

Benefits achieved

By implementing the new system developed by TechPG using GeneXus, the Michigan Department of Treasury succeeded in improving the following aspects of the process:

Loans:

• Standardized the management, processing, tracking and reporting of loan information.

• Consolidated loan receivable information for the School Bond Loan Fund and School Loan Revolving Fund.

• Streamlined business processes and improved data accuracy and integrity. Pre-filled loan applications can be emailed to 150 school districts at the push of a button. Under the old process, school districts accessed the Treasury website to download the forms and filled them in manually; under the new process, error rates and follow-ups have been significantly reduced.

• Improves ability to track compliance with the requirements of the program.

• Provides expanded reporting capabilities necessary to ensure compliance with bond requirements.

• Saves accounting staff hours spent preparing monthly, quarterly and annual financial reports and streamlines the year-end closing process.

Bonds:

•• Standardizes the management, processing, tracking and reporting of bond information.

• Ensures data integrity. New bond structures such as multi-modal, auction rate, etc., require more advanced data collection, forecasting and reporting capabilities. LBMS replaced various Excel spreadsheets that were used to maintain this information in the past, and helps ensure data accuracy and reliability.

• Connects and tracks bond proceeds to their corresponding loans.

• Expands reporting capabilities. Information that was kept in various formats and locations is now consolidated in an electronic format and standard reports have been developed to provide on-demand management reports.

• Automates creation of financial reports for State Treasurer Annual Report.

Contacts:

• Replaces various databases and rolodex-type systems with a consolidated Bureau-wide contact management system. This reduces redundancy and saves time spent looking for and maintaining current contact information.

General:

• Increases data security through role-based privileges.
• Allows configurable report views into the data.
• Meets Michigan's IT and web system requirements.
Provides an integrated ad hoc query tool to simplify the creation of one-time and unique reports.

In sum, services to loan program members were improved through the automation of manual processes while enhancing the accuracy of loan program information and program accountability through the consolidation of bond records.