• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

ABC Analysis

Thomas Kuriakose

Active Member
Dear All,

I read the ABC analysis in chandoo.org and tried to apply the template for an ABC analysis on total usage and unit value, but due to zero usage values the formulas and not giving the figures.

Column D = is the unit price of the item
Column E = is the total consumption in 2 years
Column F = is the total consummation value or usage value.

We need the summary as in summary sheet to be populated based on the ABC analysis.
The number of items should be the count of items in A Class, B Class and C Class respectively.
The Items with no usage/consumption should be another class.

Please find attached the file for you reference.


Kindly help in using this template to get the summary values.

thanks,

with regards,
thomas
 

Attachments

  • ABC.xlsx
    182.2 KB · Views: 4
Thomas
The problem isn't the 0's
Goto cell ABC Analysis! E577 and fix the contents

Voila
 
Dear Hui,

Apologies for overlooking cell E577 value. Thank you for the correction on this.

But I am not able to get the result in the summary sheet as required. The total number of items in the data is 1551, but the ABC classification is showing other figures greater than 1551.

Class A should be 70% of the total yearly movement of inventory
Class B should be 20% of the total yearly movement of inventory
Class C should be 10% of the total yearly movement of inventory &
Class D should be items with no movement.

Kindly help in getting this value and the percentage as per the summary sheet.

thank you so much.

with regards,
thomas
 

Attachments

  • ABC.xlsx
    232.3 KB · Views: 0
Hi Thomas ,

Can you explain what dictates the classification of items as Class A , Class B or Class C ? I assume Class D is all those items whose # Units value is 0.

Narayan
 
Dear Sir,

Kindly find below the breakup of the classes

Class A= 70% of the total yearly movement of inventory.
Class B=20% of the total yearly movement of inventory.
Class C=10% of the total yearly movement of inventory.
Class D = No movement and slow moving material (SMM)

The value entered in the units column is the consumption of the materials in the past 12 months.

The total cost column is (Total consumption in last 12 months X unit cost).

Thanks you so much.

with regards.
thomas
 
Hi Thomas ,

Words do not mean anything ; please explain with reference to worksheet cells / rows / columns / tabs ...

70 % of what value will make an item eligible to be called a Class A item ?

Narayan
 
Dear Sir,

Sorry for not providing he references, was not able to use the parameters in the chandoo format.

I have prepared a sheet with the ABCD analysis manually, which I would like to use in the chandoo format.

Please find attached the sheet.

Thanks

with regards,
thomas
 

Attachments

  • ABC Analysis -1.xlsx
    178.7 KB · Views: 3
Hi Thomas ,

What I can understand from your latest upload is :

Class A items are those which are within a cumulative percentage of 70 % or more. The cumulative percentage value in column H crosses 70 % in row 90 , with a value of 70.21 %.

Adding 20 % to this will result in a Class B threshold of 90.21 %.

Thus , Class B items are those which are within a cumulative percentage of 90.21 % or more. The cumulative percentage value in column H crosses 90.21 % in row 237 , with a value of 90.23 %.

Class C items comprise those whose cumulative percentage is less than or equal to 100 % , with a movement ( consumption value in column D ) greater than zero ; this is all those items in rows 238 through 887.

Class D items are all those whose consumption value in column D is equal to zero ; these are all those items in rows 888 through 1552.

Can you confirm that all this is correct ?

Narayan
 
Respected Sirs,

Thank you so much for the solution provided.

One question Hui Sir,

I need to understand the no. of items and how is this calculated. Can we have the actual counts of the items in this

upload_2015-7-14_10-15-23.png

Thank you so much sirs once again for this great help.

with regards,
thomas
 
It is the cummulative Number of Items less than or equal to the No of Items * the %
ie: There are 20128 Items (Sum Column D)

There are 20128 *1% = 210 items in the 1st 1%
There are 20128 *10% = 2103 items in the 1st 10% (1% + 9%)
There are 20128 *30% = 6308 items in the 1st 30% (1% + 9% + 20%)
There are 20128 *100% = 21028 items in the 1st 100% (1% + 9% + 20% + 70%)
 
Back
Top