“If you only have a hammer, you tend to see every problem as a nail.”
- Abraham Maslow
Microsoft Excel is one of the most powerful, widely used software applications of all time—and the most misused. In 2003 Microsoft CEO Steve Ballmer said there were about 400 million users of Microsoft Excel. Everywhere you look; Excel is used, misused, and poorly used to “solve” a wide variety of problems.
Excel is wonderful. It enables end users to create simple ad-hoc solutions and sophisticated applications. It is the common solution for proposal pricing, cost analysis and cost estimating. Excel’s greatest strength, however, is also a great risk for companies. As software is developed, bugs and errors surface and are fixed. Excel applications usually short circuit the development process and break, a lot, when it counts.
A Brief History of Excel
In the mid-1980s, Microsoft Corporation developed Excel to compete with Lotus 1-2-3. Released in 1987, the first incarnation of Excel was version Excel 2.05 (there was no Excel 1.0). By 1988, Excel displaced Lotus 1-2-3 and has dominated the spreadsheet market ever since. Excel versions 2.05 through 7.0 had 16,384 rows and 256 columns. Excel versions 8.0 through 11.0 had 65,536 rows and 256 columns. Excel 2007 Version 12.0 and later workbooks are “limited” to 1,048,576 rows and 16,384 columns and the number of spreadsheets in a workbook is only limited by the PC’s memory. Like bacteria growing in a Petrie dish, the number of cells in Excel has grown logarithmically – from 4.2 million to 16.8 million to today’s 17.2 billion.
Spreadsheet Errors Just Happen
Google the term “Excel error”. In February 2015, the query yielded 130 million results. In his 1998 paper, “What We Know About Spreadsheet Errors,” Professor Raymond Panko of the University of Hawaii wrote that of 113 spreadsheets audited in seven studies, 88% contained errors.
Not all spreadsheet errors are inconsequential. Like a child playing with matches who burns down the house, some spreadsheet mistakes have traumatic consequences.
A few examples show how spreadsheet errors can lead to disaster:
- A “faulty reference” in a spreadsheet created by the Utah State Office of
Education caused a $25 million mistake in the state’s budget.
- In November 1994, Fidelity’s Magellan fund estimated it would make a $4.32/share distribution at the end of the year. An accountant omitted a minus sign in a cell of his worksheet and counted $1.3 billion as a gain rather than a loss. The missing symbol caused the estimate to be off by $2.6 billion.
- In October 2003, Fannie Mae, the leading source of residential mortgage credit in the United States “discovered” a $1.136 billion error in shareholder equity.
- The 2010 study by eminent Harvard University economists Carmen Reinhart and Kenneth Rogoff, “Growth in a Time of Debt,” concludes that historical data correlate slow economic growth with high government debt. In 2013, as part of a class paper, University of Massachusetts at Amherst student Thomas Herndon tried to replicate Reinhart and Rogoff’s findings and discovered a “basic spreadsheet error” that weakened their widely cited conclusions.
It is helpful to categorize spreadsheets into three groups:
- Accidental legacy
Intentional spreadsheet applications are purposefully developed by software programmers using standard software controls and testing that are then deployed to end users. De-facto spreadsheets are constructed by end users and deployed within their own work group. Accidental spreadsheets are designed by an end user for personal use, inherited by the person who succeeds the designer and by precedence become part of the established process. Accidental legacy spreadsheets are the most common.
Fragile, Robust, and Antifragile Applications
In his book Antifragile: Things That Gain from Disorder, Nassim Taleb suggests seeing anything that matters in three categories of exposure:
“Antifragile” is his neologism for the opposite of fragile. Where a robust or resilient system stays the same by resisting shocks and change, an antifragile system grows and improves with exposure to volatility. In a fragile system, mistakes are rare, consequential, usually irreversible, and painful. Mistakes in an antifragile system are routine, small, benign, quickly overcome, and avoided in the future – errors are learning opportunities.
Compliance and Control
Spreadsheets are often viewed as tactical tools not subject to the same scrutiny as other database or functional applications. In most organizations, however, spreadsheets are mission critical parts of their analytical, decision-making and reporting processes.
Clearly, business systems that rely on electronic files stored on personal computers and file servers connected to networks are better, faster, and cheaper than the antediluvian systems they replaced. But the requirement for reliable compliance frameworks compels organizations to establish controls on the creation, documentation, use, and maintenance of mission-critical spreadsheets.
Corrective Action Steps
The following steps identify fragile accidental legacy spreadsheets and suggest a path to transition from fragile to robust solutions, and from robust to antifragile. With a focused scope and time-limited charter, the process improvements are cost effective and avoid the trap of becoming an end in themselves rather than a means to achieve process improvement.
- Survey Business Processes
The first step is to inventory the organization’s spreadsheets. The survey can be done manually by asking users and supervisors for lists of spreadsheets, or automatically using software to scan corporate networks.
- Identify Mission-Critical Spreadsheets
Most spreadsheets are fragile, but not mission-critical. Managers, supervisors, and end users should systematically review their work processes to differentiate between informal, scratch-pad spreadsheets and the mission-critical files.
- Assess Fragility and Risk
Once mission-critical spreadsheets are identified, fragility can be assessed from:
- Workbook file size
- Spreadsheet formulae complexity
- Volume of links, the number of spreadsheets in a workbook, and the number of other files linked to
- Volume of data
- Existence and volume of visual basic code.
Fortunately, scanning tool applications can be used to analyze workbooks. The website “Spreadsheet Analytics” lists 17 applications that audit and assess the complexity of spreadsheets.
Evaluate the risk associated with critical spreadsheet error based on the potential impact and assign a subjective grade (e.g., Green, Yellow, and Red).
- Replace Fragile Mission-Critical Spreadsheets
Many of the spreadsheets identified in the preceding steps can be eliminated and replaced by commercial off-the-shelf software applications. PROPRICER™ offers solutions that will save you time, eliminate redundant tasks, prevent formula errors and much more. The top government contractors and federal agencies choose PROPRICER™ for their proposal pricing and cost analysis solution.
Ready to ditch spreadsheets?
Request a 1-on-1 demo today to learn how PROPRICER can help you shave days off your proposal process!