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 is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,301
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,136,260
Messages
5,674,681
Members
419,520
Latest member
Jennifer4Dillon

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