Home > Business Tools
An Excel Tutorial:
Predict Business Bankruptcy
Using Z Scores with Excel
Here's a simple analytical method that can help you to predict
whether a business is headed for bankruptcy.
by Charley Kyd
"General Motors Corp. and Ford Motor Co., the two biggest U.S.
automakers, have about a 46 percent chance of default within five years,
according to Edward Altman, a finance professor at New York University's
Stern School of Business.
"'Both are in very serious shape and the markets reflect that,' Altman,
the creator of the Z-score mathematical formula that measures bankruptcy
risk, said in an interview with Bloomberg Television. The model shows
that these companies are 'on the verge of bankruptcy,' he said."
"GM, Ford `On the Verge of Bankruptcy,' Altman Says",
Bloomberg, July 22, 2008
Nearly 9,000 US companies filed for bankruptcy in the first quarter of
2008. That number is 38% greater than the same period one year ago. Will your
customer file next week? Will a critical vendor? A key investment? Your own
If you knew that a bankruptcy might occur in the next year or two,
you could better protect yourself. But how can you predict which
businesses are likely to go bankrupt and which are not?
About 40 years ago, Edward I. Altman set out to answer this question.
Altman, then a financial economist at New York University's Graduate School
of Business, developed a model for predicting the likelihood that a firm
would go bankrupt. This model uses five financial ratios that combine in
a specific way to produce a single number. This number, called the Z
Score, is a general measure of corporate financial health.
Later, Altman developed a modified version for private manufacturing
firms and a second version for use by all businesses. This article
describes all three versions, which are easy to use in Excel.
A Short Z-Score History
In 1966 Altman selected a sample of 66 corporations, 33 of which had
filed for bankruptcy in the past 20 years, and 33 of which were randomly
selected from those that had not. The asset size of all corporations
ranged from $1 million to $26 million...approximately $5 million to $130
million in 2005 dollars.
Altman calculated 22 common financial ratios for all 66 corporations.
(For the bankrupt firms, he used the financial statements issued one
year prior to bankruptcy.) His goal was to choose a small number of
those ratios that could best distinguish between a bankrupt firm and a
To make his selection Altman used the statistical technique of
multiple discriminant analysis. This approach shows which
characteristics in which proportions can best be used for determining to
which of several categories a subject belongs: bankrupt versus nonbankrupt, rich versus poor, young versus old, and so on.
The advantage to MDA is that many characteristics can be combined
into a single score. A low score implies membership in one group, a high
score implies membership in the other group, and a middling score causes
uncertainty as to which group the subject belongs.
Finally, to test the model, Altman calculated the Z Scores for new
groups of bankrupt and nonbankrupt firms. For the nonbankrupt firms,
however, he chose corporations that had reported deficits during earlier
years. His goal was to discover how well the Z Score model could
distinguish between sick firms and the terminally ill.
Altman found that about 95% of the bankrupt firms were correctly
classified as bankrupt. And roughly 80% of the sick, nonbankrupt firms
were correctly classified as nonbankrupt. Of the misclassified
nonbankrupt firms, the scores of nearly three fourths of these fell into
the gray area.
The Z Score Ingredients
The Z Score is calculated by multiplying each of several financial
ratios by an appropriate coefficient and then summing the results. The
ratios rely on these financial measures:
- Working Capital is equal to Current Assets minus Current
- Total Assets is the total of the Assets section of the Balance
- Retained Earnings is found in the Equity section of the Balance
- EBIT (Earnings Before Interest and Taxes) includes the income or
loss from operations and from any unusual or extraordinary items but
not the tax effects of these items. It can be calculated as follows:
Find Net Income; add back any income tax expenses and subtract any
income tax benefits; then add back any interest expenses.
- Market Value of Equity is the total value of all shares of
common and preferred stock. The dates these values are chosen need
not correspond exactly with the dates of the financial statements to
which the market value is compared.
- Net Worth is also known as Shareholders' Equity or, simply,
Equity. It is equal to Total Assets minus Total Liabilities.
- Book Value of Total Liabilities is the sum of all current and
long-term liabilities from the Balance Sheet.
- Sales includes other income normally categorized as revenues in
the firm's Income Statement.
Use balance sheet figures from the end of the reporting period for
all Z Score calculations.
The following table shows how these measures are used to calculate
the three versions of the Z Score. The table is explained below.
In other words, the three Z Score versions (described below) are calculated as follows:
- Z = 1.2*X1 + 1.4*X2 + 3.3*X3 + .6*X4 + X5
- Z1 = .717*X1 + .847*X2 + 3.107*X3 + .42*X4A + .998*X5
- Z2 = 6.56*X1 + 3.26*X2 + 6.72*X3 + 1.05*X4A
Reasons for Multiple Versions
Two of the ratios shown in the figure have tended to limit the
usefulness of the original Z Score measure.
One of these ratios is X4, the Market Value of Equity divided by
Total Liabilities. Obviously, if a firm is not publicly traded, its
equity has no market value. So private firms can't use the Z Score.
The other problem is X5, Assets Turnover. This ratio varies
significantly by industry. Jewelry stores, for example, have a low asset
turnover while grocery stores have a high turnover. But since the Z
Score expects a value that is common to manufacturing, it could be
biased in such a way that a healthy jewelry store looks sick and a
sickly grocery store looks healthy.
To deal with these problems, Altman used his original data to
calculate two modified versions of the Z Score, shown above. The Z Score
is for public manufacturing companies; the Z1 Score is for private
manufacturing companies; and the Z2 is for general use.
Therefore, according to the table, if a company's Z2 score is greater
than 2.60, it's currently safe from bankruptcy. If the score is less
than 1.10, it's headed for bankruptcy. Otherwise, it's in a gray area.
How to Interpret the Z Score
The Z Score is not intended to predict when a firm will file a formal
declaration of bankruptcy in a federal district court. It is instead a
measure of how closely a firm resembles other firms that have filed for
bankruptcy. It is a measure of corporate financial distress, a measure
of economic bankruptcy.
How accurately does the Z Score measure economic bankruptcy? The
original model has drawn several statistical objections over the years.
The model uses unadjusted accounting data; it uses data from relatively
small firms; and it uses data that today is nearly 60 years old.
And yet, despite these concerns, the original Z Score model is the
best-known and most widely used measure of its kind. This measure is far
from perfect, but it's easy to calculate in Excel and many users continue to find it useful.
At last count, for example, Google offered 308,000 links to the phrase,
The Z Score model is a tool that can complement your other analytical
tools. Seldom, however, should you use any of the Z Score measures as
your only means of analysis.