Managing Critical Spreadsheets in a Compliant Environment

Managing Critical Spreadsheets in a Compli ant Environment Soheil Saadat President and CEO Prodiance Corporation 5000 Executive Parkway , Suite 270 San Ramon, CA 94583 – U SA soheil.saadat@prodiance.c om Spreadsheets – The Hidde n Risks The use of uncon trolled financia l spreadsheets can expose org anizations to unacceptable business and compliance risks, in cluding errors in the f inancial reporting process, sp readshee t misuse and fraud, or even significant operational errors. T hese error s ha ve bee n well documented and thoroughly researched by the European Spreadsheet Risks Interest Group [EuSpRIG, 2005 ]. With the adv ent of regulato ry m andat es such a s SOX 404 a nd FDI CIA in the U.S., and M iFID, Basel I I and Combined Code in the UK, le ading t ax and audit firms are now recommending organizations automate internal controls ov er critical spreadsheets and other end- use com puting a pplications, including Mi crosoft Access databases. At a minimum, auditors mandate version control, chang e control and access control for operational spreadsheets, and more adv anced controls for c ritical financial spreadsheets [PwC, 2004]. While regulatory compliance has remained a key bu siness driver, many organizatio ns are implementing spreadsheet controls to manage operat ional risk, and to achiev e sound corporate governance and process im provements. The inheren t com plexities of operational and finan cial spread sheets expose technological shortcomings of available spreadsheet management solutions. Specifically, financial spreadsheets often contain external links to other s preadsheets and databases. For example, a consolidated re venue spreadsheet may cont ain inbound li nks fr om individual product revenue reports, and outbound links providing results to e xecutive dashboards or the overall balance sheet. Often these critical spreadsheets reside in employee d esktops, in email attachments, or on corporate shared driv es – an uncontrolled environm ent that is absent traditional I T controls. As such, s ecurity over t hese critical spreadsheets te nds to be weak, access is often not controll ed, file versioning is not im plemented, t here i s no visibility in to chang es being made, nor validation tha t external links are correct. The bottom line is critical business decisions are bei ng made e veryday ba sed on data produced by cr itical sprea dsheets, yet executiv es have li ttle confidence or trust in the data being produced in uncontro lled environments. Spreadsheet Links Creat e a Technology Challenge Auditors and executives alike agree t hat the r ight solution t o address t hese challenges is “to move to an automated, contr olled, yet flexible technology- based environment" [Ernst & Young, 2007]. Centralizing spreadsheet control creates a new system of rec ord for all critical spreadsheets, and enables organizations to apply aud itor recomm ended IT controls s uch a s versioning, security and access control, records retention, archival and backup, change control and workflow a utomation [PwC, 2004]. However, si mply moving spreadsheets into a document m anagement system through tr aditional m ethods often bre aks the link s, and this requires many additional man hours to re- establish the links. Without t he use of technology, this is a labor-intensive and manual process, and the lack of visibility i nto spreadsheet links and lack of docum entation compounds the problem. Notwithstanding, today’s co mmercial document management systems are not designed to work seamlessly w ith Microsoft O ffice Excel to preserv e and update spreadsheet links. I f a spreadsheet is moved into a document managem ent system, or e ven to another network file location, the li nks will break. Many companies have t ried migrating crit ical sp readsheets into d ocument management sy stems, only to have exasperated en d users who cannot update t heir spreadsheet input data through the resulting broken links. These projects have f ailed miserably, le aving IT project managers, auditors and financial exec utives to look for alternativ es. Solution for Managing Li nked Spreadsheets Fortunately, for these technology and business challenges there is a solution. A proven approach to efficiently managing li nked s preadsheets in a controlled and compliant environment: • Automates spreadsheet di scovery, documentation a nd risk analysis, including the creation dependency diagra ms to provide visibility into existing spreadsheet links. • Provides tools for the migration of critical spreadsheets i nto secure, do cum ent managem ent r epository while automatically re-establishing any and all links (to their new web folder location, e. g. http://sharepoint/). • Generates a migration or inventory log of spreadsheets migrated and a ny c hanges to spreadsheet links. • Incorporates a t echnology i ntegration layer e nabling leading document managem ent systems (e.g. SharePoint) to au tomatically update real-time dat a feeds through spreadsheet links. • Incorporates auditing of spreadsheet changes down to the cell level to satisfy c hange control requirements. • Automates spreadsheet change request, testing, review and a pproval processes via workflow for both develop ers and end users. Automating Discovery, D ocumentation, and Risk A nalysis To help organizations a utomate spreadsheet i nventory efforts, discovery tools can be leveraged to search across a wide variety of data sources and report on spreadsheets and other end-user applications (including Access databases) that are being used within an organization [Protiviti, 2006]. Through a consolidated in terface, users can search and generate an inventory report on spreadsheets meeting generic or custom search criteria (e.g. all spreadsheets where “Date Last Saved” equals “2006” or “ 2007” would represent spreadshe ets last saved during the past year, or all spreads heets where “Risk” equals “H igh” or “Medium”). Spreadsheet a nalysis t ools c an perform a risk-based analysis ( based on complexity and materiality) while a utomatically generating documentation about critical spreadshee ts. Fo r example a cell and formula diagnostic report can show form ulas with errors conditions, uncover very hidden worksheets, invisible cells, inconsistent form ulas, and a w hole host of other key areas of risk. Inventory reports can also be generated listing all critical spreadsheets (and their depende nts) along with a host of documentation, including date created, date last modified, owner, location, number of external links, number of workshee ts, number of formulas, and many other criteria to show complexity. Spreadsheet experts often refer to this process as a model audit (o r analysis of the correc tness) of spreadsheets [Cro ll, 2007]. Link Migration Tools Once relevant s preadsheets have been discovered, they should be migrated from uncontrolled desktops a nd shared drives i nto a secure, web repository. As mentioned above, thi s c an be a challenging and time consuming task given the alternative of manual copy/paste operations and manually reestablishing links to dependant spreadsheets. An automated and proven approach requires a migration tool that automatically updates any links based on the new repository location of the spr eadsheets. By moving spreadsheets into a secure, web based document management repo sitory, a host of features and controls such as im proved secur ity and access control, versioning, check-in/check- out, recor ds retention, workflow automation, and file level audit trails ar e available. Support for Leading Do cument Management Syst ems An additional requirement includes support for leading 3 rd part y document management systems, including Microsoft Office SharePoin t Services and SharePoint Server. Mos t organizations a lready have t hese technologies in place, and will want to leverage their investments. As such, spreadsheet control solutions should support l eading document managem ent reposi tories via th e WebDAV protocol, which allows the repository to exp ose itself as a network drive letter and pr ovides a se amless end user experience. For example, a user op ening a c ontrolled spreadsheet fr om a Shar ePoint repository could sim ply use the standard File > Open dialog from wit hin Excel t o open a critical spreadsheet, c heck it o ut, makes c hanges, and then use t he standard File > Save dialog to sav e the chang es, chec k t he file in, and then automatically subm it it into a review and approval workflow process where electronic signatures are captured. It is through WebDAV that Excel can automatically and successfully update spreads heet links with 3 rd party docum ent management repositories. With this approach, the impact to end users is minimized, as i s the impact to existing bus iness processes. In addition, hav ing no software requirements for client com puters insulates end users from t he complexities of the technology, minimizes training requirements, and reduces IT support. Change Management Following auditor guidance, there are two aspects of change managem ent that are required for critical spreadsheets, a d etailed audit history of changes down to the cel l level, and an automated workflow process to en force the requesting, incorporating, reviewing and approving of all changes. Spreadsheet control solutions can capture changes to data, formulas, macros, queries, and also r eport o n row and column insertions/deletions and alert u sers via email on any t hese changes. This prov ides a n extensive d atabase for m anagement reporting. Combined wit h automated workflows, spreadshe ets can be r outed for r eview, validation and approval to enforce corporate change management policies and to ensure that financial spreadsheets are appropriat ely reviewed during the quarter and y ear-end close process. [Pank o and Ordway, 2008] Business Benefits By incorpor ating the technology capabi lities described i n this paper, or ganizations can take a proactive approach to automating t he spreadsheet compliance lifecycle, from discovery and inventory, to ris k analysis, management and automation or key fina ncial workflows. Whether the need is being driven by the ne ed to satisfy regulatory compliance mandates, improve existing bus iness processes, or to better manage operational risk, controls over c ritical spreadsheets c an be automated t o help restore c onfidence and trust i n key financial da ta analyzed and reported in spreadsheets. Business benefits realized t o date by organizations successfully automating sp readsheet controls include : 1. Improved visibility i nto end user computing environm ent ( e.g. spreadsheets and Access databases) via m anagement and change reports 2. Improved compliance with regulatory mandates, including SOX 404, FDICI A, MiFID, Basel II and Combined Code that satisfies audi tor scrutiny 3. Improved internal controls via technology autom ation 4. Improved financial process es via workflow autom ation 5. Improved spreadsheet dev elopment and use 6. Improved productivity for e nd users References: [1] Ernst & Young, A pril 2007. Insurance Industry Str uggling to Meet Heightene d Data Management Demand Acco rding to Ernst & Young Actuarial Transformation (TM) Roundtable . Available online: http://ww w.thefreelibrary.com . [2] EuSpRI G, 2005. Available online: http://www.eusp rig.org/stories.htm 8/4/05 9 :20. [3] Croll, G. J. 2007. A Typ ical Model Audit Approach : Spreadsheet Audit Metho dologies in the City of London . Availab le online: http://arxiv.org /ftp/arxiv/papers/0712/0712.2591.pdf. [4] Panko, R. and Ordway , N., April 2005. Sarbanes-Oxley: What About all the Spreadsheets? Proceedings of the European Spreadsheet Ri sks I nterest Group (EuSpRIG) 2005 15-47 ISB N:1-902724-16-X . Available online: h ttp://arxiv.org/abs/0804.0797v 1 [5] PricewaterhouseCoopers, J uly 2004. The Use of Spreadsheets: Considerations for Section 404 of t he Sarbanes-Oxley Act . http://www.pwc.com/ images/gx/eng/fs/insu/rt5.pdf Acc essed 27 May 08 10:12 [6] Protiviti, February 2006. Excel in Managing Spreadsheet Risk . Internal Audit & Business Risk, February 2006, Page 32. http://www.protiv iti.com/downloads/PRO/pro- gb/Excel_in_managing _spreadsheet_risk.pdf Accessed 27 May 08 10:08