Reducing Spreadsheet Risk

By Frank Riccobono
"Frank Riccobono received his M.S. in Computer Science from Stevens Institute of Technology. He currently works as a consultant with Benchmark Technologies International, specializing in the development of policies and procedures manuals and spreadsheet documentation."
1. Understanding Spreadsheet Risk

The use of spreadsheets for supporting decision making and managing key processes has become so prolific that frequently spreadsheets take the place of IT-developed systems. While end-user computing solutions like spreadsheets contribute significantly to improving analyses and productivity, these tools generally lack the controls that protect traditional systems.

When spreadsheets are relied on for performing critical processes, including financial calculations, basic controls are needed to mitigate operational risk. These basic controls for mission-critical spreadsheets include processes for validating calculations and managing changes. A third critical control, spreadsheet documentation, is the subject of this article.

2. How Spreadsheet Documentation Reduces Risk

Spreadsheets are frequently developed as ad hoc solutions to a particular business need. Once the spreadsheet has proved useful, additional features are added as needed. Because of the ease with which they can be created and modified, spreadsheets often have multiple authors or frequent updates. The result is a complex tool whose inner workings have, after multiple iterations, become a bit of a mystery.

Spreadsheet documentation provides benefits similar to traditional systems documentation, facilitating change management, knowledge transfer between staff, and business continuity. Additionally, the process of documenting it offers a chance to examine the design of the spreadsheet and surface computational issues -- before an auditor finds them first -- as well as inefficiencies and inconsistencies.

Finally, for an especially complex and critical spreadsheet, comprehensive documentation serves as a valuable resource for programmers should the decision ever be made to replace the spreadsheet with an IT-driven solution. Such documentation reduces the amount of time business staff need to spend describing the tool to software developers.

3. What Spreadsheet Documentation Should Include

In order to be useful, the documentation should contain sufficient detail to address key needs of several types of stakeholders:

  • Business management, who need a high-level understanding of how the data they receive was created;
  • Spreadsheet users, who rely on the spreadsheet to aid their productivity and need to understand how to use the spreadsheet; and
  • Spreadsheet maintainers, who need to understand the functionality of the spreadsheet at a very detailed level.

As with systems documentation, the most important component of spreadsheet documentation is a description of requirements. Requirements describe functions or tasks the spreadsheet must perform. For spreadsheets that have been in use for some time, the requirements are probably generally understood as the list of things the spreadsheet is used to do; however, a formal description of requirements -- what the spreadsheet must do as opposed to what it can do -- is critical for understanding the spreadsheet. A set of high-level requirements can provide a sufficient overview of the functions performed by the spreadsheet (e.g. "The cash flow projection spreadsheet must calculate projected revenue from sales for the next five years."), while sub-requirements can expand upon high-level requirements to achieve the necessary level of detail (e.g. "The cash flow projection spreadsheet must calculate average annual revenue for each product type based on quarterly sales.").

Documentation should additionally address how these requirements have been implemented in the spreadsheet. This part of the documentation should include all inputs and where they come from, all outputs and where they go, and an overview of the calculations performed by the spreadsheet. It is important to note, in the documentation, interfaces with other groups or departments, which are frequently the source of input data or recipients of output data. If the spreadsheet includes multiple worksheets, it is important to provide an understanding of how the different worksheets interrelate, describing the overall structure of the spreadsheet as well as the inputs, outputs, and calculations of each worksheet as a unit.

4. Summary

Spreadsheets are a useful tool for businesses. They facilitate rapid development of business presentable solutions that can be easily shared and updated. But this ease of use can lead to complex and unmanageable spreadsheets with little transparency or control. Effective and comprehensive documentation, which describes what the spreadsheet does and how the spreadsheet does it, is a critical tool for managing and understanding spreadsheets.

If you would like further details on the strategies outlined in the article, please visit: http://www.rapiddocumentation.org.