Spreadsheet Control Grenville Croll C.Eng October 2008

1. Spreadsheet ControlGrenville Croll C.EngOctober 2008

2. Agenda* Welcome to Trintech Spreadsheet Risk Spreadsheet Criticality Spreadsheet Controls Survey The Control Process Discovery Risk Assessment Remediation Control Trintech XLNET technology Summary

3. About Trintech Irish HQ US offices - Dallas, Chicago, San Jose, Kansas International Offices United Kingdom Netherlands Broad Customer Base 600+ companies across a variety of industries 20 Years in Financial Solutions GRC platform Financial Close Risk management GL Reconciliations Transaction Reconciliations Spreadsheet Management Data Flow Services ASP and Hosting Public – TTPA on Nasdaq IPO Sept 1999 Strong financial foundation Profitable core business Cash > $20 million No debt 66% recurring revenue 23% YoY Growth 210+ Employees Global Partner Alliances Accenture HP Microsoft Oracle Local Partnerships Cube ( Poland) Spectrum ( Australia)

4. Spreadsheet Risks Error Over 90% of Spreadsheets have errors, of which 50% material In a recent study 20 spreadsheets had between them a total of $259m of material defects Fraud Due to the mixture of formulae, VBA & data, the spreadsheet is a perfect environment for perpetrating fraud Overconfidence Because users don’t look for defects, they assume there aren’t any Overdependence Spreadsheets are ubiquitous Interpretation There is more than one way of making a Business Decision Enterprise Interoperability Formal Limitations on Systems of Spreadsheets

5. Why worry about Spreadsheet Risk? SarahSarah

6. Some Materialized Risks: Close calls – a utility company found, at the last minute, that in very long spreadsheet formula, the parentheses were out of place Projected gains fell from $200M to $25M Embarrassing errors – a utility company submitted the wrong week's gas storage figures, leading to an artificial inflation of natural gas prices. The company had used the same computer file name for each week's storage balance spreadsheet report, making it easy for the wrong one to be sent Errors in the range of $200M to $1B Financial re-statements - two weeks after releasing their third quarter earnings, a mortgage company confirmed a mistake made in a spreadsheet used in implementation of a new accounting standard Earnings restated by $1.2B Fraud – executives of a healthcare service provider admitted to preparing a false spreadsheet for auditors that inflated the assets thus falsifying the company’s worth Earnings overstated by at least $3.5B SarahSarah

7. Spreadsheet Risk: The Business Issues Accuracy of Financial Data Integrity of Financial Processes Compliance Managerial Control Visibility Transparency Productivity

8. Who Owns Spreadsheet Risk? Important that there is no confusion Establish EUC policy Business Owners do not want to cede control over their applications IT does not want to become a foster parent To adopted applications In which it has played no previous role Question: who can properly judge the risk? Operational Risk Business units own the spreadsheets (and the risk) IT owns the control framework

9. Spreadsheet Ubiquity “Put simply and succinctly, despite the higher operational risk, Excel is everywhere - it is the primary front-line tool of analysis in the financial business. Most traders price deals in spreadsheets and enter them in large-scale deal capture systems afterwards” “Excel is utterly pervasive. Nothing large (good or bad) happens without it passing at some time though Excel”

10. Spreadsheet Ubiquity “Spreadsheets are integral to the function and operation of the global financial system” An Anonymous Regulator, 2005

11. Spreadsheet Criticality Critical Spreadsheet Material error could compromise a government, a regulator, a financial market, or other significant public entity and cause a breach of the law and/or individual or collective fiduciary duty. May place those responsible at significant risk of criminal and/or civil legal proceedings and/or disciplinary action Key Spreadsheet Material error could cause significant business impact in terms of incorrectly stated assets, liabilities, costs, revenues, profits or taxation etc. May place those responsible at risk of adverse publicity and at risk of civil proceedings for negligence or breach of duty and/or internal disciplinary action Important Spreadsheet Material error could cause significant impact on the individual in terms of job performance and career progression without directly, greatly, immediately or irreversibly affecting business or the organization.

12. Critical Spreadsheets: Key Resources EuSpRIG www.eusprig.org Spreadsheet Risks research – 10 year track record Annual Conference Next conference “The Role of Spreadsheets in Organisational Excellence” Paris, France, 2/3 July 2009 Discussion Group Conference Proceedings filed on www.arxiv.org search for “spreadsheet” – about 100 papers & management summaries

13. Spreadsheet Survey Completed by Prodiance / Jefferson Wells Monthly Webinar on Spreadsheet Remediation & Control 2007 / 2008 Several Thousand Delegates Senior Finance Internal Audit Broad Range of Companies Responded to Three Online Survey Questions

14. Spreadsheet Survey I Q1: How important is it to have the proper safeguards and controls for your organization’s mission critical spreadsheets?

15. Spreadsheet Survey II Q2: Do you feel most organizations today have adequate spreadsheet controls in place?

16. Spreadsheet Survey III Q3: What is your organization currently doing about addressing spreadsheet controls?

17. Spreadsheet Survey Summary 83% of financial executives who responded felt having proper safeguards and controls in place was important Yet few (8%) felt that adequate controls were implemented in most organisations Most (76%) organisations were in the early stages of implementing spreadsheet controls Building a Business Case Evaluating Existing Controls Implementing a Control Framework

18. Spreadsheet Control Framework

19. Discovery Purpose is to create an inventory Top Down Process Based Not generally Thorough Enough Bottom Up File Search based Exhaustive Audit Firms Recommend Automated Discovery “…commercially available or homegrown tools that can be configured to scan network resources and return a list of all spreadsheets used in the organization. Providing that all relevant resources are scanned, this technique will result in the most complete spreadsheet population list possible.”

20. Discovery Search all computers, file shares, document & records management repositories & employee PC’s Scan Initially May come up with 10-100,000 files or more Then Periodically (weekly) Discover new files since last scan Scan All file names, Zip files & *.xls, *.xlsx Search password protected files too Be Exhaustive Create a centralized inventory Can be a challenging exercise

21. Discovery Results Lots of Spreadsheets Only some of which will be Key or Critical Search just after period financial close is a good way Typically, about 100 to 1,000 will be key or critical to the organisation ie relevant in say financial reporting Need to narrow down the search and focus on the riskiest Automatically Calculate Risk by searching through Spreadsheets and assessing them for Materiality, Complexity & Overall Risk Focus remediation & control efforts on the Riskiest

22. Risk Assessment Materiality Metric – What is in the Spreadsheet? Cell Values; Currency Values; operational values; document properties; file names; sheet names; file paths; external links Assign a score to each of these discovered attributes Materiality is Immaterial, Material or Critical Complexity Metric – How big is the Spreadsheet? #Worksheets; #formulas; #cells; #formula errors; #Nested Ifs; # External Links; #Macros; #Hidden Sheets; #Very hidden sheets Assign a score to each of these discovered attributes Criticality is Rudimentary, Light, Intermediate or Advanced Use Materiality & Complexity to compute overall Risk Overall Risk is High, Medium or Low

23. Complexity Criteria

24. Materiality Criteria

25. Spreadsheet Risk Matrix

26. Calculating Overall Spreadsheet Risk

27. Discovery & Risk Assessment Summary Discover all relevant spreadsheets across the network Create centralized inventory Perform risk assessment based on pre-defined materiality and complexity criteria Generate and distribute initial spreadsheet inventory and risk report Repeat the entire process per a weekly or monthly schedule to identify any new high risk spreadsheets

28. Spreadsheet Remediation Categories This approach taken by Allied Irish Bank Determine Appropriate Course of Action for Each Spreadsheet Document Test Control Minor Enhancement Enhancement Migration Replacement Ie put in place those parts of the software development process that have been missing Can be outsourced to specialist remediation shops

29. Spreadsheet Remediation – Business Impact Initial User Consultation Validation of Documentation Checking Test Results Follow-up Each Business Area charged back for the remediation effort

30. Remediation Plan Categories

31. Spreadsheet Testing There is only one effective method, which is: Independent Cell-by Cell inspection of key & critical Spreadsheets by multiple independent reviewers Inspect all formulas, cells, links, graphics Check for commercial correctness Perform structured testing Test cases Extreme Values Regression Testing Create/update documentation Remediation Tools are useful Shown to pick up many kinds of seeded errors Then Place the Remediated Spreadsheets in a Controlled Environment to prevent unauthorised modification

32. Spreadsheet Testing – Following Links

33. Spreadsheet Testing – Examine Structure

34. HMRC – Spreadsheet Remediation Case Study

35. Spreadsheet Control Secure Environment Full Access Control Rights & Permissioning Change Monitoring Version Control Capturing new versions on save or on schedule Differencing between new and last version Reporting changes Alerting Changes by reports or email Approval Workflow Ensuring that required changes go through a permissioning (and re-testing) process Ie Configuration Management for Spreadsheets

36. Spreadsheet Control - Dashboard View

37. Spreadsheet Control – Change Log

38. Spreadsheet Control – Change Log

39. XLNET Spreadsheet Management Platform

40. Spreadsheet Control: Anagrammatical Summary Spreadsheet The issue Heated Press A result of spreadsheet error Heads Pester What your boss then does Hearts Speed During the remediation process Phased Reset Stability following control

41. Thank you - any questions? Grenville.croll@trintech.com +44 (0) 207 628 5235 +44 (0) 7935 323499