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:

 

[Andrew Poulsom]

Please post your actual data rather than images so that it can be copied and pasted into Excel.

 

[illusionek]

I could not find option to attach files, so that's the reason for screen shots. Please find links to files below. I would really appreciate any help I can get.


http://www.filedropper.com/stock-copy

http://www.filedropper.com/salesforecast

http://www.filedropper.com/solution_3

 

[Andrew Poulsom]

Your links aren't working. Just copy the data in Excel and paste it into a reply.

 

[illusionek]

Please find below:

Stock:

Product	Qty	Sell by date
Item 1	100	16/09/2014
Item 1	50	23/09/2014
Item 2	11	30/09/2014
Item 2	500	14/10/2014
Item 1	100	01/10/2014

<tbody>
</tbody><colgroup><col span="2"><col></colgroup>

Sales:

Product	Forecasted Qty	Forecasted Sale Date
Item 1	5	16/09/2014
Item 2	1	18/09/2014
Item 1	94	18/09/2014
Item 1	5	20/09/2014
Item 2	15	25/09/2014
Item 2	11	29/09/2014

<tbody>
</tbody><colgroup><col span="2"><col></colgroup>

Desired Solution:

Item Name	Sell By Date	Sell By Date Qty	16/09/2014	17/09/2014	18/09/2014	19/09/2014	20/09/2014	21/09/2014	22/09/2014	23/09/2014	24/09/2014	25/09/2014	26/09/2014	27/09/2014	28/09/2014	29/09/2014
Item 1	16/09/2014	100	95
Item 1	23/09/2014	50	50	50
Item 1	01/10/2014	100	100	100	56	56	51	51	51	51	51	51	51	51	51	51
Item 2	30/09/2014	11	11	11	10	10	10	10	10	10	10
Item 2	14/10/2014	500	500	500	500	500	500	500	500	500	500	495	495	495	495	484

<tbody>
</tbody><colgroup><col><col><col><col span="14"></colgroup>

 

[Andrew Poulsom]

I've tried a few things, but haven't found a solution, sorry.

 

[illusionek]

Hi Andrew

Many thanks for having a look. Hopefully someone else can come up with a solution

 

[Snakehips]

ilusionek,

Can you tell me if it is an easy matter for you to have your columns B, C, & D data i.e. Item, Date & Qty, within the Solution sheet, sorted by item / date order as per your sample file?

 

[illusionek]

I can do any layout/sort if that would help achieve solution.

 

[Snakehips]

Hopefully, the following will be of some value.
Because the stock balances are the product of whatever pattern of sales that precedes them, I have not been able to find a direct way to calculate the stock balances.
I have had to settle for an intermediate 'helper' sheet ,'Stock Helper' that calculates the sales to date for each batch of stock and then in the 'Stock' sheet, deduct these values from the total batch quantity in order to get the unsold balance.
I have added an, Available Date as in the date the batch of items is available from stock.

So....

Sales forecast pretty much as was.

Excel Workbook
1	Product	Forecast Sale	Date
2	Item 1	5	16/09/2014
3	Item 2	1	18/09/2014
4	Item 1	94	18/09/2014
5	Item 1	5	20/09/2014
6	Item 2	15	25/09/2014
7	Item 2	11	29/09/2014
8	Item 3	9	25/09/2014
9	Item 3	10	26/09/2014
10	Item 1	5	20/09/2014
Excel 2007

<p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sale Forcast</p>

Stock Helper sheet. Once formulas are extended to suit your data there is no need for data entry in this sheet. In fact it could be hidden.

Excel Workbook
1	Row 3 data linked to Stock sheet row 3 by formula.				****NO DATA INPUT IN THIS SHEET***** Just extend formulas down and across as required*****
2	Data in B4:E?? Linked to Stock sheet by formula. Cells F4:?? Contain Sales values calcu;ated by formula -- can drag B4:?? Down as required and F3:F?? Across as required
3	Item Name	Available Date	Sell By Date	Sell By Date Qty	16/09/2014	17/09/2014	18/09/2014	19/09/2014	20/09/2014	21/09/2014	22/09/2014	23/09/2014	24/09/2014	25/09/2014
4	Item 1	15/09/2014	16/09/2014	100	5	5	5	5	5	5	5	5	5	5
5	Item 1	16/09/2014	23/09/2014	50	0	0	50	50	50	50	50	50	50	50
6	Item 1	17/09/2014	01/10/2014	100	0	0	44	44	54	54	54	54	54	54
7	Item 1	01/10/2014	05/10/2014	75	0	0	0	0	0	0	0	0	0	0
8	Item 2	17/09/2014	30/09/2014	11	0	0	1	1	1	1	1	1	1	11
9	Item 2	23/09/2014	14/10/2014	500	0	0	0	0	0	0	0	0	0	5
10	Item 2	23/09/2014	28/09/2014	50	0	0	0	0	0	0	0	0	0	0
11	Item 2	25/09/2014	02/10/2014	40	0	0	0	0	0	0	0	0	0	0
12	Item 3	19/09/2014	03/10/2014	12	0	0	0	0	0	0	0	0	0	9
Excel 2007

<p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Stock Helper</p><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">Stock!B3="","",Stock!B3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=IF(<font color="Blue">Stock!B4="","",Stock!B4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=Stock!F3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=MIN(<font color="Blue">SUMIFS(<font color="Red">'Sale Forcast'!$B$2:$B$20,'Sale Forcast'!$A$2:$A$20,$B4,'Sale Forcast'!$C$2:$C$20,"<="&MIN(<font color="Green">F$3,$D4</font>)</font>)-SUMIF(<font color="Red">$B$3:$B3,$B4,F$3:F3</font>),$E4</font>)</td></tr></table></td></tr></table>


Stock sheet. Dates to be entered in green cells row 3 as far as required.
Stock receipt details entered in yellow cells.
F4 formula copy down and across as required ( in line with Stock Helper range)

Yellow rows B4:E?? can be sorted but balances will compute even if not sorted.

White cells compute balance available on given day.
There are values in cells that appear blank but they have white text by virtue of conditional formatting.
Cells where balance remains unsold by Sell By Date will highlight red.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3	Item Name	Available Date	Sell By Date	Sell By Date Qty	16/09	17/09	18/09	19/09	20/09	21/09	22/09	23/09	24/09	25/09	26/09	27/09	28/09	29/09	30/09	01/10	02/10	03/10
4	Item 1	15/09/2014	16/09/2014	100	95	95	95	95	95	95	95	95	95	95	95	95	95	95	95	95	95	95
5	Item 1	16/09/2014	23/09/2014	50	50	50	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Item 1	17/09/2014	01/10/2014	100	-	100	56	56	46	46	46	46	46	46	46	46	46	46	46	46	46	46
7	Item 1	01/10/2014	05/10/2014	75	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	75	75	75
8	Item 2	17/09/2014	30/09/2014	11	-	11	10	10	10	10	10	10	10	0	0	0	0	0	0	0	0	0
9	Item 2	23/09/2014	14/10/2014	500	-	-	-	-	-	-	-	500	500	495	495	495	495	484	484	484	484	484
10	Item 2	23/09/2014	28/09/2014	50	-	-	-	-	-	-	-	50	50	50	50	50	50	61	61	61	61	61
11	Item 2	25/09/2014	02/10/2014	40	-	-	-	-	-	-	-	-	-	40	40	40	40	29	29	29	29	29
12	Item 3	19/09/2014	03/10/2014	12	-	-	-	12	12	12	12	12	12	3	0	0	0	0	0	0	0	0
13					-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Stock

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4	1. / Formula is =AND(F4>0,G4>0,F$3=$D4)	Abc
F4	2. / Formula is =OR(F$3>$D4,F4=0)	Abc

I think that's about it?

Link to file available here as at time of posting. File

Hope that helps.