- Overview
- By Industry
- Banking
- Business and Professional Markets
- Capital Markets
- Commercial Real Estate
- Federal/Public Sector
- Healthcare and Pharmaceuticals
- Independent Software Vendors
- Insurance
- Lending
- Manufacturing
- Media/Entertainment
- Transportation/Distribution/Logistics
- Utilities/Telco
- By Area of Expertise
- Business Intelligence
- Custom .NET Application Development
- Enterprise Application Integration
- Workflow and Collaboration
Case Studies
Transaction Management System Provides Better Management and Reporting Tools
Project at a Glance |
|---|
RDA created a transaction management system for a major bank that allowed our client to manage payment and reconciliation transactions more effectively and efficiently. Also, this automated system saves time, meets rating agency requirements and positions our client for future business growth. |
About Our Client
Our client is one of the largest banks in the United States. Since 1997, its Asset Backed Securitization (ABS) group has performed master servicing, investor reporting and bond administration functions for bank issued securitized transactions. The ABS group currently manages $22 billion worth of transactions consisting of 26 deals and manages over $750 million in average daily deposits. The underlying collateral for the deals includes student loans, home equity loans, home equity lines of credit and auto leases.
Background
Prior to this project, ABS managed the business process exclusively using Microsoft Excel spreadsheets and Access databases. Payments and reconciliations involved a manual, time-intensive process that hindered business growth and did not meet rating agency requirements.
To overcome these impediments, our client decided to build a new system that allows them to manage transactions more effectively and efficiently once they are "live" in the capital markets. Automation would provide better management and reporting tools to both our client and the deal investors.
The following factors were critical to the success of the project:- Price: The bank needed a low cost solution that could be implemented quickly and would meet their Return on Investment criteria.
- Flexibility: The types of loan products the bank offers change based on market demand and rate fluctuation. Furthermore, each deal of a loan product is structured differently. Therefore, the ABS system needed to support a wide range of loan products and also be flexible enough to add new deal level capabilities quickly and easily.
- Ranking by Rating Agencies: A part of the ABS group's growth strategy is to service non-bank originated deals. However, in order to persuade external customers to use the ABS servicing system, the system would need a ranking from rating agencies. A robust management system that controls access to data and tracks changes to data is a prerequisite for ranking by the rating agencies.
- Collaboration: The system needed to provide a mechanism to allow analysts to work on investor reports and to allow administrators to approve reports.
Solution Detail
RDA's solution consists of the following:- Excel 2003 workbook models, marked up with XSDs, to host the calculations and investor report format for a deal
- InfoPath forms to collect monthly input data in XML format, to be imported into the corresponding Excel workbook
- SharePoint libraries to host and track changes to Excel models, InfoPath forms and investor reports
- SQL Server 2000 database to interface with external Home Equity Line of Credit (HELOC) transaction systems and to perform balance calculations on lines of credit based on imported transactions
- Web Services as the data access layer to retrieve balance calculations in XML format for Excel investor report models
The key challenge for the system was flexibility. It needed to allow for new loan types and to accommodate varying calculations across deals. The solution includes:
- Excel calculations engine and spreadsheet format to create deal dependent investor reports
- Common inputs schema (XSD) for HELOC loan type used to mark up a deal Excel model. As long as all the input fields required to perform HELOC servicing were captured by the schema, the calculations in deal level Excel models could be varied across deals. Furthermore, as new loan types are added, the XSD can be extended to support additional loan types.
- XML Maps to facilitate import/export of data to/from Excel models
- To address the rating agency ranking and collaboration needs, SharePoint was used. It provided a great environment to set up sites for various loan types, configure access to ABS analysts and administrators using Active Directory, and track changes using versioning and user IDs. In addition, InfoPath provided the right technology to accumulate user input and external system data into a single, underlying, XML file that could be imported into the Excel deal model via XML Maps
