The disregard for control over end user computing, and spreadsheet models in particular, has meant that more and more organisations that rely on these tools for their decision making are acting on inaccurate or misleading information. In fact, a study by KPMG Management Consulting in London showed that 95% of spreadsheets reviewed contained significant errors. Yet the solution is simple. Develop standards, train your staff, and test the models developed i.e. give end-user development the respect it deserves!
End User Computing (EUC) encompasses a wide range of issues surrounding data storage, system development and reporting of information under the direct control of end users i.e. outside the control of the traditional `IT department`. EUC includes PCs, 4GLs and general or special purpose report writers.
It has become apparent that a growing number of businesses are relying on end-user computing to make business decisions. The glut of information and the existence of a multitude of options, types of financing and complex financing arrangements has meant that businesses have needed to analyse more scenarios to reach a decision. To make matters worse this needs to be done in less time than ever before. The advent of personal computers and spreadsheets in particular has catered for this need and has meant that various complex scenarios can be analysed within minutes. Today the successful business will act decisively and SAFELY in response to possible opportunities and threats.
It may therefore be fair to say that some spreadsheets are more important to the business than the systems that effectively only record the results of the decisions previously made. Yet millions are spent on historical recording systems, and end user development spending is limited to purchasing licenses for the desired tool, be it Excel, Lotus, Access, Quattro Pro or any other package.
It would appear that the risks surrounding the use of spreadsheets have been ignored. Perhaps it`s the professional look of the packages on offer that results in business taking as gospel information from these spreadsheets. However, these spreadsheets are often designed by a person with no programming experience or understanding of good programming practices. Because, for all the simplicity of spreadsheets, they are just a front end behind which complex programming occurs and which needs to be understood to ensure that the objectives of the model are met. Of bigger concern however are the small, human errors that creep into large, complex spreadsheets due largely to lack of planning and standards. And that is for the spreadsheets that management are aware of. What about end users own `time- saving` spreadsheets? The KPMG study revealed that
. 95% of models reviewed contained major errors (errors that could affect decisions based on the results of the model),
. 92% that dealt with tax issues had significant tax errors,
. 75% had significant accounting errors, and
. 59% were judged to have poor model design.
Causes of spreadsheet errors are numerous. Often the developer does not have a clear picture of what is required or there is clumsy design due to lack of forethought and planning. Other times the requirements are changed during the development process resulting in `add ons` and `work arounds` to achieve the desired result. Users often `customise` the model received from a developer without understanding the implications. Complex formulas are accidentally overwritten due to lack of cell protection. Critical rates such as VAT and tax rates are hard coded into cells and not changed when these rates change.
Based on the importance of spreadsheets, why should their developments be treated any differently from any other system development? In fact, given that users who have no formal system development training often perform the development, the need appears to be even greater. Standards and policies should be drawn up, particularly for those critical spreadsheets/ models. A methodology should be introduced for all end user developments. Training should be given to end users on the proper methodologies to use. Once developed the model should be comprehensively tested under various scenarios as with any system development. An inventory should be kept of all strategically important models, and backups and documentation should be maintained. Quality assurance procedures should be put in place to monitor these critical end user developments and where necessary internal and external audit should be involved.
The development of the personal computer and spreadsheets in particular has provided end users with more power than ever before. By managing and controlling the risks, users will obtain the maximum benefit from an indispensable decision making tool.
KPMG make use of automated tools and have developed comprehensive methodologies to both review and design spreadsheets for financial and non financial models, as well as providing assistance to clients in establishing the appropriate policies, standards and control procedures over end user development.
For more information contact.
Share