By David Parmenter

The 10 common problems with spreadsheets are:

1. Broken links or formulas: An individual may add or eliminate a row or column so when a group of spreadsheets are rolled up, the master spreadsheet is taking the wrong number from the one that was modified.
2. Consolidation errors: I say to attendees that Excel is one of the few applications that can make a grown person cry. Often, a spreadsheet will lock up or show a screen full of “REF”, “REF” “REF” errors, because it was not designed to be a tool for handling a rollup of dozens of different worksheets.
3. Input of the wrong numbers: Entering the wrong number can happen in any process, but spreadsheet-based systems often require re-keying of information, which can produce data inconsistencies. A spreadsheet might use a look-up table that may be out of date or an entry might have been inadvertently or mistakenly overwritten. A spreadsheet may require a cost to be entered as a negative number or a positive number. Rekeying a cost form one spreadsheet to another can mean the sign is the wrong way around.
4. Incorrect formula: A subtotal might omit one or more rows, columns or both. An individual might overwrite a formula because they believe theirs is more accurate. Or, someone might use an outdated spreadsheet. Or, allocation models might not allocate 100 percent of the costs.
5. Hidden rows and columns: This common option should be banded in all your spreadsheets
6. No proper version control: Using an outdated version of a spreadsheet is very common
7. Lack of robustness: Confidence in the number a spreadsheet forecast churns out is not assured. Many times you cannot check all the formulas because they can be found in any cell of the spreadsheet.
8. Inability to accommodate changes to assumptions quickly: What would you do if the CEO asks “If we stopped production of computer printers what would be the financial impact? I need the answer at the close of play today”. Your spreadsheets are not able to provide that quick answer.
9. Design is by accounting staff who are not programmers: Most accounting staff have not been trained in system documentation, quality assurance, which you might expect from a designer of a core company system.
10. Lack of corporate office control: Many people in a business can use spreadsheets to create their own forecasts at a ridiculous level of detail. This can lead, as a friend once said to me, “To the march of a million spreadsheets.”

Rule of 100 rows

I believe you can build a forecasting model in a spreadsheet application and can keep it within 100 rows without much risk. Pass this threshold and you expose yourself, your finance team and the organization. Forecasting requires a robust tool, not a spreadsheet that was built by an innovative accountant and that, now, no one can understand. I always ask in workshops, “Who has a massive spreadsheet written by someone else that you have to pray before you use it?” You can see the pain in the instant response. Most people know that the person who built the spreadsheet certainly was not trained in operational systems design. The workbook will be a collage of evolving logic that only the originator has a chance to understand.

Often, the main hurdle is the finance team’s reluctance to divorce itself from the spreadsheet program. It has been a long and comfortable marriage, albeit one that has limited the finance team’s performance.

Some errors to remind you how your career can hang on some untested spreadsheets

Austerity cuts based on spreadsheet error

A famous 2010 academic paper, relied on by political big-hitters to bolster arguments for austerity cuts, contained significant errors; and that those errors came down to misuse of an Excel spreadsheet. Reinhart and Rogoff’s work showed average real economic growth slows (a 0.1% decline) when a country’s debt rises to more than 90% of gross domestic product (GDP) – and this 90% figure was employed repeatedly in political arguments over high-profile austerity measures.  Three errors were identified.   The most serious was that, in their Excel spreadsheet, Reinhart and Rogoff had not selected the entire row when averaging growth figures: they omitted data from Australia, Austria, Belgium, Canada and Denmark. They had accidentally only included 15 of the 20 countries under analysis in their key calculation. When that error was corrected, the “0.1% decline” data became a 2.2% average increase in economic growth.

Barclay’s bank buys more than it expected

A spreadsheet, which had been e-mailed to a law firm representing Barclays Capital Inc, prepared by a Lehman representative, contained nearly 1,000 rows and more than 24,000 individual cells.   179 contracts were to be excluded from the agreement and these were hidden and not removed from the spreadsheet. The mistake happened late at night when the Excel document was resized (unhiding the cells) and then converted into a PDF document which was attached to the agreement.

Canadian power company TransAlta lost $24 million

This happened when an employee misaligned the rows in an Excel spreadsheet. The copy-and-paste error led to bids being aligned with the wrong contracts, wiping out 10% of TransAlta’s profit for the year with a quick click.

MI5 bugged the wrong phones

A formatting error on MI5’s list of phones to be tapped resulted in the agency’s tapping 134 people entirely unrelated to investigations. The formatting error changed the last three digits of these phone numbers to 000 within the spreadsheet. As a result, random British citizens had their phones tapped by their government while the suspects went unobserved.

Fidelity Investment’s minus symbol blunder

While transferring financial records onto an Excel spreadsheet, a tax accountant neglected to put a minus sign on the fund’s net capital loss of $1.3 billion. The loss was then calculated as a net capital gain, which resulted in dividend estimates being off by a staggering $2.6 billion.  The financial institution was forced to cancel the dividend distribution after discovering that their estimation was incorrect. 

Emerson Electric Co. under quotes

Emerson incorrectly estimated the total cost of one contract bid. They came up short by $3.7 million all because of a single spreadsheet cell. The cell that contained the cost of electrical was not included in the formula that calculated total expenses. One cell missed, millions of dollars lost.

C&C Group gets investor announcement the wrong way

C&C Group, the Irish drinks manufacturer of Magners cider had to change an investor announcement made a week earlier to explain that their total revenue had actually fallen by 5% not risen by 3% as first announced.  The error was found in a spreadsheet.  The change led to a fall in the share price by 15%.

Department of Transport to assess bids incorrectly

A new financial model had been deployed by the Department of Transport to assess bids by rival companies looking to operate the UK West Coast Rail Line. The Laidlaw Report found that fundamental mistakes in the communication of assumptions in the Department’s model led to rival bids being inconsistent and incorrect conclusions being drawn about the relative merits of the bids. Having awarded the new franchise to run the railway to First Group, a change in operator from the incumbent Virgin Trains, the UK Government was forced into an embarrassing and costly climbdown.

Cuts to spending announced incorrectly

The respected health think tank, the King’s Fund, had to announce a revision to their critical analysis of cuts in NHS spending in Wales on finding a calculation error in a spreadsheet. Although cuts of 8.3% remained, the reduction was 2.4% less than the 10.7% originally announced, lessening the impact of their critique & leading to red faces all round.

AXA Rosenberg get caught trying to hid a spreadsheet error

A spreadsheet error caused an over-estimate of client investment losses. Management then did not declare this mistake. As ever, it was the cover-up that got them, leading to a fine of US$242million for this global investment manager.

The need for more robust systems in finance teams, particularly forecasting tools are further explored in  three implementation guides(Whitepaper + e-templates). These implementation guides are on sale (over 40% discount).

How to Implement a Forecasting and Planning Tool – and get it right first time – implementation guide(80 page Whitepaper + electronic templates)

How to Implement Quarterly Rolling Forecasting and Quarterly Rolling Planning – and get it right first time – implementation guide(110 page Whitepaper + electronic templates)

Annual Planning (Budgeting) in Two Weeks or Less – implementation guide(80 page Whitepaper + e-templates)

To look inside the implementation guide  and   buy the implementation guide