comparing Sales Forecast with Stock and calculating requirements

Hello!

This is going to be my first VBA project and I am looking for some advise on how to approach it. I have only a basic knowledge of VBA and at the moment I really struggle not with the code but with making an initial design.

I do not expect to get a ready to use code as I appreciate below is a complex problem. However I would be grateful for general tips how to design it and and ideally what steps I have to follow for example: "use for each loop to get data from sreadsheet X to Y and then use Workseet Funcion Z to get calculation" etc.

Thank you in advance for all 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'.

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.