Hi all,
The data I have is different batches of stock based on expiry dates and the sales forecast. I need to estimate the stock at risk of expiry. In the below example:
Sales Forecast for Product A: April 100, May 100, Jun, 100, Jul 100, Aug 100
Current On hand Stock 100 units expiring on 1-May and 300 units expiring on 1-Jun
Based on above forecast
100 units of May batch will be sold in April
100 units of 1-Jun expiry batch will be sold in May. 200 units of this batch will be obsolete as it cannot be sold beyond May.
Conclusion of above is 200 units of 1-Jun-20 batch will be unsold as it is cross its expiry date.
I am trying to automate this logic so I can replicate it for a bigger list of products each of which has multiple batches based on expiry dates.
Please refer attached excel file with sample data
Bookmarks