# ABC analysis

#### johannordgren

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

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:

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi.

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.

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

How would such a formula work do mean?

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

Replies
9
Views
1K
Replies
1
Views
649
Replies
9
Views
943
Replies
2
Views
1K
Replies
3
Views
239

1,216,126
Messages
6,129,021
Members
449,480
Latest member
yesitisasport

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back