What Is the 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 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.
How to Calculate the Altman Z Score in Excel: 3 Suitable Examples
Example 1 – Altman Z Score for a Public Manufacturing Company
We are going to use the following dataset where values of each particular are given for a public manufacturing company. 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
Steps:
- 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.
=(C5-C6)/C14
Cell C5 is the total current assets and cell C6 is the total current liabilities. We calculate the working capital by subtracting total current assets from total current liabilities. By dividing the working capital by total assets, we will get the coefficient X1.
- Press Enter.
- To calculate the coefficient X2, we will use the following formula in cell F6.
=C10/C14
We obtain coefficient X2 by dividing the retained earnings by total assets.
- To calculate the coefficient X3, we will use the following formula in cell F7.
=C9/C14
We obtain coefficient X3 by dividing the operating income by total assets.
- To calculate the coefficient X4, we will use the following formula in cell F8.
=C11/C14
We obtain coefficient X4 by dividing the market value of equity by total assets.
- To calculate the coefficient X5, we will use the following formula in cell F9.
=C12/C14
We obtain coefficient X5 by dividing the sales by total assets.
- Enter the constant values in the range of cell G5:G9 from the Altman Z score formula as shown below.
- To calculate the Altman Z Score, use the following formula in cell I5.
=G5*F5+G6*F6+G7*F7+G8*F8+G9*F9
- The Altman Z score value for this company is 2.797429.
- To calculate the zone condition, use the following formula in cell J5.
=IF(I5>3,"Safe",(IF(I5<1.8, "Distress", "Grey")))
We obtain a Z-score of 2.797429. It indicates that the company is not in a safe condition and will likely file for bankruptcy.
How Does the Formula Work?
- The IF(I5<1.8, “Distress”, “Grey”) function determines whether the condition cell I5 (means Z score value) is met, and will return “Distress” if it is true and return “Grey” if it is false.
- The IF(I5>3,”Safe”,(IF(I5<1.8, “Distress”, “Grey”))) formula determines whether the condition cell I5 (means Z score value) is met, and will return “Safe” if it is true and return “Distress” or “Grey” if it is false.
Example 2 – Altman Z Score for a Private Manufacturing Company
We are going to use the following dataset where values of each particular are given for a private manufacturing company.
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
Steps:
- To calculate the Altman Z score, we have to determine the coeficients.
- To calculate the coefficient X1, we will use the following formula in cell F5.
=(C5-C6)/C15
Cell C5 is the total current assets and cell C6 is the total current liabilities. We calculate the working capital by subtracting total current assets from total current liabilities. By dividing the working capital with total assets, we will get the coefficient X1.
- To calculate the coefficient X2, we will use the following formula in cell F6.
=C10/C15
We obtain coefficient X2 by dividing the retained earnings by total assets.
- To calculate the coefficient X3, we will use the following formula in cell F7.
=C9/C15
We obtain coefficient X3 by dividing the operating income by total assets.
- To calculate the coefficient X4A, we will use the following formula in cell F8.
=C13/C14
We obtain coefficient X4A by dividing the net worth by total liabilities.
- To calculate the coefficient X5, we will use the following formula in cell F9.
=C12/C15
We obtain coefficient X5 by dividing sales by total assets.
- Enter the constant value in the range of cell G5:G9 from the Altman Z score formula as shown below.
- To calculate the Altman Z Score, we will use the following formula in cell I5.
=G5*F5+G6*F6+G7*F7+G8*F8+G9*F9
- We will get the Altman Z score value for this company to be 1.80.
- To calculate the zone condition, we will use the following formula in cell J5.
=IF(I5>3,"Safe",(IF(I5<1.8, "Distress", "Grey")))
- 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 and will likely file for bankruptcy.
How Does the Formula Work?
- Here, the IF(I5<1.8, “Distress”, “Grey”) function determines whether the condition cell I5 (means Z score value) is met, and will return “Distress” if it is true and return “Grey” if it is false.
- The IF(I5>3,”Safe”,(IF(I5<1.8, “Distress”, “Grey”))) formula determines whether the condition cell I5 (means Z score value) is met, and will return “Safe” if it is true and return “Distress” or “Grey” if it is false.
Example 3 – Altman Z Score for a General Firm
We will demonstrate another model which is Model B.
The Altman Z score formula for a general firm is shown below:
Z = 6.56X1+3.26X2+6.72X3+1.05X4A
Steps:
- To calculate the coefficient X1, we will use the following formula in cell F5.
=(C5-C6)/C15
Cell C5 is the total current assets and cell C6 is the total current liabilities. We calculate the working capital by subtracting total current assets from total current liabilities. By dividing the working capital by total assets, we will get the coefficient X1.
- To calculate the coefficient X2, we will use the following formula in cell F6.
=C10/C15
We obtain coefficient X2 by dividing retained earnings by total assets.
- To calculate the coefficient X3, we will use the following formula in cell F7.
=C9/C15
We obtain coefficient X3 by dividing the operating income by total assets.
- To calculate the coefficient X4A, we will use the following formula in cell F8.
=C13/C14
We obtain coefficient X4A by dividing the net worth by total liabilities.
- Enter the constant values in the range of cell G5:G8 from the Altman Z score formula as shown below.
- To calculate the Altman Z Score, use the following formula in cell I5.
=G5*F5+G6*F6+G7*F7+G8*F8+G9*F9
- We will get the Altman Z score value for this company as 3.006248.
- To calculate the zone condition, we will use the following formula in cell J5.
=IF(I5>3,"Safe",(IF(I5<1.8, "Distress", "Grey")))
- The function indicates that the company is in a safe condition and will unlikely file for bankruptcy.
How Does the Formula Work?
- The IF(I5<1.8, “Distress”, “Grey”) function determines whether the condition cell I5 (means Z score value) is met, and will return “Distress” if it is true and return “Grey” if it is false.
- The IF(I5>3,”Safe”,(IF(I5<1.8, “Distress”, “Grey”))) formula determines whether the condition cell I5 (means Z score value) is met, and will return “Safe” if it is true and return “Distress” or “Grey” if it is false.
Read More: How to Calculate Z Score in Excel
Download the Practice Workbook
Related Articles
- How to Calculate Critical Z Score in Excel
- How to Calculate P Value from Z Score in Excel
- How to Calculate Z-Score with 95 Confidence Interval in Excel
- How to Calculate Probability from Z-Score in Excel
<< Go Back to Z Score in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
