Spreadsheet Safety

Designing the perfect file.

  • IN RECENT YEARS, SPREADSHEETS have taken on a role far more important than they had in the pre-computer days: shaping business strategies and being used increasingly for ongoing projects by many different people who need to go in and out of a file, to add material and even to customize the output for their specific needs.

  • THIS PRESENTS A NEW CHALLENGE for the spreadsheet developer, who must design the file so it cannot be accidentally altered by an inexperienced user.

  • HERE ARE STEPS to ensure safe spreadsheet design:
    1. Decide what you want the spreadsheet to do. List the data to be used and the output you want. Then identify the calculations needed to produce that output.

    2. Lay out the spreadsheet structure and, if necessary, divide it into logically related sections, such as identification, description, model, documentation and macros.

    3. Create the spreadsheet. Familiarize yourself with the use of absolute and relative cell references. Its proper use is critical to the success of the input, calculation and output areas.

    4. Test the spreadsheet by selecting a set of test values, and then test the results independently of your spreadsheet.

    Jennifer Kreie, PhD, is an assistant professor of accounting and business computer systems at New Mexico State University, Las Cruces. Her e-mail address is jkreie@nmsu.edu. John Pendley, CPA, PhD, is an assistant professor of accounting at the University of Alabama in Huntsville. His e-mail address is pendleyj@email.uah.edu.

In recent years, spreadsheets have taken on a role far more important than they had in the pre-computer days. Because of spreadsheet applications power, speed and adaptability, today's financial managers rely heavily on them to shape business strategies.

And while spreadsheets are excellent at quickly solving one-time or infrequently encountered computational problems, they are being used increasingly for ongoing projects that involve many people who need to go in and out of a file, adding material and even customizing the output for their specific needs.

If you build a house with a poorly designed foundation, you run a serious risk: No matter how well the rest of the building is constructed or how good the materials, the walls may sag—and eventually the whole building may collapse.

Similarly with spreadsheet files: If they're not correctly designed from the outset, you risk calculation errors in the future—especially if the spreadsheet is complex and many people use it.

This article, another in the Technology Workshop series, lays out the fundamental steps you should take to ensure the construction of a spreadsheet that guards against errors, making it as nearly foolproof as possible.

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.

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.

An Invitation
If you have a special how-to technology topic you would like the Journal to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin (telephone: 201-938-3289; e-mail: zarowin@mindspring.com).

Model. Three worksheets should contain the programming necessary to perform the spreadsheets work. Reserve places for the input, calculation and results areas. The input area should include only information that may be changed when the file is accessed.

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.


Get your clients ready for tax season

These year-end tax planning strategies address recent tax law changes enacted to help taxpayers deal with the pandemic, such as tax credits for sick leave and family leave and new rules for retirement plan distributions, as well as techniques for putting your clients in the best possible tax position.


Keeping you informed and prepared amid the coronavirus crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.