statement follow GAAP.
The balance sheet balances: the total assets must equal the
total liabilities and net worth. This balancing is done
through the use of â€˜â€˜plugâ€™â€™ numbers (see Chapter 7). With
the accounting interrelationships correctly in place, the
cash flow numbers will also â€˜â€˜footâ€™â€™ (see Chapter 11), i.e.,
the changes in cash flow must equal the change in the
cash on the balance sheet.
Although this is not a â€˜â€˜how-toâ€™â€™ book on Microsoft Excel, the
spreadsheet functions and controls discussed in this book are
those of Excel as this is now the software of choice for spread-
sheets. However, the approaches outlined here for building a
model will work on any spreadsheet program, although you
will have to make adjustments for any differences between
Excel and that program.
The screen captures are from Excel XP, which, aside from
the look, show little change from earlier versions of Excel. Other
illustrations show the general look of Excel.
Commands in Excel are described in this book using the â€˜â€˜>â€™â€™
notation. Thus, the sequence for saving a file would be shown
as File > Save, for example.
This book is just a part of what I have learned in my career as a
financial modeler in investment banking, so in thanking those
who have helped me in the writing of this book, I must give
thanks to all with whom I have worked, including the many
hundreds of colleagues in J.P.Morgan (past) and JPMorgan
Chase (present), who gave me encouragement and constructive
feedback through all of the many generations of financial models
I have developed for that firm.
In looking back at my career and how I started to build
financial models, I must return to the first time I saw a new-
fangled white box sitting on somebodyâ€™s desk sometime in the
early 1980s. I remember asking, â€˜â€˜What do you do with this?â€™â€™
And my colleague Lillian Waterbury said: â€˜â€˜Type â€˜Lotusâ€™ at the C
prompt sign.â€™â€™ I did, and at this first PC I caught my earliest
glimpse of the spreadsheet (it was Lotus 1-2-3 Release 1A).
This would be a new direction for me. Thanks, Lillian.
Thanks to my friends and colleagues from the Financial
Advisory Group. Sue McCain and Carol Brunner gave me my
first chance to work as a modeler and it made all the difference.
Juan Mesa taught me what clear thinking was about when we
built a Latin American model with financial accounting.
Christopher Wasden was my guide in the arcane accounting
for banks when we built a model for banks.
I worked together with Jim Morris and Humphrey Wu in
New York and Mike Koster in London and consider them as
cohorts and comrades-in-arms in the arcane alchemy of finance,
accounting, Excel, and Visual Basic for Applications that is the
art of financial modeling. We all gave our best to produce mod-
eling packages that were often more than the sum of their parts.
Thanks, Jim, Humphrey, and Mike.
In the new JPMorgan Chase, Pat Sparacio, Marguerita
Courtney, and Leng Lao were enthusiastic supporters of my
work, and I thank them. Jay Chapin, independent training con-
sultant, read the manuscripts and cheered me on from his home-
base in Houston. Thanks, Jay. Fern Jones, a colleague and friend
from my earliest days in finance so many years ago, also read the
manuscript and encouraged me through the dark hours that
probably every author experiences. Thanks also to Sumner
Gerard, who took the time late into the night to look over the
Finally, thanks to Susan Cabral, now of Cabral Associates,
who in 1967 built in the mainframe computer the first financial
projection model for J.P. Morgan, and quite possibly for Wall
Street. Susanâ€™s model design was still in use 15 years later and
it was the starting point for me when I began modeling for the
PC. Her design is present in almost all the models I have devel-
oped in my career. Thank you, Susan, for being the pioneer and
for showing me the way.
This page intentionally left blank.
A Financial Projection
This chapter will explain what projection models do and how
they differ between industries. There is an overview of how
projection models are used and what bits of information are
important. The three roles you perform when you do financial
modeling are covered. Finally, a suggestion about where to put
the computer mouse may help in relieving arm tension.
THE CASE FOR STANDARDIZED
Although this book will tell you how to create your own financial
model, its underlying message is that a model that can be used
across a group becomes that much more effective. It is natural to
think that a financial model is primarily a tool for quantitative
analysis. But, to the extent that a model is the standard for
a group, or even for a firm, it becomes much more than that:
it becomes a communications platform. A standardized model
achieves this in several ways:
1. It conveys to its users the analytical methodologies
that others in the group are using, because those are
embedded in its structure.
2. It becomes in its own right a teaching tool, letting new
users understand how the standard analysis should be
3. As colleagues agree to use the same model, it becomes
the common yardstick of analysis, a way to foster
cooperation and partnership across groups. Credit or
investment review committee members who are familiar
with how the numbers have been produced and how the
ratios have been calculated can proceed to the qualitative
analysis that much more quickly and reach their
decisions with greater confidence. The economic impact
is usually significant: good (or better) decisions are made;
and bad choices are avoided altogether.
4. When one standard model is used across different
projects in different industries, it facilitates management
review and oversight. To the extent that the model
includes the preferred standard analytical methodologies,
it is also a form of insurance against nonstandard
approaches to analysis.
AN ESTIMATOR, NOT A PREDICTOR
A projection model is not a crystal ball, and its output does not
dictate what the future will be. It is merely a tool to estimate
what a companyâ€™s future financial condition might be, given
certain assumptions about its performance. Conversely, it is a
tool to test what needs to happen in order for a particular
performance goal to be reached.
It is easy, for example, for a chief financial officer to say, â€˜â€˜We
will have enough cash flow in the next five years to retire $100
million of our debt.â€™â€™ This may well be true, but the validity in
such a statement lies in what needs to happen. If the statement is
based on conservative forecasts consistent with the companyâ€™s
recent performance and its current position and reputation in
its industry, then this is good and fine. If, on the other hand,
the $100 million is attainable only through rapid, unrealistic,
and unprecedented increases in revenues, then it is very likely
that the CFOâ€™s statement is just so much hot air.
A Financial Projection Model 3
This role as a testing tool means that a projection model is
best when it can allow you to change the inputs quickly for a
series of sensitivity tests. For example, what would be the oper-
ating cash flow if revenues increased by 3, 5, or 10 percent while
margins improved, held steady, or worsened? We can add other
variations in other accounts. Given all the accounts in a com-
panyâ€™s financial statement, the permutations of the sensitivities
can be nearly limitless. In fact, we can run the danger of having a
tool that can produce so much â€˜â€˜informationâ€™â€™ that it becomes
useless. So part of the exercise in building and using such a
model is knowing how to make the best use of it. Chapter 13
gives a review of the main points to keep in mind in developing
PROJECTION MODELS FOR DIFFERENT
The type of model that we will be building is most appropriate
for manufacturing- or industrial-type companies. In this type,
sales are the main revenue generator, and the net income line
in the income statement shows the result of revenue less
The balance sheet is a listing of the assets and liabilities
related to the production facilities required to produce the pro-
duct for sale and the financing to support these activities.
Shareholdersâ€™ equity shows the amount of equity capital in the
Service companies, where the revenues are derived from the
selling of a service, can also fit this framework.
Banks produce their revenues not be selling a product or service,
but by the interest yield on their main assets: the loans they have
in their loan portfolio on the balance sheet. Because banks gen-
erally have to borrow the money that they lend, they also incur
interest expense. Thus, the equivalent â€˜â€˜sales revenueâ€™â€™ line for
banks is something called â€˜â€˜net interest earningsâ€™â€™: this is the
interest income they receive on their loans, less the interest
expense on their funding liabilities.
Developing a projection model for a bank is more difficult,
primarily because of the need to include regulatory capital
requirements in the model. In the United States, banks have to
have two types of capital, called Tier I and Tier II, and a bank
must meet minimum requirements for its capitalization. What
this means is that as the model makes its projections, it also
has to keep these accounts in line with the requirements. Bank
modeling is not covered in this book.
Insurance companies can be described as a combination of a
service company earning premiums and an investment company
making interest income earnings from its investments (from all
the cash received in premiums, less what has to be paid out in