Capital Management Group Improve Fund Management Dealing Solution and Analysis Capabilities with OLAP Office Explorer™, Microsoft’s SQL Server Analysis Services (OLAP) and Microsoft Office – The Dealing Manager
In 1993, Capital Management (CMG) commenced in its current form and today is owned by its management. At CMG, performance is key and business interests are aligned with investors’ interests. In March 2005, the company had approximately £2 billion funds under management. The core product offerings being limited capacity funds that charge performance fees. These include OEIC Funds (Long only equity management), Hedge Funds, Active Value Equity Management and Private Equity Funds.
DEALING REPORTING ISSUES:
The daily fund dealing transactions were being recorded by numerous dealers within a controlled dealing blotter and spreadsheet environment. Although this was effective, the inherent limitations and lack of controls within the traditional spreadsheet environment was prone to data inconsistencies and errors. This not only increased the reconciliation requirements, but would also made querying, summarization, analysis and updating difficult.
Offshore and onshore fund transactions were being recorded in two different spreadsheets which then needed to be consolidated, summarized and queried for fund managers on a daily and ad-hoc basis. Further summaries had to be prepared for management and the accounting department. This included monthly salesmen’s commission calculations.
Tracking the net effect of fund subscriptions, redemptions and switches for any period of time for any fund needed improving. In essence, reporting was disjointed, inflexible and had limited analytical capabilities. The disconnected spreadsheet ‘islands’ of information compounded the inefficient reporting capabilities.
Additionally, issues were also being experienced when static information such as names of brokers were changed as these did not permeate through to historic data. As a result these issues needed to be resolved.
Therefore, our solution requirements and functionality included:
- To efficiently capture and centralize all dealing transactions in a more secure and controlled manner using current state-of-the-art database technologies.
- To provide a robust user-friendly interface to efficiently and effectively capture and retrieve fund transactions by numerous dealers at any one point in time.
- To reduce the amount of input required by dealers by centralizing static information to reduce the inconsistencies in the data. This would include the broker trading terms and information.
- To provide a more controlled and flexible way to maintain and update the evolving dealing solution and reporting requirements. For example, the updating and inclusion of new funds or brokers.
- To enable the ability to easily summarize and analyze deal transaction information.
- To tie-in the dealing department requirements with the accounting information required by the finance department.
- To automate the uploading of both foreign exchange rates and fund prices from Dexia and Bank of New York.
- Ad hoc querying capability to create a variety of schedules (e.g. all transactions over £1,000,000, or the top 40 brokers this year, trend analysis and graphs) in Excel or other formats.
- A well supported software and hardware infrastructure, whilst leveraging the existing internal investment in software and hardware.
- The integration to Microsoft Office and Microsoft development environments.
Based on a proven track record with OLAP Office™ whom had developed the CMG financial reporting solution, CMG decided to once again build the solution with OLAP Office™ and their product ‘OLAP Office Explorer™’. This enabled leveraging the existing robust, scalable and ‘open’ Microsoft SQL Server and Analysis Services (OLAP) platform. At the same time, CMG utilized its existing software investments in Microsoft Windows and Microsoft Office.
The OLAP Office™ project has proved a great success with the acceptance of the solution by dealers being due to the familiar Excel interface. Being able to simultaneously input through the Excel interface, whilst having all the check controls in place has improved data consistency. Using ‘OLAP Office Explorer™’ as the Report Writer and querying mechanism has enabled more versatile and accurate reports for the ad-hoc queries that are required by management. As the information is now centralized within SQL Server and Analysis Services cubes of information, we know that it is accurate and complete.
DEALING REPORTING BENEFITS INCLUDE:
- The ability to run multi-currency reports to track the original deal currency value (e.g. Euro, Yen, etc.) and the base currency value (GBP). This provides the complete ability to track the value at the point of deal and compare this to a future exchange rate.
- Consistent data entry and capture of both offshore and onshore transactional information, whilst recognizing the different transactional cut-off time requirements.
- The ability to record either cash or transactional transactions automatically.
- Reduced system maintenance and improved reporting times.
- Multi-hierarchical reporting structures on funds, enabling the analysis of fund information by class, fund or currency.
- Automated daily reporting summaries for both internal and external reporting requirements.
- A simple structured way to add new client brokers, salesmen or funds to the reporting solution and requirements as the company grows. This, whilst maintaining confidence in the financial integrity of the data.
- The automated data loading of the net asset value fund prices and exchange rates.
- A suite of summary reports that enable the control and analysis of all fund activity over any period.
- ;The ability to quickly analyze client broker activity in any potential way possible.
- The ability to track any salesman’s performance on all dealing activities, including monthly data volumes and average sale value. Also, the commissions due thereon.
- A centralized repository of all client broker terms and conditions that affect deal transactions.
- Fund pricing and exchange rate histories are centralized and easily accessible.
- Being able to drill through to the transactional information directly from a summary report.
- A task menu stores multi-dimensional templates and work options which significantly reduces spreadsheet proliferation.
- Total confidence in the numbers due to consistent data processing and the natural reporting structure associated with OLAP solutions.
DEALING REPORTING SOLUTION:
- OLAP Office™ and Excel for dynamic multi-dimensional spreadsheets for data entry and report writing.
- SQL Server warehouse for financial information.
- Analysis Services cubes holding structured reporting hierarchies of pivotal dealing data.
- Excel formatted reporting templates for consistent output.
The success of this project is partly due to OLAP Office’s vast experience in developing and delivering OLAP reporting solutions. OLAP Office™ provided the solution overview, project plan, detailed on-line help manual and collaborated on the development of a customized CMG user manual. This enabled the CMG solution to be developed quickly and accurately enabling CMG to achieve the project objectives. The ability of OLAP Office™ to provide support and training both onsite and real-time through the internet makes troubleshooting and training highly effective and efficient. CMG has experienced excellent project management, training and after sales service.
CMG now has a highly effective, dealer friendly, financial data warehouse and reporting system that has halted spreadsheet proliferation in the dealing department. It has also significantly improved the efficiency and quality of dealing information and financial data.