Estimate Stock at Risk of Expiry

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

Welcome to the forum.

I've had a look - to me, your requirements are as clear as mud, sorry. Some narrative is required, please.

I'm sure you know what you mean, however it's all gibberish to me - sorry.

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

Dears,

Wondering if anyone can help me with a solution for this. Other than a very manual way to do it can't really reach a simplified way to get the result I intend.

This proposal makes a few changes to the file in addition to adding two formulas for output.
1. The forecast is placed in cell E1
2. Cell B2 is populated with the first date: 4/30/2020
3. Cells C2:J2 are populated using: =EOMONTH(B2,1)
4. Cells M2:U2 are populated using: =B2
The formula for B3:J6 is: =IF(AND(OR(ISTEXT($L2),B$2>$L2),B$2<$L3),$E$1,0)
The formula for M3:V6 is: =IF(M$2<$L3,$K3-SUM($B3:B3),L3)
Let us know if you have any questions.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.