As the use of electronic spreadsheets becomes more widespread, the risk of related errors increases. Most accountants check their work very carefully when they first start using spreadsheets because they do not trust the computer or their computer skills. As they become more comfortable with the software, they do less checking, construct more complex spreadsheets, and frequently re-use previously developed spreadsheets. All of these procedures increase the risk of serious spreadsheet-related errors.

Review of manually prepared working papers in order to reasonably assure accuracy and completeness is a well established part of the accounting profession. Unfortunately, electronic spreadsheets are typically not subject to review, even though their use in large-dollar calculations is commonplace. Horror stories about errors in formulas are well known, even on spreadsheets that have been used in securities offerings.

Inadequate review of electronic spreadsheets usually results from two conditions. First, the individuals in the firm normally responsible for reviews (i.e., managers and partners) often do not have the necessary microcomputer expertise. Indeed, staff accountants are often more knowledgeable than their superiors about computers. Second, even if traditional reviewers have the necessary technical expertise, the widespread lack of standardized spreadsheet design renders conventional review procedures ineffective.

Imagine how difficult it would be to review manually prepared working papers that lack standard or customary formats. This is exactly what many organizations face when there are no standardized spreadsheet design policies and procedures. Failure to develop and implement such policies and procedures significantly increases the risk that errors will occur and limits the ability of reviewers to detect spreadsheet errors. Also, should a key spreadsheet user leave the firm, the lack of spreadsheet design policies and procedures can make it difficult or impossible for other employees to use spreadsheets that may have cost the firm thousands of dollars to create.

Spreadsheet design policies and procedures can be classified into two categories. First, specific quality control policies can be used to help reduce the risk of errors and to make reviewing spreadsheets easier. Second, procedures for improving efficiency can be employed to conserve time, computer memory, and keystrokes. Quality Control Policies

Policy No. 1. Assumptions should appear only once in each spreadsheet. Spreadsheets often incorporate nested assumptions (such as the rate of interest in a loan amortization table). Policy should require that all formulas used in the spreadsheet should consist solely of cell references, thereby avoiding embedded assumptions. For example, suppose a Lotus 1-2-3 spreadsheet using the @PMT" function is used to calculate a loan payment amount for a $50,000 loan with a 10% interest rate and a term of 15 years of monthly payments. A very poor way to write the formula would be: @PMT(50000,.1/12,15*12). A much better method would be to place each of the four assumptions (i.e., the $50,000, the 10%, the 15 years and the 12 payments per year) in a separate cell (i.e., cells D1, D2, D3, and D4) and to write the formula as follows: @PMT(D2,D3/D5,D4*D5).

By separating assumptions and formulas in this manner, changes are made more easily and with less risk of error. Furthermore, when the worksheet is printed, the printout will clearly reveal all assumptions on which the results are based. If possible, all assumptions should be placed in a separate, clearly identified area of the spreadsheet.

Policy No. 2. Organize spreadsheets by function into separate modules. Very complicated spreadsheets will often need a data entry area, an area to store data, an area containing formulas and calculations, a report area, and an area for macros. When deciding the placement of different modules in the spreadsheet, it is important to be aware of how memory is allocated.

Lotus 1-2-3 allocates memory by columns. If an entry is made in cell Al and another entry is made in cell A8192, over 32,000 bytes of memory will be allocated to the empty cells in column A. On the other hand, if the only entry in column A is in cell A8192, no memory will be allocated to the empty cells in the column. (Note: Once a cell has been erased, the user must execute a File Save" and a "File Retrieve" to recovery the memory.)

Because of the way Lotus 1-2-3 allocates memory, it is important to lay out modules on Lotus 1-2-3 worksheets either from left to right, across or diagonally, rather than from top to bottom. Another advantage of laying out modules in this fashion is that each module can have unique column widths.

Policy No. 3. Use the / Worksheet Global Protection Enable" command to prevent accidental changes in areas of the spreadsheet; such as formulas that should not be changed. The / Range Unprotect" command can be used to unprotect the areas of the spreadsheet containing assumptions that might need to be changed occasionally. An added benefit of this technique is that the unprotected assumptions will appear highlighted on the screen, allowing the reviewer or subsequent user (including the designer) to quickly and easily identify assumptions versus formulas.

Policy No. 4. Utilize the / Range Name Create" command to name cells that contain assumptions. Since all formulas are to be made up of cell references, simply placing the cursor on top of a formula will cause the range names to appear in the control panel in place of direct cell references. For example, in the @PMT" example in Policy No. 1, cell D1 could be named LOAN AMOUNT," cell D2 could be named "INTEREST RATE," etc. These names will appear in the control panel when the cursor is in the cell with the @PMT" formula, even if the names are assigned after formulas are constructed. This technique makes reviewing spreadsheet formulas much easier.

Policy No. 5. Employ absolute and partially absolute cell references where possible. This technique allows more efficient use of the copy command, thereby greatly reducing the number of formulas needed in many spreadsheets. The smaller the number of formulas that have to be typed, the less chance mistakes will be made.

Policy No. 6. Use the @SUM" function rather than long +" formulas to total columns of numbers. When the @SUM" function is used, new data can be entered between existing data by using the "Move" or the "Worksheet Insert Row" command and the totals will be automatically modified to include the new data. When using the @SUM" function, the sum range should span from one cell below to one cell above the data range, if possible. This allows new data to be inserted between, above or below the old data and still be included in the column total. Because labels are treated as zeros by the @SUM" function, this technique works even if the cell immediately above or below the data contains a label.

Policy No. 7. Break down complex formulas into simple steps. Long formulas are difficult to edit, understand and review. A good rule of thumb is "if the entire formula cannot be seen in the control panel, it's too long." Lookup tables can often be useful in shortening long complex formulas.