ABC Calculation

Do you use the ABC Analysis function in Visual? How are your results? Mine aren't very good. You see, it includes all part numbers with zero quantity and all obsolete part numbers as well. This skew's the result quite dramatically. For instance, in our case, when reviewing all of the parts that are available to count, 50% are A's and 20% are C's. this is the opposite of what is desirable. I would like the calculation to be based only on those part numbers that have a quantity available to count. Is there a way to do this? We are on 7.1.0 at the moment. Does v9 do it better?

Thank you!

Paul

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

Legacy Contributor

Hi,

We don’t use the ABC analysis application as we find it’s too limiting. We currently do the analysis in excel and update the ABC codes using MS Access. Using excel allows you to apply whatever ranking criteria you desire. I don’t personally know all the criteria that our inventory management team uses. I do know that one of the criteria we use is the number of turns. More turns increases the chance of discrepancies so you want to count those parts more often. This also ensures that there is no distinction between finished goods and raw material when ranking the inventory. One other thing we do is make use of a D code that we assign to long term extremely slow-moving inventory. We usually only count this inventory once a year. The cycle count for our A’s is 30 days, B’s is 60 days and C’s are 180 days. Note that we only look at setting ABC codes every 6 months or so, we don’t do it every day.

You indicated you only want to look at parts with inventory available to count. How do you know the information in the computer is correct? What if you are currently out of inventory at the time of calculation? Inventory on hand should have no bearing on you ABC calculations.

Hope this is helpful,

Regards,

Mark

Legacy Contributor

Thanks Mark. My reasoning for excluding the zero's is due to the volume. We have 9,699 available part numbers to count but there are 117,451 part numbers that we no longer inventory and have none on hand. I can tell Visual to only give me a list of parts to count that have a quantity but my issue is that 42% of our entire inventory on hand is A's. It should be 20%. We don't have the staffing to count over 4,000 part numbers 4 times a year. I don't even know if we can do it once. So I need a better system to give me 20, 30, 50 of A, B & C of what we actually have. I also don't want to be looking for 117k of part numbers that we don't have. And that list keeps growing every year.

I am curious about using excel and I know how to use access for the update portion. However, we don't count turns here and I agree that is a good rule. Or would be. Can you tell me what exactly it is that you dump to excel? Do you use value or strictly turns?

Legacy Contributor

HI,

I’m curious about the 117,000 parts. You say that you no longer inventory them and have none on hand. I’m assuming that they are not marked as obsolete as you may still sell the product. If that is the case then I can see why you want to exclude those items. However, I would suggest you not do that based on quantity on-hand, I would identify all parts that have a zero balance but have not been active in say the last year or two. Flag those with a “D” ABC code. This would give you a simple method to exclude those parts from your ABC analysis and counts.

For our analysis we pull in data from the INVENTORY_TRANS, INVENTORY_BALANCE and PART (& PART_SITE) tables. The work sheet currently takes the data and looks at Turns, Annual Issues and Annual Cost of Issues. Those three values are ranked individually then added together and ranked. We also calculate the percentage each parts annual issued cost contributes to the overall inventory issued cost. Assigning the A code is done by starting with the first ranked part and selecting all parts that add up to 70% of annual cost. B parts are all those from that point that make up the next 20% and C’s are the final 10%. This of course works for us. You may need to vary your percentage ranges to get the number of A,B and C parts you desire. What ranking factors you use is up to you and may even be unique to your business.

Hope this helps a bit.

Regards,

Mark

Legacy Contributor

Boy, if only I was a real data analyst. I don't know if I'll be able to wrap my head around this. Thanks Mark.

Copyright © 2025 Infor. All rights reserved.