The special case of Excel lineage

Excel has a special role for managers and analysts. It can serve as a data store, reporting tool, data preparation, analysis tool, and even programming language for non-programmers. Its potential for complexity is staggering.

At the same time, Excel’s potential for error is great. A 2017 article estimated that 90% of Excel spreadsheets have significant errors, including a Risk model cut-and-paste error that cost JP Morgan $6 billion.

Internal data stores in Excel may represent backup or security risks.

Sources accessed by Excel through SQL may be obsolete, out of date, or targeted for deletion because no one knows Excel is using it.

Lineage Solution

Many Excel spreadsheets can be read, parsed, and lineage can be inferred from the structures within. The Excel spreadsheet will be formalized as part of the information infrastructure. Multiple worksheets, ranges, and cell-level calculations become part of the lineage.

Is this hard-dollar or soft-dollar? If this is a tool for an ongoing program of information quality like six sigma, you may save hard consulting dollars by not repeating previous analyses, avoiding rework, and focusing SMEs on pre-approving trusted calculations.

Hard-money advantages include:

Not repeating previous analyses, thereby avoiding rework
Focus SMEs on pre-approving trusted calculations. Avoiding an Excel auditor/finance consultant could represent hard dollar savings of 6 months * $250 per hour, or $250,000.

Soft-money advantages include:

Catalog Excel spreadsheets within the enterprise
Track changes to individual spreadsheets
Identify existing analyses that the spreadsheet duplicates
Help to find errors by examining the lineage.

Is an avoided insurance risk of $6 billion hard-dollar or soft-dollar? A decision tree that says there is a 0.1% chance of a $6 billion restatement will be worth $6 million, plus incalculable soft costs in goodwill and reputation.