The Wall and The Ball: A Study of Domain Referent Spreadsheet Errors

1 The Wall and The Ball: A Study of Domain Referent Spreadsheet Errors Richard J. Irons Faculty of Business and L aw Central Queensland University Rockhampton Qld. Australia. ABSTRACT The Cell Error Rate in simple spreadsheets averages about 2% to 5%. Thi s CER has been measured in domain free environments. This paper compares the CERs occurring in domain free and applied domain tasks. The appl ied domain task requires the application of simple linear al gebra to a costing problem. The results show that domain referent knowledge influences participants' approaches to spreadsheet creation and spreadsheet usage. The conclusion is that spreadsheet error making is inf luenced by domain knowledge and domain perception. Qualitative findings a lso suggest that spreadsheet error making is a part of overall human behaviour, and ought t o be analyzed against this wi der canvas. 1 INTRODUCTION Spreadsheet errors ar e pervasive, stubborn, ubiquitous and complex . Research over the last twenty y ears has shown them to be present in nearly all spreadsheets, with an incidence rate of about 2 to 5 % of all non-text cells. This research has proc eeded on several fronts. One approac h has been the identify ing, classify ing, and measuring of errors. Another has been the e stablishm ent of educational reg imes which might reduce error making, whilst another has aimed to develop auditing tools which will help uncover errors. The ove rall aim has been the eradication of errors. Eradica tion is essential if businesses are to make reliable decisions based on sprea dsheet outcomes. Despite the most strenuous corrective exhortations from writers, spreadsheet errors still remain. For example, Schlosser in an excellent 1989 article gives checklists of possible errors, including a perva sive classic: "a variable which is not defined as a n 'if statement when it should be (this is an surprisingly frequent mistake with taxes)" The pervasiveness of this error is demonstrated in a re putable tex t published nine ye ars later: 2 Year 0 1 2 Income statement Sales $1,000 $1,070 $1,145 Costs of goods sold (910) (973) Interest payments (33) (34) Depreciation (123) (142) Profit bef ore t ax 5 (5) Taxes (2) 2 Profit af ter tax 3 (3) Divi dends ( 2) 2 Retained earnings 1 (1) Table 1: Part of Spreadsheet table 'PRO FORMA 4' , Benninga, S.,(1997), p 13. This spreadsheet demonstrates the calculation of a firm' s target debt/equity ratio. It is the same spreadsheet introduced at the start of the cha pter which is used to demonstrate financial statement forecasting. The re is no 'if statement to check whether tax is being paid on negative acc ounting income. Hence, in y ear two, positive tax is paid on n egative income. Similarly, positive dividends are being paid on the same negative income. A 'simple' logic trap would have avoided these errors. This error remains eve n in the second edition of the text. As this error is 'obvious' there is a suspicion that the code writer has not audited the model. This is not only the writer' s problem, but it i s also a problem for those contributors who have advised corrections for the sec ond edition of the tex t to the website at http://finnace.wharton.upenn.edu/-benninga/ This website invites correction to the text from readers, and lists discovered errata. I t is a good idea, and uses the power of worldwide communication to enhance a single piec e of work. The contributions to the website cover both book text errors, and Excel errors. The Excel errors noted cover the 'usual' r ange of spreadsheet err ors; mis-keying , wrong cell referenc es, and errors in formulae. None of the erra ta messages mention the omission of the noted 'if statement. These exchanges beg the question: are these errors sighted beca use finance referent knowledge is different fr om accounting referent knowledg e, or has the error just been 'overlooked' ? Thus the question of domain knowledge arises. One would expect that an author who sets out to write a text on financial modeling, is expert in accounting, economics, finance and the requisite tools of algebr a and statistics. Hence, there is a presumed suite of domain knowledges. However, this presumption may be unfounded. Accounting is not the same as finance; finance is not the same as ec onomics. More importantl y , code writing is an art in itself, and it must be combined with domain knowledge to be useful in a computer modeling setting. The underly ing formulae in this spreadsheet demonstrates this idea: 3 A B C D E Year 0 1 2 16 Income s tatement 17 Sales =B3 =B17*(1+$B$ 4) =C17*(1+$B$4) 18 Costs of goods sold =-C17*$B$8 =-D17*$B$8 19 Interest payments =-$B$10*C37 =-$B$10*D37 20 Deprecia tion =(C32-B32) =(D32-C32) 21 Profit bef ore tax =SUM (C17:C20) =SUM(D17:D20) 22 Taxes =-C21*$B$11 =-D21*$B$11 23 Profit afte r tax =C22+C21 =D22+D21 24 Dividends =-C23*$B$13 =-D23*$B$13 25 Retained earning s =C24+C23 =D24+D23 Table 2: Formula view of spreadshee t shown in Table 1. Taxes are computed in row 22 as: -C21 *$B$1 1 etc, where B 11 holds the tax rate. To compute taxes as an expected negative, the value of ' profit before tax' from row 21 is artificially made negative. However , if profit before tax in row 21 is already negative, then taxes will be calculated as a positive amount, and will be paid on negative income. The fail safe method would have been to cre ate an 'if, then' log ic trap in row 22 to prevent the pay ment of taxes on negative income. (I f carry forwar d of tax l osses is permitted, then more extensive logic is required). A similar trap could have been employ ed in row 24 to prevent the pay ment of dividends from negative income. Why have artificial negatives been cre ated in rows 22 and 24? Without second guessing the code writer, it might be arg ued that this method is simple, 'quick and dirty ' and is a simplistically sufficient for the de monstration at hand. That is, tax es and dividends will alway s be negative outflows, so the code is set up to gene rate those flows without extensive formulae. On the other hand, the code writer may have not had sufficient domain knowledge to realize that taxes and dividends are not paid on negative income. Calculation of taxes and dividends relies on accounting domain knowledge. The notion of cash flow within a computerized budget comes from finance . Given reasonable world- wide curricula, one would expect that accounting and finance knowledge would be in the same domain, but this idea cannot be taken for gra nted. Additio nally , the code writer may have simply ignored the fact that income can be ne gative at times, or alternatively , lacking domain knowledge, might have assumed that 'income' can neve r be negative. Whatever the case, poor code writing has led to an obvious error. The point is that there may be gap between the domain knowledg e actually held by a code writer, and the level of domain knowledge that the model requester expects the code w riter to have. This gap may be a factor in spreadsheet er ror making. 2 THE RESEARC H VEHICLE One task set up to investi gate error making in a domain free environment is Panko and Sprauge's (1998) (PS) 'Wall' task. The only dom ain knowledges ostens ibly required i n the task are simple arit hmetic and some concept of cost s and profits . It could be expect tha t these ar e widely held life knowledge dom ains co ming from a high school educa tion. In fact the aut hors reported t hat only 5% of par ticipant s in thei r experiment regarded the t ask as 'difficult'. 4 The curr ent experi ment set out to investi gate the extent of the gap between actua l and expecte d domain knowledg es. 3 THE RESEARC H METHO D The rese arch set out to investigate t hree main themes: 1. The fre quency of errors i n end user cre ated spreads heets 2. Whether t he diffic ulty of the spre ad sheeting task has any influe nce on the fr equency of errors 3. The att itudes par ticipant s held toward spreadsheet e rrors. The rese arch was carr ied out by asking volunteers to prepar ed two separat e spreadshee ts to solve two computational ta sks. These were th e PS Wall task, and the Ball task. (T he Ball task is similar in style t o the Wall task, but with an in creased algebrai c requirement). Par ticipant s were asked to complete each of t hese tasks in the ir own time at their own pace, and were asked not t o seek outside help. The completed spre adsheets were reviewed by the researcher and graded for errors. Fol lowing this grading, the part icipants wer e invited to a one on one interview which took about 10 minutes. This interview used a s tructured qu estionnair e, which was part ially self administering, and part ially inter active. The t ask completion-interview approach combined the methodologies of Nardi and Mi ller (199 1) and Hendry and Green (1994). The quest ions were fra med to discover the experi ence and skill backgrounds of the partici pant, and then to se ek the partici pant's attitude t o the discovered er rors. Atti tude could be an important variable i n error re search, as Brown and Gould ( 1987) found tha t subj ects' confidence i n completed spreadshe ets was ofte n unfounded. The quest ionnaire was de signed so that subj ect s were moved prog res sively from the general notion of spr ead sheeti ng through to the parti cular investigati on of actual self made errors. T he questionnai re is at tached as appe ndix A. When the "Cel l Error Rate " is used as the error frequency measure, the average reporte d CER is about 5%. Panko and Halverson (1996) reported a range of 1.7% to 9.3% i n a survey of studies on Cell Error Rates. In m ost of these st udies, the CER was measured over non-text cells. However, in both the curr ent Wall and The Ball tasks, cells cont aining text, su ch as titl e cells and heading cells wer e included i n the error count analysis. T ext cell s were incl uded because an error in t hese cell s could have a beari ng on any final decision made using the spr eadsheet output. In the partic ular case he re, the aut hor inadvertentl y reversed the column names on the solution f or the Ball task. Had this err or not been ident ified, a decision maker could have been left wit h the wrong answer for the Hydrogen and Helium alternatives. This is not an er ror of computation, but would have become an error o f decision making. Theref ore, it may be necessary to review the st andard approac h in the li teratur e which generally excludes te xt information fr om CER counts. This paper int roduces the term OCER to m easur e the Overall Cell Er ror Rate. T he OCER uses all cell s in a spre adsheet. 4 THE TASKS The Wall task is a dir ect copy of the task used by Panko and Spra uge (1998). The Wa ll task asks parti cipants to calc ulate the cost of combining labour and materia l costs i n the constr uction of a garden wall. There are two materials , lava rock or brick, which allow some testi ng of differe nt costing calculati ons. T he only change made t o t his task for the c urrent study was the change of measurement from fee t to meters for a local audience. Panko and Sprauge ( 1998) argue that the W all task focuses more direct ly on the pr ocess of sprea d sheeting as the only domain knowledg e re quired is t he volumetric calculat ion for th e rectangular s olid. 5 The Ball task was des igned to be computationa lly more difficult , as it required appli cation of algebra to m anipula te spherical volume and area, and to manipulate gas volum e and pressure relati onships under Boyle's Law. Not onl y did the pr oblem visually appear more diffi cult i t required careful applicat ion of domain knowledge. Some guidance as to how to apply the domain knowledg e of Boyle's Law was given in the task descripti on. The full texts of the Wall and The Ball tasks appear as appe ndices B and C respe ctively. 5 THE PARTICEPANTS Particip ant volunteers wer e sought from amongst undergraduat e fu ll ti me st udents and from academic staff col leagues. Unfortunate ly, the up take ra te from both groups was quite low. Only one student from a cl ass of about 60 followed the exper iment thr ough. In a secon d cl ass of about 10 student s, only 3 students volunteered for the experiment, but only one of these completed all as pects in full. Six academic colleagues completed the experiment out of a potential group of about 20. One profe ssional collea gue c ompleted all tasks. This gave eleven partici pants in al l. Some of the parti cipants had t o be vigorously pursued to complete the t asks. The low par ticipa tion rates, and poor adherence to timelines, suggests that volunteering is not popular, e speciall y when there is no i ncentive reward. Advice f rom col leagues dif fered on this issue. Some argued tha t a small inc entive, such a book voucher, would be useful, whilst ot hers argued that this scheme would bi as the responses . Panko and Spr auge (1998) offered course credit for par ticipat ion, but that opt ion was n ot open in the cur rent ci rcumstance. It would appear that some form of reward is necessar y to secure adequat e partici pation ra tes. 6 THE PROCESS Voluntee r subj ects were given the t asks, and were asked t o complete them in th eir own time, t o a standa rd which they themselves consi dered " reasonable" . The y were not to seek outside hel p, and wer e advised to spend say not more t han 20 minutes on each task. There were no defini te time lines set for th e end of the experiment but the expectati on was t hat t he tasks would be completed, and the subsequent interviews conducted within two weeks of subjec ts recei ving the material. As it turned out, ther e was a la rge variati on in completion time of each i ndividual partici pant. The short est completion time was abo ut one week, whilst the longest was about 20 weeks. It was difficult to draw any conclusions from t hese varying time lengths, but it would appear natural that a l ong time between task completion and quest ionnaire c ompletion would be best avoided. 7 ERROR MEASURE MENT PROTOCOLS Panko and Sprauge (1998) highlight the dif ficult y of arriving at a partic ular defi nition of t he CEP, and on the added dif ficulti es in cla ssifying the errors . In their paper they chose a t hree part clas sifica tion scheme: mechanical err ors, logic error s and omission error s, following Allwood (1984). Mechanical errors i nclude mis-k eying, pointing to the wrong cell , reading in a wrong number and select ing the wrong range. Logic errors inc lude fault y reasoning, using the wrong algorithm or implem enti ng the algorithm with the wrong logic. Omission errors si mply m ean the leaving out of something from a model. Other writer s have further s ubdivided or amalgamated these schemes. For example, Panko and Halverson (1997), (PH), subd ivided logic errors in to Eureka error s; those whic h are easy to prove to be incorr ect, and Cassandr a errors; those which ar e diffic ult to pro ve to be incorrect . 6 A more com prehens ive taxonomy has been proposed by Rajalingham, Chadwick and Knight (2001), (RCK). T his taxo nomy is developed b y progressive binary division from an initia l binary classi ficati on. The ini tial cl assifi cation is " system g enera ted error s" and "user generated errors" . The fina l branches of the binar y tree contain numerous cla ssifica tions. The detai l in the P H and RCK schemes will be useful for long term research i nto resea rch prevention. For the present st udy they are too detail ed. The pres ent study focused on r eplicat ing the original PH Wal l study, and in ext ending this into a n applied domain knowledge applicat ion. In this present stu dy, only the coarsest cl assific ation of er rors was used. Only three error types were identi fied. The se were: rea dabilit y, logic algebra. Readabilit y is a subje ctive error i dentifi er. Whether a spreadsheet is high or low in re adabilit y depends upon the exp ectations of the rea der. If the reader wants only a bottom line total , then readabili ty is of lit tle conce rn. However if the reader wants more detaile d information, such as subtotals for material c osts, labour costs, and prof it markup in the wall proj ect, t hen the omission of such ite ms creates readabi lity error s. Secondly, the layout of the results may make it diff icult f or a reader to discern the requir ed information. Thi rdly, readabil ity enhancements such as the use of fonts, hi ghlighting and colour may be m issi ng, inappropriate ly applied, or applied to t he wrong sections of the resul ts. The quest ion of readab ility is al so domain dependent. With in a business managerial environment there may be som e implicit or taci t understandi ng that managers require a pri nted report showing computational detail , progressive calculat ions, or segmented outputs. T hese attribu tes may enhance confidence in decision-mak ing, add to a manger's g rasp o f the probl em, or allow deci sions on sub-parts of pr oblems. Gross bottom line figures may be correct i n certai n instances , but they may not rest easy with managers. Hence, readabilit y is a key issue within a decision environment. Additi onally, the funct ion of sprea dsheet is t hat output is present ed as a report, not as a summary calc ulation. If only a summ ary calcul ation is r equired, t hen a hand held calcul ator will often provide i t. Readabilit y ought to be an issue in spr eadsheet er ror resea rch, regardless of its subj ecti ve nature. Its subj ectivity is vitall y important though in establis hing an error rat e. Overall "readabil ity" cannot be attribut ed to a sin gle cell, or single ce lls. So as to include rea dabilit y into a CER measure, some ad hoc counting system m ust be us ed. In the present case, a notional number of cell err ors were all ocated, dependi ng upon the implied information tha t managem ent may requi re. Whils t this i s an unsati sfactory measure, it al lows for a quant ifiabl e result. Logic error in the present study was a c atchall term for er rors i n reasoni ng, errors i n data relati onships, a nd for al l other keying, pointing and relations hip procedures . This is a coarse measure, but as the study here investigated overal l e rror counts the n t his catchal l classif ication was satisfactor y. The PS st udy divided t his overall classifi cation into om issi on, log ic and mechanical errors . A separate category of errors was establ ished for the pres ent study. This classifi cation was called " algebra". T he hypothesis l eading to the creation of thi s class was t hat in mov ing from the Wal l to the Ball t asks, par ticipant s were likely to make more errors in manipulating the algebraic formulae for the spherica l measures and the gas measures. The 'algebra' cat egory of errors was designed to catch these errors. In the PS study, this s pecial c lass would ha ve been included in the overall "l ogic" class. 8 ERROR MEASURE MIENT PARAMETERS The current s tudy uses t wo def initions of t he CER. One is a wide definit ion which include s all cells in ea ch spreadsheet . This global inc lusion allows f or measures of re adabilit y, by assuming 7 that all cells will have som e impact upon a decision mak er. Theref ore, c ells containing headings, t itles, text and other i nformation outs ide of t he narrow "computational" de finiti on have been incl uded in this measure. The global measure to capture err ors in all cells is termed the Overall Cell Error Rate, OCER. For dire ct comparison wi th the PS r esults, the st andard CER is also measured. This is construct ed by adding the errors in the "logic' and "al gebra" classif ications . 9 THE RESULTS Summ ary statis tics for the experi ment are given below. Spreadsheet Error A nalysis: Summary Statistics The W ALL The BALL Total Participants 11 11 Total Submitted S'Sheets 11 7 S'Sheets W ith Correct Bottom Line 9 2 S'Sheets W ith W r ong Bottom Line 2 5 % of S'Sheets with Errors 18.18% 71.42% % Useful f or Decision Making 72.72% 28.57% Average CER 1.67% 11.86% Average OCER 6.9% 12.86% Table 3: Summ ary statis tics for the Wall and the Ball expe rimental tasks. Results fr om these measures are: The Wall CER rate of 1.67 % is not signific antly dif ferent from zero at the 5% level. Th e value is at the lowest level of the Panko a nd Spra uge range. Ni ne spr eadsheets out of 11 showed no errors. The two spreadsheets with errors had multiple errors. These two resul ts wer e act ually extreme outlier s in an other wise perfect completion rate. The Ball CER rat e of 11. 86 % is significant ly different f rom zero at the 5% level. The Wa ll CER at 1.67% and the Ball CER at 11 .86% are not significantl y dif ferent from each other at t he 5% level. Both the Wall OCER at 6.9% and th e Ball OCER at 12.86% are significantl y differ ent f rom zero at the 5% l evel. The Wall OCER at 6.9% and the Ball OCER at 1 2.86% are not s ignificantl y different from each other a t the 5% l evel. The perce nt of sprea dsheets with e rrors is similar to the Panko and Sprau ge findings. 10 COMMENTS ON RESULTS. The Wa ll tas k was set out first t o give p artici pants some pract ice in the type of calculat ion required. The Ball w as set out second, as it required arguably more difficult calculations. Particip ants were not required to do the t asks in that order, but the int erviews revealed that no one did the t asks in the reverse or der. The diffe rence in t he number of submitted Wa ll and Ball spreadsheet s indic ates t hat se veral partici pants found t he Ball task simply 'too hard'. Where both spreadsheet s were sub mitted there was a diff erence in the number of errors between bot h tasks. However, t his di fference is not sta tistic ally significant . W hat is inte resting, but not stat istical ly t ested, is t he finding tha t the percent age of u seful Ball s preadsheets is much less than t he percent age of useful Wal l spreadshe ets. 8 Interestingly, some pa rticipa nts got the Bal l correct, but made s imple er rors on the Wall. In other cases, some partici pants had tr ouble following the writte n i nstructi ons e speciall y with respect t o the final balloon volume and the rela ted m easur es of waste. Som e partici pants attempted to, but could not manipulate Boyle's law. Some par ticipa nts s imply made algebraic errors i n manipulating the area a nd volume form ulae. 11 COMMENTS ON DOMAIN KNOWLEDGE The result s show t hat partic ipants had trouble in both making a n atte mpt a t, and in completing, the Bal l ta sk. Those who did not at tempt it were unanimous is saying that i t was 'too hard'. Most of thos e who a ttempted it , it the confident under standing that they c ould sol ve it, made errors i n both logic and alg ebra. E rr ors in l ogic were in inter preting the initi al data, struct uring the calculat ion, and i n working t o a result. Err ors in algebra were caus ed by poor manipulati on of the spheri cal measures, and in t he gas relati onships. Domain referent knowledge is more apparent if we examine the correl ation betwee n the two domain areas. The cor relati on is based on bo ttom line result s. Domain Know ledge Matrix Wall Ball Participants Right / Right 1 Right / W rong 8 W r ong / Right 1 W r ong / W rong 1 Table 4. Number of part icipant s who achieved a partic ular mix of soluti ons. Includes those parti cipants who did not finis h tasks. Interestingly, only one participan t completed both tasks corr ectly, whilst 8 pa rticipa nts got the Wall corr ect and the Ball wrong. An anomalous result is t hat one part icipant got the 'easy' Wall task wrong, and the 'hard' Ball task correct. T his part icular pa rticipa nt treat ed the two tas ks as interest ing intell ectual c hallenges, and hur ried through the simple Wall t ask in order to tackle the more interest ing Ball task. The eight ' ri ght/wrong' mix results highlight the im plicat ion that domain k nowledge is an important variable i n error in vestigation. There are several ot her concl usions t o draw fr om both the actual spreadsheet resul ts, and from the inter view responses: • Those who attempted t he Ball thought t hat it s requi red le vel of domain knowledge was a reasonabl e expectati on : ' ... it require d only high school algebra.' • Others at tempted it, but could not solve key parts, the calcul ation of the radi us for example. One participant manipulat ed the algebra fully, leaving r^3 as unsolved, as he could not calcul ate '... t he cubi c squa re r oot.'; ( he understood the general notion of a cube root, but was not sure how to so lve it or how to use an Excel functi on to solve it.) • Other pa rticip ants thought that it was an interesting int ellect ual chall enge, and rushed to get a soluti on, only to be disappoi nted when their result was wrong. 12 COMMENTS ON SPREADSHEET APPLICATIONS GENE RALLY 9 Research into spre adsheet error is motivated b y the desire to prevent dangerous managerial decision making . Spreadsheet s are a powerful computational and communication tool, and there is some inherent danger in t hat a poor ly prepar ed or audi ted spreadshee t c ould b e nai vely accepted by decision m akers as providing reliabl e information. T o test whethe r the attract iveness and apparent simplicity of spread sheeting could lead t o such problems, partici pants were asked; 'Do you thi nk s preadsheet error s might arise because spread s heeting encourages inexper ienced users?' Some respondents agreed with this st atement, but most respondents st ated tha t error s were bound t o occ ur, bu t t hat the attrac tiveness of spread sheetin g as not necessar ily the ca use. Whilst t he ease and a pparent fac ility of spr ead sheeting was an issue, the bigger issue s identif ied were lack of trai ning, la ck of error detection concepts, and lack of checking. On the other hand, thos e resp ondents who lacked con fidence to at tempt the Ball, sta ted that t hey would seek help with such di fficult tasks, rather than embarking upon them in ignorance. Overall, the outcomes of the inter views suggest that under -confidence is a natur al preventative in tackling diffic ult tasks, and that overconfidence is a serious precursor to spreadsheet error mak ing. Other issues which arose and s hed some light on how errors are made were; • Only one respondent submitted a pencil and pa per attempt, even though this was requested i n the inst ructions • Even though the Ball and Wall tasks lent t hemselves to visual represent ation, no respondents sketched the r elative pict ures to ai d in envisioning the si zes and volumes of the struc tures to hel p in making reasonable calculat ions. Hence, despite the clear and early exhortations of Schlosser (1998), partici pants are sti ll rushing to the computer to get a n early solution, without first ly making a pe ncil and paper estimate. 13 CONCLUSION Domain kn owledge is an im portant variable in spreadsheet error making. There is a gap between the domain knowledge expected of a modeler, and t hat actually held by a modeler. Errors also arise because of ordinary human traits, such as: procrastination, l ack of interest, lack of care or lack of attention to the t ask. Addi tionally , overconfidence and under-confidence can lead to errors or task incompleti on. Apparently , spreadsheet errors need to be portray ed ag ainst the wider canvas of basic human behavioural norms. I t is going to be difficult to continue to address error making as only an i mpersonal, technocratic process. Managing t he human conditio n seems to be the long term solution to spreadsheet error making . Potential questions which remain for further researc h are: • Can error making be properly tested in voluntary laboratory settings, where the outcomes may be m ore a function of time and task commitment than simply spread sheeting skill? 10 • I s the Ball task a true t est of spreadsheet error making, or would the same errors have been made when participants used pencil and paper. In other words, is spread sheeting or a lgebra being tested here? • Given that some participants were in different to the di scovered error s, or were tardy in completing tasks, would these attitudes be carried over t o real world work places to such a degree that mechanical error checking processes would be ignored? An interesting resear ch future remains. 14 REFERENCES Allwood, C. M. "Error Detection Processes in Statistical Problem Solving", Cognitive Science (8:4) October- December 1984, pp 413-437. Benninga, Simon. "Financ ial Modeling" The MIT Press, Cambridge, Mass. 1997. Brown, P.S. and Gould, J.D. "An Experimental Stud y of People Creating Spreadsheets," ACM Transactions on Off ice Information Systems, 5, 3 (1987), 258-272 Hendry , D. G. and Green, T. R. G. "Cre ating, Comprehending, and Explaining Spreadsheets: A Cognitive I nterpretation of What Discretionary Users Think of the Spreadsheet Model," International Journal of Human Computer Studies (40:6) Jun e 1994, pp. 1033-1065. Nardi, B.A. and Miller, E. '7winkling lig hts and Nested loops: Distribu ted Problem Solving and Spreadsheet Development," International journal of Man-Machine (34:2) Studies February 199 1, pp. 161-184 Panko, R. R. and Sprague, R.H.J. 'Hitting the Wall: Errors in Developing and Code-I nspecting a "Simple" Spreadsheet Model. Decision Support Systems, 22, 4 (1998), 337-353. Panko, R.R. and Halverson, R.P. J r., "Spreadsheets on Trial: A Survey of Research on Spreadsheet Risks," Proceedings of the Twenty-Ninth Hawaii Internation al Conference on System Sciences, Maui, Hawaii, January 4-7, 1996pp.326-335.httv://www.cba.hawaii.edu/vanko/h6wA.htm. Accessed2A0pm19/03/03 Panko, R.R. and Halverson, R.P. J r., "Are Two Hea ds Better Than One? (At reducing errors in spreadsheet modeling?). Office Systems Research Journal, 15, 1 (1997), 21-32. Rajalingham K., Chadwick D.R., and Knight, B., "Classification of Spreadshee t Errors" at http://www.gre.ac.uk/-cd02/EUSPRI G/2001/Raiali ngham 2000.htm Accessed 1.02pm. 31/03/03 Schlosser, M.," Do We Make The Most Of Spreadsheet Modeling?' Management Accounting, September, 1989, pp 44-46. 11 Append ix A. Errors in Spreadsh eets Question naire: 'The Ball' and, ‘The Wall’ Tasks Responses are to be entered by the interviewer. More than one response is applicable in some cases P lease place a tick in the box w here appropriate. 1 H ow wou ld you rate your level of experience with sp readsheets? High Moderate Low None 2 How long h ave you been involved in the use of sp readsheets? 0-1 y ears 2-5 yea rs 6-10 y ears more than 10 yea rs (Where possible, specify a number of y ears …………………………………………………..) 3 Describe your use of spreadsheets. (Tick those w hich are applicabl e.) Creation Creation and Use Auditing Spreadsheets of Others Data entry to spreadsheets Using spreadsheet output 4 What would be the average size, in number of cells, of spreadsheets you are most familiar with? (The number of cells is the product of the number of rows and columns, used for computation.) 100 400 2,500 more than 10,000 5 When dealing w ith the size of spreadsh eet you are familiar with, how confident are you of The accuracy of the spreadsheet results? Absolutely definite Reasonably confident Uncertain Very unsure 6 What activi ty do you think w ould cause the gr eatest number o f error s wi thin any spreadsheet? (Sel ect one response only.) Mistyping of values Incorrect cell re ferencing Incorrect formulae Incorrect proble m definition Ig norance o f spreadsheet operatio n 7 How confi dent are you in the re sults given by t he spreadsheets you have c reated f or the Wall and the B all tas ks? Not at all confident Mildy unsure Reasonably confident Absolutely certa in 12 8 How many computati onal errors do you t hink you have created i n the Wall and the Ball spre adsheets. (Ignore headings and text errors.) The Wall Task 0 5 10 more than 10 The Ball Task 0 5 10 more than 10 9 The number of er rors actuall y discovered i n each of your spr eadsheets is: The Wall ……………….. The Ball ……………….. 10 F or each of the f ollow ing errors, c an you suggest w hy the error w as made . Error Type Possible Reason (Select F rom the l ists below .) • S of tw are Usage errors :- Keying; layout; dat a omission; underst anding of task; logic structur e; use of al gebra; formulae stru cture; spr eadsheet func tion; spr eadsheet knowledge. • Behavioural errors:- Fat igue; time pressure; task importance; int erest; co nsequences; task size; task knowledge; background influences; conf idence. 11 Suggest ways in whi ch you might overc ome these er rors in f uture. Error Type Possibl e Solutions (Sel ect f rom the li sts below .) 13 • Softw are sol utions:- M ore time; more comm itment; more spreadshe et trai ning more familiarity with proble m; discussion with col leagues; working in groups; applying error check routines; second person aud it; worked template; known soluti on. • Behavioural s olutions:- Ensuring mental freshness ; more confidence i n task ability; more em path ic support; more experience; s maller task; more training in using sprea dsheets. 12 "Errors can be made i n spreadsheets". Ho w mi ght this st atement af f ect your f uture use of spr eadsheets? • Possibl e eff ects: Cause more time to be spent in c hecking spreadsheets; sp end more time in train ing users and creat ors; int roduce more errors f inding software t echniques; duplicate spreadsheet creati ng; expensively audit all s preadsheets; use spreads heets as a guide onl y - not a definit ive solutions; avoid spreadsheet s altogether - use some other form of computation. 13 Give your views on the fol lowing: " Do you think sp readsheet errors m ight arise because spread sheeting encourages inexperienced users?" ………………………………………………………………………………………… …………………………………………………………………….. ………………………………………………………………………………………… …………………………………………………………………….. ………………………………………………………………………………………… …………………………………………………………………….. ………………………………………………………………………………………… …………………………………………………………………….. ………………………………………………………………………………………… …………………………………………………………………….. ………………………………………………………………………………………… …………………………………………………………………….. ………………………………………………………………………………………… …………………………………………………………………….. ………………………………………………………………………………………… …………………………………………………………………….. ………………………………………………………………………………………… …………………………………………………………………….. ………………………………………………………………………………………… …………………………………………………………………….. 14 1. Appendix B Task 1: The ‘ Wall’ Task You are to develop a spreadshee t model to create a bid price for the total cost of building a garden w all. There will be two options for materials, lava rock, or brick. The wall will be 6 meters long, 2 meters high, and 0.60 meters thick. Crews of two workers will build both walls. Crews will work three eight-hour day s to build either ty pe of wall. Wages will $10.00 per hour per worker, plus an on cost of 20% to cover worker benef its. Lava rock costs $105.00 per cubic meter, a nd brick costs $70.00 per cubic meter. Your bid must add a profit margin of 30%. Adapted from: Panko, R.R. and Sprauge, R.H.J. “Hitting the Wall: Errors in Developing and Code- I nspecting a 'Simple' Spreadsheet Model” De cision Support Syst ems, (22) 1999, pp.337-353. 15 A ppendix C Task 2: The 'Ball' Task You are to develop a spreadshee t model to esti mate the total cost of materials for building a passenger -carry ing balloon. The balloon will be perfectly spherical in shape, and wil l be infl ated to a pressure of 1.4 atmospheres. The re quired volume of gas at this pressure to provide sufficient lift is 5,500 cubic meters. The materials required will be fabric for the balloon wall, and gas for filling. You are to estimate the total prices for two alternatives: an hy droge n balloon, and an helium balloon. Both hy drogen and helium gas are supplied and costed a t a pressure of 150 atmospheres. Hy drogen g as costs $4,250.00 per cubic meter, and helium gas costs $7,580.00 per cubic meter, at this pressure. The fabric for the balloon wall costs $25.00 per square me ter. There will be waste and losses in manufacture and f illin g. These allowance s are 12.00% for wall fabric, and 3.00% for g as, over and above the calculated a mounts . The formulae for spherical a rea and volume are: A = 4 * π * r^2 ; V = 4 * π * r^3 3 Where pi, π, equals 3.1416. The formula describing the re lative volumes and pressures of gasses is Boy le's L aw, which states that: P 1 V 1 = P 2 V 2 . That is: Pressure multiplied by Volume at state 1, is equal to Pressure multiplied by Volume at state 2, pr ovided temperature is constant.