FIFO Problem

[Alexandru Dan Mitache]

Hello!
I have the worksheet in the enclosed image.
It presents purchases and sells in a certain period, based on the FIFO system.
The available input data are in columns A, B, C, D and E. The rest, in columns F–I, have to be calculated. I.e. formulas in F4 to I4, to be pulled down.
I couldn't solve the problem of the price when a previously purchased quantity is finished and the next sell takes partially from the old stock and partially from the new one.
I'll be greatfull if someone could help me.
Thanks!
Alexandru

 

[StephenCrump]

Could you adapt something like this:

	A	B	C	D	E	F	G
1						Average	Average
2	Buy		Sell			Holding	Sell
3	Quantity	Price	Quantity	Price	Holding	Price	Price
4	800	$1.00			800	$1.000
5			200	$1.20	600	$1.000	$1.000
6			400	$1.25	200	$1.000	$1.000
7	100	$1.10			300	$1.033
8	150	$1.15			450	$1.072
9			300	$1.05	150	$1.150	$1.033
10	500	$1.05			650	$1.073
11			200	$1.10	450	$1.050	$1.125
12	100	$1.30			550	$1.095
13
14	Check total prices:
15		Purchases		$1,737.50
16		less: Sales		$1,135.00
17				$602.50
18		Holdings		$602.50
Sheet1

Cell Formulas
Range	Formula
E4	E4	=A4
F4:F12	F4	=LET(q,A$4:A4,p,B$4:B4,h,E4,qTot1,DROP(SCAN(SUM(q),A$3:A4,LAMBDA(a,b,a-N(b))),-1),qTot2,q+(h-qTot1)*(qTot1>h),IF(h=0,"n/a",SUM((qTot2>0)*qTot2*p)/h))
E5:E12	E5	=E4+A5-C5
G5:G12	G5	=IF(C5>0,(PRODUCT(E4:F4)-PRODUCT(E5:F5))/C5,"")
D15	D15	=SUMPRODUCT(A4:A12,B4:B12)
D16	D16	=SUMPRODUCT(C4:C12,G4:G12)
D17	D17	=D15-D16
D18	D18	=E12*F12

 

[Alexandru Dan Mitache]

Could you adapt something like this:

	A	B	C	D	E	F	G
1						Average	Average
2	Buy		Sell			Holding	Sell
3	Quantity	Price	Quantity	Price	Holding	Price	Price
4	800	$1.00			800	$1.000
5			200	$1.20	600	$1.000	$1.000
6			400	$1.25	200	$1.000	$1.000
7	100	$1.10			300	$1.033
8	150	$1.15			450	$1.072
9			300	$1.05	150	$1.150	$1.033
10	500	$1.05			650	$1.073
11			200	$1.10	450	$1.050	$1.125
12	100	$1.30			550	$1.095
13
14	Check total prices:
15		Purchases		$1,737.50
16		less: Sales		$1,135.00
17				$602.50
18		Holdings		$602.50
Sheet1

Cell Formulas
Range	Formula
E4	E4	=A4
F4:F12	F4	=LET(q,A$4:A4,p,B$4:B4,h,E4,qTot1,DROP(SCAN(SUM(q),A$3:A4,LAMBDA(a,b,a-N(b))),-1),qTot2,q+(h-qTot1)*(qTot1>h),IF(h=0,"n/a",SUM((qTot2>0)*qTot2*p)/h))
E5:E12	E5	=E4+A5-C5
G5:G12	G5	=IF(C5>0,(PRODUCT(E4:F4)-PRODUCT(E5:F5))/C5,"")
D15	D15	=SUMPRODUCT(A4:A12,B4:B12)
D16	D16	=SUMPRODUCT(C4:C12,G4:G12)
D17	D17	=D15-D16
D18	D18	=E12*F12

Hello, Stephen!
Your solution is unique and great! And it works p e r f e c t.
The brilliant formula in F4 left me... "the eyes in the sun", how we use to say in Romanian.
Congratulations & thank you very much!

Alexandru