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?
