eusprig 2007 management paper spreadsheet hell simon murphy simon.murphy@codematic.net developer –...

Eusprig 2007

Management Paper Spreadsheet Hell

Simon Murphysimon.murphy@codematic.netDeveloper – Codematic LtdWith contributions fromwww.smurfonspreadsheets.co.uk

Eusprig 2007

Question

• Who knows of an organisation that may be suffering from Spreadsheet Hell?

Eusprig 2007

Spreadsheet background• Over 1 trillion dollars ($

1,000,000,000,000)• Often linked to other technologies such

as OLAP, ADO, COM or .net etc.• Finance, Banking and Sales and

Marketing areas

Eusprig 2007

Agenda• Spreadsheet use• Micro spreadsheet hell• Macro spreadsheet hell• Summary• Formulas• Questions

Eusprig 2007

Spreadsheet useTechnology

Spreadsheets - 100%

Source:CFO (2004): http://www.cfo.com/article.cfm/3014451?f=related

Eusprig 2007

Spreadsheet use 2

Organisations with 'some' spreadsheet hell

Admit hell - 60% Deluded? - 40%

Stats source:CFO (2004): http://www.cfo.com/article.cfm/3014451?f=related, interpretation mine

Eusprig 2007

Spreadsheet Lifetimes

0 12 24 36 48 60 72 84 96 108 120

Months

Development 4

Development 3

Development 2

Development 1

Some spreadsheet development times v Lifetimes

Development Live- in use

Eusprig 2007

Spreadsheet hell types

• Micro

•Macro

Eusprig 2007

Frankensheet

Eusprig 2007

Frankensheet

Eusprig 2007

Frankensheet

Eusprig 2007

Micro Spreadsheet hell

Eusprig 2007

Micro Spreadsheet hell 2

Eusprig 2007

Demo

Eusprig 2007

Micro Spreadsheet hell 3

Eusprig 2007

Macro Spreadsheet hell

• 100% considered (mis)management the key factor (SOS)

Biggest Spreadsheet Risk

Management - 100%

Eusprig 2007

Organisational management

Eusprig 2007

Common Folder Structure

Eusprig 2007

Duplicate history

Eusprig 2007

Spreadsheet forest

Eusprig 2007

Common end user computing

Primary data sources

WorkbookWorkbook

Workbook

Workbook

Workbook

Workbook

Workbook

Primary data sources

WorkbookWorkbook

Workbook

Workbook

Workbook Workbook

Workbook

Eusprig 2007

Better end user computing

Primary source data

Reporting data

sources

Workbook

Workbook

Workbook

Reporting data

sources

Primary source data

Reporting data

sources

Workbook

Workbook

Workbook

Reporting data

sources

Eusprig 2007

Summary• Micro spreadsheet hell

– Individual spreadsheet quality– Reasonably well documented– Limited consensus on quality

• Macro spreadsheet hell– Organisational issue– Being replaced(?) by database hell– Effective infrastructure investment helps

Eusprig 2007

Formula Fun

• =SUM(#REF!)• =Cost*50%*1000• =((Area/Floors+IF(Levels>0,gArea/

Levels,0))*200%/2)^0.5*6• ='Standard Cost.xls'!DUMPSTER• =GetExtData(“Some Criteria")

+181970242+5989278

Eusprig 2007

Formula Fun

Eusprig 2007

Formula Fun

Eusprig 2007

Questions?

• Email– simon.murphy@codematic.net

• Websites– www.codematic.net– www.xlanalyst.co.uk

• Blog– www.smurfonspreadsheets.co.uk