Excel: Create ABC analysis - how it works
If you want to do an ABC analysis, Excel can save you a lot of work. In this practical tip, we will show you how to create an ABC analysis in just a few steps.
First steps: Create ABC analysis in Excel
In this example we create a classic ABC analysis. The products are measured based on their percentage of total sales. The A products have a value share of 80%, the B products 15%, the C products only 5%.
- First we enter our data and thus create the basis of the ABC analysis.
- Column A contains the names of the individual products, column B the absolute sales figures (see picture).
- In column A, the values are also summed up in the last cell. For this we use the command "= SUM (B2: B6)".
- On the basis of the picture you can understand the calculation steps in the next paragraph much better.
Excel: Create ABC analysis
- Mark the first and second column (A and B) and right click on the marked area. Under the "Sort" category, select the "Sort by size (ascending)" option. Note: Do not mark the total in column A.
- In column C we now calculate the percentage turnover of the products. Enter the command "= B2 / $ B $ 7" in cell C2. Then click on the C2 field and double-click on the small square at the bottom right of the field.
- In column D we now calculate the cumulative turnover. Write the command "= C2" in D2 and the command "= D2 + C3" in cell D3. Then double-click again on the square in the D3 field.
- After the percentages in column C, you can now enter the individual classes A, B and C in column E.
- Note: If you get decimal numbers instead of percentages, mark the fields and click on the percent symbol at the top of the menu bar.
In the next practical tip, we will show you how to calculate compound interest with Excel.