|
As you've figured out by now, spreadsheets vary widely in
terms of their functions and complexity. Some models are very basic, with just a
few simple formulas. Others are quite complex – they may include a variety of
complicated formulas and functions. All models, regardless of their size or
complexity, should include the following characteristics:
Accuracy
Clarity
Flexibility
Efficiency
Documentation
The most important characteristic of any model is accuracy.
In fact, an inaccurate model is worse than no model at all. Always test your
models for accuracy before you use them or distribute them to other users. It's
a good idea to use several sets of test data, just to make sure your model works
properly regardless of the data being used.
"Clarity" refers to the layout and organization of the model.
Is it organized so that others can follow it, or will users (besides you, of
course) be totally bewildered when they try to interpret the data? When you
design the layout of your model, identify and highlight the information that
will be of most interest to the end users of the model. Make sure the model is
organized so that users can easily identify where they should enter data, and
where they can expect to find calculated results.
Good models are flexible. That is, they're designed to
accommodate not just today's situation, but situations that may occur down the
road as well. Models should be designed so that they are easy revise. After all,
one of the primary benefits of spreadsheets is the ability to conduct "what if"
analysis. Why other to build the model at all if it won't lend itself to
changing conditions?
One way to ensure that your model is flexible is to include
an assumption set to hold input variables. An assumption is simply an
educated guess – your best estimate of a particular value. For example, let's
say your company is coming out with a new line of bobbleheads, and you're
preparing a five-year financial forecast for the product line. You don't really
know how many bobbleheads you'll sell over the next five years, nor do you know
what the selling price will be. Production costs are a guess (albeit an educated
one) at this point, as are the other expenses related to the product. Still, you
need to have some idea of whether or not the product line will be profitable. To
build the model, you'll need to develop an assumption set that represents your best
guess for the values of the variables included in the forecast.
Assumptions should be included in their own section of the
model; that way, they're easy to change when the underlying conditions of the
model change. When you write formulas, you should use the cell references of the
cells in the assumption set rather than the values themselves. When the
variables change, you don't need to search through the entire model to find all
occurrences of the variable. You just need to change the value in the assumption
set, and all formulas that refer to that value will automatically be updated.
For an example of how this might look in Excel, take a look at a sample
financial forecast for Betty's Bobbleheads.
When you build your models, your goal is to work smarter
instead of harder. That is, develop your model so that you can make the most
efficient use of the spreadsheet tools available to you. Use functions instead
of formulas whenever possible. Make appropriate use of absolute and relative
cell references; this way, you can copy formulas when necessary instead of
retyping them each time. The goal is to minimize your keystrokes. This makes the
development process go much more quickly, and helps improve the accuracy of your
model.
Thorough documentation – both internal and external – is an
absolute necessity! Internal documentation includes titles, column and
row headings, cell notes, and so on. Keep in mind that other users may not be
familiar with your abbreviations and "lingo"; make sure to explain special terms
and abbreviations used in your model.
Depending on the model, external documentation may
include several different components.
-
Overall description of the model. Explain the goal
of the spreadsheet, and give an overview of how it is organized. Highlight the
key features of the model – what makes this model worth using?
-
People involved. Who developed the model? Who is
responsible for updating and distributing the model? Who receives the updates
and uses the model?
-
Software version used. Make sure to identify the
software version used to create the model. Since software upgrades sometimes
include a change in file format, users with older versions of the software may
not be able to read files created with the new software.
-
Assumption set. Explain how you came up with
your assumption set values. Are they based on historical data? Did your
manager tell you to use them? Did you just pull them out of thin air (not
advised, but hey – it's been known to happen!)?
-
Key formulas. Explain any key formulas in your
model. If the formula is particularly complicated, break it down so the other
users know how it works.
-
Internal controls. Explain the internal controls
built into the model. Internal controls include features such as data
validation, cell protection, workbook protection, and so on. You'll learn how
to implement several types of internal controls as the quarter progresses; as
you include the controls in your models, make sure to let other users know
they exist.
-
Worksheet sections. Describe the different
sections in the model. If the model consists of multiple worksheets, make sure
to explain the purpose for each sheet.
-
Printouts. In some cases, it's a good idea to
generate a printout of the entire model. You'll probably need at least two
printouts: one that shows the calculated results, and one that shows the formulas.
-
Chronological list of changes. Keep a detailed list
of all changes. Make sure to include the following minimal information:
-
who made the change
-
a description of the change
-
the date the change was made
-
who tested the change
-
when the revised model was distributed to other users
Depending on how the model is used, you may think of other
information to include as well.
In some cases, the necessary documentation can be included
as the first sheet in the workbook. In other cases, you'll need to provide a
written report. Regardless of the format, thorough documentation is a critical
component of a good model!
Naturally, models that incorporate the characteristics
listed above don't just magically appear – those kinds of models require
extensive thought and planning. It's a good idea to follow a structured
process as you plan and develop your models. While a structured planning and
development process won't guarantee a perfect model, it's certainly a step in
the right direction. We'll break our planning and
development process down into these steps:
-
Define the
problem/identify your objectives
-
Define the data
requirements
-
Identify the
data relationships
-
Draw a map
-
Build the model
-
Test the model
-
Write the
external documentation
-
Use the model
The first step in our spreadsheet development process is to
define the problem or identify the objectives for the model. The problem
definition or objectives specification statement should be clear and concise
– usually no more than one or two sentences (although this may vary, depending
on the model). Take the time to write the problem definition down on paper,
and keep it front of you while you work – you'll stay much more focused on the
problem at hand, and won't be quite as likely to stray from the original
purpose of the model.
When you define the data requirements, you simply make a
list of the data you need to solve the problem you identified in the first
stage of the development process. I usually break this into a few different
sections – data I have, data I still need to gather, and results that will be
calculated. If you need to get some of your data from someone else, this is a
good time to ask for it. Otherwise, you could find yourself in a bit of a bind
when you actually sit down to build the model.
Data relationships are "word" formulas that identify
how the data will work together. For example, in our
Betty's Bobbleheads model, the word
formula for revenues might look like this: Revenues =
selling price per until * units sold Notice that the formula
doesn't include any numbers. We aren't really interested in the specific values
at this point; we just want to figure out how everything will work together. In
addition to helping us make sure we understand the logic behind our formulas,
data relationships help point out data that we may have missed when we defined
the data requirements earlier in the development process.
A map is a pencil-and-paper sketch of the model.
Maps can take a variety of forms. Some maps are very detailed, while others
are just "big-picture" sketches. The idea is to try to get a "picture" of how
you plan to organize the model. Where will you put the assumption set? Where
will the calculated results be located? Will you include a chart? If so,
where?
Ahhh – the moment we've been waiting for! It's time to
actually build the model. If you did a good job with the data relationships
and the map, this part of the process should be a breeze. When you build your
model, keep these tips in mind.
-
Be generous with internal documentation. Make sure
your column and row headings are clear, and that other users will be able to
understand them. Clearly identify the sections of the spreadsheet. For
example, identify sections of the model in which data should be entered.
Otherwise, an enthusiastic user may just replace all of your formulas with
constants (unless, of course, you protect the cells – we'll look at that later
this quarter)! Use cell notes when appropriate (we'll look at that later,
too).
-
Follow good practices when writing formulas. Use
absolute and relative cell references appropriately. Copy formulas when you
can – this saves time and minimizes the chance for errors. Use functions
instead of formulas whenever possible. In general, try to minimize your
keystrokes as much a possible. Every time you type, you introduce the chance
for a new error. The less typing you do, the more accurate your model will be.
-
Never use constants in your formulas! Remember,
formulas should include only cell references and operators!
-
Break formulas down into small parts. Instead of
writing one complex formula, break it down into smaller parts. Short formulas
are much easier to trouble-shoot than large, rambling ones.
-
Use appropriate formatting. If a cell represents
currency, format it accordingly. Use text alignment to your advantage – make
sure headings are properly aligned over the data they represent. Use borders
and shading to highlight key areas of the model. Be consistent in terms of the
number of decimal places you show. Some data needs to be shown at a very high
level of precision, while in other cases it's OK to show the data in
thousands. Regardless, make sure you're consistent in the model – don't
confuse other users by switching formats mid-stream.
Testing is critical. As mentioned previously, an inaccurate
model is worse than no model at all. The first line of defense is to do a
reality check – does the data make sense? For example, if you add up a column
of 3-digit numbers and end up with a 2-digit result, something went wrong!
Most models, though, require more than just a reality check. You should
perform the calculations by hand, or use some other verified results to
compare against your model. Depending on the model, you may need to use
several different sets of test data (very large numbers, very small numbers,
and something in the middle) to check your model. In addition to accuracy, you
should also check for formatting problems. When you use large numbers, are the
columns wide enough to hold the results, or do you end up with those annoying
pound signs in the cell? Keep in mind that the test data should represent a
reasonable range. For example, if your model will most likely hold data in the
thousands, don't test with data in the billions – test with data in the high
thousands.
It's actually a good idea to write your external
documentation while you develop that model; that way, everything is fresh in
your memory. As mentioned earlier, external documentation can sometimes be
included as the first sheet in the workbook. In other cases, you may need to
write a separate report. Regardless, make sure the documentation includes
everything a user needs to know about the model. Keep in mind that there's a
good chance someone else will eventually take "ownership" of the model. (After
all, we all want to be promoted out of our current job at some point, right??)
So, make sure you provide sufficient documentation for the new owner.
Keep in mind that models are rarely static – they tend to
be dynamic, which means you'll probably end up revising or updating the model
at some point. Remember to keep a log of all changes, and remember to test the
model thoroughly after each revision; a seemingly minor change can sometimes
wreak havoc where you least expect it! |
