Monday, 01 February 2016 20:49

Ending the pain of spreadsheets with Integrated ERP Software

Written by 

Used for their intended purpose, spreadsheets are an invaluable tool that enable business and allow individuals, departments and companies to share information with each other.  However, the more common picture is one of disaster, where it is not uncommon for the business in its entirety to be put at risk.

Spreadsheets: a brief history
The term "spread sheet" was originally used in the finance departments of large companies to describe large sheets of paper which were organised into rows and columns.  The columns typically represented time intervals - weeks, months, quarters or years.  Compiling data (manually) onto these spreadsheets allowed for analysis and identification of trends.  In the 1960's the first computer-based representations of these paper sheets were developed.  The term electronic spreadsheet evolved and in the 1970's Lotus 1-2-3 brought the technology to offices in large organisations.  In the 1980's, Microsoft released Excel and the spreadsheet as we know it entered the mainstream.

"Spreadsheet risk"
Since the mid-1980's various studies have identified that there are inherent dangers and business risks to the widespread adoption of spreadsheets to fill the gaps software systems don't cover.  Some of these include:

  • Training: Many spreadsheet creators are for the most part self taught.  This can mean a lack of understanding, both of the software architecture and the operation of the software itself
  • Lack of testing: Most software products undergo a QA process and extensive testing.  This is not common with user developed spreadsheets.  Therefore calculation errors often don't come to light until it is too late
  • Reliability: Various studies have shown that 90%+ of spreadsheets with more than 150 rows have at least one error.
  • Duplication: Most of the data on any given spreadsheet usually exists on another spreadsheet in the same organisation
  • Revision Control: By their nature, spreadsheets will often evolve over time.  When changes are made, there is usually no revision control and no auditing of what was changed, when and by whom.
  • Security:  It really is incredible how much sensitive information is stored on laptops, tablets, phones un-encrypted and free for anyone to read

But they are useful?
It is of course true that spreadsheets used correctly are an invaluable tool. The ease at which charts can be generated and information "sliced and diced" by even the least experienced of users means they will be here to stay.  However the key point is that spreadsheets should not be used to store data.  The data should be stored within a database, then the spreadsheet is more of a reporting and visualisation tool.

Examples of famous spreadsheet blunders
Over the years there have been many high profile stories of cases where spreadsheet errors have caused major issues.  The downfall of Allfirst Bank, a subsidiary of AIB, can at least in part be attributed to an Excel spreadsheet.  Instead of spending a mere $10,000 on an automated data feed, the bank allowed a template to be used to input and validate rates.  As the process was entirely manual, it was simple to manipulate and John Rusnak did just that, which enabled him to commit one of the worst financial frauds in history.

An embarrassing blunder for London Olympics 2012 saw an extra 10,000 tickets sold over capacity for the synchronised swimming event.  The reason? A spreadsheet was used to control ticket sales.  An operator inadvertently entered the incorrect value in the cell for the number of tickets available....

How can ERP solve these problems?
An ERP system by its nature is a fully integrated suite of modules designed to store all of the company's data in one database.  Spreadsheets are the enemy of any ERP implementation team.  A properly implemented system will ensure that

  • Data for all modules (departments) is stored in one single database - any more than one and lack of integration becomes an issue
  • The software is quality approved and tested by the product author
  • There is full audit and revision control of records
  • Security is delivered via access control and permissions to modules and data can be granted (write or read-only) or revoked as required
  • Duplication is eliminated
  • Standardised structures ensure better quality of data
  • Spreadsheets are used, but as an output, not an input.