Automate and enhance controls over CAFR and budget reporting

With IBM Cognos FSR

Published on 19-Sep-2011

Validated on 02 Dec 2013

"No other solution came close to [IBM Cognos FSR’s] functionality, and unlike other applications that we reviewed, it was a proven solution with many user references across a wide variety of implementations." - Project Manager, The Office of Management and Budget (OBM) of a US State

Customer:
The Office of Management and Budget of a US State

Industry:
Government

Solution:
BA - Business Analytics, Business Integration, Business Performance Transformation, BA - Performance Management

Overview

The Office of Management and Budget (OMB) of a US State is in charge of assembling the budget, monitoring revenues and expenditures and producing the Comprehensive Annual Financial Report (CAFR). The information in the CAFR supplements additional financial reports such as the public debt report and bond prospectuses.

Business need:
To gain approval for annual budgets and meet other critical obligations, the OMB needs to be able to produce its CAFR in an efficient and timely manner. With data held in hundreds of spreadsheets, creating these reports was a lengthy, difficult and error-prone process.

Solution:
The OMB now uses IBM® Cognos® Financial Statement Reporting to manage the entire reporting process, from initial data collection through drafting, reviews and publication.

Benefits:
Enables automated data collection and validation from disparate information sources, eliminating manual data entry and significantly reducing the risk of introducing errors. Links the report template to the underlying data, ensuring that any corrections are automatically reflected throughout the document. Employs sophisticated workflows to move the document through the various draft and review stages while keeping sensitive information secure. Creates a complete audit trail, simplifying regulatory compliance. Provides a familiar, user-friendly interface, minimizing training requirements.

Case Study

Situation analysis

The Office of Management and Budget (OMB) of a US State is in charge of assembling the budget, monitoring revenues and expenditures and producing the Comprehensive Annual Financial Report (CAFR). The information in the CAFR supplements additional financial reports such as the public debt report and bond prospectuses.

“We have to issue the CAFR in a timely manner. The next fiscal year’s budget is dependent upon the preceding CAFR, which must be reviewed by the State Legislature’s Office of State Auditor – so it had to be ready on time,” says an OMB Project Manager responsible for the Cognos FSR implementation (as of 2013 FSR is now referred to as Cognos Disclosure Management). “Bond sales are also dependent upon timely issuance of the CAFR, since the documentation is used in secondary documents like the public debt finance report to secure a good bond rating. If you have to re-issue a CAFR due to incorrect information, there are potential penalties – including a lower credit rating, restraints on borrowing in a timely fashion and so on.”

The pre-CAFR audit starts in May for the fiscal year ending June 30, with entries from the various departments and agencies being submitted by July 31 and internal postings completed by August 30. The ‘CAFR period’ runs from August to November, during which time the work of preparing the report ensues. Post-submission journal entries, audits, sign-offs on accounts and other accounting actions take place at this point, and the aim is to publish the CAFR at the beginning of December each year. However in recent years, publishing has taken four to eight weeks, often forcing the State to submit the report 30 to 60 days after the planned delivery date.

“This was always a tough time,” says the Project Manager. “Hundreds of disparate spreadsheets and text files would flow into our office from internal departments and agencies, as well as more than 35 external institutions such as colleges and universities. They would be stored on the mainframe computer that we were using to produce the consolidated CAFR file. But with so many different sources, formatting and versioning problems were continually cropping up. Every time a correction was entered, we had to verify that the corrected data was updated consistently through the entire document.”

In addition, a legacy reporting tool running on the mainframe was in its final years of operation, requiring multiple, expensive upgrades. Support for the tool eventually took the form of a single engineer at the vendor.

“When the tool went down for hours or even days at a time, we couldn’t produce CAFR data, which was very frustrating,” says the Project Manager.

The declining economy compounded the production problems. Staff workloads increased, but it was not possible to hire additional full-time or contract staff to assist. Regardless, the quality of the CAFR, which the State had a justifiable reputation in delivering accurately and on time, had to be maintained.

Quest for a unified compliance and reporting platform

The Department realized that it needed a better process for consolidating financial data, creating the narrative disclosures, rolling them up into accurate reports (specifically the CAFR), and publishing them in a timely and compliant manner.

What was needed was a single application, dynamically driven from a central database that could draw data from legacy systems and data sources. Ideally, the in-house team envisioned a solution that could leverage the formatting capabilities of Microsoft Word and the calculation features of Microsoft Excel. It also needed to foster collaboration by providing workflows and task management, and it should provide a full audit trail to increase control and ease of production. But did such a system exist?

IBM Cognos Financial Statement Reporting tops the list

OMB assembled a team of stakeholders including IT specialists, accountants and the State’s purchasing experts, each of whom had specific expertise to contribute to the project. They began by consulting industry analysts Forrester, Inc. and Gartner on report automation. “Both firms identified IBM Cognos Financial Statement Reporting [FSR] as an emerging leader in this area,” comments the Project Manager.

Demonstrations and proof-of-concept reviews followed. What the team uncovered was eye-opening. “In IBM Cognos FSR we found a single application that could converge and consolidate Microsoft Word, Excel and financial reports into a PDF for print publishing and web posting,” says the Project Manager. “No other solution came close to its functionality, and unlike other applications that we reviewed, it was a proven solution with many user references across a wide variety of implementations.”

Furthermore, licensing and maintenance agreements ensured that the OMB would receive full technical support from IBM, and would be able to take advantage of future upgrades.

Well suited for government reporting requirements

Using a prior CAFR report as the template, the team created both a data ‘cube’ that could import all manner of files and data, and a CAFR template that the cube would feed to produce the reports. Consolidated data was downloaded from the mainframe, prepared by the Office of Information Technology (OIT) and uploaded overnight to the FSR data cube. As a pilot project, the team used the model to recreate the previous year’s CAFR. Once this had been verified by the accountants, the application was moved into production for the current year’s report.

The database cube and template document that comprise the IBM Cognos FSR application have numerous features that are well suited to government reporting. For example, information errors in government documents can be both embarrassing and fraught with liabilities, and generally come from re-keying data or from the lack of a data validation tool throughout the document. IBM Cognos FSR’s direct integration with source data and its automatic loading of new data eliminated the re-keying and reformatting that had caused problems in the past.

“Thousands of keystrokes have been eliminated from our process, greatly reducing the redundancies we had experienced in the CAFR preparation process,” says the Project Manager. Furthermore, the ability to link and transfer data elements throughout the report has eliminated the possibility of errors.

“A large number of people are involved in preparing, reviewing, vetting and forwarding reports in government, so the compliance checklists in IBM Cognos FSR are a real benefit in ensuring that the right steps are being followed,” says the Project Manager. “There is also an audit trail that tracks who did what and when, increasing our confidence in the final report.” In addition, OMB now has the ability to instantly track the location of the report during the review process.

The Project Manager adds: “You don’t want everyone seeing everything throughout the CAFR period, so IBM Cognos FSR’s ability to define security policies on who can view or edit an object is much appreciated. The workflow controls also ensure that users can only see data when they are allowed to do so, which is usually during the contributory period, and not during the later stages. This is important because the Governor and Legislature must be first to see the complete, finished product.”

The Project Manager confirms that versioning, formatting and publishing problems are becoming a thing of the past. Approximately 90 percent of the financial data in the template is linked to IBM Cognos FSR from underlying data systems, so it can be refreshed automatically by the solution.

Governmental groups are sometimes criticized for being slow and unwieldy, but this was not the case with the OMB, who were ably assisted by the IBM Cognos FSR team.

“We got the solution purchased, deployed and helping us produce our CAFR within a record time period,” says the Project Manager. “The importing of the data-set was our big worry, but the FSR team had no problems either with the imports or the implementation of the publishing component.”

What the future holds

“We are now exploring using the application to produce other publications,” comments the Project Manager. “The budget book, various public finance reports and pension reporting documents are next on our list for Cognos FSR. The ability to pass data across reports – from the CAFR report to the budget report, for example – is a real benefit.”

In the future, the solution will also make it easier to collect data from the 35 different offices, agencies and institutions that contribute to the Department’s financial reports.

“Currently we roll up all the figures into the basic financial statement; but soon we’re planning to migrate to a web-based data entry model,” concludes the Project Manager. “Contributors will be able to input information directly into the IBM Cognos FSR cube and populate our CAFR template.”

About IBM Business Analytics

IBM Business Analytics software delivers actionable insights decision-makers need to achieve better business performance. IBM offers a comprehensive, unified portfolio of business intelligence, predictive and advanced analytics, financial performance and strategy management, governance, risk and compliance and analytic applications.

With IBM software, companies can spot trends, patterns and anomalies, compare “what if” scenarios, predict potential threats and opportunities, identify and manage key business risks and plan, budget and forecast resources. With these deep analytic capabilities our customers around the world can better understand, anticipate and shape business outcomes.

Products and services used

IBM products and services that were used in this case study.

Software:
Cognos Financial Statement Reporting, Cognos Disclosure Management

Legal Information

© Copyright IBM Corporation 2011. IBM Global Services, Route 100, Somers, NY 10589, U.S.A. US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. Produced in the United States of America. September 2011. All Rights Reserved. IBM, the IBM logo, ibm.com and Cognos are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. If these and other IBM trademarked terms are marked on their first occurrence in this information with a trademark symbol (® or ™), these symbols indicate U.S. registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the Web at “Copyright and trademark information” at: ibm.com/legal/copytrade.shtml. Other product, company or service names may be trademarks or service marks of others. References in this publication to IBM products or services do not imply that IBM intends to make them available in all countries in which IBM operates.