089) Which way around do you calculate a percentage change?

http://www.startribune.com/587/story/367953.html

Making a political plus of math error, Star Tribune, 12 Apr 06

DFLer Rebecca Otto is accusing Auditor Pat Anderson, a Republican, of sloppy work. In a column reporting the percentage changes in unreserved fund balances from 2003 to 2004, instead of dividing the difference by the 2003 figure, the auditor's office divided it by the 2004 figure. Deputy Auditor Tony Sutton said "The researcher who worked on that report just made a mistake in the formula in the spreadsheet. He feels bad about it." Risk: Giving political opponents an opportunity to comment adversely on the state auditor

Avoidance: Check not just the data but the formulas too.

Advice: Register Now! for the EuSpRIG 2012 Conference

088) Scoring an own goal

http://www.channelregister.co.uk/2006/03/10/ogc_spreadsheet_snafu/

The Register (UK) OGC spreadsheet madness 10 Mar 06

The Office of Government Commerce is blaming a spreadsheet error for a foul-up over accrediting suppliers for its new Catalist procurement programme. In a letter to suppliers, they confessed "Unfortunately, [we are] not, as we had hoped, in a position to accept your tender at this time. This is because an error in the original evaluation spreadsheet has been identified, necessitating rescoring of all tenders for this project... this error has now been corrected and this has caused a small number of changes to the original award decision." Risk: inaccurate choice of vendors for large government contracts, legal challenges, bad press

Avoidance: Independent review of scoring formulas against specification.

Advice: Register Now! for the EuSpRIG 2012 Conference

087) Data entry error $188,387

http://www.columbiatribune.com/2006/Feb/20060222News009.asp

HUD alleges overpayment for Section 8

Columbia Tribune, February 22, 2006. A government audit says the Columbia Housing Authority has to pay $216,352 to cover expenses incurred when the authority gave some Section 8 tenants too much room and some landlords too much rent. They admitted to $118,387, the amount that resulted from a spreadsheet data-entry error that overpaid landlords. Risk: audit exposure

Avoidance: Data controls, cross checks with source documents.

Advice: Register Now! for the EuSpRIG 2012 Conference

086) Confidential financial data gets out in the Sun

http://news.yahoo.com/s/cpress/20060210/ca_pr_on_bu/sun_life_financial_3

TORONTO (CP) - Sun Life Financial Inc.'s (TSX:SLF - news) fourth-quarter profit increased more than nine per cent to $478 million, a day early than scheduled after "human error" left the company vulnerable to a possible leak. The problem centred around the company's quarterly statistical supplement, which is regularly provided to the analyst community in advance of earnings - but with the latest quarterly results blanked out. In doing so, the spreadsheet is converted into a document file. However, the censored information could be recovered by using Acrobat software, explained spokesman Tom Reid. Risk: public embarrassment, loss of investor confidence (see also #80 below)

Avoidance: User training on how to remove data properly.

Advice: Register Now! for the EuSpRIG 2012 Conference

085) Sticking by spreadsheets despite warnings

http://www.redorbit.com/news/technology/370065/ie_bus_agency_moving_forward/index.html?source=r_technology

Jan 26, 2006

An audit report of San Bernardino Valley's bus agency Omnitrans found "long-term weaknesses" in some areas, including a lack of internal controls in purchasing "that increases the risk of potential fraud and misuse." The financial software that cost the agency more than $200,000 remains unused, and maintenance contracts for the software that can't be canceled remain in effect. The agency, which has an operating budget of $63 million, currently does most of its bookkeeping on Microsoft Excel. A US senator commented on "poor employee morale and widespread dissatisfaction with general management" Risk: loss of financial control

Avoidance: Stricter review process of IT investment, user training, HR management.

Advice: Register Now! for the EuSpRIG 2012 Conference

084) Naturally degradable spreadsheet out by $5M

http://www.nevadadailymail.com/story/1135458.html

Budget discrepancies attributed to computer error Jan 6, 2006

The Nevada city budget spreadsheet apparently worked correctly until sometime in late December 2005 when, city finance director Ron Chandler says, it developed a problem, causing the 2006 budget to show a $5 million deficit in the water and sewer fund. Chandler said that it took him most of the day Wednesday to fix the problem. While he was working on it he found some other errors in the spreadsheet that needed to be corrected. "Once it's a PDF it can't change," Chandler said. Risk: delay in city budgeting process, embarrassment

Avoidance: change tracking, comparison, validation, publishing a PDF of printed version on the web

Advice: Register Now! for the EuSpRIG 2012 Conference

083) Lots of embarrassment for this sort

http://www.venicegondolier.com/NewsArchive3/010106/tp3vn4.htm

Price mixup mars opening of lot sales Venice, Florida, Jan 1,2006 "A snafu in the posting to the web site of minimum bid prices for the first phase of North Port's abandoned lot auction led to confusion as the cost of some lots seemingly tripled overnight. The appraiser hired by the county put the auction lot number, the property ID number and the minimum bid amount onto a spreadsheet in sequential order and, inadvertently, he did not sort the value column." Risk: loss of participation of citizens in the bidding process, uproar and bad publicity

Avoidance: training in spreadsheet operation and checking against source data before posting publicly

Advice: Register Now! for the EuSpRIG 2012 Conference

082) 'Thought he had skills' to fabricate data

http://www.theage.com.au/articles/2005/11/09/1131407702080.html

Dud spreadsheet threatens case By Leonie Wood November 10, 2005 A MULTIMILLION-DOLLAR petrol price-fixing case being led by the Australian Competition and Consumer Commission in the Federal Court has been seriously jeopardised by admissions from it that one of its investigators made up some of the evidence. The ACCC is relying on a huge spreadsheet which lists petrol prices and times of price changes to corroborate oral evidence from witnesses, including some of the petrol station operators, that Geelong fuel retailers worked together to push up prices in 1999 and 2000. ACCC's chief investigator Jane Edwards admitted that "at the risk of fingering one of my staff members", one of the investigators had cut and pasted slabs of data into the spreadsheet. Ms Edwards at the time told the court that the investigator was "very enthusiastic and he thought he had skills that we weren't aware of that might be of use", so he hurdled the problem that the ACCC lacked information about price movements on some dates by duplicating data from other dates. Risk: losing a lawsuit

Avoidance: user training and consistency checks

Advice: Register Now! for the EuSpRIG 2012 Conference

081) Lack of data quality control gives a negative snapshot

http://www.marketwatch.com/news/story.asp?guid=%7B276FD56F%2D00CA%2D42AE%2DAD70%2DC66DF571FC77%7D

Kodak restates, adds $9 million to loss By Jim Jelter, MarketWatch Nov. 9, 2005 SAN FRANCISCO (MarketWatch) - The hefty $11 million severance error was traced to a faulty spreadsheet. Kodak spokesman Gerard Meuchner said "There were too many zeros added to the employee's accrued severance. But it was an accrual. There was never a payment," he said. Robert Brust, Kodak's chief financial officer, called it "an internal control deficiency that constitutes a material weakness that impacted the accounting for restructurings." Risk: loss of share value, investor confidence, career damage

Avoidance: Data quality control

Advice: Register Now! for the EuSpRIG 2012 Conference

080) Black-on-black cell text format attempt to 'hide' data

http://www.smh.com.au/news/business/westpac-jumps-the-gun-on-profit/2005/11/02/1130823280336.html

Westpac was forced to halt trading on its shares and deliver its annual profit briefing a day early after it accidentally sent its results by email to research analysts. Details of the $2.818 billion record profit result for the 12 months to September 30, were embedded in a template of last year's results and were accessible with minor manipulation of the spreadsheet. (Some reports indicated an employee had thought that a black cell background fill would hide black text.) Mr Chronican said "It is not just one error, it is a compounding of two or three errors... We will obviously be conducting a full inquiry to make sure it doesn't happen again." Risk: public embarrassment, loss of investor confidence

Avoidance: User training on how to remove data properly

Advice: Register Now! for the EuSpRIG 2012 Conference

079) How easy it is to change a spreadsheet, how hard to do version control...

http://www.namibian.com.na/2005/October/national/05E0F49179.html

The Agricultural Bank of Namibia (Agribank) is teetering on the edge of bankruptcy. "There is no system of control on which the auditors can rely nor were there satisfactory auditing procedures that could be performed to obtain reasonable assurance that the provision for doubtful debts is adequate and valid," note the auditors. Auditors found that its loan amount to the now defunct !Uri !Khubis abattoir changed from N$59,5 million on one spreadsheet to N$50,4 million on another, while the total arrears was decreased from a whopping N$9,8 million to only N$710 000. Risk: loss of financial control

Avoidance: Change management, version control

Advice: Register Now! for the EuSpRIG 2012 Conference

078) Exam scores botched by mis-sorting

http://www.nbc13.com/news/4951284/detail.html?rss=bir&psp=news

Aspiring Police Officers Say Exam Scores Were Botched (NBC13.com 8 Sep 2005)

"Some aspiring police officers who took a government exam said they were told they passed a big test, but found out later that they had actually failed. A national company called AON administered the test and told the board someone incorrectly sorted the results on a spreadsheet, so the names and scores were mismatched", NBC 13's Kathy Times reported. "When they appealed, we went back to AON and asked them to check their scores, and when they audited, they discovered they made an error," said Bruce Nichols, of the Jefferson County Personnel Board. Nichols has resigned. Risk: public embarrassment, loss of confidence

Avoidance: Spreadsheet data cross-check

Advice: Register Now! for the EuSpRIG 2012 Conference

077) Referencing and linking errors

http://efile.mpsc.cis.state.mi.us/efile/docs/13531/0349.pdf

Rebuttal Testimony of David J. Barch SBC Michigan Case No. 13531. "... a minor spreadsheet referencing error ... the correction of which is estimated to roughly double the nonrecurring provisioning TELRIC for these UNEs. Another change SBCM would consider appropriate is correcting a couple of very straightforward and simple spreadsheet link errors (i.e., linking to the wrong spreadsheet cell, which was empty) for Nonrecurring ULS Trunk Port and DS1 Trunk Port Disconnects. This is estimated to increase the TELRIC over 30 percent for these two UNEs." Risk: Exposure in public audit

Avoidance: Spreadsheet linkages cross-check

Advice: Register Now! for the EuSpRIG 2012 Conference

076) Auditor's report : a checklist of testing and maintenance standards

http://www.fdicig.gov/reports01/01-025.pdf

Office of Inspector General (OIG) December 13, 2001 Audit Report No. 01-025 Audit of the Least Cost Test Model of Federal Deposit Insurance Corporation (FDIC). "we concluded the model is generally operating as intended. However, ... controls over access, software development, and changes were weak." "no record that the software was tested ... not developed a system for requesting, making, testing, and approving changes ... little security over the macros and formulas" (Reference: OMB Circular A-130, Appendix III) "found compatibility problems" "The FDIC no longer employs the DRS employees responsible for designing the spreadsheet, and no one has documentation on how the spreadsheet was created in case modifications are needed" "some of the formulas within the template had been overwritten" Risk: loss of financial control

Avoidance: Change management, version control

Advice: Register Now! for the EuSpRIG 2012 Conference

075) Auditor's report : a checklist of documentation and change standards

http://www.anao.gov.au/WebSite.nsf/0/1EA061E6A929D67ECA256FBF00783027?OpenDocument

The Auditor-General Audit Report No.38 2004-05 Performance Audit, Goods and Services Tax (GST) . To calculate GMA and BBA, Treasury uses a Microsoft Excel spreadsheet with 32 linked worksheets "The spreadsheet was developed in-house with limited resources and without adequate consideration being given to alternatives. The resulting approach did not, in ANAO's view, adequately manage some important risks. ... difficult to navigate for the purpose of verifying that it is performing calculations correctly. ... developed in-house with limited resources and without adequate consideration being given to alternatives. ... ANAO was unable to locate any system documentation ... None of the present staff in the Unit was there when the spreadsheet was constructed. ... Repeated manual entry of the same data clearly increases the risk of errors. ... shortcomings [in] managing the records that support the data ... an unacceptable level of risk that staff responsible for updating the spreadsheet may inadvertently access an earlier version of the spreadsheet." Risk: loss of financial control

Avoidance: Change management, version control

Advice: Register Now! for the EuSpRIG 2012 Conference

074) Many tens of millions of pounds adverse effect

www.telegraph.co.uk/money/main.jhtml?xml=/money/2005/06/30/ccspread30.xml&menuId=242&sSheet=/money/2005/06/30/ixcoms.html

Only a matter of time before the spreadsheets hit the fan - Telegraph (UK), 30 June 2005

In his paper "The importance and criticality of spreadsheets in the City of London" presented to Eusprig 2005, Grenville Croll of Frontline Systems (UK) Ltd. reported on a survey of 23 professionals in the £13Bn financial services sector. The interviewees said that spreadsheets were pervasive, and many were key and critical. There is almost no spreadsheet software quality assurance and people who create or modify spreadsheets are almost entirely self-taught. Two each disclosed a recent instance where material spreadsheet error had led to adverse effects involving many tens of millions of pounds.

073) Two employees more or less

http://monroeohio.org/pdf/05%20Budget%20Report.pdf

City of Monroe 2005 budget document

Wages and benefits have increased dramatically from 2004 figures for two reasons. First, we miscalculated the amount of wages and benefits needed. This occurred because of a miscalculation in a spreadsheet formula, which exclude two Water Department employees from the calculation. Risk: public embarrassment, loss of confidence

Avoidance: Spreadsheet data cross-check

Advice: Register Now! for the EuSpRIG 2012 Conference

072) File Save As $1Bn error

http://www.dailypress.com/news/local/virginia/dp-naturalgaslawsuit0617jun17,0,2841429.story?coll=dp-headlines-virginia

Natural gas consumers sue Dominion Transmission over clerical error, 17 June 2005

A Federal Energy Regulatory Commission investigation found that the subsidiary of Richmond, Va.-based Dominion Resources Inc. submitted the wrong week's gas storage figures in November, leading to an artificial inflation of natural gas prices. The lawsuit estimates that consumer prices were hiked by between $200 million and $1 billion. "The investigation concluded that it was not deliberate, but when I hear the words clerical error, I think of negligence," plaintiff's attorney W. Coleman Allen Jr., of Richmond, Va., said Friday. "Consumers were harmed the same as if it was intentional." One explanation for the error was that the company had used the same computer file name for each week's storage balance spreadsheet report, making it easy for the wrong one to be sent. Risk: lawsuit, regulatory sanctions

Avoidance: Release management, version control

Advice: Register Now! for the EuSpRIG 2012 Conference

071) Accounting firm makes AUD$30M spreadsheet error

http://www.abc.net.au/news/newsitems/200506/s1394937.htm

Accountants make AUD$30M mistake, 17 June 2005

On the eve of the Northern Territory election, the Country Liberal Party has been forced to admit that its financial costings were out by tens of millions of dollars. The CLP had their costings assessed by accounting firm BDO. CLP Leader Denis Burke says the company made a spreadsheet error and Treasury has found more money in contingency funds from the budget. Risk: public embarrassment, loss of election

Avoidance: Spreadsheet review

Advice: Register Now! for the EuSpRIG 2012 Conference

070) Cut a percent, paste an apology

http://www.roanoke.com/business/23037.html

Go figure: 'Think-and-do tank' flubs the math, May 05, 2005

The Center for Regional Strategies recently confirmed that a researcher's errant cut-and-paste from a spreadsheet caused one measure of the region's level of educational attainment to appear a lot worse than it is. The Center for Regional Strategies, a self-described "think-and-do tank" housed at Virginia Tech, reported that a dismal 11 percent of the region's population older than 25 had bachelor's degrees or higher. That number should have been 20 percent. Stuart Mease, a spokesman for the Center for Regional Strategies, said "It was just a simple cut-and-paste error," he said. "I don't know how it happened, but it did. We apologize for our mistake and want to correct it." Risk: public embarrassment, adverse press coverage

Avoidance: Spreadsheet review

Advice: Register Now! for the EuSpRIG 2012 Conference

069) Mis-recording a number is a material weakness, lose your CEO position

http://biz.yahoo.com/ap/050329/redenvelope_guidance.html?.v=3

March 29, 2005

RedEnvelope Cuts Outlook, Shares Fall, CFO Eric Wong Resigns Amid Budget Errors

NEW YORK (AP) -- Shares of RedEnvelope Inc. tumbled more than 25 percent Tuesday after the online retailer of specialty gifts drastically reduced its fourth-quarter outlook and said its chief financial officer will resign in April. Stanford Group analyst Rebecca Jones Kujawa said in an interview. "...they were underestimating the cost of goods sold....it is likely CFO Eric Wong is being pushed out because of this error, which could demonstrate a material weakness in controls over financial reporting, an issue that usually leads to a lengthy review of accounting practices." RedEnvelope spokeswoman Jordan Goldstein said the budgeting error was simply due to a number mis-recorded in one cell of a spreadsheet that then threw off the cost forecast and was unrelated to the CFO change. Risk: loss of share value, investor confidence, career damage

Avoidance: Data quality control

Advice: Register Now! for the EuSpRIG 2012 Conference

068) 'Livid' because researcher did not check inputs against data sources

http://www.nzherald.co.nz/index.cfm?c_id=1&ObjectID=10008219

Brash 'livid' over statistics mixup in speech 27 Jan 2005

National Leader Don Brash used a wildly inflated number when he told the Ratana gathering on Monday that nearly half of all Maori children were dependent on a benefit. The figure - which strongly featured in news reports of the high-profile Maori gathering - was the result of a calculating error by the Parliamentary Library. The estimate should have been 30 per cent, said parliamentary researcher David Williams, who owned up to the Herald about making the mistake. He has apologised to Dr Brash. Mr Williams believed the error occurred when he transferred working figures from an Excel spreadsheet. Risk: public embarrassment, adverse press coverage

Avoidance: Spreadsheet reconciliation with working papers

Advice: Register Now! for the EuSpRIG 2012 Conference

067) Numbers entered as text lose school £30,000 (US$50K) from budget

http://www.accountingweb.co.uk/cgi-bin/item.cgi?id=40301

Excel spreadsheets in Budgeting; Accountingweb 5-Mar-2001

School governor Peter 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. 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.

Risk: Budget shortfall

Avoidance: As AccountingWEB member Paul Mitchell pointed out, "Perhaps the authority should look into its IT training practices"; Wolstenholme added, "The most obvious deficiency I could see was the lack of cross-checks"

Advice: Register Now! for the EuSpRIG 2012 Conference

066) There's bad news and good news; mainly bad: US$1.8M

http://biz.yahoo.com/prnews/050208/cltu108_1.html

CECO Environmental to Restate 2000 - 2003 Financial Statements February 8, 2005

CECO's in-house accounting staff has discovered an accounting error in the spreadsheet calculations used by the Company's construction division for its 'percentage of completion' accounting. The net effect will be a cumulative reduction in revenue of $1,969,000 over the four year period 2000 to 2003 with an equivalent charge to pre-tax net income. The error has a favorable effect in the current year, 2004, [it] will add approximately $180,000 to CECO's 2004 income before taxes for the nine months ended September 30, 2004.

Phillip DeZwirek, Chairman and CEO of CECO stated that, "Our in-house accounting staff discovered this miscalculation and immediately reported it to our independent auditors. As our business expands it is important to know that our Sarbanes-Oxley compliance preparation is working." Risk: Loss of investor confidence

Avoidance: Spreadsheet review

Advice: Register Now! for the EuSpRIG 2012 Conference

065) "Mistakes happen during budget planning": US$70M

http://www.theunionleader.com/articles_showfast.html?article=50185

New Hampshire Sunday News Jan 29, 2005

"Gov. John Lynch's budget team .. has to find another $70 million to make its budget balance. Figures that the Health and Human Services Department provided to budget writers in the fall contained an error that double-counted more than $17 million of Medicaid money in each of the next two years. A detailed spreadsheet that HHS gave Ways and Means Tuesday morning showed that $35 million in a specific category of hospital reimbursements would come in over the next two years. A second sheet HHS produced Tuesday afternoon showed no money in the category, reflecting the fact the funds can only be used one way - at the state hospital." Risk: Budget shortfall

Avoidance: Check applicability of the figures

Advice: Register Now! for the EuSpRIG 2012 Conference

064) NASA misstated by $644M: undetected spreadsheet errors in "ad hoc" process

http://www.gao.gov/atext/d04754t.txt Government Accounting Office Report; Mar 30, 2001

http://www.spaceref.com/news/viewsr.html?pid=5014 Testimony of Gregory D . Kutz, GAO

GAO report number GAO-04-754T entitled 'National Aeronautics and Space Administration: Significant Actions Needed to Address Long-standing Financial Management Problems' "NASA's fiscal year 1999 SBR was misstated by a reported $644 million due, in part, to a misinterpretation of guidance and errors in NASA's ad hoc process for generating budgetary information. As a result, the NASA locations used various methods to extract the data for this line item from their separate systems and entered the data on spreadsheets, which were then compiled by NASA headquarters. ... NASA officials have indicated that undetected errors in this spreadsheet process- in addition to the inclusion of the erroneous category of transactions-were also responsible for a portion of the SBR misstatement. According to NASA officials, they have strengthened internal controls over this process for fiscal year 2000. Arthur Andersen did not detect the error in NASA's SBR during its audit of the fiscal year 1999 financial statements. Evidence in Arthur Andersen's working papers relating to understanding and testing internal controls and validating underlying data for key financial statement balances was not adequate to support Arthur Andersen's unqualified audit opinion' on the SBR and Statement of Financing for fiscal year 1999. ... The other errors of approximately $32 million related to errors in the spreadsheet data may have had an impact on the P&F Schedules." Risk: Audit comments made public

Avoidance: Enhance controls around spreadsheet use

Advice: Register Now! for the EuSpRIG 2012 Conference

063) Cooking the books with a spreadsheet: about US $20M

www.theindependent.co.zw/news/2004/December/Friday17/1307.html

Fraud rocks CFX Dec 17, 2004 (Zimbabwe) $115 Billion loss concealed

"Internal investigations indicate that bank accounts 'were computer-engineered' by a key accounting manager. CFX, using the Equation banking system, fell victim to the crafty managers who tabulated a pre-tax profit of nearly $873 million in October yet the software records a near $20 billion loss. The internal audit particularly targets the information and technology department for aiding the creative accounting scandal. 'I strongly feel that the adjustments noted above are done on an Excel spreadsheet and they are adjusted backwards from the management accounts back to the Equation system, ie the management accounts showing desired results are prepared first, then adjustments to tie up to Equation are made,' the report says." (Note on currency values: http://www.xe.com/ucc/full.shtml reports that 1 USD = 5,652.00 ZWD ) Risk: Fraud, bankruptcy or central bank bailout

Avoidance: Compliance with banking laws, enforcement

Advice: Register Now! for the EuSpRIG 2012 Conference

062) Spreadsheet calculation error: $100,000 overoptimistic revenues

http://www.shirleyoracle.com/Stories/0,1413,111~5843~2583940,00.html

SHIRLEY Dec 1, 2004 - $100,000 revenue shortfall

At its meeting Monday Nov. 29, the School Committee approved its current year $7.3 million budget after learning of a calculation error in the school budget that overestimated school revenue figures by $100,000. Business manager Patty Stern said additional money from grant sources and the $167,000 approved for the school at the special town meeting covers the error. During the budget review process, Stern said a calculation error was found on the spreadsheet that doubled revenue figures by $100,000. Risk: Budget shortfall

Avoidance: Cross-check totals

Advice: Register Now! for the EuSpRIG 2012 Conference

061) Auditors say spreadsheet controls "not sufficient"

http://business.bostonherald.com/businessNews/view.bg?articleid=55580 Boston Herald Nov 25, 2004.

"Tweeter Entertainment Group Inc. reported a wider fourth-quarter net loss yesterday, $12.5 million compared with $10.2 million a year ago, and a 3.5 percent decline in sales at stores open for more than a year. To make matters worse, Tweeter announced that its auditor, Deloitte & Touche LLP, said its spreadsheet controls were 'not sufficient' in the fourth quarter. A spokeswoman declined to say how much in 'recorded adjustments' the company made. Tweeter said the errors did not affect prior periods." Risk: Audit comments made public

Avoidance: Enhance controls around spreadsheet use

Advice: Register Now! for the EuSpRIG 2012 Conference

060) Nov 15, 2004: On the day the Sarbanes-Oxley act comes into effect in the USA

http://biz.yahoo.com/prnews/041115/dam033_1.html

CARRIZO Oil & Gas, Inc. Corrects Diluted Share Computations, Nov 15, 2004.

The company recently discovered an error in a spreadsheet which tracks the average number of warrants and options outstanding. This error impacted Carrizo's financial results. The corrected number of shares for the 2004 six month period was 20.8M rather than 18.9M as previously reported. The actual diluted net income per share was $0.19 rather than $0.21. Risk: Loss of confidence

Avoidance: Spreadsheet review

Advice: Register Now! for the EuSpRIG 2012 Conference We can expect more of this in the future because of Sarbox/SOX requirements that management report on its assessment of internal controls over financial reporting in the annual filing. As a CEO is reported as saying, "I don't look good in orange".

059) Mutual mistake? Tough luck.

http://lawzone.thelawyer.com/cgi-bin/item.cgi?id=111862&d=205&h=207&f=259

When is a mistake not a mistake?, Nov 1, 2004

In a loan dispute, a spreadsheet prepared by a claimant sought to calculate the total interest due under the loan. A mistake was not discovered during the negotiations and, in due course, the parties agreed a settlement figure of £55,000 on the mutually mistaken assumption that this equated to a rate of interest of about 45%. In truth, £55,000 equated to an interest rate of approximately 33%. The court held the mistake was not the fault of either the parties and, furthermore, there was no warranty as to the spreadsheet. They were commercial parties and had agreed on a fixed sum. They were both (mutually) mistaken and there was no injustice. " Risk: financial loss

Avoidance: Checking

Advice: Register Now! for the EuSpRIG 2012 Conference

058) "In a student organization, mistakes sometimes happen"

http://www.lsureveille.com/vnews/display.v/ART/2004/10/27/417f3c7c41064

News Sorority's scores inaccurate LSU Daily Reveille Tue, 26 Oct 2004

"A miscalculation in a computer spreadsheet almost cost Chi Omega sorority first place in the Homecoming competition. The error occurred when the spreadsheet formula used to calculate scores from Homecoming Week events left two scores out of the tabulation. 'They understood in a student organization, mistakes sometimes happen.' Throughout the week, Gilbert and Homecoming coordinators tallied the results of each event and kept all related paperwork in case a score came into question. " Risk: Embarrassment

Avoidance: As shown: keeping the paperwork for checking the audit trail

Advice: Register Now! for the EuSpRIG 2012 Conference

057) Tabulation error - there's always a first time!

http://www.reuters.com/newsArticle.jhtml?type=businessNews&storyID=6480933

Business Council: Economic Survey Wrong, Tue Oct 12, 2004

"NEW YORK (Reuters) - The Business Council, a group of 125 U.S. CEOs, last week said 70 percent of its members expected flat to 2 percent growth in the U.S. economy next year -- painting a much more bearish outlook than major economists. But on Tuesday, the group said it had gotten the survey wrong and its members had actually projected moderate to solid economic growth in 2005, from 2.1 percent to 4.5 percent, and were 'guardedly optimistic' about the economy's prospects. 'It was a computational error. The spreadsheet shifted so the wrong numbers appeared in the wrong columns,' Cassidy said. 'It was just one column on the spreadsheet.' He said the error, which was caught on Friday afternoon, has never happened before in the survey's history." Risk: Suspicion around election time

Avoidance: Checking results

Advice: Register Now! for the EuSpRIG 2012 Conference

056) Adding apples and oranges

http://www.hansard.act.gov.au/hansard/1998/pdfs/19981124.pdf

ACT Hansard, 24 Nov 1998

"My office did prepare a table [that] double-counted dividends provided and projected... It was the most obvious and fundamental of mistakes in a simple spreadsheet. I wish to register my regret regarding the matter, particularly as it has more than undone an effective grassroots campaign... I do have one very contrite staffer who has otherwise been performing exemplary work." [Another speaker] "What it shows is absolutely no financial credibility, no capacity, even when it is so obvious, to work out what the problem was. But even worse, [he] added the cash dividends out of the cash flow statement to profits from the operating statement. It is like adding apples and oranges." Risk: Public embarrassment, loss of credibility

Avoidance: Review by person with knowledge of finance

Advice: Register Now! for the EuSpRIG 2012 Conference

055) Houston, we have a $25,652 problem

Source: http://www.ci.houston.tx.us/citygovt/controller/audit/00-09.pdf

Houston City-wide Direct Costs Audit December 20, 1999

Finance and Administration (F&A) was undercharged $25,652 for rent in fiscal 1999 due to an error in a spreadsheet formula. Risk: Audit finding

Avoidance: Checking

Advice: Register Now! for the EuSpRIG 2012 Conference

054) $400,000 water under the bridge

Source: http://www.city.west-lafayette.in.us/government/minutes/cc050602.htm

May 6, 2002

The problem a couple of years ago was Purdue discovered an error in a spreadsheet used to calculate the water bill and that spreadsheet had been used for several years. So we went back and recalculated the bills and brought that to the City's attention and then promptly paid that amount. I believe the amount is a little over $400,000, was the adjustment made at that time. Risk: Public embarrassment

Avoidance: Checking

Advice: Register Now! for the EuSpRIG 2012 Conference

053) Understating the benefit of unbundling by $50M

Source: www.comcom.govt.nz/telecommunications/llu/Conf/tclreviewcba.PDF

13 Nov 2003

A review of Telecom's critique of the OXERA cost benefit analysis of unbundling the local loop finds: "A spreadsheet error increases net benefit Voice prices are set too high due to a spreadsheet error. Correction adds $26M to surplus of $180M. Incorrect spreadsheet references means that the net benefit of Option 1 is understated by $50M" Risk: Loss of public confidence

Avoidance: Check cell references

Advice: Register Now! for the EuSpRIG 2012 Conference

052) Reportable condition: $2M

Source: www.sao.state.ut.us/reports/03-37.pdf

June 30,2003

Utah State Auditor DEPARTMENT OF NATURAL RESOURCES FINDING AND RECOMMENDATION

1 INDIRECT COST RATE CALCULATED INCORRECTLY (Reportable Condition)

"We noted the following errors while testing the fiscal year 2003 indirect cost rate for the Division of Wildlife Resources: a. The indirect cost electronic spreadsheet for Habitat Management direct salaries and benefits, titled "Short Form Method," had a cell that was linked to an incorrect spreadsheet. This resulted in an overstatement of the direct cost base by over $2 million. " Risk: Reported breach of standards

Avoidance: Check external links

Advice: Register Now! for the EuSpRIG 2012 Conference

051) Watch your typing. Literally.

Source: http://catless.ncl.ac.uk/Risks/21.94.html

Yet another case of a program changing your input Risks Digest 10 Mar 2002

I was entering grades in an Excel spreadsheet and realized that although in my notes I had a mix of A's and A-'s, the spreadsheet had changed all the A grades to A-'s. Why? I was entering grades looking at my notes and not the screen. So I typed A- followed by ENTER, then A at which point Excel suggested A- as a possible input. Without looking I pressed ENTER, thus entering A- instead of A. This only works if the longer input precedes the shorter in the original list (i.e., the list you are typing from), since if there is ambiguity about the suggestion, Excel shuts up. Risk: Wrongly grading students

Avoidance: Be aware of "helpful" features of software, check inputs against source documents

Advice: Register Now! for the EuSpRIG 2012 Conference

050) The silent loss of $19,130

Source: http://catless.ncl.ac.uk/Risks/20.30.html#subj10

Risks Digest 16 April 1999

From: Ben Bederson Subject: Space character in number causes silent Excel miscalculation Error of US$19,130

"I had specifically right-justified the column in question earlier. The issue here is that the "19, 130" was interpreted by Excel as text rather than as a number. Since Excel doesn't generate warnings when adding text, but rather interprets it as 0, I had no notification of the problem. Risk: losing a chunk of your budget

Avoidance: Add data type validation to input cells

Advice: Register Now! for the EuSpRIG 2012 Conference

049) Violation of regulatory reporting by $105,000

Source: www.fcc.gov/Bureaus/Common_Carrier/Orders/1995/fcc95075.txt

March 3, 1995

NYNEX Telephone Operating Companies, FCC 95-75 Before the FEDERAL COMMUNICATIONS COMMISSION

34. Apparent Violation No. 15: The independent auditor found that NET overstated its CL revenues for January through November 1988. NET stated that an erroneous spreadsheet caused this overstatement. The independent auditor stated that this error understated NET's CL and total interstate revenue requirements for those months by $105,000. Risk: Audit exposure

Avoidance: Strengthen review controls

Advice: Register Now! for the EuSpRIG 2012 Conference

048) Data pollution

Source: www.bcen.bc.ca/bcerart/Vol10/acleaner.htm

A Cleaner Future for BC's Polluting Pulp Mills by Peter Ronald, campaign coordinator, Georgia Strait Alliance 2001 "in November it was revealed that this mill (formerly owned by MacMillan Bloedel) has been under-reporting its Total Reduced Sulphur (TRS) emissions by 32-times for the past 14 years! This 'miscalculation; was embedded in a spreadsheet formula uncovered during an internal audit." Risk: public embarrassment in the newspapers

Avoidance: Strengthen review controls

Advice: Register Now! for the EuSpRIG 2012 Conference

047) Called to account (UK)

Source: www.publications.parliament.uk/pa/cm199899/cmselect/cmpubacc/278/9022415.htm

24 June 1999 Select Committee on Public Accounts Minutes of Evidence "Please accept my sincere apologies for the percentage error in Table 2 you correctly identified in your letter. The figure should be 5 per cent and not 13 per cent (a mistake in the spreadsheet used in calculations). The previous version of Table 2 did have a number 'rounding-up' anomalies, and miscalculation in providing percentage figures. These were the result of problems in the spreadsheet and, in the case of the 1998-99 "London Exclusion Total" a simple typographical error. " Risk: public embarrassment, brought before Public Accounts Committee

Avoidance: Strengthen review controls

Advice: Register Now! for the EuSpRIG 2012 Conference

046) Called to account (US)

Source: www.whitehouse.gov/omb/legislative/testimony/19970716-23269.html

G. Edward Deseve Statement to House Small Business Committee, 07/16/1997 "We at OMB and, as Administrator Alvarez will testify, SBA believe that it is an extremely serious matter and are taking steps to correct it. I accept the findings of SBA's Inspector General that both SBA and OMB share responsibility for the problem....The error in the calculation of the FY 1997 7(a) loan subsidy estimate discovered by GAO was an incorrect reference formula in one cell of a summary spreadsheet. " Risk: public embarrassment, brought before House committee

Avoidance: Strengthen review controls

Advice: Register Now! for the EuSpRIG 2012 Conference

045) Carelessness can cost jobs

Source: www.thepolypost.com/story.php?story=648

The Poly Post August 18th, 2004 Miscalculation Found in Budget As recently as two weeks ago, Children's Center staff were concerned that the facility would close as early as next quarter.

Hoffman said a spreadsheet error and an overestimation in staff salary brought the budget down by 50 percent. Risk: closure of children's centre

Avoidance: Detailed review

Advice: Register Now! for the EuSpRIG 2012 Conference

044) USAID Audit shows agencies needing aid for controls

Source: www.usaid.gov/oig/public/fy03rpts/7-685-03-003-p.pdf

Audit of USAID/Senegal's Casamance Conflict Resolution Program 7-685-03-003-P May 30, 2003 "Enterprise Works provided self-created spreadsheets to support the results reported to the Mission for two selected indicators... However, they could not provide any original documentation to support the figures in the spreadsheets." "CRS could not provide support for the data reported in their spreadsheet for the first selected indicator, "number of jobs created." Furthermore, the spreadsheet was mathematically incorrect... even though the Mission has in place procedures for periodically checking data, these procedures were not fully implemented; a lack of periodic checks of the data also contributed to the problem." Risk: possible loss of aid funding

Avoidance: Not just review, but verification

Advice: Register Now! for the EuSpRIG 2012 Conference

043) Legislative Auditor finds $58M optimism in DPS

Source: www.lla.state.la.us/perform/dps02.pdf

Department of Public Service January 2002 Audit Control # 02500734 Two significant audit findings. "We found numerous errors in the mathematical computations. In addition, the review controls are weak. The department reported a value of $184.5 million for the key performance indicator Direct savings to ratepayers. We determined that this value should have been $126.5 million; therefore, the first quarter value reported was overstated by almost $58 million, or 45.8%. This miscalculation was due to a spreadsheet formula error. " "The department computes many of its performance indicator values using manual calculations. This situation increases the chance of human error in performing the calculations. The department should increase its use of electronic spreadsheet and/or database programs to perform calculations."(!) Risk: Audit exposure

Avoidance: Strengthen review controls

Advice: Register Now! for the EuSpRIG 2012 Conference

042) Uncleared carryforward ... $10,395

Source: www.mcall.com/news/local/all-b3_4basdaug10,0,2437486.story?coll=all-newslocal-hed

Morning Call Aug 10, 2004 An audit found that the state gave the district $10,395 more than entitled because the number of pupils transported for the 2001-02 school year was overstated by 27 to the Department of Education. Stanley Majewski, Bethlehem Area's director of business affairs, said the overpayment was the result of a simple clerical error. "Our transportation director didn't clear out the previous year's private school riders from our spreadsheet ... We didn't realize it until the auditors pointed it out to us."

More: http://www.nj.com/news/expresstimes/pa/index.ssf?/base/news-12/109213236859950.xml Risk: Audit exposure, fines

Avoidance: following correct operating procedures

Advice: Register Now! for the EuSpRIG 2012 Conference

041) "Clerical error" ... tens of thousands of dollars

Source: http://www.timesdispatch.com

Richmond Times-Dispatch Aug 8, 2004 "City officials miscalculated the amount of sales taxes generated at Stony Point Fashion Park during the first couple of months of operation. The mistake inadvertently inflated the figures by tens of thousands of dollars, which, in turn, meant the total sales estimate was overblown by millions of dollars. 'It was a clerical error. There was no intention to mislead anyone,' said Andrew T. Rountree, the city's finance director. The city blamed the mistake primarily on an error in a spreadsheet formula, which amplified a subtotal amount." Risk: Having to explain oneself in public

Avoidance: Cross-checking control totals

Advice: Register Now! for the EuSpRIG 2012 Conference

040) $5M actuarial overstatement

Source: http://www.ir.jameshardie.com.au/repositories/files/NEWS.2004.KPMGACTURIALREVIEW.pdf

James Hardie Actuarial Expert Witness Report, KPMG Actuaries Pty Ltd 4 June 2004 Over the years 1996-2001, James Hardie Industries Ltd ('James Hardie') received

advice from Trowbridge Consulting, the actuarial subsidiary of Deloitte Touche

Tohmatsu ('Trowbridge'), in relation to the level of asbestos liabilities to which it was

potentially exposed.

There are some errors in the report and the valuation model. Most notably, the

insurance recoveries have been over-discounted due to spreadsheet errors

(this had been corrected by the 13 February 2001 valuation). This has resulted

in a $5m overstatement in the liability Trowbridge have recommended relative

to that which they ought to have recommended on their valuation basis. Risk: Public exposure, loss of reputation

Avoidance: Checking

Advice: Register Now! for the EuSpRIG 2012 Conference

039) Automatic conversion features of Excel munge data

Source: http://www.biomedcentral.com/1471-2105/5/80

Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics. Barry R Zeeberg et al.BMC Bioinformatics, June 2004 When processing microarray data sets, they noticed that some gene names were being changed inadvertently to non-gene names. For example, the text SEPT2 was converted to a date. The problem was default date format conversions and floating-point format conversions in Excel. The date conversions affect at least 30 gene names; the floating-point conversions affect at least 2,000 if Riken identifiers are included. These conversions are irreversible; the original gene names cannot be recovered. Risk: Misinterpretation of genetic research results

Avoidance: Awareness of Excel limitations, 'helpful' automatic features, bugs, and workarounds

Advice: Register Now! for the EuSpRIG 2012 Conference

038) Environmental toxic waste miscalculation

Source: http://www.envirogroup.com/JE%20Spreadsheet%20Model%20Error.pdf

Technical Memorandum Superfund JE Model Spreadsheet Error By Eric Wannamaker, EnviroGroup Ltd July 2004 A bad VLOOKUP was discovered in one of the cells of the US EPA Superfund spreadsheet used to determine the dispersal of the toxic wastes through ground water. This is how the EPA determines which toxic waste sites to clean up first. The impact of the JE model error is highly case specific, depending on site characteristics, regulatory goals, and local geology. The quantitative impact of the error is a difference in indoor air concentration estimate ranging from 0 to ~200%. The "cost" of this misestimate would depend on how it is used in the overall site assessment process. Risk: Project mis-estimation, possible wasted resources

Avoidance: model review

Advice: Register Now! for the EuSpRIG 2012 Conference

037) Misplaced parentheses

Source: http://www.solutionmatrix.com/newsletter41.html

Cost/Benefit Newsletter June 2004 'We just lost our negotiating room - the pitfalls of Excel.' "I found at the last minute that some very long spreadsheet formulas had parentheses out of place. When I put them where they belonged, our projected gains fell from $200M to $25M". Gives two Excel formulas for estimating salary and overhead costs in Year 2 of a multi-year analysis period - see if you can spot the error. Risk: 'Winning' the wrong contract - see Transalta!

Avoidance: Double-checking the results

Advice: Register Now! for the EuSpRIG 2012 Conference

036) Moving cut and paste

Source: http://www.newportnewstimes.com/articles/2004/05/28/news/news12.txt

Newport News Times OR,USA May 28, 2004 "LINCOM nears budget resolution" "She had intended to move only the proposed figure for the coming fiscal year. But when she made the change, the spreadsheet program moved the line for the lease, not just for the proposed 2004-05 year, but also the figures for the adopted 2003-04 figure, the actual 2002-03 year, and the two previous fiscal years' actual expenditures for the lease. Further, the bottom line totals for 'Materials and Services' in the columns for each of those fiscal years (for things like training, insurance, phones, and the lease) were visibly incorrect." Risk: budget delay, confusion

Avoidance: checking that cut and paste applied only to what was intended

Advice: Register Now! for the EuSpRIG 2012 Conference

035) Profiting from mispricing

Source: http://news.ft.com/servlet/ContentServer?pagename=FT.com/StoryFT/FullStory&c=StoryFT&cid=1084907736196

Financial Times, May 21, 2004: John Walter: "Run a fund? I can do that" "...we take relatively big positions in companies that we believe are temporarily under or over-priced and where we expect that mis-pricing to be sorted out quickly. By way of example, in November last year we bought ScS Upholstery (a specialist sofa retailer) at 172p after it warned on profits. A spreadsheet error had caused the wrong cost to be input into the system, with the result that their gross margin had slipped without management knowing. We knew that this would quickly be rectified, and sure enough the share price bounced back rapidly." Risk: loss of share value

Avoidance: independent review, checking answers

Advice: Register Now! for the EuSpRIG 2012 Conference

034) Spreadsheet typo loses Toledo U. $2.4M

Source: http://www.toledoblade.com/apps/pbcs.dll/article?AID=/20040501/NEWS21/405010344/-1/NEWS

Toledo Blade May 1, 2004 University of Toledo loses $2.4M in projected revenue. "While official UT projections call for a 10 percent decline in graduate student enrollment, an increase mistakenly was shown in a spreadsheet formula that led officials to overestimate enrollment and therefore revenue, Mr. Decatur said." "Dr. Johnson said no job action will be taken against the employee who made the mistake, who has a good performance record. Officials will, however, pursue systemic changes to provide more safeguards in the future. "We have very competent people," Dr. Johnson said. "I do think that the continuing fiscal pressures on universities have forced us to a level of staff support where there is little or no redundance in the process." Risk: loss of budget

Avoidance: user training and certification

Advice: Register Now! for the EuSpRIG 2012 Conference

033) Spreadsheet macros in the 1994 SA election: "amateurish shambles"

Source: http://www.mg.co.za/Content/l3.asp?ao=34102

Globe and Mail, South Africa, 14 April 2004 "Long before the results of 1994's election were finally announced, the results control centre (RCC) responsible for tabulating ballots had collapsed. The reason for its closing -- given by the transitional IEC's chairperson, Judge Johann Kriegler -- was that a hacker had breached its counting centre's security and tampered with the results. According to volunteers who worked as data entry clerks at the RCC, the chance of there ever having been a hacker is unlikely. The errors Kriegler blamed on a hacker appear to have been caused by bugs in spreadsheet macros and typos from moving data from fax to computer. " Risk: "widespread suspicion that the final election results weren't calculated by counting the votes people cast after patiently queuing for hours in the sun, but by letting political parties do backroom horse-trading."

Avoidance: Actually testing the system?

Advice: Register Now! for the EuSpRIG 2012 Conference

032) Investigators made mistakes

Source: http://www.newsday.com/news/printedition/longisland/ny-likrol283726003mar28,0,5128077.story?coll=ny-linews-print

March 28, 2004 "The town of Brookhaven paid 2,400-employee Kroll $2.2M to look for possible corruption in the Highway Department, But at the resulting trial, investigators have been caught red-faced by revelations that they went to an incorrect address and exaggerated financial figures because of typos ... two numbers were transposed on a spreadsheet, and Kroll said Milvid overbilled by $18,000 for concrete work on Boyle Road in Port Jefferson Station when he had actually overbilled by less than $3,000." Risk: Maybe losing a court case?

Avoidance: As reported: "Defense attorneys were incredulous that Kroll wouldn't double-check the largest item on its list of alleged overbillings"

Advice: Register Now! for the EuSpRIG 2012 Conference

031) Many a slip between spreadsheet and pulp

Source: http://www.gmtoday.com/news/politics/state/topstory141.asp

March 17, 2004 "Boxes of former Gov. Tommy Thompson's records meant for the Wisconsin Historical Society archives, were inadvertently put on the wrong truck and sent to a Green Bay paper mill, where they were turned into pulp. Administration Deputy Secretary Laura Engan said someone incorrectly manipulated a spreadsheet listing records that could be destroyed and records that should be retained. 'We basically identified a problem. There was a very significant human error. It won't happen again. Period.'" Source: http://www.madison.com/wisconsinstatejournal/local/75815.php (June 05,2004)

"No suspicious activity could be found," the DOA reported, adding they did find that the supervisor who botched the spreadsheet transfer had a record that "consistently suggested a lack of proficiency with the software used to manage the database." Previously, "The archivists would put a red sticker on the boxes to be destroyed, and a green sticker on the boxes to be saved. [That backup visual system] was discontinued last February, because there are fewer boxes saved these days and because it took a lot of staff time." Risk: Loss of irreplaceable historical records

Avoidance: As reported, Laura Engan said 'Because of the incident, the agency plans to create a records report that requires no human manipulation'

Advice: Register Now! for the EuSpRIG 2012 Conference

030) Spreadsheet slips on a banana

Source: http://www.abc.net.au/pm/content/2004/s1068099.htm

Australian Broadcasting Corporation radio, 17 March 2004 Banana Growers furious with Australia's quarantine watchdog over a draft report clearing the way for banana imports from the Philippines want it to hand over its computer models so they can check them. Mary Harwood, Executive Manager of Biosecurity Australia, said 'What it shows is that a simple human error was made in an electronic spreadsheet that was found in an easy way by a person looking at it.' The Australian Government Department of Agriculture report says: "Several stakeholders have requested a copy of the electronic spreadsheet that is used with risk modelling software called @RISK in the banana IRA. The spreadsheet will be loaded onto Biosecurity Australia's website when the addendum to the revised draft IRA report is released." Risk: Loss of trust

Avoidance: As said by Ron Boswell: 'I would have thought that as a matter that was as important as this, that it would be the transcription would have been checked and checked again'

Advice: Register Now! for the EuSpRIG 2012 Conference

029) Extracts from FDA Inspectional Observations and Warning Letters

Source: http://www.labcompliance.com/books/macros/ (undated) "Failure to have an adequate validation procedure for computerized spreadsheets used for in-process and finished product analytical calculations. ... SOP 644.00, QA/QC Spreadsheet Validation, is deficient in that only a small range of values are being used to challenge computerized spreadsheet mathematical calculations. Failure to use fully validated computer spreadsheets to calculate analytical results for in-process and finished product testing [21 CFR 211.165(e)]. ... Failure to validate computer software used as part of the quality system for its intended use according to an established protocol as required by 21 CFR 820.70(i). " Risk: non-compliance with regulations, fines, penalties, wasted management time

Avoidance: Know that end-user developed applications are not exempt from regulation!

Advice: Register Now! for the EuSpRIG 2012 Conference

028) Shurgard stock dives after auditor quits over company's accounting

Source: http://seattletimes.nwsource.com/html/businesstechnology/2001794064_shurgard18.html

Alwyn Scott, Seattle Times November 18, 2003 Shares of the Seattle self-storage company fell 7.1 percent after Shurgard disclosed that Deloitte and Touche quit upon learning Shurgard overpaid its chief executive and another investor $700,000 each and took more than six months to find and correct the error. Shurgard said the overpayment occurred when the company bought out a limited partnership owned by Chief Executive Charles Barbo and another investor. A person in Shurgard's accounting department used the wrong spreadsheet to figure out what the two were due, spokesman Jeff Szorik said. The payment was discovered and repaid six months later. Risk: Loss of stock value

Avoidance: Timely review

Advice: Register Now! for the EuSpRIG 2012 Conference

027) Spreadsheet glitch scrambles city election tally

November 8, 2003 Albuquerque Tribune, NM

Source: http://www.abqtrib.com/archives/news03/110803_news_canvass.shtml

City Clerk Judy Chavez on Friday released new vote counts from the recent city bond elections correcting totals for six of the 10 bond questions. In one case, the count was off by more than 11,000 votes. She is reported as saying "A clerk happened to compare the print-out from an old-fashioned adding machine to the print-out of the computer spreadsheet and noticed discrepancies. Individual, precinct-by-precinct numbers were accurate. The inaccuracies were in some of the bottom-line totals. A programming error in the city's computer spreadsheet caused the problem but it has been corrected". Risk: Legal challenges.

Avoidance: Spreadsheet formula audit.

Also reported at: http://kobtv.com/index.cfm?viewer=storyviewer&id=6048&cat=HOME

Advice: Register Now! for the EuSpRIG 2012 Conference

026) NYSE fines local trader $475,000

November 01, 2003 The Grand Rapids Press

Source: http://www.mlive.com/news/grpress/index.ssf?/base/news-11/1067685394184490.xml

A well-known Grand Rapids investment manager's alleged mishandling of stock orders has led to $475,000 in fines by the New York Stock Exchange and censures for him and two other employees involved in the orders.

The stock exchange said Cusack, 47, bought large blocks of Foremost stock in September and October of 1999, but didn't properly document which of his clients would have the stock placed in their accounts. The lack of documentation raised the possibility that he could wait to decide how to distribute the shares until he knew how the shares performed that day -- a violation of NYSE rules and Securities and Exchange Commission laws.

Cusack told the stock exchange he had a spreadsheet that spelled out how the stock would be distributed when the orders were placed, but the spreadsheet was thrown away after the transactions and other documentation were completed. Risk: Fines and penalties

Avoidance: Document and archive your work

Advice: Register Now! for the EuSpRIG 2012 Conference

025) Errors and omissions excepted: $3.7M

Source: http://www.gao.gov/decisions/bidpro/292555.htm October 10, 2003 "An agency may permit correction of a bid where clear and convincing evidence establishes both the existence of a mistake and the bid actually intended. " "Our review of the record, including Emerson's computer-generated spreadsheets, confirms that the price at spreadsheet cell number D159 (for electrical work) was not included in the subtotal at cell number D160. Based on the format of the spreadsheet, it is clear that the $3,702,025 price at cell number D159 was intended to be included in the firm's subtotal price. "

Also at: http://www.pubklaw.com/rd/gao/2003/B-2925552.html Risk: loss of business

Avoidance: do your own checking rather than relying on post-hoc correction leniency

Advice: Register Now! for the EuSpRIG 2012 Conference

024) Computational error in performing complicated calculations

October 2003, Yahoo! news Source: http://biz.yahoo.com/prnews/031030/dcth018_1.html

Fannie Mae filed a Form 8-K/A with the SEC amending their third quarter press release to correct computational errors in that release. "There were honest mistakes made in a spreadsheet used in the implementation of a new accounting standard. The bottom line is that the correction has no impact on our income statement, but resulted in increases to unrealized gains on securities, accumulated other comprehensive income, and total shareholder equity (of $1.279 billion, $1.136 billion, and $1.136 billion, respectively)... the correction had to do with a computational error in performing complicated calculations required in the implementation of FAS 149." Risk: Loss of shareholder confidence.

Avoidance: Peer review

Advice: Register Now! for the EuSpRIG 2012 Conference

023) Somebody actually checked a spreadsheet

October 2003, The Tennessean

Source: http://www.tennessean.com/education/archives/03/10/41323597.shtml?Element_ID=41323597 Education Networks of America (ENA) operates the Internet network connecting schools across Tennessee. ENA twice won multimillion-dollar state contracts with bids that appeared higher than others. Jacqueline Shrago, who was the Education Department's project manager, said the ISIS 2000 bid appeared cheaper until costs for all three and a half years were computed. Looking more closely, it appeared that ISIS had made an error on a spreadsheet and that the bid actually was higher than ENA's, she said. 'We asked questions in the protest period, and they would not give an answer,' Shrago said. She said ISIS never explained, either, to federal agencies, which fund a large part of the schools computer system. So ENA got the contract. Risk: Mis-awarded contracts, legal challenges.

Avoidance: As above - don't believe that a spreadsheet is always accurate

Advice: Register Now! for the EuSpRIG 2012 Conference

022) Spreadsheet variance at MCI

Oct. 10, 2003 Telecommunications News PRNEWSWIRE

Source: http://www.prnewswire.com/cgi-bin/stories.pl?ACCT=SVBIZINK1.story&STORY=/www/story/10-09-2003/0002033002&EDATE=THU+Oct+09+2003,+12:28+PM

Watchdog Calls for Further Investigation of MCI

The August 2003 Department of Defense IG report states, "... many of the invoice amounts listed on the MCIWorldCom spreadsheet were inaccurate. Specifically, 467 invoices on the MCIWorldCom spreadsheet differed from the hard copy invoices by $2.1 million. Last month, The New York Post reported a former MCI employee's claim about over-billing at the State Department: "It was wrong and I regret it. We were over-billing the government $20,000 a month on one circuit. [The order to overcharge] came down from a vice president and through three layers of management." Risk: Fraud

Avoidance: Independent audit

Advice: Register Now! for the EuSpRIG 2012 Conference

021) Excel-COBOL transfer loses voter data

Source: http://www.ddtonline.com/articles/2003/10/08/news/news2.txt

October 2003, Delta Democrat Times

The city has a three-step process for elections: The first part was getting the voting books from the county and checking them. After that step was completed, the names were added to an Excel spreadsheet in the city's computer. "The information from Excel had to be entered into another database, COBOL. Apparently what happened is that when the rolls were printed, all the information did not get transferred," Alexander said. "Entire neighborhoods were left off of the voter rolls." Risk: Legal challenges

Avoidance: cross-checking import and export data (e.g. by hash totals)

Advice: Register Now! for the EuSpRIG 2012 Conference

020) $12M Error inflates school impact fees in Florida

September 12, 2003 Orlando Sentinel

Assistant County Manager Cindy Hall, in a memo to commissioners on Thursday, wrote that the April 22 study by Henderson Young & Co. duplicated the cost of building a new elementary school. The extra $12 million cost was corrected on a spreadsheet in the study, but it wasn't later adjusted on the total cost of school projects for the next five years. Jim Drake, director of finance for Lake County Public Schools, said: "It was basically a simple spreadsheet error. But obviously it's going to have an impact on building new facilities." County Commissioner Jennifer Hill said the consultant was given the final numbers for its study from the School Board only a few months before the study's completion, "It was rush, rush, rush," she said. Hill called the mistake "a simple mathematical error". Risk: Public disquiet and budget management problems.

Avoidance: Check your work

Advice: Register Now! for the EuSpRIG 2012 Conference

019) Spreadsheet Fraud

Aug. 27, 2003 Kansas City

Source: http://www.kansascity.com/mld/kansascity/business/6632965.htm

Two ex-HealthSouth executives plead guilty to conspiracy, fraud (Associated Press) Earnings were overstated by at least $3.5 billion. "This investigation is not about a mere accounting fraud, but rather a business scheme to fraudulently boost HealthSouth's reported earnings," said U.S. Attorney Alice Martin in a statement. Brown pleaded guilty to conspiracy to commit securities fraud. He admitted preparing a false spreadsheet for auditors that inflated HealthSouth's assets and made the company appear to be worth more that it was. Risk: Fraud

Avoidance: Independent audit

Advice: Register Now! for the EuSpRIG 2012 Conference

018) $24-million spreadsheet "clerical error"

Source: http://www.theregister.co.uk/content/67/31298.html

June 03, 2003 TORONTO (Reuters) - TransAlta Corp. said on Tuesday it will take a $24 million charge to earnings after a bidding snafu landed it more U.S. power transmission hedging contracts than it bargained for, at higher prices than it wanted to pay.

[...] the company's computer spreadsheet contained mismatched bids for the contracts, it said. "It was literally a cut-and-paste error in an Excel spreadsheet that we did not detect when we did our final sorting and ranking bids prior to submission," TransAlta chief executive Steve Snyder said in a conference call. "I am clearly disappointed over this event. The important thing is to learn from it, which we've done."

As New York ISO rules did not allow for a reversal of the bids, the contracts went ahead.

Also at http://www.siliconrepublic.com/srep.ic/login.html?storyid=single1576 Risk: Huge financial loss

Avoidance: Peer review, double-checking outputs

Advice: Register Now! for the EuSpRIG 2012 Conference

017) Economic Testimony - The Good, the Bad, and the Ugly

Article: http://www.rrc-inc.com/articles_economic_testimony.php (undated) "There are times in which the economic expert is either unprepared or led into difficult positions. Likewise, there are times in which the cross examination opens the door for further damaging economic testimony. Over the years, we have compiled a list of some of the goofs and some of the successes among economic experts we have observed. ". What spreadsheet? During trial the plaintiff's attorney during cross examination focused upon the computer spreadsheet used by the defense's economic expert to allegedly prove that there were zero economic damages. The expert had relied heavily upon research assistants to construct the spreadsheet, but under the expert's supervision. Unfortunately, the expert had not carefully examined the calculations and was not aware of several calculation errors. The spreadsheet was blown up as a trial exhibit, along with an alternate spreadsheet with corrected figures. The expert's testimony at trial was inconsistent with the corrected spreadsheet. The expert was forced to acknowledge to the court the calculation error, and tried to explain it was not a serious one. Yet it was too late. The jury lost confidence in the testimony. The jury awarded the plaintiff more than $12 million in damages. Did you copy all the way down? A plaintiff's economic expert was claiming that damages were to accrue over an extended period of time due to a permanently damaged reputation. Due to an incorrect use of a copy command in the spreadsheet, the lost profits after the tenth year of the projection became discontinuous, abruptly decreasing in one month, then following a smooth trend upward though the remainder of the time horizon. The economist had revised the spreadsheet, but failed to copy the revision down all cells, stopping after ten years. The remainder of the projection used the unrevised formula which abruptly took the profits into the negative regions. Since the profits were projected for a surgeon, it made little economic sense that a surgeon would continue practicing for several years with a negative income. Upon questioning in the deposition, the expert could not quickly figure out the reason for the questions and admitted that, if the projection showed it, it was quite possible for profits to be negative for short periods of time. Problems and inconsistencies could have been detected by the expert by simply graphing the result. However, the expert chose not to admit the possibility of a mistake in a deposition and provided damaging testimony instead.

016) Reportable condition: $1.5M

http://www.rdc.noaa.gov/~finance/DOC%20Mgt%20Ltr.pdf

Dec 27, 2002 "During our FY 2002 audit of the Department's consolidated financial statements, we noted certain matters involving internal control over financial reporting and its operation that we consider to be reportable conditions under standards established by the American Institute of Certified Public Accountants The amount recorded as a grant accrual in CAMS disagreed with the grant accrual spreadsheet, resulting in a $1.5 million understatement of grant accruals. This difference occurred because of an error in the spreadsheet formula. " Risk: Reported breach of standards

Avoidance: Check formulas

Advice: Register Now! for the EuSpRIG 2012 Conference

015) Spreadsheet penalties

August 12, 2002 - in a regulated industry, the process is as important as the product:

Source: http://tis.eh.doe.gov/enforce/eas/EA-2002-03WS.pdf

Preliminary Notice of Violation and Proposed Imposition of Civil Penalty $137,500 ... quality assurance issues affecting nuclear safety surrounding the discrepant nondestructive assay (NDA) data provided to Bechtel Hanford, Inc., in support of their decontamination and dismantlement activities at Building [ ]. Contrary to the above, between September 1998 and May 2001, work was not performed to established standards and controls using approved procedures. Examples include the following

... the modification to the spreadsheet was not subjected to validation

... the revisions to the spreadsheet used in the [ ] NDA process were not uniquely identified and labeled

... several deficiencies related to the use of NDA applicable spreadsheets Risk: Fines and penalties

Avoidance: Document and archive your work

Advice: Register Now! for the EuSpRIG 2012 Conference

014) IRR or XIRR - periodic or not?

Source: http://www.blonnet.com/ew/2002/07/10/stories/2002071000010300.htm

V. Krishnaraj from THE HINDU, Jul 10, 2002 "Users often fail to realise the implication of Spreadsheets they develop. There is no third party independently testing whether these sheets work correctly. In course of time, they get used unquestioningly with nobody cross checking the validity of calculations. We've never come across a company validating software embedded in Spreadsheets. " "To illustrate, here's a case we encountered with a corporate that was showing its procedures to be supported by well-documented Spreadsheets. Taking a deeper look at their sheets, we were surprised to find them using a wrong function for IRR calculation (Tip: There are 2 Excel IRR functions IRR() and XIRR(). XIRR() handles non-periodic cashflows while IRR() can handle only periodic cash flows). Over the last couple of years, the top management has been looking at reports with this wrong computation!! We can't imagine what decisions were made using these numbers. "

013) Debating association out of sorts

Source: www.debating.net/aida/Results/Easters%202002%20Team%20Report.htm 2002: Australasian intervarsity debating association

"The major error in the tabulation was introduced sometime after the draw for round two was announced, but before results from round two had begun to be entered into the tab spreadsheet. The data in the spreadsheet was re-sorted, so that the teams were listed alphabetically, but, mistakenly, not all columns were sorted. " Risk: Making the competition results invalid, requiring all or part it to be re-run

Avoidance: Team work and peer review

Advice: Register Now! for the EuSpRIG 2012 Conference

Source: http://www.foodinc.co.nz/Software~Archive/Nutrition%20software%20summary%20(9).PDF

End-2002, Anny Dentener reviewed several nutrition software packages for their suitability to provide information needed under the new ANZFA nutrition panel requirements. She says "Overall, spreadsheets are cheap to run but can be time-consuming and error prone. A typical spreadsheet error spotted was for instance a burger chain understating % energy from fat exactly by the factor difference between a large fries, and 100 gram" Risk: believing that external data sources are correct

Avoidance: If your business depends on it, validate bought-in data

Advice: Register Now! for the EuSpRIG 2012 Conference

Source: http://www.ed.gov/about/offices/list/oig/auditreports/a07c0009.doc 2002: Audit Of The Colorado Student Loan Program's Establishment And Use Of Federal And Operating Funds

"CSLP made an error in calculating the amount needed to establish the Operating Fund. We found a $36,131 transfer to the Operating Fund that was not supported. CSLP explained that it had made an error on the spreadsheet CSLP used to calculate the beginning balance of the Operating Fund. A formula in the spreadsheet picked-up the date, 12/02/98, and interpreted it as a dollar amount, resulting in an error of $36,131. As a result, the beginning balance of the Federal Fund was understated by $36,131. We calculated that CSLP owed the Federal Fund an additional $8,884 in imputed interest on this amount calculated through March 31, 2002. Not correcting such an error would be inconsistent with sound business practices, as required by 34 CFR § (a)(11)(iii)(B)."

"We recommend that the COO for FSA require CSLP to Reimburse the Federal Fund for the remaining $1,172 in imputed interest on the spreadsheet error calculated through March 31, 2002. " Risk: wrong data type used

Avoidance: Check type consistency

Advice: Register Now! for the EuSpRIG 2012 Conference

Source: http://www.leg.state.nv.us/71st/Interim/StatCom/Audit/Minutes/IM-Audit-20020127-307.html AUDIT SUBCOMMITTEE OF THE LEGISLATIVE COMMISSION January 17, 2002

Department of Motor Vehicles and Public Safety, Administrative Services Division

"ASD did not have current policies and procedures to help ensure revenue distributions were correct, and controls over supervision also need strengthening. Spreadsheets used to distribute tax revenue to the State's General Fund contained a formula error. Consequently, from October 1999 through May 2001, approximately $9.5 million in sales tax revenue was not distributed to the General Fund. Although this spreadsheet error was corrected in June 2001 and the $9.5 million was transferred to the General Fund, internal controls are still weak. Consequently, further spreadsheet errors could occur and go undetected. " Risk: weak controls

Avoidance: Follow up audits by strengthening controls

Advice: Register Now! for the EuSpRIG 2012 Conference

009) Accounting error forces bank to $3b write-down

Source: http://www.abc.net.au/pm/s357092.htm

PM Archive - Monday, 3 September, 2001

"The National Australia Bank wrote down the value of its US mortgage business HomeSide Lending by a massive AUS$3 billion. The news triggered a free fall in the NAB's share price that knocked more than $6.5 billion of the bank's market value. Contributing to the write down was an incorrect interest rate assumption fed into HomeSide's financial modelling. This alone has cost the lender's $755 million. A selling spree knocked more than 13 per cent of the value of NAB shares." Risk: Huge financial loss

Avoidance: Peer review, double-checking assumptions

Advice: Register Now! for the EuSpRIG 2012 Conference

008) The role of spreadsheets in the AIB/Allfirst currency trading fraud

Source: http://www.gre.ac.uk/~cd02/eusprig/2001/AIB_Spreadsheets.htm 2001

Allfirst "Would not pay the US$ 10,000 for a direct data feed from Reuters to the risk control section". Instead, they got Rusnak to download his Reuters feed into a spreadsheet. He then substituted links to his private manipulated spreadsheet. The total losses hidden by the fraud were almost US$700M. Rusnak exaggerated bonuses by over half a million dollars. Ray Butler points out in the article above that "One error in a spreadsheet will subvert all the controls in all the systems feeding into it". An auditing tool (SpACE) would have found the external links in the key spreadsheet. Risk: Fraud, huge financial loss

Avoidance: Peer review, double-checking outputs

Advice: Register Now! for the EuSpRIG 2012 Conference

007) Using a spreadsheet to catch a spreadsheet error

Source: http://www.irlgov.ie/committees-00/c-publicaccounts/000926/page2.htm

September 2000: An extract from the proceedings of the Public Accounts committee in Ireland:

"The second error was discovered during the process [...] of preparing the council's defence against the claim made by SIAC in the initial tender assessment. The bills of quantities and the rates were inserted at that stage on a spreadsheet for the purpose of putting them in electronic form and checking them. The error showed up during that process many months later. Section 05 - drainage and service ducts - the £42,000 should have been carried forward but someone misread it as a title head as distinct from being a summary of another page. It was as simple and unbelievable as that, but that is what happened. As the county engineer has reminded me, it happened in two tenders and it was not seen by us in the checking process. "

Acting Chairman: ...If we accept that there will always be errors in tenders and, in this instance, that human error has cost the Exchequer so much money, it is obvious that change is needed, particularly in view of the fact that a compensation culture has come into existence. Risk: Public disquiet and budget management problems.

Avoidance: Check your work

Advice: Register Now! for the EuSpRIG 2012 Conference

Source: http://oig.hhs.gov/oas/reports/region4/40202016.pdf Spreadsheet Errors

"The FY 1999 Medicare/Medicaid crossover log contained calculation errors, resulting in the log being overstated by $38,240. The hospital recorded the Medicare bad debts for patients who also qualified for Medicaid on an electronic spreadsheet. Each month the spreadsheet was updated with additional Medicare/Medicaid crossover patients. The spreadsheet contained a summation function, allowing various cells to be added to compute a total. The hospital inadvertently included some cells in a subtotal and also added them a second time in the grand total. In addition, the hospital inadvertently omitted one subtotal from the grand total. These errors resulted in the summary totals being overstated. " Risk: Audit embarrassment.

Avoidance: Cross-check totals

Advice: Register Now! for the EuSpRIG 2012 Conference

005) "Strict liability" means no excuses

http://www.epa.gov/oalj/orders/vemco-pad.pdf 1999: "Respondent explains that many of the violations alleged were the result of mistakes in formulas used to calculated materials usage and emission rates. Respondent states that it 'is prepared to demonstrate at hearing that these formulas were in a typical spreadsheet format, that once these formulas are developed, they are opaque to the users of the spreadsheets, and that during the time the error persisted, Vemco did not know or have reason to know that it was violating its permit requirements.' "[Discussion] With strict liability, factors of intent, good faith, willfulness or fault are not relevant to a finding of liability for violations. Therefore, Respondent's arguments that it intended to comply, did not know of the violations, and made an innocent mistake are not defenses to liability. These arguments, and the assertion that errors in calculating emissions were discovered, reported and corrected by Respondent, may be relevant to assessment of a penalty, but are not relevant to the issue of liability." Risk: fines

Avoidance: realise that mistakes are not always treated leniently

Advice: Register Now! for the EuSpRIG 2012 Conference

004) $100 million here or there...

http://canadagazette.gc.ca/partII/2003/20030312/html/sor79-e.html CANADIAN ENVIRONMENTAL PROTECTION ACT, 1999

Estimated Incremental Capital and Operating Costs, 2002 to 2016 The total cost number and its present discounted value are about $100 million less than the original estimates pre-published in the Canada Gazette, Part I. This difference is the result of a spreadsheet mistake that was detected at the time the 1999 estimates were updated. Risk: loss of credibility in estimates and projections

Avoidance: perform an alternate analysis for comparison

Advice: Register Now! for the EuSpRIG 2012 Conference

003) Spreadsheet Blues

Source: http://lims.taratec.com/lims/199806/msg00048.html

June 1998 LIMSList- Spreadsheet blues

"From my perspective as a NAMAS assessor (ISO Guide 25) and working in the pharmaceutical induistry I think that speadsheets are the highest risk of any application. When you read on you will see why....

A pharamceutical quality control laboratory used an Excel sporeadsheet to calculate the percentage dissolution from tablets. To improve efficiency the Excel guru (should that be gnu?) programmed a macro. The whole data supporting a product licence application was calculated with this macro. The package was submitted......

Then somebody had a good idea: should we validate the macro? To cut the story short, there was a problem with the macro and there was a gross error in the calculation: some batches that were in were now out of specification. The 277 page report that was prepared to explain this fiasco delayed the approval by several months. Calculate the cost of an average drug sale at $1 million per week or more (V1@gr@ excepted) and you have the approximate cost of the error. " Risk: non-compliance with regulations, fines, penalties, wasted management time

Avoidance: check all the work including the hidden features like VBA code

Advice: Register Now! for the EuSpRIG 2012 Conference

002) The missing minus sign

Source: http://catless.ncl.ac.uk/Risks/16.72.html

Jan. 1995: Computing error at Fidelity's Magellan fund

"There was a big flap recently over Fidelity's Magellan fund estimating in November that they would make a $4.32/share distribution at the end of year, and then not doing so. A letter of explanation was sent to the shareholders (including me) from J. Gary Burkhead, the President of Fidelity, including the following pertinent items: "During the estimating process, a tax accountant is required to transcribe the net realized gain or loss from the fund's financial records (which were correct at all times) to a separate spreadsheet, where additional calculations are performed. The error occurred when the accountant omitted the minus sign on a net capital loss of $1.3 billion and incorrectly treated it as a net capital gain on this separate spreadsheet. This meant that the dividend estimate spreadsheet was off by $2.6 billion.... Risk: loss of share value

Avoidance: independent review and comparison of source with output

Advice: Register Now! for the EuSpRIG 2012 Conference

001) Archive of case studies 1984-1995