This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.

Latest Stories

Latest Stories

Spreadsheet Safety

Designing the perfect file.

BY JENNIFER KREIE AND JOHN PENDLEY

Related

TOPICS

Uncategorized Article

EXECUTIVE SUMMARY

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:

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.

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

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.

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.

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.

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.

There are over 30 million small businesses in the U.S., and many of them are optimistic in their outlook. Are you familiar with the obstacles and opportunities they are facing? Test your small business acumen with this quiz sponsored by Chase Ink®.