Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stock expiry projection - Help-
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stock expiry projection - Help-
On Monday, 23 April 2012 12:16:04 UTC+10, wrote: 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 On Monday, 23 April 2012 12:16:04 UTC+10, wrote: 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 On Monday, 23 April 2012 12:16:04 UTC+10, wrote: 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 On Monday, 23 April 2012 12:16:04 UTC+10, wrote: 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 Hi, is there anyone to help me. this is taking me 1 day of work everymonth cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stock expiry projection | Excel Programming | |||
How many days the ready stock+expected stock will last as allocate | Excel Discussion (Misc queries) | |||
Stock control formulas, counting and reporting remaining stock | Excel Programming | |||
Web Query With Multiple Stock Symbols & Stock Scouter | Excel Worksheet Functions | |||
Office 2003 Stock Action Add-In - Stock Handling Capacity/Numbers | Excel Worksheet Functions |