the spreadsheet lifecycle[1] - clusterseven · 2019-10-04 · the$worldrunsonspreadsheets$!!...

The Spreadsheet Management Lifecycle

Ian Cleaver, VP Product & Professional Services

Introductions

Confidential © ClusterSeven 2017

o Based in London and New York, ClusterSeven is the leading provider of strategic software for EUC management.o Trusted by the world’s leading institutions, we have users in more than 20 countries on five continents.o We’re a Microsoft gold-certified Partner and a Google Enterprise Partner

ClusterSeven helps you strike the right balance between flexibility and control. Using our platform, you can efficiently identify, track and manage your Excel spreadsheets, Access® databases and other EUCs — without affecting the agility they bring to your business or the productivity of your users.

Ian Cleaver– VP Product & Professional Services

o Ian joined ClusterSeven in 2012o Responsible for Product Management, Support & Professional Services Globally.o Ian brings over 20 years of experience in financial services, most recently at HSBC and RBC.

ClusterSeven: Global leader of EUC governance & control solutions


Strategic insightExperts in delivering technology to enable EUC governance and control.

Active ManagementDiscovery

Inventory Management

Industry leader

since 2003

Deep technology expertise 25+ years experience

Chartis Operational Risk Management Systems RiskTechQuadrant® 2015: Best of Breed Provider

Proven Industry Partnerships

Over a 1/3 of worlds leading banks as customers

3 Global officesLondon, New York, Boston

The Spreadsheet Lifecycle


The World runs on spreadsheets !!


Excel Released : September 30, 1985;; 32 years ago

More than 1.2 billion people useMicrosoft Office in 140 Countries

The foreign exchange market in London alone is over $2 trillion a day How much is dependent upon the existence of Excel?.

Spreadsheets Can Solve Any Problem

6

Literally Any Problem

7

The Value of Spreadsheets


Spreadsheets are increasingly used throughout many organizations in and around business critical processes due to:

• Limitations of existing corporate systems

• The time and resources needed to develop new software applications

• The spreadsheet is readily available at no cost;; and is flexible enough to allow users to swiftly produce new reports, models and complex calculations, from the ground up

Over 60% of organizations rely on spreadsheets for business critical data processes *

* The Annual State of the Spreadsheet 2016 Survey

However there are costs associated


Regulatory requirements

Regulatory requirements

Model Risk Management(SR11V7)BCBS 239

Data Requirements

Governance

PRA

Market Requirements

Direct costs and risks

Errors

Inactivity

Key Man dependency

Regulatory requirements and expectations

Indirect losses that may be introducedTechnology

Training

Indirect costs

Reputational loss

Asset investment

IT road map andimplementation

Auditors/Executives

Clients/Shareholders

Intellectual Property

Data Compatibilities

But when they do go wrong….


• Researchers discovered a major math error in a 2010 study by Harvard economists Carmen Reinhart and Kenneth Rogoff on the sustainability of debt, poking holes in their popular economic theory based on spreadsheet calculations. This resulted in widely reported speculation that their theory, quoted by experts in several different publications, was incorrect.

But when they go wrong….


JPMorgan Chase lost more than $6 billion in its “London Whale” incident, in part due to Excel spreadsheet errors (including alleged copying and pasting of incorrect information from multiple spreadsheets).

In a sad twist of fate, the British bank Barclays sent an offer to purchase another firm in 2008 that hid—instead of deleting—nearly 200 spreadsheet cells, resulting in unnecessary losses.



Marks and Spencer experienced trouble when it mistakenly issued an inaccurate company result due to a spreadsheet error. Double-counting in a spreadsheet led M&S to say that sales had risen by 1.3% in the three months leading up to July when in reality they had fallen by 0.4%.



In total, spreadsheet calculations represent up to £38bn of British private sector investment decisions per year, data harvested through YouGov found.

Yet 16% of large companies have admitted finding inaccurate information in spreadsheets more than 10 times in 2014.

And spreadsheets are found increasingly in Regulatory frameworks


Ø Board of Governors of the Federal Reserve System and Office of the Comptroller of the Currency OCC 2011-12

Ø Supervisory Guidance on Model Risk Management, April 2011

Ø Dodd-Frank Act Stress Testing (DFAST) Reporting Instructions, December 2014

Ø Sarbanes-Oxley (esp. Section 404), July 2002

Ø Capital Planning at Large Bank Holding Companies: Supervisory Expectations & Range of Current Practice, Federal Reserve, August 2013

Ø Federal Housing Finance Agency – Model Risk Management Guidance, November 2013

Ø Staff Audit Practice Alert No. 11 October , 2013

Ø GTAG Global Technology Audit Guide Auditing User-developed Applications, June 2010

Ø Basel Committee on Banking Supervision: Principles for effective risk data aggregation and risk reporting (BCBS 239), January 2013

Ø Monetary Authority of Singapore Technology Risk Management Guidelines, June 2013

Ø Jersey Financial Services Commission, Banking Business, Prudential Reporting Summary Findings, September 2014

Ø Solvency II: internal model approval process data review findings, Financial Services Authority, September 2012

Ø Anforderungen an individuelleDatenverarbeitung aus aufsichtsrechtlicherSicht MaRisk (AT 4.4 & 7.2), sowie KWG $25a

Ø Prudential Practice Guide;; CPG 235 –Managing Data Risk;; PPG234 Management of security risk in Information and information technology

w

Problem: Most systems are reliant on spreadsheets to supplement functionality


Financial systems:AccountingRisk ManagementTradingTax

Many different purposes but very little visibility of this end user activity at the enterprise level.

w

Their role in modelling & report generation is critical


Regulatory & management reports

CCARDodd FrankSolvency IICOREPFINREPTax

Sarbanes-Oxley

Model /Data risk management = the whole information supply chain from data to consumer

Supplementary models

Financial systems:– Accounting– Risk Management– Trading– Tax

Final mile report

manipulation

Data integration /extractionData flows

Key

X-spreadsheetreconciliation / visualization

Data preparation / aggregation

Spreadsheet development


Spreadsheet development – In theory

Spreadsheet development – In practice

Spreadsheets do not typically follow traditional development life-cycles, instead they change and grow until they become critical components of business processes.

But why bother testing?


Lack of testing and visibilityof how a spreadsheet changes results in significant time wasted and potential financial impactsto the organisation

Managing the Spreadsheet Lifecycle


ClusterSeven suite provides a complete spreadsheet solution


Discovery Inventory

Each registered Model/spreadsheet registered with consistent set of data e.g.• Business area• Process supported• Criticality Assessment • Controls• Rectification• Audit

• Optimised search to establish file estate

• Filtered reports and detailed analysis to validate Inventory

• Data lineage and technical complexity scoring

Hundreds of Files

Thousands of Files

Millions of Files

Monitoringand

Comparison

Detailed change analysis to support:• Control Objectives (e.g. Structure, Macro,

Formula changes) • Operational efficiencies (Data reconciliations) (Excel and Access only)

Inventory validation

Identify critical files requiring active management

Active monitoring validation


Level 1

Level ???

Single Excel File feeding the Capital Model

High Risk

Protected

File Exists Data Link

Has Macro

Level 2Level 3

ClusterSeven - Discovery Discovery

ClusterSeven suite provides a complete spreadsheet solution


Discovery Inventory

Each registered Model/spreadsheet registered with consistent set of data e.g.• Business area• Process supported• Criticality Assessment • Controls• Rectification• Audit

• Optimised search to establish file estate

• Filtered reports and detailed analysis to validate Inventory

• Data lineage and technical complexity scoring

Hundreds of Files

Thousands of Files

Millions of Files

Detailed change analysis to support:• Control Objectives (e.g. Structure, Macro,

Formula changes) • Operational efficiencies (Data reconciliations) (Excel and Access only)

Inventory validation

Identify critical files requiring active management

Active monitoring validation

Monitoringand

Comparison

The benefits of Managing the Spreadsheet Lifecycle


ClusterSeven Benefits within the Spreadsheet Lifecycle


• Discovering spreadsheets & their linkage relationships (clear visibility)

• Identifying potential issues and bad practice

• Manage spreadsheet versions

• Monitoring spreadsheet changes

• Documentation through the spreadsheet lifecycle

The Benefits of Managing Spreadsheets


The implementation of a Spreadsheet Management / EUC Technology delivers Return On Investment in a number of ways:

• Allows you to retain the value of the business agility enabled by spreadsheets

• Reduces the risk of using Spreadsheets in an uncontrolled and unmanaged environment

• Assists in the meeting of regulatory compliance requirements

• Improves data quality in the business processes (related to spreadsheets)

• Improving the efficiency of spreadsheet usage

• Improves the corporate knowledge of EUC assets

Thank you for listening

Ian [email protected]

the spreadsheet lifecycle[1] - clusterseven · 2019-10-04 · the$worldrunsonspreadsheets$!!...

Documents