The following case studies describe some of the projects in which ATD consultants have played leading roles:
Case Study 1: Honda Finance Europe
Case Study 2: Volkswagen Financial Services
Case Study 3: The Insolvency Service
Case Study 4: Toyota Financial Services
Case Study 5: Lloyd’s Underwriting Agency
Case Study 1: Honda Finance Europe
Honda Finance Europe (HFE) provides finance for Honda cars and motorcycles, including retail finance (to assist customers in the purchase of Honda vehicles) and wholesale finance (funding dealers’ stocks of new and used cars). Initially HFE outsourced the day-to-day administration of both retail and wholesale finance agreements to Lloyds TSB Asset Finance (LTSB). HFE initiated a programme to bring its business functions in-house, which involved the implementation of a new system and migration from LTSB’s systems of more than 60,000 existing finance agreements and all their associated data.
Data Services
ATD won the contract to devise a strategy for the conversion of data into the new system. A strategy study was successfully completed and was followed by a detailed design phase, during which ATD worked closely with HFE business managers, LTSB and Fimasys, the supplier of the new system, to develop specifications for the conversion procedures. Crucially, given the extensive experience of its consultants, ATD was able to approach the conversion requirements from both business and technical viewpoints, in order to produce detailed specifications and plans for the whole conversion process.
ATD went on to manage the successful implementation of the conversion process for both wholesale and retail finance agreements. A team of up to 8 ATD consultants was responsible for development and testing of the ETL processes, the associated business transition processes, and reconciliation procedures. We carried out cleaning of the source data, in collaboration with LTSB, and we provided HFE users with an enquiry system through which they could view the source data in an intermediate Microsoft SQL Server database. This module forms part of ATD’s own software toolset (XTLQuanta), which contains a database of over 800 financial and non-financial rules to ensure data integrity.
The conversion of some 14,000 wholesale finance agreements was successfully completed, followed by the conversion of over 50,000 retail finance agreements only 4 months later. One of the key acceptance criteria for the data migration was the reconciliation of outstanding customer receivables between the old and new systems: remarkably, this was achieved to a tolerance of less than 0.00005%.
Speaking after the completion of the project, Jonathan Chenoweth, Head of Finance and Credit at HFE, commented: “The conversion process was never going to be easy. We knew that some of our data had to be significantly transformed to fit the structure of the Fimasys system. ATD staff showed their ability to understand financial data and software, and equally importantly, the Finance business. Their effectiveness in managing the conversion was admirably demonstrated by the results of the receivables reconciliation.”
Business and Systems Consultancy
ATD’s experience of the data conversion, and our consequent knowledge of the systems, data structures and business processes, has enabled us to assist HFE in the implementation of further enhancements to its core systems, and to support the business users during the crucial post-implementation period. In particular, we have been responsible for the design, specification and testing of new functionality for collections and cash management, for the provision of SQL-based reports to support securitisation and marketing activities, and for the development of automated reconciliation procedures.
Top...
Case Study 2: Volkswagen Financial Services
Volkswagen Financial Services (VWFS): ATD was invited to review the procedures used by the Risk Management Department for the production of operational reports and ad hoc management information. The procedures had become somewhat convoluted, and reliant on a series of Access databases which were not formally supported by the I.T. Department. ATD’s review included analysis and documentation of the existing procedures, and design of a new, consolidated reporting data structure. We then tested and implemented the new reporting database, which was based on Microsoft SQL Server. This has greatly simplified the monthly data extraction procedures whilst at the same time improving their reliability and security. The new structure still allows the users, who are experienced data and risk analysts, to take advantage of Access as a flexible front-end reporting and modelling tool, without suffering from its limitations as a database management system. Having developed secure, reproducible data extraction and database maintenance procedures, we were able to hand those procedures over to I.T., thereby freeing the users from the mechanical tasks involved in the monthly reporting cycle and allowing them to concentrate on data analysis.
Top...
Case Study 3: The Insolvency Service
The Insolvency Service, an executive agency of the DTI, has two major systems for the financial management of insolvency cases. The service has a long term objective to implement an integrated financial system, and to migrate the data from the “legacy” systems.
ATD was invited to carry out a strategic review of the service’s plans for ETL (extract, transformation and load). A team of five ATD consultants subsequently undertook an assignment to map the data from the two existing financial systems, and two corresponding case management systems, into a unified structure. We used the XTLQuanta toolset to facilitate complex data mappings, profiling of the data, and analysis of data quality issues.
We worked with the service to improve the quality and integrity of the data in the source systems, both to enhance their operational effectiveness, and to address issues which may prove critical in any future system conversion.
Top...
Case Study 4: Toyota Financial Services
Toyota Financial Services (TFS) undertook a major business transition programme to in-source its back-office functions. This required that a large number of Toyota and Lexus retail finance contracts, together with all their associated transaction data, be converted from an external IBM mainframe-based system to an in-house system. The new system was based on the Lynx "Portfolio" software package using Unix and Oracle technology. ATD consultants worked closely with TFS throughout the data conversion project in the following roles:
- contributing to a feasibility study into the risks and benefits of undertaking a data conversion from the outsourced external system, and presenting the findings of the study to the TFS management board, which approved the data conversion project
- managing the data conversion project from the start, through to its successful conclusion 18 months later
- acting as the principal contacts with both the existing external system supplier (Black Horse) and the suppliers of the new in-house system, for the resolution of technical and data-related issues
- support for the transition of the business to an in-house operation, particularly through the provision of management information from the source data
- data analysis, data mapping, design of the migration procedures and development of migration software
- unit and system testing of the data migration procedures and software, as well as support for user acceptance testing
- development of procedures for both the financial and non-financial reconciliation of the data conversion.
The director of the transition programme was Blair Pollock, TFS’ General Manager, Business Development, who commented:
"When the ATD consultants joined TFS, the administration of our finance portfolio was outsourced to Black Horse, a subsidiary of Lloyds TSB Bank. We had decided to bring this part of our business in-house and were looking for someone to lead the effort to convert our retail customer data from the Black Horse systems to our own.
It is a great testament to the quality of the work performed by the ATD consultants that in a recent review of our insourcing project, the Board of Management of TFS recognised the retail conversion as a major success.”
Top...
Case Study 5: Lloyd’s Underwriting Agency
The company required better information on its overall exposure to catastrophic risks, which had not been readily available from its loss modelling systems. A small team of ATD consultants has developed an exposure reporting system, whose core is an ETL process to extract predicted loss data from the two main modelling systems, to merge it with data from the policy administration system, and to load the results into a dedicated Microsoft SQL Server database. The system also provides a Windows-based user interface to enable loss reports and EP curves to be generated for individual policies, classes of business, geographic areas, etc., and a suite of reconciliation reports to ensure that common data items can be kept in synchronisation between the source systems.
Top...