cfo.com

cfo tagline

Sloppier Spreadsheets: How Bad Can They Get?

Readers weigh in again on Excel sins and solutions. Tips include totaling at the top, while traps like poor version control can even threaten to force restatements.

Roy Harris, CFO.com | US
August 20, 2008

Safer Spreadsheets

Some other resources to reduce the incidence of sloppy spreadsheets in 2009: Jason Karaian's article "Spreadsheets: Formula for Success" reports on the 2008 conference of the European Spreadsheet Risks Interest Group (Eusprig). The next is 2-3 July 2009 in Paris. Their site www.eusprig.org has free PDF downloads on good practice guidelines including Phil Bewig's succint 'How do you know your spreadsheet is right'? For those sseking more assurance, there is a Spreadsheet Safe certification, see www.spreadsheetsafe.com To declare my own interest, I am the author of 'Spreadsheet Check and Control' which describes 47 Excel techniques to assure spreadsheet quality. Thanks to all who contributed to the CFO threads. If you would like to take it further, Eusprig has a discussion forum at http://tech.groups.yahoo.com/group/eusprig/

Posted by Patrick Obeirne | December 31, 2008 06:09 am

Conditional formatting

I utilize excel's conditional formatting tool to make data (or results) stand out. I recommend this tool as an effective way to quickly bring attention to values over or under a certain threshold - such as growth rates or returns.

Posted by Gerald Langone | December 30, 2008 01:54 pm

Tick and Bop

"Rows Don't Foot" ??? 1) I am unfamiliar with the term "rows don't foot". Could you please elaborate? Posted by Cynthia Berndt | Sep 2, 2008 2:27 PM ET Footing just means adding. One seldom sees this language anymore, though it was still fairly common in the 70's when I articled. As an audit junior, one was given the task to "tick and bop" (the former being a mark you made on the page, the latter being the motion you made with your head). One of the things checked was that reports footed and cross footed ie. Added down and across.

Posted by Paul Makulski | November 19, 2008 01:49 pm

Taxonomy

I have found a very simpel set of rules do wonders for making spreadsheets useful. For example, a summary tab, all input cells are color coded in one color used by the entire group, or 'what if' variables color coded in another unique color. When used consistently, the rules are very helpful in passing information among the group, with some understatnding of the structure and thought process of the person producing the sheet.

Posted by Mark Eckman | September 29, 2008 02:18 pm

Excel 2007

If you want to read an Excel 2007 spreadsheet in an earlier version, download a free utility from Microsoft that will allow you to read the file.

Posted by Richard Wendt | September 02, 2008 06:43 pm

Tips: Totals at the Top

If you are refreshing the data in your spreadsheet, the top left cell is your starting point to populate the sheet to the right and to the bottom of the page. Control totals at the top of the page (or to the left of your starting cell) do not get overwritten by data refreshing and you do not have to constantly manually add back in your control formulas.

Posted by Amy Alost | September 02, 2008 03:37 pm

"Rows Don't Foot" ??? and Hyperlinks

1) I am unfamiliar with the term "rows don't foot". Could you please elaborate? 2) I have found that using a "Table of Contents" worksheet with Hyperlinks to important worksheets/cells works well. Each of the other worksheets also has a Hyperlink back to the "Table of Contents" to facilitate moving around the workbook.

Posted by Cynthia Berndt | September 02, 2008 02:27 pm

Using Database with Excel

I find that using an external database (eg, Access, Applix/TM1, or open source tools like Jedox/Palo) and controlling the quality of the data there is very effective at reducing errors. Part of the problem with spreadsheets is that the data and the format for the reporting/analysis are in the same place. Using an external database with Excel as a reporting front-end keeps the file size down and lets the spreadsheet maker concentrate on the logic and format clarity, rather than wasting time pasting or rekeying data.

Posted by Roy Magruder | August 28, 2008 01:08 pm

Sending files with links

There were two related comments about sending files with too much data or sending files with links to files the receiver does not have access to. One of the suggested solutions was to copy and paste the cells with the critical information. My solution is to create a summary tab that links to the backup detail then just copy the whole tab to a new sheet then break the links using Edit/Links/Break Links. This way I maintain the integrity of the supporting details without burdening my reader with an over size file and a bunch detail that will serve to confuse them. Like wise if I receive a file with links to files I do not have I use the break link and save the file.

Posted by Marshall Griffin | August 22, 2008 05:15 pm

SOX hazards

Spreadsheets in general can be a problem in regard to SOX. Some of the big complex sheets need to be replaced by database applications. Before you get arrested.

Posted by Steve Hovland | August 22, 2008 10:36 am

CFO Publishing Corporation 2009. All rights reserved.