Why working outside ERP software and BI systems with spreadsheet programs like Excel can be so dangerous.
Microsoft Excel was the first successful program for interactive use on a computer that allowed users to edit data in tabular form and was developed in 1985. Before that, there was already a predecessor - Multiplan - which, however, could not realise the commercial success.
Today there are several different so-called spreadsheet programs, of which "Excel" is the leading one with over 750 million users worldwide. Other providers are for example Apple with "Numbers" and Google with "Spreadsheets" and other, also open source manufacturers.
Today, hardly any company, authority or university can do without Excel, or can do without the use of spreadsheet programs.
First of all, this is evidence that users and employees in companies have a need for data analysis, tables and lists.
But over 80% of all Excel worksheets contain errors, say the experts who professionally deal with Excel worksheets and their quality assurance. (See sources at the end of the article).
The economist and professor Raymond Panko of the University of Hawaii has determined in many series of experiments that about 94% of all Excel worksheets should contain errors. Even assuming that this figure is too high, because many worksheets are of low complexity, our own sampling has shown that about 60% of all Excel worksheets in use contain errors.
Often, at first glance, it's just little things like a wrong sign. This is what happened at Fidelity Investments, which forgot a minus sign when calculating returns for a fund called the Magellan Fund, causing the calculation to be overstated by $2.6 billion and represent an incorrect value. Experts from the "European Spreadsheet Risks Interest Group" (Eusprig) believe that half of all calculations used by large companies in their day-to-day business are incorrect. It is also crucial to know whether the errors identified can be categorized. This would be a great help to facilitate a quality assurance for the calculation of the tables. According to Prof. Raymond Panko, an error classification can be made in three categories:
Errors in classification number three (errors of omission) tend to have the greatest impact or can be particularly serious because they are least likely to be detected. An example of this is a study by Harvard economists Carmen Reinhart and Kenneth Rogoff, who forgot to include five important countries in a table and, according to an author of F1F9 Robin Aitken, calculated a loss of around ten billion pounds for Great Britain alone because budgetary savings measures were taken on the basis of the study that were based on the incorrect calculation. The study by Rogoff, who also served in a previous capacity as the former chief economist of the International Monetary Fund, dealt with the calculation of government debt and concluded at the time that countries whose debt accounts for more than 90 percent of gross domestic product (GDP) grow more slowly or not at all. The study came to the conclusion that economic stimulus programs are no longer worthwhile in order to lead a country out of a recession, but actually harm the country if the national debt accounts for more than 90 percent of GDP.
The error was that in the cell L/51 of the table in the formula that is used to calculate the average of 20 countries, 5 rows were simply forgotten. This was also serious because it was economically strong economies that were forgotten:
In this regard, the facts show a clear and unambiguous statement: Yes, it has serious effects. There are 3 points that lead to this statement: 1. What assurance is there that the Excel spreadsheets that are in use in the company are error-free? 2. What quality assurance measures and Excel audits are in place to reduce and eliminate errors? 3. How are the calculations documented so that they can be traced? In our ERP consulting work, we experience again and again that exactly these three questions are only answered to a limited extent and that the sensitivity for possible errors in spreadsheet programs is also not very pronounced.
Especially when defining requirements in the context of an ERP consultancy to create an ERP specification and a later ERP implementation, our clients expect that the many Excel sheets are reduced and integrated into the ERP software.
As a rule, it is also clear from our experience that the older the ERP software or the poorer (lower) the functionality of the ERP software, the more individual Excel sheets are in use. In order to nevertheless obtain data for decision-making, Excel solutions are often used. This is done from sales to purchasing, controlling, quality management, production, financial accounting and human resources.
For this reason, we have now developed quality assurance measures to check the quality of the Excel worksheets first and foremost during ERP consulting and when creating ERP specifications, and to provide assistance to our customers. In this way, we want to avoid possible, unknown errors being transferred to a new ERP software.
These are measures within the scope of version controls, the creation of rules for data entry and data manipulation as well as the storage and copying of files on different computers. Another focus is dealing with the formulas and calculations embedded in the worksheets, tables, columns and rows. It is often difficult to determine dependencies or data on which calculations are then made.
Ideal for this work would be a software tool in the background to check the logic and plausibility of calculations. Unfortunately, however, the old programming language Visual Basic is still used by Excel to calculate formulas. Therefore, there is currently no alternative for documentation and systematic quality assurance. Parallel to this, it is important to pay the utmost attention to the generation of data, the calculation of results and, above all, the quality assurance of master data in ERP consulting. Only then can the results be trusted and only then can IT provide a basis for data processing and thus foundations for management so that decisions can be made that are based on correct figures.
Sources: Own ERP consulting and ERP project experience. F1F9 https://www.eusprig.org/ https://panko.shidler.hawaii.edu/SSR/index.htm
Microsoft, Excel, Harvard are brand names belonging to the respective brand owners