how to substract sales from stock with oldest date first and then move to next date?

Hello!

Until now, I thought I know Excel well but below is above my abilities. Can anyone help?

Please see my problem below:


Stock spreadsheet has all the items I am trying to sell with 'sell by date' after which I cannot sell this item. Then in Sales Forecast I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.

I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with 'sale by date' 23/09 and so on.

So based on the attached example, I can see that on 16/09 I will consume only 5 cases from 'sell by date' 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.

Ideally I would like also to include the logic that if Item is out of date it would move to the next 'sell by date' and if possible I would like to avoid VBA.

So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10

For Item 2 I can see that units with 'Sell by date' 30/09 will be consumed on 25/09 and I will start taking stock from next 'sell by date' which is 14/10.

I hope all this makes sense and someone would be able to help me out.

I attached both spreadsheets plus an illustration of a solution I am looking for, which at the moment is done manually.


Thank you in advance for all help.

Can anyone help please?