ABC analysis

[johannordgren]

As the title suggests im working on an ABC-analysis, and since I'm here, naturally I need some help/advice/tips..

Link o sheet:
https://1drv.ms/x/s!Asdl-iuZDQdchJ0h1ICV0Vx9usUQ0A

If you don't know what an ABC-analysis does, it sorts "thing" after useage/cost/worth etc. (whatever you would like really) after certain criterias, whereas "A-articles" is articles that stand for 80% of a companys total income, but only stand for 20% of the companys total number of different articles. This is called the "80-20 rule".

So..Problem being.
I would like some formula (or some other way) to sort out theese articles.
As you can see in the sheet I have a column for "avg.mothly use" and one for "STD cost". Theese are the columns I would like to use.

End of note: Please help me sort out the articles that stand for 80% of the total "STD cost" and 20% of the total "montly average use"

My excel language is set to swedish. I don't know if it automaticly changes to your preferred language.

Thank you!

 

[Gerald Higgins]

Hi.

I don't want to follow your link.

The 80-20 rule is not really a hard rule, it's a guide that is often roughly accurate, but rarely exactly accurate.

I expect you will find that items that account for 80% of total STD cost do not actually account for exactly 20% of monthly average use.
You need to decide for yourself which measure you want to use as the driving criteria here - maybe STD cost, maybe monthly average use, but not both.
In any case, you may also find that no items account for EXACTLY 80% of anything - maybe 10 items account for 79%, and 11 items account for 83%, so you will need to decide how to handle that.

What I would do, is pick one measure to use as the main determining factor - let's say STD cost.
Calculate the STD cost for each item.
Then sort the data using STD cost as the first sort criteria.
Then apply a formula that works out the cumulative STD cost for each item plus the previous items, as a percentage of total STD costs.
Then determine manually at which point the 80% level is hit / crossed (you can do this by formula as well if you like).

It might be interesting to also repeat the above exercise on the other measure, monthly average use.

 

[johannordgren]

Ofc I don't need it to be exact, I just need a rough estimate.

How would such a formula work do mean?

 

[billfinn]

I always do ABC analysis using the total number of pieces used plus number of pieces sold multiplied by the cost of the item. then go through an exercise like this;
Create a worksheet with the column headings Item Number, UOM, Annual Usage and Cost in columns A,B,C and D. Add a formula to multiply the annual use by the cost to get the annual dollar value in Column E. Sort by descending order
Enter =E2 in F2. Enter =E3+F2 in F3
Copy the second formula to the bottom of the list.
The result is a complete running total of Inventory.
Enter Percentage of Running Total in column G and enter =F2/$F$21
Copy the formula to the bottom of the list
Enter ABC in column H. Enter this formula in Cell H2
=IF(G2<0.7,”A”,IF(G2<0.95,”B”,”C”))
Copy the formula to the bottom of the list.
Inventory is now ranked in ABC order.

Bill