Inventory 101

Epicor ERP 10
1

My company is trying to address inventory issues related to stock levels (we’re hoping to assess, with some fair accuracy, which parts are consistently over-stocked so that we can generate a plan for sensible reduction of that stock).

I’ve been asked to put together a BAQ that basically looks at quantity on hand and compares that value with our minimum and safety levels. I understand that I should be able to derive these values directly from [PartWhse] - because that table shows these levels for all warehouse locations.

In the field notes for the [PartWhse.SafetyQty] field, Epicor states:

Note: Safety + Minimum = Reorder Point… using this formula the reorder point is the amount at which to reorder to maintain at least the prescribed minimum quantity.

… which leads me to believe that:

[PartWhse.OnHandQty] - ( [PartWhse.SafetyQty] + [PartWhse.MinimumQty] ) = “Reorder Flag”

All of that said, I do understand that, between MRP and PO Suggestions, there is a lot more behind the scenes that Epicor calculates within the realm of demand to help planners/buyers assess when the right time should be to order a part. Our Supply Chain supervisor is also of the opinion that the concept of “inventory” cannot be assessed solely on values contained in the [PartWhse] table.

Yet, I cannot seem to shake the idea that “inventory” (in terms of counts-to-stock levels) is merely a snapshot of “what do I have right now” compared to “what am I supposed to have as a minimum/maximum”. If I am consistently below the defined minimum stock level, then I need to develop a plan to increase the stock-level of that part. If I’m exceeding my maximum (assuming a max is defined), then I am over-stocked and I need to scale back ordering of that part. When a max is not defined, it is perhaps because my only concern is maintaining inventory at the minimum level. Therefore, if I’m looking at a report indicating a minimum stock level of 3, and my report consistently says I have 6 quantity on hand, then I would also need to scale back orders for that part.

To me, “inventory” is a different animal than “stock”. Inventory is what you have on hand plus your WIP, plus your allocations, plus, potentially, your future demand. Stock is merely what you have in the warehouse that is not yet in WIP, or bolted on to an assembly and being carted around the production floor.

So… my question is… if my company is merely interested in current stock levels (not part WIP; not part allocations; not scheduled demand), then should I not be looking exclusively at the [PartWhse] table? Why would the idea of comparing [PartWhse.OnHandQty] to [PartWhse.SafetyQty] + [PartWhse.MinimumQty] be a foolish one? If it is foolish, then what is the purpose of the [PartWhse] table?

Thank you, everyone.

2

Hi Bill,

I think to evaluate current stock levels you need to take lead time, average usage, and standard deviations on both, into account, at least for regularly used parts. From that you can see if your planned SS is enough to handle deviations and your MinOH is enough to cover average usage over lead time. Then I’d look at your QOH and see how it lines up with that planned reorder (SS + MOH) point.

Nancy

3

Thank you, Nancy. If I may, what out-of-the-box Epicor modules, or reports might you personally use to help you make an overall analysis of min/max/safety levels with the factors pertaining to lead times and average part consumption? I realize that most often, lead times for purchased parts are not typically under your control; for in-house manufactured parts, adjustments can be made (OT anyone?).

Are you developing your own BAQs to know how often parts are being consumed? Might there be an out-of-the-box report that addresses part consumption rate over a specific date range?

Does the Epicor Embedded Education possibly address this?

4

Hi Bill,

Yes, we developed our own queries. We did them as views for external queries, before the ability to do subqueries came out, so they are on our SQL server. If they were BAQs I’d share. I’m sorry :frowning:

We developed them because we could not find any out of the box means in E9.
I think you want to pull all part trans records for consuming transactions and sum for a day, or a week, or a month, and then get avg and stddev across those days, weeks, or months. We use weekly average usage. There are some other posts where people developed reports or dashboards with average usage, such as here:
Inventory Usage dashboard - summaries Monthly horizontally?
and here
Inventory Usage BAQ Report
Inventory Usage BAQ?
I wonder if they are still in group and might have helpful suggestions.

Nancy

1 Like
5

Nancy – you are awesome. This gets me pointed in the right direction. I appreciate your willingness to lend a hand. Thanks and enjoy the rest of your week.

1 Like