Stock expiry projection

[pierreeven.au]

Dear all,
have been searching the forums with no luck.
My issue is the following: We are extracting the current stock on hand by line:
one product can have a number of different batches with different expiry.
the goal is to determine how many units of this batch will be wrtiten off (will not be sold before expiry)
We alway sale the product with the lowest expiry. and in order to simplify the issue, we assume that the

Mat No product Stock on hand Forecast per month
318161 Product A 509308 31-May-14 5,400 29,912
318161 Product A 510359 31-Aug-14 20,040 29,912
318161 Product A 511356 31-Aug-14 20,160 29,912
318161 Product A 509866 31-Aug-14 28,292 29,912
319864 Product B 502721 30-Sep-13 4,839 50
300442 Product C 506196 31-Dec-15 474 5
300442 Product C 510223 31-Dec-15 959 5
311736 Product D 509039 31-Jan-16 9,996 5,700
321781 Product E 507897 28-Feb-13 8,575 5,000
321781 Product E 508945 30-Jun-14 10,080 5,000
321781 Product E 509418 30-Jun-14 10,080 5,000
321781 Product E 510697 30-Jun-14 10,200 5,000
321781 Product E 510262 30-Jun-14 10,320 5,000

 

[pierreeven.au]

Dear all,
have been searching the forums with no luck.
My issue is the following: We are extracting the current stock on hand by line:
one product can have a number of different batches with different expiry.
the goal is to determine how many units of this batch will be wrtiten off (will not be sold before expiry)
We alway sale the product with the lowest expiry. and in order to simplify the issue, we assume that the forecasts are flate (same value for every month)

What would make my life easier is to have the quatity of product that will expire (if any)on each line.

thanks a lot in advance for you precious help

Mat No product Batch No Expiry Stock Forecast(per month) qty to destroy

318161 Product A 509308 31-May-14 5,400 29,912
318161 Product A 510359 31-Aug-14 20,040 29,912
318161 Product A 511356 31-Aug-14 20,160 29,912
318161 Product A 509866 31-Aug-14 28,292 29,912
319864 Product B 502721 30-Sep-13 4,839 50
300442 Product C 506196 31-Dec-15 474 5
300442 Product C 510223 31-Dec-15 959 5
311736 Product D 509039 31-Jan-16 9,996 5,700
321781 Product E 507897 28-Feb-13 8,575 5,000
321781 Product E 508945 30-Jun-14 10,080 5,000
321781 Product E 509418 30-Jun-14 10,080 5,000
321781 Product E 510697 30-Jun-14 10,200 5,000
321781 Product E 510262 30-Jun-14 10,320 5,000