cfo.com

cfo tagline

Spreadsheet "Worst Practices"

Here's how finance executives abuse the most-useful of computer programs — and how to do better.

Shahid Ansari, CFO.com | US
May 14, 2008

Cutting and pasting errors

A large source of errors in the Enterprise use of Excel is the cutting and pasting of data in an effort to merge supplied info. We have found Distributed Spreadsheet eliminated the cut & paste process and has reduced our error rate.

Posted by Mark Ryan | October 03, 2008 09:15 pm

Spreadsheets link in article.

How can get access to the spreadsheets link at the end of the article? The link doesn't seem to work anymore.

Posted by John Purcell | September 03, 2008 11:44 am

Managing Scenarios

Messrs Ansari and Block make several good suggestions for improving the usefulness and accuracy of spreadsheets. With regard to their comments in the "Now it's There; Now it's Not" section there is a better alternative to creating separate worksheet tabs of various scenarios. Excel has "Scenario Manager" (in 2007 DATA > What-if Analysis > Scenario Manager) in which the user documents various assumptions underlying the model. The analyst can then view the results of each scenario (while retaining the original results) or have a comparative report automatically generated.

Posted by James Weisel | June 26, 2008 03:02 pm

spread sheet standards may become a regulatory requirement

I have been an auditor for thirty years and although I cant say I have seen it all I can comfortably say I have seen plenty as far spread sheet sloppiness. One advantage of spread sheets its is flexibility and ability to personalize this in turn can be a disadvantage if one is preparing a spread sheet that must meet certain conventions and or legal requirements. You would think that common sense would dictate a minimum of structure that one would expect and in turn use in the preparation of spread sheet, unfortunately even when you have official structure as a requirement , you will find significant deviations. One solution is to publish a set of standards with detailed structure and requirements for the company . I have strived all my professional career to encourage this but have encountered strong opposition. One saving grace is that under Sarbanes Oxley IT documentation including Spreadsheets can come under scrutiny particularly if they provide key information such as calculation estimates to mention one that may have material effects on the financial statements. perhaps this will be the impetus to better spread sheet design and preparation

Posted by Eduardo Casas | June 26, 2008 12:21 pm

spread shet standards may become a regulatory requirement

I have been an auditor for thirty years and although I acnt say I seen it all I can comfortably say I have seen plenty as far spread sheet sloppiness. One advantage of spread sheets its is flexibility and ability to personalize this in turn can be a disadvantage if one is preparing a spread sheet that must meet certain conventions and or legal requirements. You would think that common sense would dictate a minimum of structure that one would expect and in turn use in the preparation of spread sheet, unfortunately even when you have official structure as a requirement , you will find significant deviations. One solution is to publish a set of standards with detailed structure and requirements for the company . I have strived all my professional career to encourage this but have encountered strong opposition. One saving grace is that under Sarbanes Oxley IT documentation including Spreadsheets can come under scrutiny particularly if they provide key information such as calculation estimates to mention one that may have material effects on the financial statements. perhaps this will be the impetus to better spread sheet design and preparation

Posted by Eduardo Casas | June 26, 2008 12:20 pm

The More Things Change ...

As the old saying goes 'The more they stay the same'. I recall during my Sophomore year in college I was looking down my nose aghast at my management accounting professor as he declared that 25% of the grade on exams, papers and projects would be based on having our work / answers supported well organized, clear & concise calculations & tables, etc. He growled at us: 'I can't waste my time trying to figure out how you arrive at a number or what the point is your trying to get across if you give me a bunch of crumbled up papers full of 'scribble' or something that isn't organized and concise. If you think this will succeed in the workplace, don't pursue an accounting / finance career.' He backed up his position with war stories from his days in public accounting and as a VP-Finance / CFO and how he would send his lieutenants & foot soldiers back to trenches after a glance at poorly designed, vague, incoherent schedules and presentation slides and how he put his reputation on the line by presenting these to his I walked away with a very valuable nugget of career advice. Spreadsheets, presentations, workpapers, etc., whether prepared with pencil & paper (in those days) or electronically today, are critical communication tools. Each has a 'story' to tell and point to make. It's construction must be undertaken to allow it's message to reach it's target audience, clearly, concisely and persuasively. Otherwise, in my sage's words, 'your wasting your time and the time of important people who hold the keys to your career advancement. Very true. We've all seen our share of meetings / work sessions / presentations, etc. where the 'wheels come off' because the presentation materials don't attract the audiences attention (or just as worse, allow people's busy minds to wander off to other topics / issues). If not effective communication, opportunity lost. Like all messages, spoken, written or typed, keep it focused on the 'customer.'

Posted by Christopher Thurner | June 24, 2008 06:52 pm

Step 1 of formula integrity

I have found the most basic, often-forgotten rule in building spreadsheets that are populated with complex formulas is the cross check. It is very easy to get deep with your lovely data design, only to find the rows and columns do not foot. This is so common that I now refuse to review spreadsheet work where the footing cell is not visible. I no longer waste time on work that is fatally flawed.

Posted by Patricia Chapman | June 20, 2008 09:09 am

Most common error

The most common error I have encountered with poorly trained analysts and accountants is manually adding numbers to a calculated cell in a spreadsheet (without documentation or notes that the overide is there). When people go to update the spreadsheet the next week or next year, they forget the manually overriden formula, and it generates incorrect results. Since the spreadsheets are designed to calculate for you, there usually is no proof that you are out of balance. I have been embarrased numerous times with spreadsheets like this and have instituted the following controls: I have found it best to do two things 1) Training: Clearly instruct all staff on the issue and call them out on the error if I ever spot manually overriden numbers where there should just be a formula. This usually fixes the problem. 2) where possible at the bottom of totalled columns, I will have a "diff" row just below the total where the formula compares the result of the formula, and what the answer should be, perhaps on another sheet within the workbook, or perhaps through "cross footing" the rows and columns.

Posted by Walter Lybeck | June 18, 2008 06:17 pm

Figures 2a thru 3d missing

The article on "Spreadsheet Worst Practices" refers to figures that are not on the website article. It would be helpful to have these.

Posted by Mary Reece | June 02, 2008 06:33 pm

A Different Approach: Quantrix

Anyone noting the difficulties with spreadsheets may which to check out Quantrix, an alternative to traditional spreadsheets,developed by the Maine Company Subx. Quantrix was based on the old Lotus Improv, rewritten to take advantage of new development tools. An example of the difference is the removal of arbitrarily labeled cells and the automatic residency of formulae outside the cells. The downside is that its not as intuitive as Spreadsheets and a license for the full version is about $1,000. (I have no interest in or connection with Quantrix).

Posted by Super Heater | June 01, 2008 01:32 am

There are tools out there to help

Documenting changes in spreadsheets where notes are kept with the versions, as well as the ability to compare different versions to easily spot the changes. Of course there are pitfalls in the use of Excel, but the ease of use means that spreadsheets are here to stay. http://www.lyquidity.com

Posted by Seddon | May 30, 2008 02:14 pm

Problems Even More Basic

Even before getting to the spreadsheet construction and calculations, I've encountered more people than I ever expected who seem to forget to put a heading on a spreadsheet that is to be distributed and to include a title that is at least somewhat discriptive of the purpose of the spreadsheet. This has included both new staff and experienced professionals who just developed sloppy practices in their previous assignments. For some reason, they assume that other readers will automatically understand the purpose and that if they have to refer to the spreadsheet again next week, next month, or next year, that they will also remember the purpose.

Posted by Richard Archer | May 29, 2008 10:41 pm

Best Practice Spreadsheet Modelling Standards

A comprehensive set of Best Practice Spreadsheet Modelling Standards and Conventions are available at www.ssrb.org. The Standards aim to promote discipline and consistency amongst spreadsheet model developers. They Standards are quite comprehensive and can be applied to any spreadsheet modelling task.

Posted by Nicolas Boston | May 29, 2008 07:39 pm

Spreadsheet Risk Management

You may also find this article useful http://blogs.zdnet.com/Berlind/?p=943

Posted by Ralph Baxter | May 22, 2008 11:53 am

Spreadsheet Best Practices for Enterprises

While this article does an excellent job of describing some important things to consider when creating a spreadsheet, it doesn't cover the *use* or content risks of a spreadsheet, which is another layer that is often of critical focus for enterprises. Here's a link to a white paper that may be helpful: http://www.compassoft.com/5steps.aspx

Posted by Peter Gaylord | May 16, 2008 04:41 pm

Other sources of best practices

The European Spreadsheet Risk Interest Group (EuSpRIG) is having a conference on July 10&11 this year to discuss these very issues. Their website http://www.eusprig.org contains downloadable documents with recommended practices. Their sponsor this year is Spreadsheet Safe, http://www.spreadsheetsafe.com Patrick O'Beirne http://www.sysmod.com

Posted by Patrick Obeirne | May 16, 2008 10:30 am

Spreadsheet Best Practices

The best way to compare changes in data in Excel is to use Scenarios. That easily lets the user switch between different data sets and can then create side-by-side comparisons to summarize the differences. Much easier than keeping track of separate sheets - especially if formulas change in those sheets. No need to update multiple documents.

Posted by Dawna Bate | May 15, 2008 10:21 am

What is the question

Apparently the directive was to do one thing, produce a quick estimate. That was done. The directive was not to produce detailed documentation about constraints and data sources, etc. That was not done. It flows back to the original directive.

Posted by Roland Cycan | May 15, 2008 09:28 am

Boon or Bane ?

Very well written, pragmatic article. Spreadsheets have been a great tool for the finance and accounting department. However, they need to be carefully constructed for long term use rather than for short term benefit. Cells and important formulae should be properly protected.

Posted by Chandrasekar Venkataraman | May 15, 2008 07:36 am

CFO Publishing Corporation 2009. All rights reserved.