Excel spreadsheets in School budgeting - a cautionary tale (2001)
School governor Peter Wolstenholme provided AccountingWEB members with a cautionary tale of the dangers that can lurk unseen within budget documents constructed with Excel.
In a posting on AccountingWEB's Any Answers section Wolstenholme explained how his school faced a £30,000 shortfall because values in a budget spreadsheet had not been added up correctly.
With help from several of AccountingWEB's Excel gurus, he managed to identify the underlying problem was due to budget figures being entered as right-aligned text values rather than as numbers. The text figures were not included within the =Sum calculation and rippled through to the school's budget submission.
"Excel is consistent in as much as if the total formula had read "=A1+A2+A3", the correct total would have been returned," commented AccountingWEB member Paul Mitchell. " However, when you use =Sum it just ignores the entries it considers to be text." While useful in building spreadsheets where the user wants to add up a range of cells that include numbers and words, it does carry the risk of creating the error that troubled Wolstenholme. The Forumla Wizard in Excel explains more about the Sum formula, he added.
Blame for the error rests with whoever created the spreadsheet, said Mitchell, which would make it difficult for the school to make any claim for restitution from the vendor. Perhaps the authority should look into its IT training practices, he said, adding: "The spreadsheet will only do what it is programmed to do. I think it is too easy to blame the software for a mistake that was made by a person."
Wolstenholme acknowleded that he often gets text values turning up in Excel spreadsheets when he extracts data from his accounting system using the ODBC mechanism. "Excel it does this with account numbers and cheque numbers/transaction reference numbers."
Accepting Mitchell's verdict, the unfortunate school governor added, "The most obvious deficiency I could see was the lack of cross-checks, and we are looking forward to seeing whether this years version of the spreadsheets contain any."
Neil Eglintine, himself a local authority accountant, warned that spreadsheets often take on a life of their own - and one that is not recorded in any formal documentation.
"By the time the sheet has been modified, linked and cross linked several dozen times, the author of any of it (and sometimes the original purpose) is lost in the shifting sands of timek," said Eglintine.
After suggesting a couple of tips to help spot such rogue entries, Eglintine added, "People have often asked why I cross-check calculations in computer generated documents. Perhaps the audit habit dies hard, perhaps [it is due to] coming from the slide rule generation, where a reasonablness check is always required."
This article and the Any Answers query on which it is based were first posted in 2001.