Unfortunately I cannot as it contains sensitive company data. I'm not comparing SKUs, but batches and their line items. Put a little more simply, lets say SKU A has 3 batches and they all expire at different times Batch 1 expires 12/12/2020 Batch 2 expires 12/30/2020 Batch 3 expires 01/20/2021 From today, 09/17/2020, we have 94 days to sell batch 1. Given the forward looking forecast, we have 50 days of stock in batch 1, so we'll sell through the entire quantity on 11/06/2020. Batch 2 will start selling 50 days from now, 11/07/2020, and expires 12/30/2020 only giving us 53 days to sell the product. However, we have 60 days worth of inventory so the equivalent quantity of 7 days worth of stock will expire. Batch 3 will start selling on 12/31/2020 as the remnants of batch 2 expires on 12/30/2020. As batch 3 expires on 01/20/2021, we have exactly 20 days to sell this stock before it expires. In supply planning we plan in days of supply, not quantity. When I order my lot sizes are set to quantity values but SAP plans replenishment point based on my safety stock requirements in days; IE: I always want to hold 90 days of stock on hand so SAP plans an order every time I get to 90 days of supply Here are the formulas that I'm utilizing Batch Quantity on Hand / Average Daily Sales = Days on Hand Inventory IF we are already selling batch then Batch Start Selling Date = Today IF we are not selling batch then Batch Start Selling Date = If Previous Batch Sell through Date < Previous Batch Sell By Date then Previous Batch Sell through Date, IF Greater then Previous Batch Sell By Date + 1 Batch Start Selling Date + Days on Hand Inventory = Sell Through Date IF Sell Through Date < Sell By Date then 0 inventory expires IF Sell Through Date > Sell By Date then Sell By Date - Sell Through Date = Days of inventory expire Days of Inventory to Expire * Average Daily Sales = Quantity to Expire Here are the steps I'm trying to figure out 1. Determine current batch that is selling (oldest) 2. Using Today as the Start Selling Date, determine using above the Sell Through Date 3. The next batch that will sell will use the Sell Through Date of the batch currently shipping unless part of the current batch is expiring, then it would use the Sell By Date + 1. 4. Use this logic on the next batch, and then the next, and so on.
... View more
