ABC analysis

johannordgren

New Member
Joined
Apr 24, 2018
Messages
30
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!
 
Last edited:

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Apr 24, 2018
Messages
30
Ofc I don't need it to be exact, I just need a rough estimate.

How would such a formula work do mean?
 

billfinn

Board Regular
Joined
Jun 7, 2018
Messages
114
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,788
Messages
5,470,789
Members
406,721
Latest member
Laiceyshae

This Week's Hot Topics

Top