Excel 2007 : Predicting Material Obsolescence based on future demand AND Inventory Shelf Life

[senanpeter]

Hi,

I have a 2 variants of the same product, A and B. Both can satisfy the same demand signal, the only difference is the shelf life, 200 days vs. 150 days from date of manufacture.

Based on a demand signal, assuming I allocate based on worst case expiry, how can I measure the month on month obsolescence?

In the attachment.

• There is a table of batch receipts with the yield and date of batch expiry
• There is a table of monthly demand that needs to be satisfied

I want to determine how much material expiries each month and from which batch in order to forecast the cost of expiring this material and to check if I can reallocate this material to another manufacturing facility

Has this inventory assessment ever been discussed/resolved here?

Thanks in advance for the suggestions

[senanpeter]

how interesting that I still have this problem after two years! :-)

Any help would be much appreciated