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.

**Table of Contents**hide

## 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**.

`=(C5-C6)/C14`

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**.

`=C10/C14`

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**.

`=C9/C14`

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**.

`=C11/C14`

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**.

`=C12/C14`

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**.

`=G5*F5+G6*F6+G7*F7+G8*F8+G9*F9`

- 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**.

`=IF(I5>3,"Safe",(IF(I5<1.8, "Distress", "Grey")))`

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.

**đź”Ž 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.

### 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**.

`=(C5-C6)/C15`

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**.

`=C10/C15`

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**.

`=C9/C15`

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**.

`=C13/C14`

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**.

`=C12/C15`

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**.

`=G5*F5+G6*F6+G7*F7+G8*F8+G9*F9`

- 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**.

`=IF(I5>3,"Safe",(IF(I5<1.8, "Distress", "Grey")))`

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.

**đź”Ž 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.

### 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**.

`=(C5-C6)/C15`

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**.

`=C10/C15`

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**.

`=C9/C15`

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**.

`=C13/C14`

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**.

`=G5*F5+G6*F6+G7*F7+G8*F8+G9*F9`

- 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**.

`=IF(I5>3,"Safe",(IF(I5<1.8, "Distress", "Grey")))`

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.

**đź”Ž 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.

## đź’¬ 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!