Comparing List Items

Highlighted
Occasional Contributor

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?

2 REPLIES 2
Highlighted
Frequent Contributor

Re: Comparing List Items

Hi jedge@dsi.com,

 

You want to compare the latest data(SKU B) with the currently data (SKU A and SKU B)? or can you share the screenshot excel file

 

Regards,

 

Panji

Highlighted
Occasional Contributor

Re: Comparing List Items

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.