Standard Spreadsheet Design Guide
Copyright, Paul Scarbrough, all rights reserved.
Spreadsheets have a significant influence on business decisions. Because of this influence, they must provide reasonable and reliable information. However, many spreadsheets (especially large ones) contain flaws which lead to inaccurate results, which in turn, lead to erroneous management decisions.
Spreadsheets are actually specialized information systems created by end-users, not by system experts. End-users, of course, do not typically have the knowledge to create reliable information systems, including spreadsheets. The problems with end-user created programs are so significant that many companies are working to implement more careful program design and user practices. Internal auditors and management accountants are strongly affected by the problem and have begun to take some responsibility for doing "spreadsheet audits" in their companies. The purpose of this handout is to provide you with some help in maintaining the integrity of your spreadsheet.
Things to Remember:
Spreadsheets are mainly communications devices, not computational devices. The reason you use a spreadsheet is to capture your thought process and share it with someone later. This means that the look-and-feel and writing quality must be very good. Also, the spreadsheet must be easily understandable by someone other than the creator. It must also look cool [you can go far with this alone!].
Spreadsheets must be error-free and error-resistant. Errors cause incorrect decisions! Not only must your spreadsheet not have errors, but it must resist errors caused by future changes.
Spreadsheets must be easy and economical to use. Using the spreadsheet should not take time from peoples day, but add time to it. Spreadsheets must be easy to understand, easy to get around, and easy to use. That means that they must be completely explained in clear concise language; they must have navigation tools for quick movement; and they must help the user control printing and other error-prone functions without allowing failures (repeated printing to get the pagination correct).
The following sections have descriptions of practices to address spreadsheet functions and reduce errors.
Spreadsheet Errors
There are four main types of errors seen in spreadsheets:
You can reduce each of these classes of errors through careful preparation and spreadsheet design. The most difficult to prevent is the last, "Revisions not made properly." You cannot control this type of error techniques on the spreadsheet itself, but only through the operating practices of the people doing the revision. You can easily prevent the first three types of error, however, using the techniques in this Guideline.
Failure to update cells. Cells that mix formula and values cannot ever update properly [ever, ever, ever]. This is the main cause of spreadsheet errors. An example would be a cell with the following expression: D12*.8. This was a formula to compute a 20% reduction in a cost, and it works correctly. However, the next time the spreadsheet is used the user may want to estimate a 15% reduction instead. The only way to change this is to find every cell with the .8 in it and manually make the change. If the spreadsheet contains more than a few of these cells it is inevitable that one or more cells will not be changed and thus, the results will be incorrect. This actually happens quite often when a range of cells cannot be seen on one screen and the user misses a few cells when changing screens. Every year we see several examples of multi-million dollar mistakes resulting from spreadsheet errors traced to this class of errors. The answer to this is a completely inflexible separation of formulas and values.
Cell formulas difficult or impossible to understand and fix. The nightmare of spreadsheet problems gets blackest when you are trying to find an error embedded in the sheet. Because most users write long cell formulas, they are almost impossible to fix in any reasonable amount of time. It can easily take 10 minutes or more to figure out the logic of a complex cell formula, especially if it contains functions [try 4 levels of nested IF statements to determine what the borrowing or repayment of loans should be). When you have 4 or 5 hundred cells to check this is almost impossible. The answer to this is a strong practice to limit cells to 6 or fewer characters, while allowing 2-3 exceptions per spreadsheet.
Accidental deletions and changes by users. This is the easiest to reduce. Consistent use of sheet protection except for data entry areas almost completely eliminates this class of error.
The use of these techniques is described below.
I. Planning
Many end-users create their spreadsheets "on-the-fly," which causes many of the errors. If you take the time to review the purpose of the spreadsheet and what it should look like, many of the problems can be avoided. You should think about how you want to approach the spreadsheet: the form, the purpose, and the tasks.
II. Spreadsheet Design
Modern [3-dimensional] spreadsheets should always use the "sheets" as sections, or blocks when applying these guidelines.
It is rare, however you may occasionally need several small computations on the same sheet. Such a sheet should be designed in a staircase format to enable a user to add a column or a row without damaging the other parts of the spreadsheet, although this is almost obsolete, the need may arise occasionally.This is how we used to do things before spreadsheets had tabs.
The basic idea behind good spreadsheet design is to calculate the end results in stages. Often, each small stage is likely to be an important business process that should be examined separately, but that can be hidden in complex spreadsheets. Initial computations are made in the computations section. These results are then used in the schedules which follow. By breaking the formulas down into smaller parts, a user is less likely to make a mistake. Then, as mistakes are discovered, smaller formulas make them easier to locate. Using this format, the spreadsheet should be divided into the following sheets (the first three being mandatory).
1. Index/Introduction - The index is a list of all the sections contained in the spreadsheet, where each section is located and an explanation of what the spreadsheet contains.
The basic information and order is very similar to that of a financial statement, as follows:
- Spreadsheet Name (What does the spreadsheet do? not the file name)
- Company Name
- Preparor name
- Preparor contact information
- Creation Date
- Modification Dates
- Last User
- Last User contact information
- Last Use Date
- Description
- Index listing each section of the spreadsheet [with macros]
For example: the Data section begins at F5 and ends at I20, and the Computations section begins at J21 and ends at N43. The index also contains the introduction: a short description of what the spreadsheet is going to be used for as well as what is contained within the spreadsheet. This is crucial because usually the designer of the spreadsheet is not the only user, or even the main user.
Macros are a series of instructions to the computer that automate certain repetitive tasks, such as navigating to or printing out certain sections of the spreadsheet. Macros may be attached to buttons to make them easier to invoke. These buttons should be located in the index section. For help on creating macros, click here.
2. Raw Data - This sheet lists the numerical values of the information which the formulas in the spreadsheet will use. The purpose of listing the values here is that it enables the user to change them easily without disrupting the integrity of the spreadsheet by having to change the value in several places, possibly forgetting some. Errors are less likely to occur with this method, as opposed to going through the spreadsheet and changing each individual number or formula.
3. Computations - All the accounting methods leading to a single reused number, such as depreciation or inventory valuation, or cost per unit are computed in this sheet. This sheet should also be used for all unusual or complex calculations. This segment should contain computations that will remain constant from month to month.
4. Output section - The ultimate goal of efficient spreadsheet techniques is to design an output section that produces the information required for good decision-making. This section contain no numbers, but only formulas and cell references. For example, in a budgeting exercise, an output area consists of the various budget schedules. These schedules contain formulas that use the Raw Data and Computations sections.
By changing a number in the Raw Data section, universal changes are made throughout the information generated in the spreadsheet. This design allows a user to use the same spreadsheet over and over or change estimates (i.e., perform what-if analyses) without having to worry about any deterioration in the spreadsheet's integrity.
The output area is the only area most people will probably see. It will be printed and used at meetings and other decision-making times. Because of this each schedule in the output area must be correctly labled as a stand-alone document.
5. Ratio Analysis (if required) - This section is the least complex. The user formulates the selected key business ratios, such as the Quick or Current Ratio, through references to the budgeted statements. The qualitative analysis of the ratios is then left up to the individual.
III. Validating
After the spreadsheet has been designed and completed, a spreadsheet's formulas should be reviewed to ensure validity. Usually, companies have special software to review the spreadsheet. The major problem with such programs is that they mainly review for circular formulas. The only way to check the spreadsheet's logic is to review the spreadsheet and determine if everything is referenced correctly. It may be helpful to have some key manual calculations prepared for a test set of data and to compare them to the spreadsheet's calculations. The audit functions on the spreadsheet can be used to trace computations, and are very helpful.
The purpose of this page is to help you develop spreadsheet skills that will be beneficial to you in the business environment. By breaking down the budgets into segments and developing the segments carefully, users can ensure the integrity of their spreadsheets. The approaches outlined in this statement are the most current practices, and may even be of immediate use to you in your current employment.
IV. Examples
Here is an example of a spreadsheet to answer a specific design problem. It is fairly good, and illustrates an approach to spreadsheet problems. It also has a number of errors, so do not follow it too closely. For example, the Index Section is not complete. In case of any difference between these spreadsheets and the Design Standards, you are responsible for the Design Standards as well as any modifications made by your professor.