How have you from-scratch built lot-level, geo-based Inventory Consumption?

rachel.bisch

We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some customers, but only 180 days for others. We reflect this in our demand inputs, and we feed these into our consumption calculation, which is structured to consume lots with the lowest shelf life first, across shelf life requirements from lowest to highest. Here is an example of what that would look like:

 

inventory
item	Lot	Lot Shelf Life	Lot Rank	Lot Qty
A	1234	190	1	500
A	5678	211	2	600
A	9876	345	3	7000

  

demand
item	shelf life requirement	demand
A	180	450
A	270	3000

  

consumption
item	Lot	Lot Shelf Life	Lot Rank	Lot Qty	Shelf life requirement considered	consumed qty	remaining qty	remaining 180 demand	remaining 270 demand
A	1234	190	1	500	180	450	50	0	3000
A	5678	211	2	600	180	0	600	0	3000
A	9876	345	3	7000	270	3000	4000	0	0

  In this logic, Lot 1234 is consumed first because it has the lowest shelf life that still satisfies the lowest shelf life requirement. We currently use rank cumulate chains that run sequentially, each handling one shelf life rule by consuming inventory and updating the available quantity of the lot before the next shelf life rule is applied. Questions:

1. Can we reduce or eliminate the need for multiple rank cumulates?
   Ideally, we’d use a module dimensioned by shelf life rule or something similar, so we only maintain one rank cumulate. Is it possible to track consumption of a lot’s inventory across multiple list members (e.g., shelf life rules) without rebuilding rank cumulates for each?
2. If that’s not feasible, can the same logic be modeled using the Optimizer?
   We’re open to using Optimizer if it allows a cleaner or more scalable solution. We have (like many) struggled to translate into the right strucutre & obj function

 Thanks in advance for your thoughts—happy to provide more details or walk through the model if helpful.

Sai_Bharadwaj_Venati

Hi rachel, this can be achieved through optimize. Rough model i've built for your reference

we can think about rank cumulate solution as well but i generally prefer to use optimizer wherever it is available and solution is feasible