Inventory obsolete calculation

Hello

I'm bumping my head against an issue since a few weeks and I cannot get to the solution to calculate the obsolete inventory, meaning to project if - given inventory x lot and demand - there's any lot quantity that might not be sold before reaching a specific expiry date.

More in details
For each item, I have an inventory divided in multiple lot. Each lot might have a different expiry date and all are sold in FIFO logic (we sell the oldest lot first).

Demand is at item level and consumes gradually the lots, however often it happens that a lot is too big and there's not enough demand to consume it before reaching the expiry date

I am looking to a solution - think that VBA would be easier - to calculate at lot level if the demand is consuming all the stock before expiry date.

the data set is simple
ITEM // LOT NUMBER // EXPIRY DATE // Qty ---------- this is the extract of inventory
ABC 123456 Apr 2020 50
ABC 234567 Jun 2020 100
ABC 555555 Dec 2020 100

DEMAND for ITEM ABC
Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov
10 10 10 10 10 10 10 10 10 10 10 10 10 10

in this example, lot 123456 is completely consumed by demand. Lot 233456 is consumed only by demand of March-June (remaining 60 units, obsolete) and lot 55555 is consumed by demand of Jul - Dec (remaining 40 units, obsolete).

The situation may get complex if you imagine having many lots, as the monthly demand can deplete multiple lots.

thanks in advance for any suggestion :-)

*****************************Updated with more information
in the attachment, you can find an example with more data (lot data + demand), how the calculation should work and the desired output report.

regards

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.