www trintech com NASDAQ TTPA Spreadsheet Control Grenville Croll C Eng October 2008 Agenda • Welcome to Trintech • Spreadsheet Risk • Spreadsheet

Document Sample
www trintech com NASDAQ TTPA Spreadsheet Control Grenville Croll C Eng October 2008 Agenda • Welcome to Trintech • Spreadsheet Risk • Spreadsheet Powered By Docstoc
					     www.trintech.com | NASDAQ: TTPA




Spreadsheet Control
       Grenville Croll C.Eng

                   October 2008
 Agenda*

• Welcome to Trintech
• Spreadsheet Risk
• Spreadsheet Criticality
• Spreadsheet Controls Survey
• The Control Process
   –   Discovery
   –   Risk Assessment
   –   Remediation
   –   Control
• Trintech XLNET technology
• Summary


   *Based upon “Automating Spreadsheet Discovery & Risk Assessment” Eric Perry, Prodiance Corpo, Proc. EuSpRIG Greenwich 2008.
 About Trintech

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



                                      •   Financial Losses
          Error                       •   Loss of reputation
                                      •   Loss of stock value
          Fraud                       •   Fines and penalties
                                      •   Legal challenges
         Abuse                        •   Job lost


    End User Computing Applications present a significant risk
                exposure for all organizations
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


                Information obtained from htttp://www.eusprig.org/stories.htm
Spreadsheet Risk: The Business Issues


• Accuracy of Financial Data
• Integrity of Financial Processes
• Compliance
• Managerial Control
• Visibility
• Transparency
• Productivity
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
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”
Spreadsheet Ubiquity




 “Spreadsheets are integral to
  the function and operation of
   the global financial system”

          An Anonymous Regulator, 2005
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.
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
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
Spreadsheet Survey I

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

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

• Q3: What is your organization currently doing about
  addressing spreadsheet controls?
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
Spreadsheet Control Framework




             Spreadsheets
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.”
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
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
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
Complexity Criteria
Materiality Criteria
Spreadsheet Risk Matrix
Calculating Overall Spreadsheet Risk
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
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
Spreadsheet Remediation – Business Impact



• Initial User Consultation
• Validation of Documentation
• Checking Test Results
• Follow-up
• Each Business Area charged back for the remediation
  effort
Remediation Plan Categories

Document   The application requires procedural and/or technical
           documentation

Test       The application requires testing to ensure it performs the stated
           function
Control    The application will be migrated to a controlled IT environment

Minor      The application requires minor enhancement without the need
Enhance    for significant involvement from business users

Enhance    The application requires enhancements to its functionality.
           These would include building interfaces to other systems,
           automating report generation etc
Migrate    The application should be migrated to a different platform as
           the current platform cannot support the required functionality

Replace    The platform is unsuitable for the required task. The application
           should be replaced either by extending the functionality of an
           existing IT-supported system or by a new development on a
           more robust platform
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
Spreadsheet Testing – Following Links
Spreadsheet Testing – Examine Structure
HMRC – Spreadsheet Remediation Case Study
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
Spreadsheet Control - Dashboard View
Spreadsheet Control – Change Log
Spreadsheet Control – Change Log
  XLNET Spreadsheet Management Platform



                                                                                   Link Migration
                                                                                      Manager
                                                                                 Move spreadsheets into
                                                                                  controlled environment,
                                                   Spreadsheet IQ                automatically update links
                                                   Inventory, Analyze,
Spreadsheet Risk Calculator                         Document & Assess
                                                    Spreadsheet Risk

      Categorize Risk using                        Optimize Spreadsheets
      materiality and complexity


                                                    Secure Repository
                                                    Cell/File Audit Trails
                                                    Versioning
                                                    Access Control
                                                    Approval Workflow
                                                    Email Alerts
                                                    KPIs and Reporting



                                                  Enterprise Spreadsheet
                                                     Manager Server
                                                                              Enterprise Spreadsheet
                                                                               Manager Web Client
                                   Uncontrolled                                 Track and compare
                                   spreadsheets                                 changes to spreadsheets,
      eDiscovery                                                                Access database s
   Discover uncontrolled
    spreadsheets & EUCs
 39:
  across disparate sources
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
www.trintech.com | NASDAQ: TTPA




Thank you - any
    questions?

   Grenville.croll@trintech.com
            +44 (0) 207 628 5235
            +44 (0) 7935 323499

				
DOCUMENT INFO
Description: .Xls Mortgage Calculator Spreadsheet document sample