Planned stock expiry calculation

Hi all,

For a whole week I was trying to get planning spreadsheet to identify and return the value, wherever stock goes out of life. We are planning on 12 weeks horizon, some of our products are not selling within shelflife, so some write-off's occur.

I got the "ideal world" formula, when stock is being replenished on the day it is required, but most of the times two or more batches may overlap, hence we may hold 2 or more Best Before in stock.

How do I calculate planned waste for the whole 12 weeks horizon? A dummy with more explanation attached.

Thanks in advance for yourpriceless input...

By the way, the solution can be either - formula or VBA. Actually the person who could manage to write a UDF for this - deserves a statue of his/her own.

Anybody? Bump...

I knew this one is ain't going to be the easy bite... Any thoughts? As we say in England, no idea is a crap idea...

Sorry mods, bu got to bump it again..