Referencing Batches of Same SKU to calculation QTY of expiring materials
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?
Answers
-
Hi,
I would suggest that you come up with a simple example on Excel, using the assumptions you described above and the desired results.
That helps remove any pre requisites knowledge in inventory management, and allows the fine folks here in the forum to concentrate on the technical problem.
Thanks,
LipChean
0 -
Since time is not a dimension, I would suggest you build a batch system module that has all the properties of that batch, like begin date, expiry date, quantity, etc...
Then in your output module that has product by batch, you can reference the system module you built above to do your logic.
As @LipChean_Soh suggested you might also add a spreadsheet that shows a made up example if what I suggested is too simplistic.
I think we can come up with a snazzy solution for you!
0 -
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),"")
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.
0
