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.

"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 largest customer file next week? Will a critical vendor? A key investment? Your own employer?

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 healthy one.

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 Liabilities.
   
• Total Assets is the total of the Assets section of the Balance Sheet.
   
• Retained Earnings is found in the Equity section of the Balance Sheet.
   
• 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, "Z Score".

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.