Depletion for expiration date product - multiple batches

[putmousehere]

Hi All,

I am trying to understand the best combination of formulas to calculate the sell through of products with different expiration dates.

All products with the closest expiration date (column c) to todays date (cell E1) by SKU (column a) should be depleted first inline with the weekly sales rate (column g). Once this has sold through the next batch should start to deplete.

---

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Todays Date[/TD]
[TD]12-11-19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SKU[/TD]
[TD]Arrival Date[/TD]
[TD]Expiry Date[/TD]
[TD]Stock On Hand[/TD]
[TD]Days Remaining[/TD]
[TD]SKU[/TD]
[TD]Weekly Sales Rate[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]25-06-19[/TD]
[TD]27-11-19[/TD]
[TD]56[/TD]
[TD]21[/TD]
[TD]123[/TD]
[TD]21.5[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]25-06-19[/TD]
[TD]14-02-20[/TD]
[TD]24[/TD]
[TD]100[/TD]
[TD]456[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]25-06-19[/TD]
[TD]04-12-19[/TD]
[TD]336[/TD]
[TD]28[/TD]
[TD]789[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]15-10-19[/TD]
[TD]13-06-20[/TD]
[TD]198[/TD]
[TD]220[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]10-09-19[/TD]
[TD]12-04-20[/TD]
[TD]162[/TD]
[TD]158[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]26-08-19[/TD]
[TD]18-03-20[/TD]
[TD]144[/TD]
[TD]133[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]01-11-19[/TD]
[TD]13-04-20[/TD]
[TD]320[/TD]
[TD]159[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]01-11-19[/TD]
[TD]27-04-20[/TD]
[TD]48[/TD]
[TD]173[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]01-11-19[/TD]
[TD]04-05-20[/TD]
[TD]192[/TD]
[TD]180[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi putmousehere,

if you move your SKU-Weekly Sales Rate table a bit to the right (in my example to L1:M4), you could do:

F2: "Older stock"
F3: =SUMIFS($D$3:$D$11,$A$3:$A$11,$A3,$E$3:$E$11,"<"&$E3) -> this gives the amount of stock of a certain SKU that is older based on Days remaining, drag down
G2: "Start selling"
G3: =$E$1+7*F3/VLOOKUP(A3,$L$2:$M$4,2,FALSE) -> calculates the date that that batch starts selling
G2: "End selling"
G3: =$G3+7*D3/VLOOKUP(A3,$L$2:$M$4,2,FALSE) -> last sales date of that batch

Hope that gets you started,
Koen