how to take control of spreadsheet risk

Taking Control of Spreadsheet RiskDiane Robinette, President

April 8, 2014

© 2014 Incisive Software Corporation. All rights reserved.

2

Objective Help you understand where spreadsheet risk

hides so that you can have informed conversations with your users to: Improve the quality of spreadsheets Reduce the time spent finding errors Implement effective internal controls

© 2014 Incisive Software Corporation. All rights reserved.

3

Agenda Business Challenges Technology’s Role Spreadsheet Uses Areas of Spreadsheet Risk Case Studies

© 2014 Incisive Software Corporation. All rights reserved.

4

Companies have spent millions of dollars on enterprise software TRYING to replace spreadsheets

© 2014 Incisive Software Corporation. All rights reserved.

5

Spreadsheet Uses

Simple Calculations “What if?” Analysis

Collect data from multiple

people

Charts and Reports

User Interface to Software

Systems

© 2014 Incisive Software Corporation. All rights reserved.

6

Spreadsheets: Love Me, Love Me Not

• Allows for flexibility and agility• Does not require IT

intervention• Does not require users to

change the way they work• Cost of entry is “free”

© 2014 Incisive Software Corporation. All rights reserved.

7

Spreadsheets: Love Me, Love Me Not

• Allows for flexibility and agility• Does not require IT

intervention• Does not require users to

change the way they work• Cost of entry is “free”

• Error prone• Possibility for data corruption• No data validation• Security and controls is limited• Cost and complexity increases

over time

© 2014 Incisive Software Corporation. All rights reserved.

8

Mistakes happen…

© 2014 Incisive Software Corporation. All rights reserved.

9

Bad happens…

© 2014 Incisive Software Corporation. All rights reserved.

10

Early Spreadsheet Technology 1979

Software Arts VisiCalc Columns and Rows Formula command line

Ran on an affordable, personal machine

1983 Lotus 1-2-3 Quickly replaced VisiCalc

1985 Microsoft Excel

Pull down menus Point and click using a mouse

Sources: www.bricklin.com, http://www.dssresources.com/history/sshistory.html

VisiCalc v1.0

1-2-3 v1.0

Excel v1.0

© 2014 Incisive Software Corporation. All rights reserved.

11

Today: Microsoft Excel 2010 1,048,576 rows by 16,384 columns 66,530 hyperlinks Length of formula contents = 8,192

characters Dependency on a single cell = 4 billion

formulas

© 2014 Incisive Software Corporation. All rights reserved.

12

Areas of Spreadsheet Risks Formula Errors Defies Best Practice Hidden Information Difficult to see

© 2014 Incisive Software Corporation. All rights reserved.

13

Formula Errors Formula Fails to Cover Area Inconsistent Formulas Data in Formula Range LOOKUP Validation Currency Error Complexity

© 2014 Incisive Software Corporation. All rights reserved.

14

Defies Best Practice Constant in Formula Forward Reference Number Formatted as Text Referencing Blank Cells Referencing White Space

© 2014 Incisive Software Corporation. All rights reserved.

15

Hidden Information Hidden Rows and Columns Hidden Sheets Hidden by Color or Formatting Hidden by Protection

© 2014 Incisive Software Corporation. All rights reserved.

16

Difficult to See External References Missing External References Conditional Formatting Reference Named Ranges Formulas Reference No Other Cell Outlayers

© 2014 Incisive Software Corporation. All rights reserved.

17

Standardize Spreadsheet ModelsCompany Real estate development, investment and

management firm with $24.8B in assets under management

Objective Improve the quality of spreadsheet, consistent modelsSolution Create “perfect” templates Deploy technology for Internal Audit to compare

spreadsheets to templates and evaluate discrepanciesBenefits Consistent and accurate spreadsheets

© 2014 Incisive Software Corporation. All rights reserved.

18

Spreadsheet Governance ProgramCompany $1.2B global chemical products manufacturing companyObjective Reduce risk of critical spreadsheets on the organizationSolution Develop spreadsheet inventory to identify top 1000 “In

Scope” spreadsheets Deploy technology for users of critical spreadsheets to

scan spreadsheets to highlight risks Implement internal control: users must scan critical

spreadsheets at least once a quarterBenefits Scalable spreadsheet governance program Improved spreadsheet quality

© 2014 Incisive Software Corporation. All rights reserved.

19

Proactive Business UnitCompany Fortune 10 International Oil and Gas CompanyObjective Accurate and reliable global tax spreadsheetsSolution Deploy technology for global tax users of

spreadsheets to scan spreadsheets to ensure spreadsheets are working properly

Benefits Confidence in tax spreadsheets

© 2014 Incisive Software Corporation. All rights reserved.

20

Contact

Diane M. Z. RobinettePresident

diane@incisive.com408.660.3077

© 2014 Incisive Software Corporation. All rights reserved.