In Pursuit of Spreadsheet Excellence

In Pursuit of Sprea dsheet Exc ellence Grenville J. Crol l Chair Europea n Spreadsheet Ri sks Intere st Group www.euspri g.org Grenville@spreadsheetrisks.co m Abstract The first fully-documented stu dy into the quantitative impact of errors in operational spreadsheets identified an intere sting anomaly. One of the five particip ating organis ations invo lved in the s tudy contribu ted a set of five spreadsheets of su ch quality that they s et the organisation apart in a statistical sense. Thi s virtuoso performance gave rise to a simple sampling test – T he Clean Sheet Test - wh ich can be us ed to objectively evaluate if an organisation is i n control of the spreads heets i t i s using in important processes suc h as financial reporting . 1. Introduction Following a p rotracted search, Pow ell, Lawson and Baker [ Powell et al, 2007] managed to find five or ganisations which were willing to contribute operational sp readsheets for detailed examinatio n. The objective of their stud y was to determine the financial i mpact of spreadsheet errors in o rganisations. The five organisations com prised two consulting compani es, a very large financial services firm, a manu facturing compan y and a college. Each or ganisation provi ded five differing spr eadsheets for examination by th e res earchers. Amon gst othe r pre-specified criteria, the voluntee red spreadshe ets were quite large, w ell understood b y the volunteers and developed b y the v olunteers within the last twelve months. Each spreadsheet was then independently examined by two researchers and the issues determined by the researchers’ examination were pooled. The agreed issues observed jo intly by the researchers were fed back to th e volunteer developers and categorised as an error, poo r practice o r not an er ror. Actual errors we re then corrected and the chan ge in the relevant output cell was recorded as the quantitative measure of the impa ct of the error. 2. Original T abulated Results We reproduce, i n a sl ightly condensed fo rm, Powell et al’s Table 1 whi ch summ arises their results. As is evident, following t he detail ed ex amination of 25 spreadsheets contributed b y five o rganisations, the y found 381 Issu es which tr anslated i nto 177 agreed errors. 79 of the errors had a non zero impact. The absolut e im pact of the maxi mum error found in each wo rkbook v aried from $0.22m to $110m dollars. The total o f the max imum absolute error impacts across the original 25 worksheets i s in excess of $259m. Table 1 (of Pow ell et al 2007, condensed) Error s with Organisation / Non-zero Max % Max Abs Workbo ok #Issues #Erro rs I mpact Impact I mpact 1.1 7 3 0 1.2 50 6 5 28% $32 m 1.3 18 7 3 137% $110 m 1.4 4 1 0 1.5 0 0 0 2.1 19 6 5 3.6% $14 m 2.2 27 11 7 16% $74 m 2.3 6 0 0 2.4 30 4 3 416 % $11 m 2.5 40 2 2 3.1 19 2 2 5.3 % $0.2 4m 3.2 1 1 0 3.3 11 2 2 16 % $4. 9m 3.4 6 1 0 3.5 23 1 0 4.1 27 22 12 117 % $13 m 4.2 8 4 2 142 % $0.2 7m 4.3 0 0 0 4.4 1 0 0 4.5 79 44 27 39 % $0.22 m 5.1 2 0 0 5.2 2 0 0 5.3 0 0 0 5.4 0 0 0 5.5 1 0 0 Tota l 381 117 70 Note that in o rganisation five, the number of iss ues found was ver y low com pared t o the other four or ganisations. Note also that the number of result ing errors in each o f their five workbooks was nil. Given the ubiq uity o f s preadsheet error as reported in ever y other study [ Panko, 2000, 2007] , the existence of one organisation producing five error free workbooks out of five vo lunteered for examinatio n appears at the outset to be anomalous. Powell et al recognise thi s and state that: “Organisati on 5 is a small consulting company with highly educated employees and a culture that demands excellence. The spr eadsheets w e audited fro m this firm w ere works of art: thoughtf ully designed, well documented, and error f ree” Our subm ission is that the results from this firm are anomalou s in that such ex cellence is presentl y not the nor m. We h ypothesise that the spreadsheets p roduced b y this organisation for this stud y are the result of a significant soft ware engineering process. The developm ent o f spr eadsheets is an error pro ne process [P anko, 200 0] [Panko & Ordway, 200 5]. The only kn own m ethod of s y stematic ally det ecting and correcting spreadsheet errors is t hrough repeated cell by cell inspe ction of e very cell in a spreadsheet by multiple test ers [ Panko, 2006]. It is not possib le to produce b y chance a large error fr ee spreads heet. Field and laborato ry testing over the l ast t wenty years reflects this. The producti on of error fr ee spreadsheets im plies the use of repeated cell b y cell testing b y mul tiple test ers or some ot her not eworth y but hithe rto unkno wn s oftware engineering or other pro cess [Panko, 20 07]. 3. Anomaly Detection We can t est statisticall y the likelihood that an organisation mi ght p roduce five error o r defect free spreadsheets by chance alone. If we look at column 3 o f Table 1, we s ee a colu mn entitl ed “Er rors”. Fro m this colu mn we can c reate a further column entitl ed “Errors? ”. W e can represent an error free spreadsheet with a 0, and a spreadsheet with at least on e error with a 1. If we look at column 4 of Table 1, we see a c olumn entitled “Errors with Non Zero Impact”. From this colum n we can c reate a further column entitl ed “De fects?”. We can represent a defect free s preadsheet wit h a 0, and a s preadsheet with at least one defect with a 1. We can also create a fina l colu mn which repres ents our h y pothesis. The final column is 0 for each o f the spre adsheets from organisations 1 to 4 representing our h ypothesis that we do n ot beli eve th em to be anom alous. These or ganisations exhibit the characteristics found in all known previ ous field testin g. Each o f the 5 spreadsh eets from organisation 5 is represented b y a 1 in the final column representing our h ypothesis that they are all anomalous in a singular way. We reproduce this addit ional information in Table 2 below. We can now use the simple statist ical technique of L inear Regression to determine whether there i s an y stati stical correlation betw een the h ypothesised anom alous status o f five spreadsheets and th eir Error and Defect status. W e later confirm this result usin g Logistic Regression. Table 2 – Errors & Def ects Error s with Organisation / Non-zero Workbo ok #Erro rs I mpact Error s? Defects? Anomalous? 1.1 3 0 1 0 0 1.2 6 5 1 1 0 1.3 7 3 1 1 0 1.4 1 0 1 0 0 1.5 0 0 0 0 0 2.1 6 5 1 1 0 2.2 11 7 1 1 0 2.3 0 0 0 0 0 2.4 4 3 1 1 0 2.5 2 2 1 1 0 3.1 2 2 1 1 0 3.2 1 0 1 0 0 3.3 2 2 1 1 0 3.4 1 0 1 0 0 3.5 1 0 1 0 0 4.1 22 12 1 1 0 4.2 4 2 1 1 0 4.3 0 0 0 0 0 4.4 0 0 0 0 0 4.5 44 27 1 1 0 5.1 0 0 0 0 1 5.2 0 0 0 0 1 5.3 0 0 0 0 1 5.4 0 0 0 0 1 5.5 0 0 0 0 1 Tota ls 117 70 16 11 5 4. Regression Results A simple li near regressio n with the E rrors? v ariable as the dependent Y-variable and the Anomalous st atus as t he independ ent X-Vari able shows a re asonable stati stical correlation (Adj. R 2 = 0.44). The si gnificance s tatistics for the Anomalou s variable show beyond reasonable doub t (t = -4.28, p = 0.000 27) that the Errors? status of the five spreadsheets volunteered by or ganisation five are statisticall y v ery different from the other twent y supplied b y the ot her four organisations . The ch ance th at organis ation five might have produced such Error Free spreadsheets by chanc e alone is of th e order of 0.03%. It i s almo st c ertain t herefore that organisation five genera ted t heir fiv e spreadsheets by a pro cess that was desi gned to el iminate error. Note that results from a logistic regression of this binary data are similar [Pez ullo, 2008]. The linear regression w as repeated usin g the Defects? column as the dependent Y- variable. The results show a weaker correlation (Adj. R 2 = 0.20), h owever the Anomalous variable is still significant (t = -2.4, p = 0.027). T he chance that or ganisation five shoul d produce five Defect Free spreadsheets b y chance i s approximatel y 2.7%. If organisation five had produced one o r two defect free spre adsheets, the ir performance would have been indist inguishable from the other organisations. Note that in the 20 sprea dsheets that w ere not anom alous, 16 (75%) of th em had errors. This is l ower than the usual error rate detected in oth er studies, wher e a rat e in excess of 90% is normall y observe d. This m ay be because error rates are now improv ing. Or, more likely, due t o th e us e o f a repeatable error disco very proto col whi ch m ay hav e be en absent across other studies, a l ack of dom ain knowledge b y the res earchers or d ue t o the spreadsheet selection criteria used. Note t hat i n the 20 spreadsheets that were not anomalous 9 (45%) of them were defect free. 5. The Clean S heet Test Using the data in Table 2 as a guide, if we assume that 25% o f all corporate spreadsheets are presentl y e rror free, the probabilit y th at an o rganisation might b y chance h ave five error free spreadsheets in a random sample of five is 0.25^5 or 0.097%. Likewise, t he probabilit y that an organ isation might b y chance have five defect f ree s preadsheets in a sample of five is 0.45^5 or 1.8%. Note that appro ximatel y 50% of errors are not d efects. These probabiliti es, together wi th the size of the defects identified b y Powell et al highlight the risks to financial reporting in tegrity where spr eadsheets a re used in the financial reporting proc ess. In additio n, the structure of Powell et al’s spreadsheet experiment gives us a method for determining if an organis ation is in control of it s Business Critical spreadsheets. Firstly, we must randoml y select fiv e sp readsheets from an organi sation. For example we could s elect five spreadsheets from the man y do zens or hundreds used in the financial reporting process. We should then examine them using Powell et al’s process [Powell et al 2006] or another proce ss such as that described in an earlier paper [C roll, 2003]. Secondly, if we find five defect free spreadsh eets, then based u pon on th e error and defect rates detect ed in this study, the organisation woul d appear to have a pro cess in place to control errors an d defects. If an o rganisation i s unabl e to pass the C lean Shee t Test we should be war y about placing any relian ce upon an y informatio n based upon th ose spreadsheets o r the popu lation from which th ey have been randoml y selected, as Powell et al and other repo rts [ EuSpRIG, 2008] suggest that the Impact of such defects i s material, if not critical [ Croll, 2005] . 6. The Benef its of Clean Spreadsheets There are obvi ous benefits in ensuring financ ial integrity throu gh the use of well designed, test ed and documented (i.e. Clean or Defec t Free) spre adsheets in Financial Reporting and other imp ortant corporat e processes. The Clean Sheet T est provides a simple method for determining if this is the case for an y particular organisation. Organisations whi ch pass the Cl ean Sheet T est co uld be s aid to be ex hibiting Spreadshe et Excellence. Finally, there is a pos sibilit y that a link can be established between Spreadsheet Excellence as determined above and public ly disclosed measures of corporate performance. MacMill an [ 2000] established a positive stati stical link between the use of quantitative decision making methods such as D CF, Mont e Carlo Simul ation and Real Options and enhanced financial performance within approxim ately 30 participant s in the UK Offshore Oil and Ga s Industry. Given t hat th ere is a stron g relationsh ip between the use of these te chniques and the use o f spr eadsheets, the establishment of a similar link between Sp readsheet Excellenc e and improved financial performance m ay not b e too difficult to prove and wou ld be a useful and important di scovery. 7. Conclusion A consulting o rganisatio n delivered a set of fiv e spreadsheets for testi ng which up on examination turned out to have z ero defects. We show that this unusual outcome was statisticall y measurable and set the organisation apart from the o thers in the s tudy. We used this observation to create a r elativel y simple test – The C lean Sheet Test - which can be used to identi fy Spreadsheet Excellence i n o rganisations. W e state th e obvious benefits of Spreadsheet Excellence in financial r eporting and propose an ex periment to determine if there is a statisti cal link between Spreadsheet Excellence and improved financial performance. References Croll, G.J. (2003) “A Typical Model Audit App roach”, IFIP, I ntegrity and Internal Control in Information Systems, Vol 124, pp. 213-219, http://arxiv.org /abs/0712.2591 Croll, G. J. (20 05) “The Im portance and Criticality of Spreadsheets i n the City of London” , European Spreadsheet Risks I nterest Group, http://arxiv.org /abs/0709.4063 EuSpR I G (2008) “Sprea dsheet Mistakes: News S tories”, http://www.eusprig.org/s tories.htm , Accessed 5 th June 2008 14:00 MacMillan, F. (2000) “Risk , Uncertainty and Investment Decision-Making in the Upstream Oil and Gas Industry”, Ph.D. Thesis, 2000, University of A berdeen, Scotland. Pezullo, J.C., (2008) “Logistic Regression ”, http://statpag es.org/logistic.html Accessed 5 Jun 08 10:50 Panko, R., (2 000) “Spreadsheet Errors: What We K now. What We Think We Can Do”, Euro pean Spreadsheet Risks Interest Group, 1st Annu al Symposium, Univ ersity of Greenwich, pp7-18 http://arxiv.org/abs/0802.3457 Panko, R., Ordway, N., (2005) “Sarbanes-Oxley: What About All the Spreadsheets?”, European Spreadsheet Risks Interest Group, pp15- 47, http://arxiv.org/abs/0804.0797 Panko, R., (2006) “Recommended Practices f or Spreadsheet T esting”, Proc. European Spreadsheet Risks Interest Group, pp73- 84, http://arxiv.org/abs/0712.0109 Panko, R., (2007) “Thinking is Bad: Implications of Human Err or Research for Spreadsheet Research and Practice”, Proc. European Spreadsheet Risks I nterest Group, pp69-80, http://arxiv.org/abs/0801.3114 Powell, S., Ba ker, K., a nd Lawson, B. (2006). “An Auditing Protocol for Spreadsheet Models.” Spreadsheet Eng ineering Research Project working paper. http://mba.tuck.dartmouth.edu/spreadsheet /product_pubs.html Accessed 5th June 2008 11:02. Powell, S., Lawson, B., Baker, K. (2007) “Impact of Errors i n Operational Spreadsheets”, European Spreadsheet Risks I nterest Group, 8 th Annual Conference, University of Gre enwich, pp57-68. http://arxiv.org/abs/0801.0715