Comparing List Items
Hello All,
I'm attempting to do expiration calculation on lot codes based on a few points
QTY in the lot
Sell by Date
Days on Hand
When importing the inventory, we add 1,2,3 etc... to each line and use that as the code for the list. So we import the codes into the list first, then in the module it would look something like this (with a lot more data)
1 = SKU A, BATCH 123
2 = SKU A, BATCH 456
3 = SKU B, BATCH 654
I also have another list that uses the lot code as the code which I use for other modules.
Now when I do this in excel, it's a complex formula but it's doable. Basically, I sort by SKU and then sell by date. Then the formula is something like
IF row above is the not same SKU as current row, then QTY on hand divided by average daily forecast demand for next 6 months (this gets days on hand)
Days on hand plus today's date = Sell through Date
IF sell through date is less than sell by date, 0 goes short dated, IF sell through date is greater than sell by date then (Sell through date - Sell by date) * average daily forecasted demand
IF row above is the same SKU, then same DOH calculation based on qty in lot, plus the sell through date of the row above
The issue that I'm running up against is in Anaplan you can compare line items but I don't know how to compare list items. I saw SOME documentation around this but it seemed kind of murky.
There's the ISFIRSTOCCURANCE which could potentially work, but it looks like the calculation only has 1 line item to look up against and I would need 2. I guess I could just use AND. However, I'd still need to add onto the sell through date on the above list item. Any thoughts on how to do that?
Comments
-
You want to compare the latest data(SKU
with the currently data (SKU A and SKU ? or can you share the screenshot excel fileRegards,
Panji
0 -
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.
0
