Untitled Page

Home | Login | Contact Us

Client Reference: Dealing Solution

Please contact o2olap directly for the specific reference client information and the original user story. The relevant information has been omitted for confidentiality reasons.

XXXXX Group Improve Fund Management Dealing Solution and Analysis Capabilities with o2olap for Excel, Microsoft’s SQL Server Analysis Services (OLAP) and Microsoft Office – Dealing Manager

In 1993, XXXXX commenced in its current form and today is owned by its management. At XXXXX 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 good the inherent limitations and lack of controls within the traditional spreadsheet environment was prone to inconsistencies and error. This not only increased the reconciliation requirements but would also make querying, summarisation, analysis and updating difficult.

Offshore and onshore fund transactions were being recorded in two different spreadsheets which then needed to be consolidated, summarised 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.

Being able to quickly track both the net effect of 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 helped precipitate the inefficient reporting capabilities.

Additionally issues were also being experienced when static information such as names of brokers were changing and as a result these needed to be resolved.

Therefore our solution requirements and functionality included:

To efficiently capture and centralise 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 fund transactions by numerous dealers at any one point in time.
To reduce the amount of input required by dealers by centralising 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 summarise and analyse 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 America.
Ad hoc querying ability 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 format.
A well supported software and hardware infrastructure, whilst leveraging the existing investment in software and hardware.
The integration to Microsoft Office and Microsoft development environments.

Based on a proven track record with o2olap whom had developed the XXXXX financial reporting solution, XXXXX decided on building the solution with o2olap and their product ‘o2olap for Excel’. This enabled the leveraging the robust, scaleable and ‘open’ Microsoft SQL Server and Analysis Services (OLAP) platform. At the same time, XXXXX utilized its existing software investments in Microsoft Windows 2000 and Office 2000.

The o2olap 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 ‘o2olap for Excel’ as the Report Writer and querying mechanism has enabled for far more versatile and accurate reports for those 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.

DEALING REPORTING BENEFITS INCLUDE:

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 requirement.
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 the confidence in the financial integrity of the data.
The automated data loading of fund pricing and exchange rates.
A suite of summary reports that enable the control and analysis of all fund activity.
The ability to quickly analyze client broker activity in any potential way possible.
The ability to see salesmen performance on all dealing activity, including monthly data volumes and average sale value. Salesmen can also review their own commission data.
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:

Infrastructure

Excel and o2olap 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.

o2olap for Excel Some of the key and useful features include:

The Cube Browser generates any user defined, ad hoc, multidimensional, free-form, Excel report. Therefore you can create and model any financially friendly output that you require.
The Cube Browser enables users to quickly summarise any combination of members (i.e. specific data) in the dimensional hierarchies without having to write formulae.
Data output is defined by the structure of the cubes and their dimensions (i.e. data categories). The core cube structures were defined during implementation and continue to evolve with the business.
Multiple cubes can be browsed in the same Excel worksheet with centralized control for dimension settings. You can even model existing worksheets to the cube data.
Multiple dimensional hierarchies are maintained through Excel and are automatically updated for new account or entity members. Existing reports automatically have access to the changes in structure and referencing.
Flexible and efficient input of data, i.e. see the results as you input changes and recalculate ‘on-the-fly’.
Provides a good platform for creating trend, comparative analysis and graphs.
Publish data to an intranet environment, or any other format, including Word, Adobe PDF and PowerPoint.
‘Stop light’ capabilities and summarization for indicating significant variances or adverse conditions.
The multidimensional reports now replace multiple spreadsheets and workbooks. Each report giving users access to any entity, account, time period, currency or version (actuals, budget, forecast, variances) of data.

CONCLUSION:

Part of the success of this project is due to o2olap’s vast experience in developing and delivering OLAP reporting solutions. o2olap provided the solution overview, project plan, detailed on-line help manual and collaborated on the development of a customized XXXXX user manual. This enabled the XXXXX solution to be developed quickly and accurately enabling XXXXX to achieve the project objectives. The ability of o2olap to provide support and training both onsite and real-time through the internet makes troubleshooting and training highly effective and efficient. XXXXX has experienced excellent project management, training and after sales service. XXXXX 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.