Spreadsheet Safety

For instance, managers routinely use customized spreadsheets to continually evaluate product mix and pricing decisions based on regional economic and consumer demand forecasts. In addition, spreadsheets frequently serve as an interface with more complex information systems. For example, a salesperson at a remote location can record or change sales data in a spreadsheet application running on a laptop computer; later, that same data can be uploaded to the corporate information system. In auditing, spreadsheet software can be integrated into engagement management systems.

As should be obvious, repeated-use spreadsheet files differ significantly from one-time-use files. First, the users of a repeated-use file probably didn’t design it, so they probably don’t know the proper format of the input data or, worse, may innocently overwrite and destroy critical formulas or links. Further, when either the original spreadsheet designer or another user needs to change the design setup, the project will be labor-intensive and susceptible to errors. Finally, while errors are of concern in any spreadsheet, they are of relatively greater concern in spreadsheets developed for repeated use, and that’s because such errors become magnified and hard to locate every time the file is updated.

Many of these problems can be avoided by properly preplanning the design. In most cases, the time to develop such a design is short compared with the time needed to correct subsequent problems. Unfortunately, many accountants create their spreadsheets without sufficient consideration of the longer-term purpose of the file; something that surely affects its layout or controls. For illustration purposes in this article, we use Microsoft’s Excel.

A MODULAR DESIGN
Follow these steps to ensure a spreadsheet design that is both safe and easy to work on:

Step 1 Decide what you want the spreadsheet to do. List the data to be used and the output you desire. Then identify the calculations needed to produce that output.

Step 2 Design the spreadsheet structure, if necessary dividing it into logically related sections, each in a separate worksheet (Excels terminology) or tab (Lotus 1-2-3s terminology). Here’s what the sections should include:

Identification. The first worksheet (see exhibit 1) should be the identification page of the file. It should include the name of the spreadsheet, the author, the creation or revision date, its file name and an outline of each section’s contents. However, it shouldn’t list all of the specifics (formulas) about how results are calculated; reserve that for the documentation section.

The calculation and results areas should be protected by password to guard against accidental changes. The worksheet should be set up in such a way that data can be entered only in the designated input areas. Separating the input area from the calculation and results areas does double duty: It makes the spreadsheet easier to use and prevents the overwriting of formulas.

Documentation. This section includes detailed explanations about the application. It may contain specific clarifications such as data format, identity of protected cells, special printing instructions or any other important information the developer wants to include.

Macros. In many complex spreadsheets, macros, the customized miniprograms that users build into an application, are used extensively to automate repeated tasks, such as printing sections of the spreadsheet. Often users hide macros; that’s not a good idea. Instead, put them on a separate sheet, easily and clearly identified.

Step 3 Now, finally, you can begin to create the spreadsheet. Be sure you’re familiar with the use of absolute and relative cell references. If you’re not, click on Help in the tool bar or press the F1 key to call up the help screen and type in cell references. Proper use of cell references is critical to the success of the input, calculation and results areas you established in the model section.

Important: Don’t embed input values in any of the formulas you create. For example, say a client wants you to evaluate real estate investment opportunities. Important variables for this calculation usually include data about the mortgage loan, projected operating income and expenses, property appreciation and tax factors. In order to evaluate different scenarios, cells that represent that data must be blank, waiting for data. Exhibit 2, is an example of how the description section might look for such a spreadsheet.

The model area contains three worksheets. The first is the input area (see exhibit 3). It contains all the variables, with shading around the data entry cells to make them easier to locate. All cells in the worksheet except the data entry cells should be locked so a user can’t accidentally place a variable in an incorrect cell. Exhibit 4 contains all the calculations, and exhibit 5, the final results. All formulas on these two worksheets contain references to the appropriate cells on the input sheet. The user need not, and shouldn’t be able to, enter data or change any of the cells on worksheets two and three. Exhibit 6 shows all the macros used in this project.

Finally, an example of the documentation sheet is given in exhibit 7. This is where you should provide explanations for all formulas, except the most basic ones. For instance, in the real estate investment analysis, the investment is assumed to be residential property with 80% of the purchase price allocated to buildings, factors important to the calculation of depreciation. This information could have been treated as a variable and placed in the input section. Since it was not, however, the depreciation calculation needs to be clearly described in this section.

All spreadsheets should contain instructions on how to input or print data, if appropriate. For complex spreadsheets, details should be added about the model structure. For instance, there are numerous ways of solving the real estate investment analysis, and a description of this method would be useful to someone unfamiliar with the application.

Step 4 Test the spreadsheet. Choose a set of test values and calculate the results independently of your spreadsheet. This is important; it’s the only way to ensure the accuracy of the formulas. Too often users skip this test, and that’s unfortunate because the investment in testing time is small compared with the cost of an error.

In the testing phase, it’s not necessary to enter live data. For instance, in the real estate investment example, you might create some realistic variables, enter them into worksheet one of the model section, and then selectively recalculate formulas at key points (such as projected market values, rental expenses and depreciation). Also, trace selected values through the worksheets to verify that references were formed correctly. And make sure complex calculations such as the cash sales proceeds at the end of year five are correct.

After running those tests, ask yourself if the intermediate calculations and the final results are reasonable. Are the results what you expected? Frequently, errors will appear as illogical or inconsistent values.

While all of the above steps may seem obvious, most spreadsheet creators tend to skip over them, considering them too basic and figuring they can save time and effort by getting right to the formulas and the data. But the reality is that these steps, while basic, are vital for a reliable spreadsheet, and they are easy to accomplish. In the long run, they will save you time. So resist the temptation to jump right in.