I have a list of inventory by best before date & SKU and would like to calculate on mass, what stock will be not suitable at the time of dispatch based on the latest demand profile against the shelf life parameters. Essentially projecting a list of 'surplus to demand'
Rules
1. No stock must be sold past 50% of its total life.
2. Assuming correct stock rotation in the warehouse, stock will be consumed by shortest date first until the week of demand is later than the rule 1
3. Once stock of the lowest best before date is consumed, the calculation needs to move onto the next.
I have had a couple of attempts at this within excel but feel I may need to move onto Visual Basic to get what I need.
Does anyone have any suggestions?
Rules
1. No stock must be sold past 50% of its total life.
2. Assuming correct stock rotation in the warehouse, stock will be consumed by shortest date first until the week of demand is later than the rule 1
3. Once stock of the lowest best before date is consumed, the calculation needs to move onto the next.
I have had a couple of attempts at this within excel but feel I may need to move onto Visual Basic to get what I need.
Does anyone have any suggestions?
