Depletion for expiration date product - multiple batches

putmousehere

New Member
Joined
Nov 5, 2019
Messages
1
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 Date12-11-19
SKUArrival DateExpiry DateStock On HandDays RemainingSKUWeekly Sales Rate
12325-06-1927-11-19562112321.5
12325-06-1914-02-202410045612
12325-06-1904-12-193362878998
45615-10-1913-06-20198220
45610-09-1912-04-20162158
45626-08-1918-03-20144133
78901-11-1913-04-20320159
78901-11-1927-04-2048173
78901-11-1904-05-20192180

<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
Joined
Oct 17, 2005
Messages
1,302
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top