to take pen and paper and start sketching out the flows.
The clearer the modeling goal, the less messy the model.
Being clear goes a long way in helping you follow the KISS
Sometimes, you have a clear idea but the idea is that the
model should have more than one primary function. This is to be
expected if you are building a standard model that will get used
in many different situations. For example, your model may be
used for credit analysis and for an equity valuation. The credit
model may need a â€˜â€˜cash sweepâ€™â€™ module, whereby excess cash
produced as a result of your assumptions can be used to auto-
matically repay outstanding debt (see Chapter 14). The valuation
model would have to pay attention to the development of â€˜â€˜free
cash flows.â€™â€™ In this case, a good approach to take is to develop
one solid â€˜â€˜calculation engineâ€™â€™ at the core of the model, the
output of which can be used in different ways.
An important distinction here if you are building a model
for others is that their sense of what the model needs to be may
be different than yours. Always build your models to match, or
exceed, your usersâ€™ expectations.
Be Clear About What the Users Want and Expect
If you are creating a model for others to use, be absolutely clear
about what your audience wants and expects. Do not assume
that you know what they wantâ€”often they themselves only
have a vague idea of what they are looking for, making it
likely that what you produce for them will meet with a thumbs-
down reception. If they have a model they like that they are
already using, it is a good idea to make your (new and
improved) model follow some of the layout and analytical
steps used in the old model. Users generally like to stick to the
steps that they are comfortable with.
You will also have to gauge the skill levels of the users and
develop your interface appropriately. Another important tip:
check the version of Excel that they have and make sure that
there are no compatibility problems with the version in which
you are developing your model.
Maintain a Logical Arrangement of the Parts
With the goal clear in your mind, the natural way to set out a
good layout is to follow the flow of calculations. The bigger the
model, the more important it is that this principle is followed.
What do you need to calculate first in order to get to the next
Design Principles for Good Model Building 17
round of calculations? After that, what else in order to get to the
final result? In this way, it will be easy to follow and check the
Many models are unnecessarily difficult to follow because
the calculations are done by formulas that are spread out willy-
nilly across the model. Granted, there will be times when the
calculation blocks cannot follow each other in one smooth flow,
but the more they can be ordered in a logical and visually acces-
sible way, the easier it will be for you and your users to work
with the model.
In terms of the final output, this can be a separate sheet that
organizes and presents the various parts of the model in one
Make All Calculations in the Model Visible
A corollary of the logical arrangement is that all calculations
must be visible. A â€˜â€˜black boxâ€™â€™ model is the most intimidating
kind of model. This is the kind of model where the calculations
are not visible and the model produces its results with no indica-
tion of how it does so.
By the same token, nothing is more reassuring to users than
to see how the model is working and to be able to check for
themselves the calculationsâ€”all the better if the formulas are
arranged in a logical fashion. And not only formulas, but also
the â€˜â€˜togglesâ€™â€™ or settings that allow you to set how the formulas
Be Consistent in Everything You Do
As much as possible, make the parts of the model be consistent
with one another. Use the same label for the same item if it is
shown at different places in the model. Calling the same row of
information â€˜â€˜cash flow from operationsâ€™â€™ in one place but
â€˜â€˜operating cash flowâ€™â€™ in another is a prescription for confusion
The same columns in the sheets should contain the same
year. When you know that every sheetâ€™s column H contains the
data for year 2003, formula references across sheets become less
prone to error.
The same font and font size should designate the same type
of item. If you are using colors in your fonts and cells, be sure to
follow this consistency rule, too.
Use One Input for One Data Point
There should be only one place in the model to enter one data
point. For example, if you need to work with the current stock
price of a company, enter it in one place only and have the model
always read that input, either directly or indirectly, when it needs
to calculate anything that would use the current stock price.
Having multiple inputs for the same data item will exasperate
your users, and only leaves room for conflicting inputs for the
same data point.
Build the model so that it has blocks or modules of formulas that
perform discrete operations within them. As a block completes
its tasks, it passes the results to the next block. This approach
makes the work of building the model, and later of checking and
auditing it, that much easier. It also makes changes easier to
implement, as you can work with the modules and not have to
roam over the whole model to change formulas.
In military parlance, the expression â€˜â€˜fire and forgetâ€™â€™ refers
to missiles that unerringly hit their target, no matter what
the battle conditions are after the launch. The parallel for devel-
oping models the right way is â€˜â€˜develop and forgetâ€™â€™: develop
and construct your model robustly, and let it be capable of
future changes easily. The â€˜â€˜think modularâ€™â€™ approach is by far
the most effective way to get to this level of model-building
Make Full Use of Excelâ€™s Power
A valid way to describe Excel, or any spreadsheet program, is
that it is a big calculator. Just as you would not take a pencil and
Design Principles for Good Model Building 19
do a sum on paper before entering that number into a calculator,
you also should not have any intermediate tools between you
and the spreadsheet. Do everything in it.
Excel has a whole repertory of over 250 functions (pre-
formatted formulas) that make it a hugely powerful calculator.
These functions are divided into the following types:
Date and time
Lookup & reference
You wonâ€™t need to know all the functions. In fact, for the
financial modeling that is used in investment banking and
finance, you will only need to know as a start about 35 or
so functions, and these are listed in Chapter 5, â€˜â€˜Your Model-
Building Toolbox: Functions.â€™â€™ Because Excelâ€™s functions work
with one another, putting combinations of functions together
will often give you exponential leaps in your modeling expertise.
Excel also lists its functions when you click on the Function icon
on the menu bar. Help screens can be called up to help you
understand what each function does.
Excel has its own programming language, called Visual
Basic for Applications (VBA). This is a powerful language for
writing macros to create various user forms to help with the
user interface or to automate tasks. Later chapters will provide
an introduction to VBA, but VBA is a full-length subject in its
Provide Ways to Prevent or Back Out of Errors
There are two types of errors to worry about:
Formula errors. Formulas can work well when all the data
are entered but will show an error if a data point is
missing. An example is the #DIV/0! (â€˜â€˜divided by zeroâ€™â€™)
error in formulas where a number is being divided by
another. In this case, the simple remedy is to write the
formula with a way to prevent the error, by checking
whether the denominator is zero or not. If it is, the
statement just returns a zero.
User errors. A good developer can usually guess what the
â€˜â€˜typicalâ€™â€™ user will do, given a particular point in working
with a model, but there is no way to guess what the
â€˜â€˜untypicalâ€™â€™ will do! There are countless unexpected ways
that users interact with a model. Where the number is
expected, they may put text and vice versa; formulas that
they have been told not to touch get altered, and altered
radically; messages displayed in the middle of the screen
describing the next step go unread, and the wrong button
is clicked; and so on.
To prevent user errors, we can employ a variety of
approaches such as designing the screen to guide the user
to do the right thing as much as possible; using Excelâ€™s
data validation features that prevent the wrong type of
inputs (e.g., a number when a text string is expected) from
being entered; writing very clear and explicit messages on
the screen about what to do. However, there is every
likelihood that users will still make mistakes.
Save In-Progress Versions Under Different
Names, and Save Them Often
This is not so much a design principle as an operating principle
to use when you are designing. Anytime you work with any
electronic documents, you should remember to save frequently.
And donâ€™t just save under the same name. This is because you
want to have a record of your work over time, in case the latest
version gets corrupted. For example, if you had saved a work-
in-progress 30 minutes ago as Newmodel08.xls and the current
Newmodel09.xls has up and died, then you can go back to ver-
sion 08 and pick up the work again. You will have lost only 30