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:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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,130,029
Messages
5,639,623
Members
417,101
Latest member
amoverton2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top