Referencing Batches of Same SKU to calculation QTY of expiring materials

Options
jedge@dsi.com
jedge@dsi.com Member, ALL USERS Posts: 5 Occasional Contributor
in Planning & Modeling

Hi All,

 

I've been racking my brain over this one and I can't think of a good solution.  I want to calculate the amount of inventory that will go expired for a given batch of materials, but as we cannot reference another rows easily like I can in Excel, I can't think of a good way to do it.  Here are my basic assumptions and data points to work with

 

QTY of Inventory On Hand by Batch - self explanatory

90 Days of sales by SKU - I take this number divided by 90 to get my daily sales average by SKU

QTY On Hand / Daily Sales = Days on hand of inventory

Days on Hand of Inventory + today's date = sell through date (only works with oldest batch)

Expiry date = again self explanatory

If sell through date > expiry date then sell through date - expiry * daily sales = expiring amount

 

As I said above, this only works with the oldest batch.  As we use FIFO, for any batch that we have on hand that expires after the oldest batch, today's date has to be replaced by the sell through date of the older batch.  In excel I would just do an IF statement saying if the SKU in the row above matches the SKU in this row, use that date above, if not then use today's date.

 

This doesn't involve time so I wasn't sure if I could use OFFSETS.  Something like, IF SKU OFFSET - 1 = SKU THEN OFFSET - 1 SELL THROUGH DATE ELSE SELL THROUGH DATE

 

Thoughts?

 

Tagged:

Comments

  • jedge@dsi.com
    jedge@dsi.com Member, ALL USERS Posts: 5 Occasional Contributor

    Hi Guys,

     

    I already have it built in excel and I'm trying to port it over to Anaplan. 

     

    On the attached for the formula for cell E11 is

     

    =IFERROR(IF(G11=G10,E10+D11,TODAY()+D11),"")

     

    Annotation 2020-02-17 142211.jpg

     

     

     

    As you can see, as long as the material code which is in column G matches the row above, then I add the DOH (Days on Hand) to the Sell Through Date from the prior batch and if it's the first material code in the selection then it's just today + the days on hand.  To get the amount to go short dated by batch I take the sell through date and subtract the short date to get the amount of days of material, then multiply that by the daily sales number which is the average daily sales.

     

    Really the only issue I'm having is figuring out how to use the Sell Through Date of batches that came in first as the Start Ship date for the subsequent batches that were received.  I was able to get the ISFIRSTOCCURRENCE Boolean to reflect the first batch of non short dated (within 6-12 months of expiry) because I feel that would be the start of the formula.  For any row that is checked, it will be CURRENTPERIODSTART() + Days on Hand = Sell Through Day.  It's the subsequent batches where I need add the days on hand to the sell through date from the previous batch.

Categories