Excel 2007 and Excel Services – New Tools Bring Spreadsheets into Compliance

Excel spreadsheets are so flexible and powerful that companies, especially financial firms, have greatly expanded their use across the enterprise.

In financial services, Excel is widely employed to develop new trading products, to fill the gaps between applications and help banks close their books at the end of a reporting period, and to meet regulatory reporting requirements. Industries of all kinds use spreadsheets to support critical business functions; estimates are that over half of financial management reporting is performed through spreadsheets.

As a result, spreadsheets have proliferated. Richard Gissing, founder and chief technology officer of Gissing Software, said his firm was asked to conduct an audit for one client; they found three million spreadsheets. Some were old, some were copies, but the firm had no inventory, no catalog, and as a result, little control. A major oil company estimates it has 175,000 spreadsheets in use across the firm, many of them linked to each other, and no way to keep track of them.

Poor spreadsheet control can create big risks. John Rusnak, the rogue trader at Allied Irish Bank in Baltimore, was able to lose $691 million by manipulating his trading spreadsheets, according to Ray Panko, professor of information technology management, as reported in The Banker magazine.

In most companies there is a huge disparity between the importance of spreadsheets to business processes and the level of corporate resources devoted to spreadsheet development, testing, and maintenance.

Regulatory Drivers

New regulations aimed at public companies, and financial corporations in particular, are increasing pressures to ensure that financials reported are transparent and well documented. To meet the requirements of the Sarbanes-Oxley Act (United States), the Data Protection Act (European Union), and the Basel Capital Accord (financial services), which together impact most publicly-traded companies around the world, firms will have to bring their use of spreadsheets into a compliance framework.

Although technology plays a key role in the compliance framework, the compliance effort begins on the business side with a compliance process.

1. Evaluate the current situation, beginning with an inventory of spreadsheets, and identify those that may have an impact on compliance. In most organizations, this applies to spreadsheets concentrated in specific high-risk departments, such as the reporting and analysis of financial account data in corporate finance. Within banking, trading, risk management and new product development would be key areas of concern. During the inventory, note each spreadsheet’s purpose and relationship to critical business processes. This information will be important for determining the appropriate controls for each spreadsheet. Inventories can be completed manually by inspecting hard drives and shared folders, or automatically by using software that scans a corporate network to target spreadsheets.
2. Identify business-critical spreadsheets. Not all spreadsheets in an organization require rigorous compliance controls. An inventory may return thousands, if not hundreds of thousands of spreadsheets, many of which will likely have little or no compliance impact. Teams should identify and isolate the spreadsheets that support critical business processes, where a lack of controls could lead to material errors, which in corporate finance is often defined as an error that involves five percent of net income or total assets.
3. Work with both business and IT to develop solutions so the strategy doesn’t hinder business productivity. Collaboration is particularly important when defining spreadsheet controls because members of the business team often serve as both users and developers of critical spreadsheet applications. Because IT staff have knowledge of spreadsheet functionality, getting them involved early can help reduce duplication of work and increase the robustness of the solution.
4. Seek enough resources. Don’t underestimate the task. Spreadsheets have been proliferating for decades; bringing the business-critical spreadsheets into a compliance framework will take time and people from IT, internal audit, finance, trading and risk management.

Once this process is complete, technology can help enforce policies and increase business productivity.

Office 2007 – Taking Spreadsheets to the Enterprise
 
The 2007 release of the Microsoft Office system contains a set of technologies that can be used in conjunction with a sound compliance strategy to address compliance challenges with spreadsheet use. These capabilities include:

• Preventing unauthorized access to spreadsheets.
• Managing and monitoring spreadsheet changes.
• Retaining and archiving spreadsheets.
• Developing robust spreadsheet models.

The new version of the Office system will include two ways to use spreadsheets. The first is an upgrade to Excel on the desktop, with new power and features – its grid size has been increased to one million rows and 16,000 columns, and it is multi-threaded to take advantage of multi-processor hardware. The second change, which will simplify compliance, is to place Excel functionality on Office SharePoint Server 2007, a scalable enterprise portal, content management, and collaboration server. Organizations can use Office SharePoint Server 2007 to store, protect, share, and track important documents and information, such as spreadsheets, through a single Web-based portal. All interactions within Office SharePoint Server 2007 are protected and monitored by a single sign-on system to safeguard against unauthorized access and an audit log is developed for each access.

Excel Functionality on a Server

In Office 2007 it is now possible to author spreadsheets in Excel 2007 on the client and then share with Excel Services on the server. Once a spreadsheet has been created, it can be loaded into Excel Services on the SharePoint Server. From there, users can view the spreadsheet through a Web browser, or through a new Web services interface that calling Excel spreadsheets programmatically on the server allows developers to share Excel features among applications. This allows organizations to share spreadsheets without exposing sensitive business logic. While you can’t author spreadsheets in the browser, it is possible to use Excel’s new Open XML file format to generate and modify spreadsheets programmatically on the server. Finally, because Excel Services is part of Office SharePoint Server 2007, it takes full advantage of document management and workflow capabilities to help maintain control over critical spreadsheets.

Publishing a spreadsheet to Office SharePoint Server 2007 saves the entire spreadsheet to the server to allow for data refreshes and recalculation. However, the parts of the spreadsheet accessible to viewers and available for download through the Web browser are controlled by the author of the spreadsheet.

Microsoft Office Excel 2007 spreadsheet software provides three options for controlling the viewable area of the spreadsheet on the server:

• The entire workbook (default). Users can view the entire workbook and download it to the desktop.
• A subset of sheets. The workbook author permits users to view and download a subset of sheets. This mode is useful when workbooks contain numerous “behind the scenes” worksheets that hold intermediate calculations, source data, etc., but only a few sheets that users should see. 
• A set of named items, such as Named Ranges, charts, tables, and PivotTable® and PivotChart® dynamic views. In this mode, users can only view and download specific items selected by the workbook author. Users access these items through a drop-down menu in their Web browser.

Controls and Workflow

Excel Services on SharePoint Server provides the level of control for spreadsheets which regulators require. Once the spreadsheet is on the server, it can be shared and used, but it can’t be altered. Administrators can also use SharePoint to make financial models available to users inside and outside the firm while concealing the intellectual property behind the model.

Versioning control ensures that any changes to the spreadsheet will not change the previous version, which is stored, and administrators can control who has access to make changes. An audit function records events such as Open, Modify and Delete, and several built-in mechanisms can generate custom reports.

To improve the efficiency of Excel within the financial reporting process, users can build workflows that map to important business processes. A content approval process directs spreadsheets to a manager or analyst who can review and approve the document before it is disseminated for wider use. Or the workflow can trigger emails to internal approvers to ask them to review the document and sign off that it meets internal requirements. The new system also offers processes to set business rules for retention, archiving, and periodic deletion of spreadsheets.

Finally, improvements to the Excel interface make it easier to use while reducing the chance for error or intentional tampering with spreadsheets.

With cell formatting, a user can employ color, font, borders, and data formats to indicate different functions such as input cells, formulas and output cells. To make formatting updates simple, style changes are automatically applied to all cells using that style. The 2007 version of the Office System also permits locking of specific cells, ranges or sheets. The new version of Excel also recognized tables as a native oject in the spreadsheet, which allows users to create robust tables that better maintain structure and are significantly easier to interact with. The 2007 release also simplifies the use of Defined Names with a Name Manager tool that shows important details such as a name’s reference, value and scope.

Finally, Excel 2007 provides formula auditing tools. When employed with a consistent use of cell styles and naming conventions these can accelerate the testing of spreadsheet models and reduce the risk of error once a spreadsheet is in production.

Excel in Financial Services

In a report on spreadsheets and compliance with Sarbanes-Oxley, PricewaterhouseCoopers noted that spreadsheets have moved from logging, tracking and totaling information to incorporate enhanced formulas.

“The use of macros and multiple spreadsheets which are linked together allows users to build very complicated – and sometimes convoluted – models and other business functions with minimal or no documentation,” noted the consulting firm in a report, “The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley Act”. Although the spreadsheets have moved into business-critical functionality, they are not subject to the controls that would be expected, added the consultants.

“These complex spreadsheets are not normally supported by the same control environment as formally-developed, purchased applications. For example, the developers and users of spreadsheets are usually not trained in structured programming, testing, version control or systems development life cycles, and spreadsheets are rarely restricted from unauthorized access by security controls.”

ClusterSeven, a Microsoft Certified Partner, complements the new versions of Excel and SharePoint for the financial services industry by focusing on the spreadsheet as an application rather than as a document. “While Excel provides internal logic checking, it cannot necessarily follow the change from one version of the spreadsheet to the next and therefore doesn’t support an operational process,” said Ralph Baxter, director of marketing and strategy at ClusterSeven.

“Spreadsheets provide the fastest fix but figures posted from spreadsheets to the general ledger are unauditable and open to error or abuse,” said Steve Semenzato, CEO of
ClusterSeven. The company’s enterprise management software provides an alternative approach. It does this in two ways. Firstly, it monitors activity and changes to give an audit trail by time and user, storing the information in a Microsoft SQL Server database. Second, it provides alerts on activity to provide rapid notification of unusual changes, whether these be structural (e.g. formula changes) or value-based.

Perhaps the most significant enhancement in the 2007 release of the Microsoft Office system is the way in which client applications such as Excel work better together with Office SharePoint Server 2007. Specifically, Excel Services is the technology that is part of Office SharePoint Server that will offer customers better ways to control and manage their business-critical spreadsheets. Banks will be able to model new products in Excel and offer them across the enterprise from a server where the model is managed and guarded against unauthorized changes. The result – the speed to market that is characteristic of Excel with the complaint workflow banks require.