illusionek
Board Regular
- Joined
- Jun 21, 2014
- Messages
- 104
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.
Stock:
Sales:
Solution:
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.
Stock:
Sales:
Solution: