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.

---

			Todays Date	12-11-19
SKU	Arrival Date	Expiry Date	Stock On Hand	Days Remaining	SKU	Weekly Sales Rate
123	25-06-19	27-11-19	56	21	123	21.5
123	25-06-19	14-02-20	24	100	456	12
123	25-06-19	04-12-19	336	28	789	98
456	15-10-19	13-06-20	198	220
456	10-09-19	12-04-20	162	158
456	26-08-19	18-03-20	144	133
789	01-11-19	13-04-20	320	159
789	01-11-19	27-04-20	48	173
789	01-11-19	04-05-20	192	180

<tbody>
</tbody>

 

[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