Automating Spreadsheet Discovery & Risk Assessment

Automating Spreadsheet D iscovery & Risk Assessment Eric Perry Proce edings of EuSpRIG 2 008 Conference " In P ursuit of Sprea dsheet Excellence " ISBN : 9 78-90561 7-69-2 Copyright: European Spreadsheet Risks Interest Group ( www.eusprig.o rg ) and Author 61 A uto mating Spreadsheet Di scovery & Risk A sse ssment Eric Perry Prodiance Corporation 5000 Executive Parkway , Suite 270 San Ramon, CA 94583 – U SA eric.perry@prodiance.com Abstract There have been many articles and m ishaps published about the risks of uncontrolled spreadsheets in today’s business environment, including non- compliance, op erational risk, errors, and fraud all lea ding to significant loss events. Spreadsheets fall into the realm of end user developed a pplications and are often absent the proper safeguards and controls a n I T org anization would enforce for enterprise applications. There is also an overall lack of software programming discipline enforced in how spreadsheets are developed. However, before an organization can app ly pr oper controls and discipline to critical spreadsheets, a n accurate and liv ing inventory o f spreadsheets across the enterprise must be cr eated, and all critical spreadshe ets must be i dentified. As such, this paper proposes a n autom ated approach to the i nitial stages of the spreadsh eet managem ent lifecycle - di scovery, i nventory and risk as sessment. Without the use of technology, these phases ar e often treated as a one- off project. By leveraging technology, they become a sustainable busin ess process. Keywords Spreadsheet discovery , spreadsheet risk assessm ent, spreadsheet controls 1.0 Spreadsheet Survey & Results During the co urse of 20 07 and 2008, Prodiance Corporation and Jefferson Wells International hosted a m onthly series o f educational webcas ts on Spreadsheet Remediation and Control. Thousands of senior f inance and internal audit executives across a broad range of companies attended these events and responded t o a s eries of survey questi ons. The questions and results of this spreadsheet surv ey are as follows: • Q1: How important is it to have the proper safeguar ds and controls for y our organization’s m ission critical spreadsheets? Figure 1: Im portance of Proper Spreadsheet Con trols Automating Spreadsheet D iscovery & Risk Assessment Eric Perry Proce edings of EuSpRIG 2 008 Conference " In P ursuit of Sprea dsheet Excellence " ISBN : 9 78-90561 7-69-2 Copyright: European Spreadsheet Risks Interest Group ( www.eusprig.o rg ) and Author 62 • Q2: Do you feel m ost organizations today have adeq uate spreadshee t controls in place? Figure 2: Adequacy of Cur rent Spreadsheet Contr ols • • Q3: What is your organiz ation currently doing about addressing spread sheet controls? Figure 3: Spreadsheet Cont rols Progress From the survey results, financial executives who responded felt having proper spreadsheet safeguards and controls in plac e is very important (83%), yet f ew felt that adequate controls were implemented in most organizations (8%). In additi on, most organizations surveyed (76%) were in the early st ages of implementing spreadsheet controls – either building a business case, evaluating existing controls, creating an inventory, or evaluating spreadsheet management and control solutions. T hese survey results c onfirm the need for spreadsheet discovery, inventory and risk assessment to furth er the adoption of spre adsheet controls. 2.0 Spreadsheets – Which Ones Are Critica l? In reading th e latest research from indust ry experts [6] a nd analysts [7], we see that many organizations today are either unaware of the many pot ential risks uncontrolled spreadsheets can present to their organization, or t oo busy working on higher priority projects. Panko [ 5] asserts t hat “ spreadshee t error rates are unacceptable in corporations today” and that th e solution to addressing these high error rates and t he risks they present requires comprehensive testing. However, many organizations may not be ready for t esting because they ar e unaware of all of the spreadsheets that have been developed, where they are stored, and how t o categorize Automating Spreadsheet D iscovery & Risk Assessment Eric Perry Proce edings of EuSpRIG 2 008 Conference " In P ursuit of Sprea dsheet Excellence " ISBN : 9 78-90561 7-69-2 Copyright: European Spreadsheet Risks Interest Group ( www.eusprig.o rg ) and Author 63 them in t erms of business risk. Prior to t he recommended s preadsheet testing eff orts, organizations f irst need to focus on sp readsheet d iscovery and the creation of a l iving inventory. A thorough risk assessment and c ategorization of this inventory will help determ ine which spreadsheets are mission critical, or p ose high r isk to an organization. Typically, high risk s preadsheets have a direct i mpact on the financial close and financial reporting process, tracking of P&L ( e.g. revenue recognition), or regulatory reporting. In a n e arlier EuSpRIG paper, Cr oll [ 1] defined a critical spreadsheet as one where a “material error could compromise a government, a regulator, a financial market, or othe r si gnificant public entity and cause a breach of the l aw and/or individual or collective fiduciary duty.” Often a good place to start looking for critical spreads heets is the financial c lose process. 3.0 Discovery & Risk As sessment – Typical App roach Unless spreadsheet use wit hin an organization is is olated to a sing le finance department, discovery, inventory and risk assessment can be a challenging exer cise. In many cases, thousands or even hundreds of thousands of spreadsheets exist across a distribute d network and multiple geographic locations. End users often have no idea where latest the versions of spreadsheets are stored or where prior versions are archived. As such, creating an inventory is often a manual and time and resource intensive effort. Once an inv entory i s c reated, it i s quickly outdated as new spreadsheets a re often created on a daily, weekly and m onthly basis. Spreadsheet discovery is an ongoing chall enge, and industry experience has shown the i nitial process can absorb countless man hou rs and extend for up t o 2 or 3 m onths before a n a ccurate inventory is created. In addition, once critical spreadsheets are discovered, those creating the inventory (t ypically internal auditors) often have limited knowledge of s preadsheet u se and complexity. Thi s makes risk assessment difficult, and without a quantifiable methodology t o assess risk and i mpact t o business, it can be impossible. Many o rganizations have co mpiled risk m atrice s, but applying them manually can lead to inconsistent results. 4.0 Discovery & Spread sheet Risk Assessmen t - Automated Approa ch Automating spreadsheet discovery requires the application of software technology and a pr oven methodology. Leading audit firms [2] recommend usi ng “…c ommercially availa ble or homegrown t ools t hat can be configured to scan network resources and return a list of all spreadsheets used in the organization. Pr oviding that all relevant resources are s canned, this technique will result in the m ost complete spreadsheet population list possible.” The following g uidelines have been used recent ly and with a great deal of success at sev eral leading banks and insuranc e companies. • First, any and all compute rs should be identified; in cluding corporate file shares, document and records m anagement repositories, and e m ployee PCs. Particular attention should be given to those suspected of c ontaining critical spre adsheets. • Then, these computers shou ld be scanned initial ly and on a scheduled b asis (i.e. weekly is recomm ended) to create a centralized inventory. • Scanning criteria should include any and all files con taining file nam es known to be critical (e.g. revenue recog nition.xls, 2Q_2008_earnin gs.xls, etc.), those that have been created or last saved/m odified during the financial c lose cycle, and those created or modified since the last disc overy. • The software should also h ave options to capture any spreadsheets with incorrec t or missing file extensions, o r those compressed in ZI P folders. Automating Spreadsheet D iscovery & Risk Assessment Eric Perry Proce edings of EuSpRIG 2 008 Conference " In P ursuit of Sprea dsheet Excellence " ISBN : 9 78-90561 7-69-2 Copyright: European Spreadsheet Risks Interest Group ( www.eusprig.o rg ) and Author 64 • Advanced options for find ing any linked (e.g. depen dent spreadsheets or d ata sources) should strongly be consider ed. If a spreadsheet is deem ed critical, then any spreadsheets feeding data i nto it should also be con sidered critical. • Finally, read only perm ission must be granted to any user or software discov ering files on employee desktops, or an opt ional agent should be deployed to run undetecte d by the local user (and perfo rm the scan). 5.0 Spreadsheet Risk Asse ssment Methodology Initial discovery r esults are likely t o contain an inventory of t housands of spreadsheets, not all of which are critical. To e fficiently identify the critica l spreadsheets in your inventory, leading audit firms have prescribed an evaluation of magnitude (or materiality) and complexity [2]. Spreadsheet materiality c an be evaluated by ana lyzing the following criteria: cell values, currency values, operational values, document properties, file names, sheet names, file path, external links. Consider the following example to de fine spreadsheet m ateriality: Figure 4: Spreadsheet Mate riality Criteria For example, using the grid in Figure 4, we can assign a score of “10” to any spreadsheet with a cell value containing the text string “Income”, and an additional “80” points (for a total of 90 points) to an y s preadsheet with a $ value exceeding “5,000,000”. A spreadsheet t hen satisfying both of these criteria would be classified as “ CRI TICAL” acc ording to the Materiality Definition. Similarly, spreadsheet c om plexity can be evaluated by analyzing key spreadsheet elements, including: worksheets, f ormulas, formula errors, array for mulas, nested IFs and num ber of levels, external links, macros, named it ems, invisible cells, hidden sheets/rows/colum ns, very hidden sheets, workbook size, password protection, and workbook size. Consider the f ollowing example to define spreadsh eet complexity: Automating Spreadsheet D iscovery & Risk Assessment Eric Perry Proce edings of EuSpRIG 2 008 Conference " In P ursuit of Sprea dsheet Excellence " ISBN : 9 78-90561 7-69-2 Copyright: European Spreadsheet Risks Interest Group ( www.eusprig.o rg ) and Author 65 Figure 5: Spreadsheet Com plexity Criteria For example, using the grid in Figure 5, we can assign a score of “75” to any sp readsheet containing more t han “1” formula error, “10” more poi nts to any spreadsheet that “Contains Invisible Cells” and an additional “10” points to any spre adsheet that is “Password Protected”. A spr eadsheet meeting all 3 o f these complexit y criteria would sc ore a “95” points a nd r ate “ADVANCED” in terms of complexity. The final step in automating ris k a ssessment is to assign a risk level. T he f ollowing table is an example of how spreadsheet risk can be assigned based on the intersection of materiality and complexity. Figure 6: Spreadsheet Risk Matrix For e xam ple, a spreadsheet that r anks “CRITICAL” for Materiality and “Intermediate” for Complexity would rank as “High” Risk according to the grid in Figure 6. However, to f ully a utomate spreadsheet discovery a nd r isk as sessment, a software application should execute the aforem entioned process in an au tom ated and scheduled process as follows : Automating Spreadsheet D iscovery & Risk Assessment Eric Perry Proce edings of EuSpRIG 2 008 Conference " In P ursuit of Sprea dsheet Excellence " ISBN : 9 78-90561 7-69-2 Copyright: European Spreadsheet Risks Interest Group ( www.eusprig.o rg ) and Author 66 1. Discover all relevant sprea dsheets across the netwo rk 2. Create centralized inv entory 3. Perform risk assessment based on p re-defined materia lity and complexity cri teria 4. Generate and distribute ini tial spreadsheet inv entory and risk report 5. Repeat the entire pr ocess per a weekly or monthly schedule to identify any new high risk spreadsheets 6.0 Conclusion Hoye concluded that “organizations may benefit from software solutions that deliver real-tim e monitoring of critical s preadsheet activity, providing management wit h t ransparency into the control pr ocess” [4]. Real world experience has proven that leveraging software technology provides significant advantages to help overcom e the challenges of the typical approach described above, including : • Reducing the 2-3 m onth timefram e of the typical (manual) approach down to 2-3 days • Conducting a comprehensi ve scan of an entire IT netw ork for any and all spre adsheets in existence, including co rporate file servers, cont ent repositories, and ev en employee PCs • Managing a centralized, liv e inventory of all spreads heets present across an organization • Providing an automated ri sk assessment fram ework and methodology (that is consistent with auditor guidance) to h elp categorize spreadshee ts according to risk level • Enabling discovery to run a s a continuous process to help identify any newly created, high risk spreadsheets, ens uring the centraliz ed inventory is alway s current • Providing manag ement and auditors with visibility into the discovery and r isk assessment process via auto mated reports (deliv ered via email) of the inv entory and any high risk spreadsheets or control policy v iolations Typical approaches to spreadsheet discovery and ri sk assessment resulted in one-off pr ojects with i nconsistent, incomplete and r esults that a re quickly outdated. By leveraging te chnology and b est practices, spreadsheet discovery and risk as sessment reaches m aturity as a sustainable and automated business pro cess. References: [1] Croll, Grenville, September 2007. The Importance and Criticality of Spreadsheets in the City of London. Availabl e online: http://arxiv .org/abs/0709.4063. [2] Deloitte & Touche, March 2008. Improving Spread sheet Audits in Six Steps . A vailable online: http://www.theiia.or g/itaudit/features/in- depth-features-3- 10-08/feature-2/ . [3] EuSpRI G, 2005. Available online: http://www.eusp rig.org/stories.htm 8/4/05 9:20. [4] Hoye, Michael, May 20 06. SOX Requirements fo r Financial Spreadshee t Remediation & Control . Available online: http://www.jeffersonwells. com/Knowledge/I nternal/SOX- SprdshtRem&C ont.pdf. [5] Panko, Ray, D ecember 1997. Recommended Prac tices for Spreadsheet Testing . Available online: http://arxiv.org/abs/ 0712.0109. Automating Spreadsheet D iscovery & Risk Assessment Eric Perry Proce edings of EuSpRIG 2 008 Conference " In P ursuit of Sprea dsheet Excellence " ISBN : 9 78-90561 7-69-2 Copyright: European Spreadsheet Risks Interest Group ( www.eusprig.o rg ) and Author 67 [6] Panko, Ray, A pril 2008. Reducing Ov erconfidence in Spreadsheet Dev elopment. Available online: http://arxiv.org /abs/0804.0941. [7] Ventana Research, Decem ber 2007. Requirements for 21 st Century Spreadsheets: Uses and misuses of a critical busine ss technology .