Shelf Life problem - check customer shelf life requirements with shelf life on inventory

Dear Friend,

My name is Mads. I have faced an issue which i have spent hours trying to solve, however, without luck so far. I would appreciate any help on this.

The problem is to identify if there is sufficient inventory to satisfy customer orders with respect to the remaining shelfs life per. material (multiple batches) and customer shelf life requirements per material.

Data is found in attachment.
Sheet 1 (Inventory data): Explains products, quantity in stock and remaining shelf life per. material. (please notice one product may have multiple batches with different shelf life).
Sheet 2 (Customer reg. shelf life): Explains customer shelf life requirement per material. (At loading, remaining shelf life on inventory must be higher than customer requirement shelf life)
Sheet 3 (Order to customer): Explains material purchase per. customer and quantity and loading date.


So far, i have only been able to check if at least one batch of each product has greater remaining shelf life than required per customer. However, i do not know if there is enough inventory to satisfy the customer order. I would like an overview explaining if each order can be fulfilled in full quantity without violating customer requirement shelf life.

FYI: inventory data is pulled 3 months ago, hence many products will be too old.

I really appreciate if you could recommend a solution or help me along the way. The uploaded data is intended to be clean without my own suggestions to minimize confusion.

Please let me know if you need any additional information.

Thanks you very much

Best regards
Mads

Dear Oeldere,

Thank you very much for your help. Unfortunately, this solution does not answer my question, as summing up the shelf life across all batches, does not answer if the order can be fulfilled without violating shelf life and quantity. But thank you for your help.

Best,
Mads

Hi Ben Hensel,

Thank you for your reply. I recognize that this may not be a "simple" as i initially thought it would be.
Do you see any delimitations that could be made to simplify the problem?

E.g.

Check each order if material, shelf life and quantity is obeyed, but do not substract multiple sales order lines with same material from the inventory?
This would allow each order to be validated, however, two orders may use up the same inventory line - however, this can be manually checked for afterwards.

Thank you for your help. It is highly appreciated.

/Mads

Hi,

Not really. In #2 you have summed the shelf life across all batches, and use this number as inventory and compare it the demand from the sales order.
I dont think that makes any logical sense. Maybe you can elaborate?

/Mads

If the expiration dates were dates not text, it would be possible to calculate inventory that has an expiration date that was far enough into the future to be acceptable to the customer

something similar to the attached as a start