Can anyone advise the specific formula Epicor uses to calculate ABCs in ERP 10? Looking for the details including where the set-up fields are utilized in the formula so I can duplicate the calculation and get the same results in a spreadsheet. Thank you!
I actually did a LOT of homework on this for some reason.
The basic idea is (qty used) x (cost ea) per part in the timeframe. BUT there is a big flaw in how it does that.
The report shows you some of its math, but when I did this in 2019, I was REALLY confused at why one part had almost double the “usage” of another - because we never use one part without the other. They were a special engine and its special fuel tank.
In practice, serialized parts had half the usage of backflushed parts.
The reality is more complicated. Usage is based on REALLY specific transaction types AND the method to achieve them. See pic of Excel.
After a long ticket with support, we figured out that it comes down to the fact the the report’s algorithm omits transactions (PartTran table) where Plant1 = Plant2. How’s that for obscure? I’m guessing the idea was to avoid STK-STK transactions. So why not just omit STK-STK transactions?!
Report’s math
Thanks so much for all the info, Jason! In my experience, the formula should be as simple as unit cost x usage, and then you apply the desired percentages for your ABC assignments. But I cannot make sense of that in the results I am getting. When I run the Calculate ABC Codes, I am including only historical usage – no projected usage and no on-hands. We do not perform STK-STK transfers, as we have just one site, one warehouse and one plant. This should be simple, but I just cannot figure it out. Sure wish Epicor would simply state the formula in their help screens.
I would argue that the calculation SHOULD be based on “Usage”… and that the definition of usage is sometimes fluid (based on some company’s practices). Some companies only want usage to be considered only the Customer SHIPMENT and JOB Material Issues… while others want ADJUSTMENTS to be considered usage (for badly controlled backflushed parts). Some MIGHT want to consider outgoing transfer orders as part of the local site’s “usage”.
All that said, Epicor’s usage calculations in ERP10 are hard coded. The ABC Code considers the current (Qty + Usage) * UnitCost to determine the total value of the part… it then ranks those values from largest to smallest, and the first “x” percent of the parts are “A items”, the next percent are “B” and so on. Note that you are NOT limited to A, B, and C… you could only have two (A & B) or have 5 (A, B, C, D, E) with different %'s than most. Also, you can manually control the ABC Codes for some parts and assign a “N” (No Count) so that those parts are skipped during physical/cycle counting.
1 Like
Yeah, it’s not a formula so much as an algorithm. And it doesn’t work the way they expect, anyway.
They created PRB0213695 to fix it in June 2019 after my case. It was “accepted” by Development and it is categorized as “2 - Defect, no workaround.” It is not resolved nor does it have a target release, etc.
And, uh, I am not holding my breath for them to fix it.
@timshuwy is right (of course), you might as well develop your own algorithm, using a BAQ on PartTran.
Personally, I don’t care enough to bother. We only have A B and C, so the number of B parts that should have been A parts or whatever has got to be miniscule, and it’s all a bit subjective anyway.
The built-in wizard gets us close, and that’s good enough.
2 Likes
@JasonMcD makes a great point. Sometimes we worry about the “exact” nature of things, but when it comes down to it, all the ABC Code is calculating is how often an item gets automatically counted. If you have an item that is borderline between an A & B, it might flip/flop every time you run the report. If you want, you can always manually override it at the warehouse level.
But also note, Epicor controls ABC at the WAREHOUSE level… an A item in warehouse 1 could be a C item in another warehouse. This could (and often is) appropriate. My “Finished Goods” warehouse has “A” items… the value of items in finished goods might be $1000s of dollars each. The “Raw Materials” warehouse has a bunch of items and all of them are less than $10 each. If we applied ABC Across the entire site, then none of my A items would be in Raw Materials.
Don’t forget there is a hierarchy for assigning codes too. Sometimes we think Epicor is doing something wrong only to trace it through and find an override somewhere (Company Config, Warehouse, Part Site Warehouse).
Makes it really interesting when people want custom reports/dashboards but don’t account for that hierarchy. Things won’t reconcile because “Epicor is wrong”, when it turns out Epicor automatically figures some of that stuff out in the background.
Jenn
1 Like
I just found an interesting glitch on a multi-tenant customer.
We have set up ABC codes as follows
When I ran calculate ABC codes and used the PDF format
It took all of the parts that had no usage or inventory and made them “X”
when I ran calculate ABC and used the CSV or ExcelData only, it did not assign the “X” code.
Big difference because there were 20,000 parts out of 27,000 that we didn’t want to cycle count.
So was MT the reason? Are you thinking it’s looking at all sites in all companies and doing the math that way? (Even different MT customers?) Yikes if so.
True to form
When I tested this in a Public cloud customer the results were reversed.
The PDF did not assign the “X” but the CSV did.
I’m going to test this on an on premises installation.
Hi Bruce. Any update on this issue? I have a similar one in 10.2. I have the same ABC definition you have and when I run the Calculate ABC, all parts that should be X are C, only for one plant, whatever the report format I select. There is no site/warehouse ABC config.
the way that ABC Code is calculated, if your C has 100%, then the balance of all the parts that are not A or B already will become C items.
I believe that you need to do something like:
A = 80%
B = 95%
C = 99%
X = 100%
This will cause the last 1% of the product value to fall into the X category.
BUT… that would mean that SOME of the items in X would actually have value, which i think you are trying to avoid.
You have two additional options:
- manually override the items that have zero value and set them to X in the Part Entry… this is not a great idea, but you could automate this with a DMT.
- Why worry about it at all? When you do cycle counting, you can specify that items with zero on hand should be skipped.
My point is it works for one site and it’s intermittent for the other, with the same config. Per Epicor documentation, X with a 0% valuation is correct. Having C at 99% means hundreds of C parts will fall in X, which is really bad. From an audit perspective, you need those X to explain why you haven’t counted them.
Did you come up with a fix for this issue? I am having the same challenge.
It is fixed, as of last night, per PRB0213695.
Says it is fixed in release 11.2.400 (2023.2, I think?)