From Janice Bell: I completely agree, and would like to add that "version control" of spreadsheets is a major nightmare when work has to be done quickly by several people in an organization. I not only use a file name that contains the date and my initials, but I also try to include that in a header on the printed copy.

From Richard Block: Spreadsheet version control is critical, and when a spreadsheet is shared by placing it on a common or shared network drive at work, lack of critical controls for use and updating can spell disaster for users and the company. Disasters such as delayed or restated 10-Q and 10-K filings can happen if shared spreadsheets aren’t managed, protected, and controlled properly. And they often are not.

Tip: Test It Yourself
To CFO.com:
How many people actually test their spreadsheets? With the formulas hidden, it's so easy to introduce a mistake and not even know it. With calculations spanning data on multiple worksheets, there are lots of moving parts and subtle linkages that are not apparent to the naked eye.

Case in point: we received a multi-page, long-range planning worksheet from a [venture capitalist.] In using it, I found and debugged errors introduced when the spreadsheet was passed on to someone else to "enhance." This is a common pitfall with complex spreadsheets: expecting the next person updating it to understand the thinking of the creator.

Many of us, myself included, use spreadsheets in lieu of IT support. Why? Their lengthy "process," time investment, control of product, and ability to make changes on the fly. In reality, some spreadsheets probably should be replaced by programs (using the spreadsheet as a requirements statement.)

My real point: we need to validate the results in our worksheets — applying common sense and comparing to our expectations, and making sure it is fit for purpose. This is part of the IT overhead that we need to adopt. Think about this: spreadsheets are, hands down, the most widely used analytic tool for business. Scary, because they are the least controlled. —Michelle Wallace

From Janice Bell: When I create a complex spreadsheet, I test it by using numbers that should be reasonable, and then calculate the results by hand to compare to the spreadsheet results. If it works, I then generate data that should be out of the range of normal inputs, for instance, negative inventory, to see how the spreadsheet handles exceptions.

From Richard Block: Michelle, you are spot on. The key is to validate the results of a spreadsheet you create by inputting sample data to confirm the results you have predetermined to be correct. Jan's suggestion further emphasizes this point by inputting data that knowingly will produce a "bad" result to test whether or how the spreadsheet logic will handle "bad data." These steps are critical to appropriately developing a spreadsheet.

Trap: When Rows Don't Foot
To CFO.com:
I have found the most basic, often-forgotten rule in building spreadsheets — the ones 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. —Patricia Chapman

From Janice Bell: We've commented on this before, but it bears repeating.

From Richard Block: Ditto!

Tip: Publish Your Standards
To CFO.com:
If some advantages of spreadsheets are flexibility, and ability to personalize, they can become a disadvantage if one is preparing a spreadsheet that must meet certain conventions and/or legal requirements. Common sense should dictate a minimum of structure in the preparation of spreadsheets. But 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 striven 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 com under scrutiny, particularly if they provide key information, such as calculation estimates—to mention one that may have material effects on financial statements. Perhaps this will be the impetus to better spreadsheet design and preparation. —Eduardo Casas

From Richard Block: Eduardo, don't let the masses keep you from publishing your spreadsheet standards, assumptions, and other documentation. Spreadsheets are too often used as critical parts of the accounting and financial reporting process. Thus, they should designed to be able to sustain the same critical scrutiny and audit review that is applied to all other accounting and operational processes as part of a Sarbanes-Oxley internal controls review. Sadly, most spreadsheets do not pass muster. So tell your "strong opposition" that unless they don't want their area to pass an audit, they should listen to you...and allow you to document profusely.

• « previous
• 1
• 2
• 3
• next »