I thought I'd share a few good sales spreadsheet horror stories for you to share at your next campfire
It is accepted as fact that spreadsheets are a dangerous way to manage sales compensation, especially in businesses that are highly competitive and growing. The advantages of Flexibility quickly become overcome by the disadvantages of lack of Scalability as the more you try to do, the more issues you begin to hit. In light of the recent macroeconomic debate about the role of the Fed in US monetary policy and how a spreadsheet error may undermine policies advocated by some voices in this debate, we thought we’d take a step back and share three stories published by the European Spreadsheet Risk Interest Group that highlight five problems common to spreadsheet based accounting systems. While these example do not discuss Sales Compensation specific mistakes; all of us are aware, or have heard of, versions of nightmares similar to the ones listed below…
Risk: Credibility
"In a paper, 'Does High Public Debt Consistently Stifle Economic Growth? A Critique of Reinhart and Rogoff,' Thomas Herndon, Michael Ash, and Robert Pollin of the University of Massachusetts, Amherst criticize a 2010 paper by Harvard economists Carmen Reinhart and Kenneth Rogoff, 'Growth in a Time of Debt.' They find three main issues: First, Reinhart and Rogoff selectively exclude years of high debt and average growth. Second, they use a debatable method to weight the countries. Third, there also appears to be a coding error that excludes high-debt and average-growth countries. All three bias in favor of their result, and without them you don't get their controversial result."
Spreadsheet Nightmare: At one point R&R set cell L51 equal to AVERAGE(L30:L44) when the correct procedure was AVERAGE(L30:L49). They left Denmark, Canada, Belgium, Austria, and Australia out of the average. When you fix the Excel error, a -0.1 percent growth rate turns into 0.2 percent growth.
Risk: Complexity outgrows the technology, spreadsheet becomes a monster!
“The town of Framingham mistakenly reckoned it had $1.5 million more in this year's budget than it actually has and must now use $600,000 in unexpected state aid to help fill that gap, officials said yesterday.
Spreadsheet too ‘Monsterous’ Chief Financial Officer Mary Ellen Kelley said she takes responsibility for the mistake, which she found Wednesday night on the debt services line item in the $208.6 million fiscal 2012 operating budget. She said a figure went missing as staff managed "monstrous spreadsheets."
Risk: Bad decisions based on flawed data
In a report considered by the authority's corporate governance committee on Monday (September 27) the head of finance and assets Roger Parry said the audit was virtually complete but a revised set of accounts had been prepared. "A significant error was made in the valuation of the council's assets," he said. "Along with other adjustments this has meant a £21m (3.5 per cent) reduction in the assets shown on the council's balance sheet. "The error was picked up internally prior to the audit commencing but after the draft accounts had been produced. "Now that the valuations department has moved into the finance department a full review of all the procedures is being undertaken and a significantly improved process will be implemented for the 2010-2011 accounts production."
A council spokeswoman explained later that the discrepancy had occurred because of an error on a spreadsheet where some assets had been double-counted.
Risk: Law Suit, Job & Career Security
Former Albemarle employees who say they've been cheated out of money by the county are considering taking legal action against the local government, according to former police Officer Ron Kesner. The county has offered employees monthly stipends in exchange for retiring early as a way to reduce personnel expenses. However, officials say a miscalculation caused them to offer 42 employees more money than intended. While the county is not making the employees pay back money they've received, the retirees are receiving less than they had been promised.
“All I want is what’s fair,” said Juanita Irvine, a former senior family support worker for the county’s social services department. “I made my decision to retire based on their figures.” Kesner said that affected retirees are hoping to resolve the discrepancy with county officials and are considering legal action only as a “last resort.”
Officials have estimated that the mistake cost the county $66,000 in over-payments; had the error not been corrected, the county would have been out $360,000. Questions about precisely how the stipends were miscalculated and who was to blame remain unanswered.
Kimberly Suyes, director of the county’s Human Resources Department, said there was a “miscalculation in the spreadsheet. There’s not much more.” An employee in Human Resources made the error. Suyes declined to say whether the employee who made the error was fired.
