Capital Management Group: Streamline and Enhance the Reporting Process with OLAP Office Explorer™, Microsoft’s Analysis Services (OLAP) and Microsoft Office -Matthew George ACIS, CA(SA) - Group Financial Controller

In 1993, Capital Management Group (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.

FINANCIAL REPORTING ISSUES:

The core general ledger system is Sage Line 50. Whilst good in some areas, Sage has inherent limitations relative to the requirements of the business. These include inflexible and limited reporting capabilities, limited analytical capabilities and limited access to the proprietary database.

Initially Excel spreadsheets were being used for consolidating 3 entities, which when the Group grew to 11 entities became unwieldy, inaccurate and too inefficient for accurate reporting. Too much time was spent reconciling and cross checking the accuracy of the spreadsheet reporting solution. This process was further complicated by a change in year-end which resulted in an 18 month reporting period for some companies, which Sage could not handle. Therefore our solution requirements included:

• An automated, user friendly, robust and flexible reporting platform for financial reporting for multiple formats including Management, Statutory and Regulatory reporting.
• A flexible and scaleable solution that could be easily integrated with any general ledger system and potentially with other systems in the Group with the aim of improving management information.
• An easily accessible database to retrieve and input current and historic financial data.
• Current and historic reporting capabilities and functionality for actual, budget, forecast and variance analysis.
• Ad hoc querying facilities and the ability to create a variety of schedules (e.g. consolidation schedules, trend analysis and graphs) in Excel or other format.
• Control over all aspects of the solution so that the finance department could develop it as and when the business developed and changed, without reliance on the IT department.
• A well supported software and hardware infrastructure, whilst leveraging the existing investment in software and hardware.
• The capability to go beyond financial reporting and provide a scaleable and flexible platform for other Group solutions (e.g. a data warehouse).
• The integration to Microsoft Office and Microsoft development environments.

Based on our investigations to achieve these requirements, CMG decided on the solution offered by OLAP Office™, where their product ‘OLAP Office Explorer™’ leveraged the robust, scaleable and ‘open’Microsoft SQL Server and Analysis Services (OLAP) platform. At the same time, CMG utilized its existing software investments in Microsoft Windows 2000, Office 2000 and Sage Line 50.

The OLAP Office™ project has proved a great success with the acceptance of the solution by users being due to the familiar Excel interface. Using Excel as the Report Writer and modeling environment facilitates a friendly, easy to use, fast financial reporting and development environment. Using the ‘OLAP Office Explorer™’ interface enables us to create ad hoc reports from our cubes (databases) of information without having to know or program any complex queries. We can model Excel reports on-the-fly with reports referencing multiple cubes with centralized control settings. Also having the ability to efficiently capture data through Excel to Analysis Services has enhanced the way our department works and operates. Of paramount importance and benefit has been the improvement in the quality of information and the quantum leap in the speed of output due to overcoming the inherent time consuming tasks that are common in a spreadsheet reporting environment. Integral to the success of the solution is the automated data loading process using Microsoft’s Data Transformation Services (DTS) and the fact that we now have a more structured way to maintain and update our reporting application.

FINANCIAL REPORTING BENEFITS INCLUDE:

• Multi-hierarchical reporting enabling the production of consistent and reconciled Management, Statutory and Regulatory information for each entity as well as the consolidated Group.
• Comparative, standard and ad hoc reporting of both current and historic data. Specific reporting periods are easily accessible and comparable.
• Automated and progressive forecasting functionality. The ;cumulative forecast capability and inputting allows users to assess their past and future predictions whilst maintaining and comparing version histories. The forecast template allows input by entity, ledger account and period.
• Budgeting integration and tools to simplify the budget process. Inclusive are budget templates for data input – by entity, ledger account and period.
• Adding new companies or ledger accounts to the existing reporting structure is simple, whilst maintaining the confidence in the financial integrity of the data.
• The numbers are simple to audit and additional year-end audit benefits include being able to generate audit schedules ‘on the fly’.
• 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.

FINANCIAL REPORTING SOLUTION:

Infrastructure

• Sage General Ledger for accounting transaction data.
• SQL Server warehouse for financial information.
• Analysis Services cubes holding structured reporting hierarchies of pivotal reporting data.
• Excel and OLAP Office Explorer™ for dynamic multi-dimensional spreadsheets for data entry and report writing.
• Excel formatted reporting templates for consistent output. In CMG’s case existing Excel reports were also modeled to reference the new cubes of data.

OLAP Office Explorer™

Some of the key and useful features include:

• The Cube Explorer 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 Explorer 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 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, accountant friendly, financial data warehouse and reporting system that has halted spreadsheet proliferation in the finance department. It has also significantly improved the efficiency and quality of management information and financial data.

Looking forward, CMG is able to take advantage of some of the new and innovative features available through ‘OLAP Office Explorer™’ and Microsoft’s Analysis Services. CMG is also able to expand the OLAP Office™ solution offering to other system areas including customer relationship management, data feeds, fund accounting and settlements systems.