Find Date at Which Open Orders will Exhaust Finished Goods on Hand

[shapeshiftingkiwi]

Hello!

Company has 2 SKUs. Below is a standard report run on these SKUs. You can see that SKU 2 has enough stock to fill all open SOs (sales orders) but SKU 1 does not.

MrExcel Worksheet.xlsx
	A	B	C	D	E	F
1	Item ID	Qty Per Pallet	Qty on Hand	Qty on SO	Qty Available	Net Weight
2	SKU 1	5	4,380	6,720	-2,340	20
3	SKU 2	10	4,650	3,050	1,600	40
Inventory

Here are the ship dates of the open orders.

MrExcel Worksheet.xlsx
	A	B	C	D	E	F	G
1	Drop Ship	SO No	Ship By	Customer Name	Item ID	Line Description	Qty Remaining
2			8/19/22		SKU 1		240
3			9/1/22		SKU 2		550
4			9/1/22		SKU 1		240
5			9/2/22		SKU 2		500
6			9/6/22		SKU 1		240
7			9/7/22		SKU 1		240
8			9/8/22		SKU 1		240
9			9/8/22		SKU 1		240
10			9/8/22		SKU 1		240
11			9/8/22		SKU 2		650
12			9/8/22		SKU 2		450
13			9/8/22		SKU 2		400
14			9/8/22		SKU 1		480
15			9/9/22		SKU 2		500
16			9/12/22		SKU 1		240
17			9/15/22		SKU 1		240
18			9/15/22		SKU 1		480
19			9/15/22		SKU 1		240
20			9/15/22		SKU 1		240
21			9/15/22		SKU 1		240
22			9/15/22		SKU 1		240
23			9/16/22		SKU 1		720
24			9/20/22		SKU 1		1,200
25			9/22/22		SKU 1		240
26			9/22/22		SKU 1		240
27			9/29/22		SKU 1		240
Ship Dates

The highlighted cells below are my request. Cell F2 should report "9/16/22" since that is the earliest date for which Company does not have stock available. Cell F3 should report "N/A" since Company has inventory to support all open orders.

MrExcel Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Item ID	Qty Per Pallet	Qty on Hand	Qty on SO	Qty Available	DATE NEEDED	DATE TO PRODUCE	Net Weight (pounds)	# on Hand	# Needed	# on SO
2	SKU 1	5	4380	6720	-2340		################	20	87600	-46800	134400
3	SKU 2	10	4650	3050	1600		################	40	186000	64000	122000
Output

Cell Formulas
Range	Formula
B2:B3	B2	=VLOOKUP(A2,Inventory!A:F,2,FALSE)
C2:C3	C2	=VLOOKUP(A2,Inventory!A:F,3,FALSE)
D2:D3	D2	=VLOOKUP(A2,Inventory!A:F,4,FALSE)
E2:E3	E2	=VLOOKUP(A2,Inventory!A:F,5,FALSE)
G2:G3	G2	=F2-7
H2:H3	H2	=VLOOKUP(A2,Inventory!A:F,6,FALSE)
I2:I3	I2	=C2*H2
J2:J3	J2	=E2*H2
K2:K3	K2	=D2*H2

 

[Eric W]

Try this in F2:

=LET(f,'Ship Dates'!$E$2:$E$30=A2,d,FILTER('Ship Dates'!$C$2:$C$30,f),a,FILTER('Ship Dates'!$G$2:$G$30,f),v,SEQUENCE(ROWS(a)),h,TRANSPOSE(v),s,MMULT(--(v>=h),a),XLOOKUP(C2+1,s,d,"NA",1))

 

[shapeshiftingkiwi]

Amazing! I was able to adapt this to my real world use and it's a big time saver!

Thanks so much!

 

[Eric W]

Happy to help! Thanks for the update.