# Depletion for expiration date product - multiple batches

#### putmousehere

##### New Member
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>

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Rijnsent

##### Well-known Member
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

Replies
1
Views
92
Replies
4
Views
98
Replies
7
Views
86
Replies
5
Views
494
Replies
7
Views
48

1,140,917
Messages
5,703,169
Members
421,279
Latest member
emzy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back