Insurance companies come in two types: life insurance com-
panies and non-life insurance companies.
Forecasts for life insurance companies need good, extensive,
and expensive actuarial data, and even then, assumptions of how
many insurees the company will have over time and the long
time horizon for its insurees can make the exercise difficult.
Non-life (property and casualty) insurance companies are
easier to model, since the claims can be more easily estimated
via probability theories and the known finite useful lives for
Insurance companies are again a different animal from the
basic industrial/manufacturing companies that we want to
model, so they will not be covered in the book.
WHERE PROJECTION MODELS ARE USEFUL
To lend or not to lend? Or, to put it more bluntly, will we get our
money back if we lend it to this particular company? Thus, mod-
eling for credit analysis necessarily requires a focus on cash flows
A Financial Projection Model 5
and ratios. If we can show that the company will be producing
enough cash in excess of its operational and investment needs
over the term of the loan to repay the loan, then it would be a
â€˜â€˜goâ€™â€™ decision to lend, at least insofar as the numbers are con-
cerned. (Good lending decisions must consider other, qualitative
factors.) The challenge for the credit decisionmakers occurs when
the company is considered a â€˜â€˜goodâ€™â€™ company, but the cash flow
is less robust. This is why skilled and experienced credit officers
are always in demand by lending institutions.
Equity investors need projections to estimate their equity returns
through the internal rate of return (IRR) calculations. In these cal-
culations, it is important to be as precise as possible in modeling
the timing of the investments, so that they are not all the â€˜â€˜year
endâ€™â€™ according to the model. In this case, one often sees quarterly
or even monthly models. This is one reason why many equity
investment models, such as those used in project finance and
leveraged buyout situations, use periodicities shorter than a year.
In a leveraged buyout (or LBO), a company is bought out by a
group of investors, which usually includes the current manage-
ment, using debt to finance the purchase. Modeling such a trans-
action requires a focus on both the debt and equity changes at
the deal date, the effects on the stub year (the portion of the year
subsequent to the transaction), and the remaining forecast years.
On a purchase LBO, goodwill will have to be calculated; on a
recapitalization LBO, it will not.
Mergers and Acquisitions
Where an LBO involves one company, a merger or acquisition
would involve two companies. (Of course, a company could buy
another company and the new company can then buy a third,
and so forth, but we can think of this as a succession of mergers,
each involving only two companies.)
Merger modeling really involves modeling three companies:
the first company, which is the acquirer; the second company,
which is the target; and the third, which is the combined new
company. The acquirer and the target should be modeled sepa-
rately through the forecast period, especially if the two compa-
nies operate in different industries or different sectors of an
With the exception of the numbers for the period from the
last available data date to the deal date, for which some estimates
would be needed, all of the information for the pre-deal period
can be taken straight from the historical data.
Merger accounting is complex because of the need to keep
track of the flows of the two companies and layering in the effects
of the transaction in the capitalization and the cash flows. Asset
revaluations and goodwill calculations add to the complexity.
WHAT TO FOCUS ON
Critical Numbers in Any Projection Model
A useful projection model focuses on only five main points:
The earnings before interest and taxes (EBIT) in the
The earnings before interest, taxes, depreciation, and
amortization (EBITDA) in the income statement
The net income number
The operating working capital (OWC) and capital
expenditures levels, as measures of the use of cash on
the balance sheet
The level of debt on the balance sheet
EBIT is an important number because it shows the earnings
related to the main operations of a company. EBIT is reven-
ues less the expenses that are directly related to the revenue-
generating operations. These operating earnings give you a clue
as to how robust the companyâ€™s business is, outside of other
A Financial Projection Model 7
nonoperating flows such as interest or investment. The trend
over the most recent years can show you how well the company
is positioned for future growth.
EBITDA is EBIT, but with depreciation and amortization of intan-
gibles added back. Depreciation and amortization are noncash
expenses; there is no actual cash that the company has to pay
out. So EBITDA is a good way to arrive at the idea of â€˜â€˜cash
earnings,â€™â€™ the amount of cash generated by the operations.
This can give you a good indication of a companyâ€™s absolute
ability to pay interest. A zero EBIT can mean that there is still
some cash, from the add back of depreciation and amortization; a
zero EBITDA, on the other hand, means that there is absolutely
no cash coming from the revenue-generating activities.
Below EBIT and EBITDA, the net income number is produced by
the inclusion of other nonoperational revenues and expenses.
Usually there are more expenses than revenues, and the biggest
expenses are interest expenses and taxes.
Net income is a useful number because this is the usual
measure of whether a company is â€˜â€˜profitableâ€™â€™ or not and is
the basis of calculations such as earnings per share (EPS). However,
a company can be profitable but still run out of cash because of
large demands for working capital and/or capital expenditures,
so net income (and all other measures of a company) is best
viewed in the context of other factors and ratios.
Operating Working Capital
Working capital by definition is current assets less current lia-
bilities. However, a more useful measure for working capital is
what might be termed operating working capital (OWC). This is
current assets without cash or short-term investments, less current
liabilities without short-term debt (including the current portion
of long-term debt). Thus, OWC is primarily:
Ã¾ Other current assets
Ã€ Accounts payable
Ã€ Other current liabilities
OWC is a measure of how much cash a company must
invest in its operations. Cash and debt are the result of separate
financing decisions. This is why they are excluded from OWC. A
high level of OWC (because of accounts receivables not being
collected quickly and/or poor inventory management, for exam-
ple) means that a company has a large amount of its cash tied up
as receivables and inventory, which limits its ability to use its
cash for other purposes.
Capital expenditures, or capex for short, is the other major use of
cash in the balance sheet. Capex is generally an ongoing expense
because a company must continue to invest in its production
equipment, which over time needs to be maintained or replaced.
Most companies have debt on their balance sheet. Whether a
company has â€˜â€˜too muchâ€™â€™ or â€˜â€˜too littleâ€™â€™ debt is not a function of
the dollar value of the debt, but rather its cash flow to â€˜â€˜serviceâ€™â€™
the debt (i.e., can it pay the ongoing interest expense and make
timely repayments of the debt itself).
In modeling forecast debt levels, you would need to enter
known amortization schedules so that you would have a base
line of the outstanding (and decreasing) debt. A good model with
realistic assumptions will then show what the additional borrow-
ing, if any, would be required in the forecast years.
YOU AS THE MODEL DEVELOPER
You will be wearing many hats when you are a model developer:
You are the finance expert, working with the elements
of the income statement, balance sheet, and cash flow
A Financial Projection Model 9
statement, using your knowledge of GAAP conventions to
produce the correct presentation of the results.
You are the spreadsheet wizard, pushing your knowledge
of Excel to the limit to squeeze the last ounce of perfor-
mance out of your model.
You are the visual designer and virtual architect, manip-
ulating the screen and the structure of your worksheet to
make your model as easy and fun to use as possible. You
give meaning to the term user friendly.
Balancing the Three
How much you focus on each of the three parts will determine
the look and feel of your model. Obviously, a model that looks
spectacularly attractive and is user friendly but produces inaccu-
rate outputs is not what we want. On the other hand, a model
that is powerful and provides useful analytical information
but has an interface so forbidding that no one understands
how to use it is also not our goal. So a balance among the
three approaches is important to get to a final, optimal product.
Give Yourself Time
I hope that the model that you will create if you follow all the
steps in the book will be the first of many that you will build. As
you develop and create more models, it will seem that there is
always a â€˜â€˜nextâ€™â€™ model to do. A good model takes time and
passes through many versions. How many versions exactly?
My experience is that you would need at least three:
1. The first version is the attempt to gather together the
right set of calculations in the right way to get the answer
you want, but typically this results in a model that is not
very user friendly and has lots of errors.