The NASPP Blog

The recent banter around option exchanges is probably not news to you. What might be news, however, and was pointed out in a recent webcast we offered on this topic, The Dark Side of Option Exchanges, is the likelihood that spreadsheets will become the practical interim solution to support the accounting and financial reporting for these option exchange transactions. It’s certainly not unusual for companies to use spreadsheets to manage transactions such as these when their existing accounting applications or stock plan database programs can’t accommodate them. But, there are risks associated with spreadsheet use – an incorrect formula in one cell can throw off the integrity of the entire spreadsheet, and potentially cause a material misstatement in your company’s financial statements.

My blog today is intended to be a refresher on some of the ways to maximize spreadsheet accuracy and reliability and help your organization maintain more comfort when using spreadsheets for financial reporting purposes.

A Few Guidelines for Accurate & Reliable Spreadsheets

A. Version Control: Use standard naming conventions in your spreadsheets, maintain historical files no longer available for update in a segregated drive and lock them as “read only,” use common header information and don’t use automatic headers for information that could change i.e., dates, periods, versions. Do, however, use the automatic footer feature for the file name and location.

B. Security and Integrity of Data: Excel has several options to ensure that data embedded in spreadsheets is current and secure. This can be done by “locking” or protecting cells to prevent inadvertent or unintended changes to standing data.

C. Sheet Protection or Validation: These functions can be used to help prevent accidental errors, but won’t guarantee error free spreadsheets.

Sheet Protection vs. Sheet Validation

• Sheet Protection: Is easier to use, quicker to set up and you can choose which functions to allow/lock, but you won’t get any detail/guidance as to why the cell is locked.

• Sheet Validation: Is more complicated to use, takes longer to set up, but will provide more detail/guidance for users as to why the cell is locked and will allow certain types of data to be entered in cells.

D. Documentation: As a separate page within your workbook, document the procedures for each spreadsheet including, the spreadsheets purpose, where and how to get input data, which cells to update, what calculations are being made, what kind of analysis is needed and what results are expected. Documentation is vital for people inheriting spreadsheets and those using or reviewing the results.

E. Overall Analytics: Develop a checklist of procedures to perform each time the spreadsheet is completed/updated including, checking that the version used is the most current and up-to-date version, checking that input totals are correct and that formulas were copied to newly inserted rows/columns where appropriate, confirming the logic of formulas, checking all cells that should have formulas do not have values typed in, checking the footing and/or cross footing totals are correct, checking that spreadsheet protection/validation is activated, and finally, checking that the file is named correctly and saved in the proper location.

In Summary

Spreadsheets can be easily changed, may lack certain internal control activities, and are vulnerable to human error. By applying these recommendations and others not mentioned here, organizations can control spreadsheets for accuracy and minimize data mistakes. More important, controlling spreadsheets needs to be an integral part of Section 404 compliance efforts in all public corporations when spreadsheets are used to support and create financial statements. These spreadsheets should receive the same care and maintenance granted to other business applications and associated databases.

A similar topic was covered in the 2008 NASPP Annual Conference session, “Get Your Geek On: A Primer for Non-Techies on Technology and Project Management;” check out the session materials in your Conference books (or order the Conference books and audio if you weren’t able to attend last year).

-Robyn