How to Calculate Altman Z Score in Excel (3 Suitable Examples)

If you are looking for some special tricks to calculate the Altman Z score in Excel, you’ve come to the right place. There are numerous ways to calculate the Altman Z score in Excel. This article will discuss three suitable examples to calculate the Altman Z score in Excel. Let’s follow the complete guide to learn all of this.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and examples in different spreadsheets for a clear understanding.

What Is Altman Z Score?

Using the Altman Z-score, you can predict if a company will be bankrupt within two years. Developed by Edward Altman, it measures the likelihood of a company going bankrupt. By combining five financial ratios, it measures the probability.

Significance of Altman Z Score

The significance of the Altman Z score:

• The Altman Z score of <1.8 indicates financial distress and a high risk of bankruptcy for the company.
• The company’s Altman Z score is 1.8 to 3, which indicates it is in the gray area and will likely file for bankruptcy.
• The company’s Altman Z score is greater than 3, which indicates it is in the safe area and will unlikely file for bankruptcy.

3 Suitable Examples to Calculate Altman Z Score in Excel

We will use three effective and tricky examples to calculate the Altman Z score in Excel. The first one is for a public manufacturing company, the second one is Model A which is for a private manufacturing company and the third one is called Model B which is for a general firm. This section provides extensive details on the three examples.  You can use either one for your purpose, they have a wide range of flexibility when it comes to customization. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge.

1. Altman Z Score for Public Manufacturing Company

Here, we will demonstrate how to calculate the Altman Z score for a public manufacturing company in Excel. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. For demonstration purposes, we are going to use the following dataset where values of each particular are given for a public manufacturing company. Here, we will use the IF function to calculate the zone condition of the Altman Z score.

The Altman Z score formula for a public manufacturing company is shown below:

Z = 1.2 X1+1.4 X2+3.3 X3+0.6 X4+1.0 X5

Let’s walk through the following steps to calculate the Altman Z score.

📌 Steps:

• First of all, to calculate the Altman Z score, we have to find out some coefficients. To calculate the coefficient X1, we will use the following formula in cell F5.

Here, cell C5 is the total current assets and cell C6 is the total current liabilities. Now, we calculate the working capital by subtracting total current assets from total current liabilities. Then dividing the working capital by total assets, we will get the coefficient X1.

• Press Enter.

• Next, to calculate the coefficient X2, we will use the following formula in cell F6.

We obtain coefficient X2 by dividing retained earnings by total assets.

• Then, press Enter.

• Next, to calculate the coefficient X3, we will use the following formula in cell F7.

We obtain coefficient X3 by dividing operating income by total assets.

• Then, press Enter.

• Next, to calculate the coefficient X4, we will use the following formula in cell F8.

Here, we obtain coefficient X4 by dividing the market value of equity by total assets.

• Then, press Enter.

• Next, to calculate the coefficient X5, we will use the following formula in cell F9.

We obtain coefficient X5 by dividing sales by total assets

• Then, press Enter.

• Now enter the constant value in the range of cell G5:G9 from the Altman Z score formula as shown below.

• Next, to calculate the Altman Z Score, we will use the following formula in cell I5.

• Then, press Enter.

• As a consequence, we will get the Altman Z score value for this company is 2.797429.
• Next, to calculate the zone condition, we will use the following formula in cell J5.

Here, the IF function determines whether the condition is met and returns one value if it is true and returns another value if it is false.

• Then, press Enter.

From the above calculation, we obtain a Z-score for this public manufacturing company is 2.797429. It indicates that the company is not in a safe condition, it is in grey condition and will likely file for bankruptcy.

Read More: How to Calculate Critical Z Score in Excel (3 Suitable Examples)

2. Altman Z Score for Private Manufacturing Company

Now, we will demonstrate another model which is basically Model A. This model is for calculating the Altman Z score for a private manufacturing company.  For demonstration purposes, we are going to use the following dataset where values of each particular are given for a private manufacturing company. Here, we will use the IF function to calculate the zone condition of the Altman Z score.

The Altman Z score formula for a private manufacturing company is shown below:

Z = 0.717X1+0.847X2+3.107X3+0.42X4A+ 0.998 X5

Let’s walk through the following steps to calculate the Altman Z score.

📌 Steps:

• First of all, to calculate the Altman Z score, we have to find out some coefficients. To calculate the coefficient X1, we will use the following formula in cell F5.

Here, cell C5 is the total current assets and cell C6 is the total current liabilities. Now, we calculate the working capital by subtracting total current assets from total current liabilities. Then dividing the working capital by total assets, we will get the coefficient X1.

• Then, press Enter.

• Next, to calculate the coefficient X2, we will use the following formula in cell F6.

We obtain coefficient X2 by dividing retained earnings by total assets.

• Then, press Enter.

• Next, to calculate the coefficient X3, we will use the following formula in cell F7.

Here, we obtain coefficient X3 by dividing operating income by total assets.

• Then, press Enter.

• Next, to calculate the coefficient X4A, we will use the following formula in cell F8.

We obtain coefficient X4A by dividing the net worth by total liabilities.

• Then, press Enter.

• Next, to calculate the coefficient X5, we will use the following formula in cell F9.

Here, we obtain coefficient X5 by dividing sales by total assets

• Then, press Enter.

• Now enter the constant value in the range of cell G5:G9 from the Altman Z score formula as shown below.

• Next, to calculate the Altman Z Score, we will use the following formula in cell I5.

• Then, press Enter.

• As a consequence, we will get the Altman Z score value for this company to be 1.80.
• Next, to calculate the zone condition, we will use the following formula in cell J5.

Here, the IF function determines whether the condition is met and returns one value if it is true and returns another value if it is false.

• Then, press Enter.

• From the above calculation, we obtain a Z -score for this public manufacturing company is 1.80. It indicates that the company is not in a safe condition, it is in grey condition and will likely file for bankruptcy.

3. Altman Z Score for General Firm

Now, we will demonstrate another model which is basically  Model B. This model is for calculating the Altman Z score for a private manufacturing company.  For demonstration purposes, we are going to use the following dataset. Here, we will use the IF function to calculate the zone condition of the Altman Z score.

The Altman Z score formula for a general firm is shown below:

Z = 6.56X1+3.26X2+6.72X3+1.05X4A

Let’s walk through the following steps to calculate the Altman Z score.

📌 Steps:

• First of all, to calculate the Altman Z score, we have to find out some coefficients. To calculate the coefficient X1, we will use the following formula in cell F5.

Here, cell C5 is the total current assets and cell C6 is the total current liabilities. Now, we calculate the working capital by subtracting total current assets from total current liabilities. Then dividing the working capital by total assets, we will get the coefficient X1.

• Then, press Enter.

• Next, to calculate the coefficient X2, we will use the following formula in cell F6.

We obtain coefficient X2 by dividing retained earnings by total assets.

• Then, press Enter.

• Next, to calculate the coefficient X3, we will use the following formula in cell F7.

Here, we obtain coefficient X3 by dividing operating income by total assets.

• Then, press Enter.

• Next, to calculate the coefficient X4A, we will use the following formula in cell F8.

We obtain coefficient X4A by dividing the net worth by total liabilities.

• Then, press Enter.

• Now enter the constant value in the range of cell G5:G8 from the Altman Z score formula as shown below.

• Next, to calculate the Altman Z Score, we will use the following formula in cell I5.

• Then, press Enter.

• As a consequence, we will get the Altman Z score value for this company to be 3.006248.
• Next, to calculate the zone condition, we will use the following formula in cell J5.

Here, the IF function determines whether the condition is met and returns one value if it is true and returns another value if it is false.

• Then, press Enter.

• From the above calculation, we obtain a Z -score for this public manufacturing company is 3.006248. It indicates that the company is in a safe condition and will unlikely file for bankruptcy.

💬 Things to Remember

✎ When you are going to calculate the coefficients, carefully select the cells.

✎ Next, you have to adjust row height after following each method. When you use the nested IF function carefully gives all the required parentheses.

Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to calculate the Altman Z score in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!

Related Articles

• How to Calculate P Value from Z Score in Excel (with Quick Steps)
• How to Calculate BMI Z Score in Excel (with Quick Steps)