EuSpRIG TEAM work:Tools, Education, Audit, Management

- 1 - EuSpRIG TEAM w ork: Tools, Education, Audit, Management David Chadwick (EuSpRIG Chair), University of Greenwich, Greenwich, London SE1 0 9LS D.R.Chadwick@gre.ac.uk 1. The T.E.A.M Approach Research on spreadsheet errors began over fifteen years ago. During that time, there has been ample evidence demonstrating that s preadsheet errors are common and nontrivial. Quite simply, spreadsheet error rates are comparable to error r ates in other human cognit ive activities and are caused by fundamental limitat ions in human cognition, not mere sloppiness. N or does ordinary "being careful" eliminate errors or r educe them to acceptable levels. Panko R [ 1] In many ways, Ray has summed up the entire problem of spreadsh eet errors - they are due to our fundamental human weaknesses, our limitati ons and inabilities. As Ray so rightl y points out, just 'being careful' is not going to be enough to solve the problem. It is heartening, therefore, to see that EuSpR I G has attempted to address those main areas where our human weaknesses ma y be identified, accommodated, overcome and hopefull y reduced to negligible effect. A review of all the papers from past EuSpR I G conferences shows that they may be seen to fall into four distinct areas: tools, education, auditi ng and management. Interestingly, these four areas form the indicative acronym TEAM. • Tools: methodologies and software for spreadshe et building Rajalingham K. et al [51 [14] , Chadwick et al [10], O'Beirne [12], Paine J. [13], Raffensperger J. [1 5]. Grossman T. [21], Knight D [17] • Education: awareness-raising, teaching, research Rajalingham K et al [2], Ayalew Y et al [4] , Cleary P M [ 8], Banks D. et al [20] , Chadwick D [9][ 19][23], Panko R. [1], Butler R [6], • Audit: methods and software tools, Nixon D [18], Hock C. et al [7] , Ettema H et al [16], Clermont M et al [22], Croll G [25] • Management : standards and controls for man aging developments Butler R [11] [2 4], Hawker A [3], Chadwick D [19] TEAM is indicative because team-work is possibly the ke y to solving the corporate spreadsheet problem involving, as it mi ght, the use of development tools where the y e xist, education in error awareness at all levels and for common models, pr operly organised and applied audit procedures, and lastly, the adoption of sound management poli cies to support standards and controls. Sound development methods, stan dards and user education are the obvious preventive controls . ... The many examples good development practi ce is rarely codified into business procedures, and even when it is, the rules and restri ctions it requires are not follow ed to any significant degree. Butler R [6] - 2 - 2. TOOLS This is possibl y the area where there is much scop e for effect. There is, without doubt a need for a structured approach to model bui lding with the aid of software tools to direct the spreadsheet developer, advice and/or prevent possible error situation s, and document their actions. Work in this area so far presented to EuSpR IG has included approaches that require the use of programming languages to 'code' the logic of the model and then to const ruct the spreadsheet interface of rows and columns along with it's included logic. An ex ample of this is the tool ModelMaster (MM). "MM was developed from a branch of mathemati cs known as category theory ... briefly stated, category theory, like logic, is a tool for s tudying mathematical and computationa l concepts, one concerned much more with their for m than with their content.” Paine J [13] Additionally, many researchers have cited the need for methods based around software engineering principles. "The applicati on of software engineering principles to spreadsheets - s preadsheet engineering - has the potential to increase the productivity of spreadsheet programmers, decrease the frequency and severity of spreadsheet errors, enha nce spreadsheet maintainability over time, and actually be implemented by spreadsheet user s.” G rossman T. [21] Rajalingham, Chadwick and Knight have done so me work in this vein creating a tool that forms Jackson type structures to give a graph t ype descripti on of a spreadsheet during development for easy checking of logic and which also permits reverse engineerin g of a spreadsheet into its underlying Jackson structure. Hence each spreadsheet model can b e defined b y its underl ying canonical model which would remain intact ev en if the form of the spreadsheet were chan ged cosmetically. "Based on softw are engineering principles ... it has been found that spreadsheet models can be represented in a form identical to t he data structure diagram developed by Jackson.... The proposed methodology demonstrates how t hese techniques can in fact be transf erred to the production of spreadsheets.” Rajalingham et al [14] Perhaps one of the more exciti ng and commercially useful tool s has been the Brixx product, essentially a spreadsheet alternative. "The combination of the latest object technology combined with a new approach offers a genuine alternative to spreadsheets that goes some way towards satisfying the identified resistance. While challenges still remain, the future could hold an exciting new pow er and capability for all business modellers to meet the more complex mod elling challenges of the future.” Knight D. [17] Although not in existence, there might be some us e for a tool to prompt users into properl y documenting their spreadsheets. “Worksheets often duplicate some informat ion that is also recorded elsewhere and was copied in by typing, copy & paste, programming code, data base add-ins, and macros. However, modifying the original data source wil l usually not update the same information in the worksheet. Therefore, there is a high risk of creating inconsistencies betwe en worksheets and other information systems. All this is not i mmediately obvious on reading a spreadsheet as they are rarely documented” O'Beirne [12] - 3 - 3. EDUCATION Spreadsheet builders of all t y pes and at all levels of an organisation require the necessary skills either through training, formal education or in-house seminars. Such traini ng should encompass two types of skills: the possib ly complicated but straight forward practical skil ls of manipulating a given software package (package functionalit y) and the more sophisticated and more subtle skills of comprehending the business requirement and formin g a correct conceptual model of how it needs to be structured and what needs to be sho wn (modelling). "Nevertheless, nobody can and wi ll write a spreadsheet without having such a conceptual model in mind, be it of numeric nature or a layout focussed, geometrical nature” Ayalew et al [2] However, there is some suggestion, and field work i s presently under way to clarify this, that many spreadsheet practitioners are self-tau ght or receive only rudimentary training and that which they do receive is based solel y upon package functionality, the first of the skill s outlined above. There is some suggestion, thou gh more work needs to be done in this area to determine the true scale of the problem that such trainin g rarely touches upon the modelling process as outlined in the second of the skill s above. "From a look at the trai ning and teaching curricula of academia and higher education, there appears to be no attempt to present students wi th working standards for spreadsheet developments nor even an acknowledgement that such may be requir ed” Chadwick D. [19] The weaknesses of formal education courses (see Ch adwick D [19]) are often echoed in professional spreadsheet training courses b y private consultants and in-house training seminars. This is a shame as business practitioners who are t he main recipients of this training are being denied opportunities to become aware of the natur e and extent of errors and that models benefit from some kind of quality appraisal. This is particularl y important as there is evidence, and further research continues in t his area - see Cleary P M [8], - that spreadsheets are used as significant tools of business decision-makin g at even the highest levels of an organisation. However, it is gratifying to see that trainin g organisations such as the ECDL Foundation (European Computer Driving Licence) are providi ng good qualit y mater ials and also tacklin g the issues of spreadsheet qualit y . The ECDL is the Eu rope wide qualification that enables people, without prior knowledge of IT or computer skills, to gain an industry recognised qualification that demonstrates their competence in basic comp uter skills. The qualification is based around seven modules of which creating and using spreadsheets is one. 4. AUDITING Auditing strategies, methods and accompan y ing software have an im portant role to play. There are several commercial tools available: SpACE (S preadsheet Audit for Customs & Excise), OAK (Operis Analysis Kit), Spreadsheet Detective and Spreadsheet Professional to name a few. An excellent evaluation of several of these products is available in Nixon D [18] and gives a useful starting point for an yone considerin g the use of such a product. In addition, there is interesting work under way on other tools with di ffering approaches such as the experimental tool developed at the Universit y of Klagenfurt and used with the open-source spreadsheet system Gnumeric - see Clermont M et al [221. In addition to the stand-only software audit tools there are the complete spreadsheet audit approaches as used b y the typical City of London management consultancies. A good example of such an approach is described in Croll G [25]. - 4 - 5. MANAGEMENT "The risk of error ari sing from poor practice in the use of spreadsheets are known to be high, and the incidence of good practice in development usin g spreadsheets are known to be low, Despit e this, users are blissfull y unaware of these risks and are using potentially faulty decision support machinery every day to take vital business decisio ns” Butler R [6] There are many, some say too man y, issues regarding spreadsheet controls that need to be addressed through organisational poli cy. There are the issues of choosing and adopting a set of controls to be audited and reviewed at regular intervals. These mi ght include version control of spreadsheets, peer and self-audit approaches to spreadsheet build, team reviews and 'walkthroughs' of spreadsheet models, documentat ion policies, the use of trustworth y models as templates, and the recording of common errors in a database repositor y of corporate knowledge. To accomplish even some of these requirements a commitment from higher level management is needed along with a strateg y for establishin g good practice based upon reasoned selection of standards. Such a strategy ma y be found in the CobiT (Control Objectives for Information and related Technolog y ) approach. "One of the problems reported by researchers and auditors in the field of spreadsheet risks is that of getting and keeping management's at tention to the problem. Since 1996, the Information Systems Audit & Control Foundation and t he IT Governance Institute have published CobiT * which brings mainstream IT control i ssues into the corporate governance arena. T his paper illustrates how spr eadsheet risk and control issues can be mapped onto the CobiT framework and thus brought to managers’ attention in a familiar format” Butler R [11] The CobiT approach is based around the Maturit y Model for software development capabilit y defined by the Software Engineering Institute. Generall y, management uses the model (see fig. 1) to assess the current status of: • their organisation • the best practice or the general state of practice in their respective indu stry • applicable International standards And define where the organisation wants to be a gainst these levels. - 5 - 6. CONCLUSION From the above analysis of past EuSpR IG papers can be seen the comprehensive coverage of ways and means to bring awareness of the issues a nd to control and manage the problem. Full management of the risks associated with spreadsh eets has yet to be achieved and eliminatio n of all errors may remain a dream forever, but as long as a forum exists where practiti oners of all kinds can meet, the sooner the topic will become addressed openl y within industry. 7. REFERENCES [1] Spreadsheet Errors: What We Know. What We Think We Can Do. Panko R Proceedings of 1 st International Symposium on Spreadsheet Risks, EUS PRIG2000, Greenwich, UK, July 2000. http://arxiv.org/abs/0802.3457 [2] Classification of Spr eadsheet Errors Rajalingham K, Chadwick D, Knight B: Proce edings of 1st International Symposium on Spreadsheet Risks, EUSPR I G2000, Greenwich, UK, Jul y 2000. http://arxiv .org/abs/0805.4224 [3] Building Financial Accuracy into Spreadsheet s Hawker A: Proceedings of 1 st International Symposium on Spreadsheet Risks, EUS PRIG2000, Greenwich, UK, July 2000. http://arxiv.org/abs/0805.4219 [4] Detecting Errors in Spreadsheets Ayalew Y, Clermont M, Mitt ermeir R: Proceedings of 1 st International Symposium on Spreadsheet Risks, EUSPR I G2000, Greenwich, UK, Jul y 2000. http://arxiv .org/abs/0805.1740 [5] A Structured Methodology for Spreadsheet Modelli ng. Rajalingham K, Chadwick D, Knight B; Proce edings of 1 st International S ymposium on Spreadsheet Risks, EUSPR I G2000, Greenwich, UK, Jul y 2000. http://arxiv .org/abs/0805.4218 [6] Risk Assessment for Spreadsheet Developments Butler R (HM Customs and Ex cise) Proceedings of 1st International S y mposium on Spreadsheet Risks, EUSPRIG2000, Greenwich, UK, Jul y 2000. http://arxiv.org/abs/0805.4236 [7] Visual Checking Of Spreadsheets Chan H Q Chen Y: Proceedings of 1 st International S y mposium on Sp readsheet Risks, EUSPR I G2000, Greenwich, Ju ly 2000. http://arxiv .org/abs/0805.2189 [8] How Important Are Spreadsheets To Organisations? Cleary P M: Proceedings of 1 st I nternational S ymposium on Spreadsheet Risks, EUSPRIG2000, Greenwich, UK, July 2000. [9] Stop the Subversive Spreadsheet Chadwick D. Proceedings of 1 st International Symposium on Spreadsheet Risks, EUS PRIG2000, Greenwich, Jul y 2000. http:/ /arxiv.org/abs/0712.2594 [10] Teaching Spreadsheet Development Using Peer Audit and S elf-Audit Methods for Reducing Errors Chadwick D., Sue R.: Proceedings of 2 nd International S y mposium on Sp readsheet Risks, EUSPR I G2001, Amsterdam, Holland, J uly 2001. http://arxiv.org/abs/0801.1514 [11] Applying the Cobit ® Control Framework to Spreadsheet Developments Butler R J: Proceedings of 2 nd I nternational S ymposium on Spreadsheet Risks, EUSPRIG2001, Amsterdam, Holland, July 2001. http://arxiv .org/abs/0801.0609 - 6 - [12] Eur o Conversion in Spreadsheets O'Beirne P: Proceedings of 2 nd I nternational S ymposium on S preadsheet Risks, EUSPR IG2001, Amsterdam, Holland, J uly 2001 [13] Safer Spreadsheets With Mod el Master Paine J: P roceedings of 2 nd I nternational S ymposium on S preadsheet Risks, EUSPR IG2001, Amsterdam, Holland, J uly 2001. http://arx iv.org/abs/0801.3690 [14] An E valuation of the Quality of a S tructured Spreadsheet Developm ent Methodology Rajalingham K, Chadwic k D, Knight B: Proce edings of 2 nd International Symposi um on Spreadsheet Risks, EUSP RIG2001, Amsterdam, Holland, J ul y 2001. http://arxiv.org/abs/0801. 1516 [15] New Guideli nes for Writing spreadsheets Raffensperger J: Proceed ings of 2 nd International S y mposium on S preadsheet R isks, EUSPR IG 2001, Amsterd am, Holland, Ju ly 2001. [16] Ass urance By Control Around Is A Visib le Alternative To The Tr aditional Approach Ettema H, Janssen P , de Swart J: P roceedings of 2 nd I nternational S ymposium on S preadsheet Risks, EUSP RIG2001, Amsterdam, Holland, J uly 2001. http://arxiv.org/abs/ 0801.4775 [17] A Real alternative to spreadsheets Knight D,: Proceedin gs of 2 nd I nternational S ymposium on S preadsheet Risks, EUSPR IG2001, Amsterdam, Holland, J uly 2001 [18] Spr eadsheet Auditing Nixon D, O'Hara M: Proceedin gs of 2 nd International S ymposium on Spreadsheet R isks, EUSPR IG 2001, Amsterd am, Holland, Ju ly 2001 [19] Traini ng Gamble Leads To Corpora te Grumble Chadwick D: Proceedings of 3 rd International Symposi um on Spreadsheet Risks –t he Hidden Corporate Gamble, EUSP RIG 2002, Cardiff, Wales, UK, J uly 2002 [20] Interpr etation as a Factor in Understand ing Flawed Spreadsheets Banks D., Monda y A.: Proceedings of 3 rd International S ymposium on Spreadsheet Risks -the Hidden Corporate Gamble, EUS PRIG 2002, Cardiff, Wales, UK, J ul y 2002. http://arxiv.org/abs/0801. 1856 [21] Spr eadsheet Engineering: A Research Frame work Grossman T.: Proceedings of 3 rd International Symposi um on Spreadsheet Risks - th e Hidden Corporate Gamble, EUSP RIG 2002, Cardiff, Wales, UK, J uly 2002. http://arxiv.org/abs/0711. 0538 [22] A Sp readsheet Auditing Tool Evaluat ed In An Industrial Cont ext Clermont M., Hanin C ., Mittermeir R.: P roceedings of 3d International S ymposium on Spreadsheet Risks - the Hidden Corporate Gamble, EUSP RIG 2002, Cardiff, Wales, UK, J ul y 2002. http://arxiv.org/abs/ 0805.1741 [23] The Subversive Spre adsheet - 7 - Chadwick D., Knight B.: Proceedings of 3d Intern ational S y mposium o n Spreadsheet Risks - the Hidden Corporate Gamble, EUS PRIG 2002, Cardiff, W ales, UK, Jul y 2002. [24] Losing At Spreadsheet Roulette Butler R.: Proceedings of 3rd International S ymposium on S preadsheet Risks - the Hidden Corporate Gamble, EUSP RIG 2002, Cardiff, Wales, UK, J uly 2002 [25] A Typical Spr eadsheet Audit Approach Croll G.: Proceedings of 3d International S ymposium on Spreadsheet Risks - the Hidden Corporate Gamble, EUSP RIG 2002, Cardiff, Wales, UK, J uly 2002. http://arxiv.org/abs/0712. 2591