Stock expiry projection - Help-

P

pierreeven.au

Dear all,
I 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 always sale the product with the lowest expiry first. once that batch is exhausted, we start selling the next batch.
In order to simplify the issue, we assume that the forecasts are flat (same value sold every month)

What would make my life easier is to have the quantity 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
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
 
P

pierreeven.au

Dear all,
I 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 always sale the product with the lowest expiry first. once that batch is exhausted, we start selling the next batch.
In order to simplify the issue, we assume that the forecasts are flat (same value sold every month)

What would make my life easier is to have the quantity 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
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



Dear all,
I 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 always sale the product with the lowest expiry first. once that batch is exhausted, we start selling the next batch.
In order to simplify the issue, we assume that the forecasts are flat (same value sold every month)

What would make my life easier is to have the quantity 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
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



Dear all,
I 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 always sale the product with the lowest expiry first. once that batch is exhausted, we start selling the next batch.
In order to simplify the issue, we assume that the forecasts are flat (same value sold every month)

What would make my life easier is to have the quantity 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
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



Dear all,
I 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 always sale the product with the lowest expiry first. once that batch is exhausted, we start selling the next batch.
In order to simplify the issue, we assume that the forecasts are flat (same value sold every month)

What would make my life easier is to have the quantity 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
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
Click to expand...

Hi,
is there anyone to help me. this is taking me 1 day of work everymonth
cheers
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Stock expiry projection 1

Top