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

[illusionek]

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:

 

[illusionek]

Thank you so much. You are a life saver! I need to play with it but at first glance it looks like exactly what I needed. I really appreciate this!

 

[Snakehips]

You are welcome.
Please give feedback once you have tried it out properly.

 

[illusionek]

Would you be able to explaing the logic behind this formula please:

=MIN(SUMIFS('Sale Forcast'!$B$2:$B$20,'Sale Forcast'!$A$2:$A$20,$B4,'Sale Forcast'!$C$2:$C$20,"<="&MIN(F$3,$D4))-SUMIF($B$3:$B3,$B4,F$3:F3),$E4)

I understand how MIN and SUMIFS work but I just do not get logic applied that allows you do all the magic and calculate stock correctly.


Thank you!

 

[Snakehips]

Hi,

Sorry for the delay but I overlooked the fact that you had requested an explanation of the formula in the Stock Helper sheet.The formula is calculating the quantity sold from a given batch up to and including the particular 'Date' in row 3. Accept that our formula generates a 0 value prior to the 'Available Date' of that batch. Also accept that from your sample data, only 5 of Item 1 could be sold from the batch of 100 in row 4 because the balance of 95 were out of date before further sales became due. Typically, the formula returns the batch qty sold to 'Date' with the final batch quantity extending out right to all columns . Finally accept that all 50 of the batch in row 5 were sold, within date, to partially satisfy the Sale Forcast of 94 for the 18/09/2014.


Now we will look at the formula as it applies to cell H6 ie a batch of 100 of Item 1 that became Available on 18/09/2014.


The SUMIFS() is totaling sales to 'Date', from the Sale Forcast'.......
It sums.....'Sale Forcast'!$B$2:$B$20 ..... for the correct item...... 'Sale Forcast'!$A$2:$A$20,$B6 .... for all dates up to and including the earliest of either the 'Date' in question, eg H$3 or the Sell By date of the batch in question.... $D6, as given by.... 'Sale Forcast'!$C$2:$C$20,"<="&MIN(K$3,$D6) In this case the sale date is within the sell by date of the batch and so SUMIFS sums Item 1 sales up to the sales 'Date' 18/09/2014 ie 5 + 94 = 99
The SUMIF() is looking at the 'Stock Helper' sheet itself and the dynamic notation..... SUMIF($B$3:$B5,$B6,H$3:H5) means it is looking 'upwards' from row 6 to sum how many items from previous batches of that item have been sold up to that point. In this case it sees 5 + 50 = 55
Subtracting one from the other 99 -55 you get 44 the balance required to satisfy sales total to date. The enclosing MIN() function is then returning the minimum of the reqired sales balance 44 and the full batch quantity $E6. In this case 44 is the minimum so the batch provides partial sales for the total of 94 the other 50 having been supplied by the batch in row 5. Similar thinking applied to H5 determined that for sales up to 'Date' 18/09/2014 = 5 + 94 = 99. Prior sales (total of cells above) were just 5 so 94 required. Min(94, 50) = 50 .... the full batch quantity.
In the case of H4, 5 were satisfied from the batch of 100 on the 16/09/2014. With no further sales before that batches sell by date the SUMIFS() is limited by the SellBy date and the batch qty sold computes to 5 all along the row.


I have to say that it was easier to formulate the original answer than try to explain it.


I hope that it does makesome sense.