We need to talk about KPI spreadsheets

What are spreadsheets?

If you've been living in a self-imposed technical isolation for the past thirty years (occasionally, a little jealous), then Spreadsheets are software applications that are used for storing and analyzing data in tabular format. They are grid based, divided into columns and rows and intersected by cells for holding data.

They offer a great way to visually represent the relationship between different data-sets graphically, with the ability to manipulate and make calculations on the data far easier than a database program.

The spreadsheet explosion

Modern spreadsheet applications owe their existence to Daniel Bricklin and Bob Frankston who developed the first software based graphical version called VisiCalc. However, the roots of spreadsheets go much farther back. Paper based book keeping ledgers have been in existence for well over 5,000 years. This simple method of accounting would track receipts and expenses, and as accounting got more standardized (and complicated) spreadsheets were still utilized for recording the entries.

For thousands of years this remained more or less the same until the development of computers and databases. In 1961 the concept of an electronic spreadsheet was outlined in the paper "Budgeting Models and System Simulation" by Richard Mattessich and during the 60s and early 70s there were many notable developments in this concept based on applications, algorithms and programming languages but it was not until VisiCalc was released on the Apple 2 in 1979 that the first interface enabled spreadsheet was born.

Many others followed such as lotus 1-2-3, (I still remember being blown away by this), but the real Goliath of the spreadsheet world; Microsoft Excel was released in 1985.

Although there are now many other spreadsheet versions available, Excel leads the market share with a colossal 750 million users and 81% of businesses globally using the software - making it not only one of the most influential software applications but arguably the most successful.

Spreadsheets for tracking KPIs

So, spreadsheets help you track data, make calculations, analyze the data, create formulas and even visualize these in graphs and charts. They've lasted the test of time. Surely they're the ideal candidate to track Key Performance Indicators? A great way to visualize and communicate performance? Well err not so fast. It's not that there are abundant defects in spreadsheet applications, far from it – they've been refined and built upon almost as long as software has existed – truly visionary and I won't be ditching my lists or budgets anytime soon. It's just that they have several issues and inefficiencies that make them quite unfit for KPI tracking purposes.

1. Flexibility – a curse Their very versatility leaves them vulnerable to both complexity and isolation. Being able to create just about any formula imaginable and even linking data from other sheets can introduce complexity including leaving only the person who created the spreadsheet able to understand how it works. Not good when the spreadsheet covers business critical areas.
2. Sharing communicating / Multi user To get the best out of performance management the progress of KPIs towards goals and objectives needs to be quickly, concisely communicated in real time. Spreadsheets are essentially individual files, sharing can cause issues with read only versions while their ability to condense data and create read only dashboards is difficult to achieve with any consistency.
3. Input Errors / Validation Spreadsheets generally accept any type of entry in the field. Validation can be created but it's cumbersome to setup and resource intensive to manage. Without any automated checking the integrity of both charts and underlying data can be easily compromised.
4. NavigationUsers tend to rely on a single spreadsheet with underlying data being fed to a master sheet via links. This makes it incredibly difficult to analyze and investigate performance dips in the KPIs.
5. Data integrity and securityAlthough spreadsheets do have a level of security geared towards access - its woefully inadequate for protecting users updating data and formulas at will. It is possible to lock cells however you must rely on macros which adds to the complexity.
6. Speed Managing and visualizing KPIs can generate enormous amounts of data. Indeed, in the vast majority of spreadsheets this would normally be contained within the actual file. This leads to speed issues when they inevitably increase in size.
7. Analyzing Trends Being able to simply take the historic data of a KPI and compare against a previous period can be a massive undertaking in spreadsheets. They are simply not geared up to automate some of the process needed to create comparative data quickly.

Examples of Spreadsheet failures

With so many companies using such a versatile tool is almost inevitable there will be risks associated with their proliferation. It is estimated that almost 88% of corporate spreadsheets have some kind of errors , mostly these are relatively insignificant however sometimes those problems are big, almost catastrophic. There is even a dedicated spreadsheet interest group that meets annually to discuss this associated risk. The EuSpRIG even keep a gallery of horror stories dedicated to real world spreadsheet failures. For example:

Fidelity Megan Fund

A simple missing minus symbol single handily led to overstating of $2.6 billion in capital gains at Fidelity Megan Fund. The resulting expected $4.32 per share was reduced to zero when it was discovered that a $1.3 billion capital loss was treated as a gain.

MandS

A spreadsheet mistake forced the company to issue a correction after a summing error in a formula was discovered after they had stated that sales had grown by 1.3%, when in fact they had fallen by 0.4%

AstraZeneca

And spreadsheets aren't just limited to data calculation errors. From a report by Reuters; One of Britain's largest pharmaceuticals accidentally sent out confidential company information when it was embedded in a spreadsheet template. In addition, a Boeing employee inadvertently sent his spouse the personal details of over 36,000 fellow employees that were contained in hidden cells.

When you know it's time to ditch the spreadsheet?

Seldom do relationships with software last forever, once perfectly 'fit for purpose' applications begin to creek under the strain of business requirements, and expansion in terms of staff, services and product lines gain momentum, they force more efficient and cost-effective software to be sought.

Unless something dramatic occurs, rarely is it a top-level decision to change providers or choose more targeted software to manage and visualize KPIs. It's normally instigated at the user level as it becomes more arduous to update and communicate performance. The switch can also manifest itself at the departmental level as reporting performance becomes more of a necessity.

So, although spreadsheets are an undeniably valuable tool for businesses (even when tracking KPIs in the early stages), change is inevitable, safer and more efficient.