You will have to set formulas for new columns. For example these are formulas for row no. 3:
- Percentage (column "AB") = AA3 / sum($AA$1:$AA)
- Total percentage (column "AC") = AC2+AB3
- ABC (column "AD") = if(AC2<70%;"A";if(AC2<90%;"B";"C"))
Using these formulas, you will automatically get your products classified as "A", "B" or "C". You can change the percentage for each class to fit your business. I have used 70% for class A, 20% for class B and last 10% for class C products.
In the above example, I have total of 352 different products classified as:
- 28 (cca 8%) class A products that account for 70% of total yearly consumption value.
- 96 (cca 27%) class B products that account for 20% of total yearly consumption value.
- 228 (cca 65%) class C products that account for 10% of total yearly consumption value
It's a good practice to combine ABC method with XYZ method and create an ABCXYZ matrix that will help you better analyze your inventory.