Subtracting from oldest first

[Ant Dunne]

Hi there,
Old member with new account. (Been a long time since posting). I have been racking my brain trying to work out how to subtract stock value from oldest first then next oldest and so on per item for an aged report. In the excerpt example below I want to subtract all of the "Out" column from the "In" column FIFO. In this instance subtract all down to 11/12/2020 from 8816.00 and the balance (1200.00) from 608 then 912 and so on. I hope this makes sense.
Thank you all and good to be back
Ant

Posting Date​	Entry Type​	Item No.​	Cost Amount In/Out​	In​	Out​
1/05/2020​	Purchase​	PR050T-STD-04​	8816.00​	8816.00​
19/05/2020​	Sale​	PR050T-STD-04​	-820.00​		-820.00​
29/05/2020​	Sale​	PR050T-STD-04​	-760.00​		-760.00​
29/05/2020​	Sale​	PR050T-STD-04​	-152.00​		-152.00​
29/05/2020​	Sale​	PR050T-STD-04​	-608.00​		-608.00​
29/05/2020​	Sale​	PR050T-STD-04​	-608.00​		-608.00​
29/05/2020​	Sale​	PR050T-STD-04​	608.00​	608.00​
12/06/2020​	Sale​	PR050T-STD-04​	-1216.00​		-1216.00​
29/06/2020​	Sale​	PR050T-STD-04​	-304.00​		-304.00​
1/07/2020​	Purchase​	PR050T-STD-04​	912.00​	912.00​
1/08/2020​	Sale​	PR050T-STD-04​	-304.00​		-304.00​
27/08/2020​	Sale​	PR050T-STD-04​	-988.00​		-988.00​
24/09/2020​	Sale​	PR050T-STD-04​	-608​		-608​
9/10/2020​	Sale​	PR050T-STD-04​	-380​		-380​
23/10/2020​	Sale​	PR050T-STD-04​	-608​		-608​
30/10/2020​	Sale​	PR050T-STD-04​	-380​		-380​
18/11/2020​	Sale​	PR050T-STD-04​	-304​		-304​
11/12/2020​	Sale​	PR050T-STD-04​	-1976​		-1976​
14/12/2020​	Negative Adjmt.​	PR050T-STD-04​	-228​		-228​
31/12/2020​	Purchase​	PR050T-STD-04​	1216​	1216​
31/12/2020​	Sale​	PR050T-STD-04​	-3242.8​		-3242.8​
15/03/2021​	Sale​	PR050T-STD-04​	-456​		-456​
23/03/2021​	Sale​	PR050T-STD-04​	-304​		-304​
31/03/2021​	Purchase​	PR050T-STD-04​	2888​	2888​
3/06/2021​	Negative Adjmt.​	PR050T-STD-04​	-76​		-76​

 

[rondeondo]

Hi Ant
How do you want to represent this? something like batches? so all out stock will be from batch 1 purchased 1/05/2020? They are all the same item so a simple running total will show stock on hand, though doing this shows your stock go negative on 31/12/2020 not a good time to be run out

 

[Ant Dunne]

Hi Rondeondo,
Not in batches no and the example I gave wasnt a good one. Hopefully the attached will make it more clear.

First column are in ins/outs, second is deducted from the first receipt second column from the second etc. colour coded accordingly.

 

[rondeondo]

Maybe you can do something with a sumif(A$1:a2,"<0") for adding up the negative values and countif(A$1:A2,">0") for identifying the count of ins. I'm sorry I cant' see a sensible way of doing it, may require VBA. Hopefully someone else here will have a better suggestion

 

[Ant Dunne]

OK. So I worked this out using a bunch of helper columns. Not as streamlined as I would have liked but hopefully this might be able to help others... Top row here is the formulas and the second row is the referenced column.

Formula​		=IF(M189>0,"In","Out")​	=W189&E189​	=MAX(SUMIF($X$4:X189,"In"&E189,$V$4:V189)+SUMIF($X:$X,"Out"&E189,$V:$V),0)​	=IF(E189<>E188,Y189,Y189-Y188)​
E	M	W	X	Y	Z
Part#​	Value​	In/Out​	In/Out & Part#​	Calculation​	Cleaned for running balance​
BT-DBKT-200-015​	813.6​	In​	InBT-DBKT-200-015​	508.50​	508.50​
BT-DBKT-200-016​	-135.6​	Out​	OutBT-DBKT-200-015​	508.50​	0.00​
BT-DBKT-200-017​	-67.8​	Out​	OutBT-DBKT-200-015​	508.50​	0.00​
BT-DBKT-200-018​	-101.7​	Out​	OutBT-DBKT-200-015​	508.50​	0.00​
BT-DBKT-200-019​	555.8​	In​	InBT-DBKT-250-015​	115.13​	115.13​
BT-DBKT-200-020​	79.4​	In​	InBT-DBKT-250-015​	194.53​	79.40​
BT-DBKT-200-021​	-182.62​	Out​	OutBT-DBKT-250-015​	194.53​	0.00​
BT-DBKT-200-022​	-59.55​	Out​	OutBT-DBKT-250-015​	194.53​	0.00​
BT-DBKT-200-023​	-19.85​	Out​	OutBT-DBKT-250-015​	194.53​	0.00​
BT-DBKT-200-024​	-178.65​	Out​	OutBT-DBKT-250-015​	194.53​	0.00​
BT-DBKT-200-025​	381.60​	In​	InBT-DBKT-300-015​	0.00​	0.00​
BT-DBKT-200-026​	-214.65​	Out​	OutBT-DBKT-300-015​	0.00​	0.00​
BT-DBKT-200-027​	-166.95​	Out​	OutBT-DBKT-300-015​	0.00​	0.00​
FFPE-FS-075​	295.5​	In​	InFFPE-FS-075​	0​	0​
FFPE-FS-076​	591​	In​	InFFPE-FS-075​	543.72​	543.72​
FFPE-FS-077​	-23.64​	Out​	OutFFPE-FS-075​	543.72​	0​
FFPE-FS-078​	-47.28​	Out​	OutFFPE-FS-075​	543.72​	0​
FFPE-FS-079​	-35.46​	Out​	OutFFPE-FS-075​	543.72​	0​
FFPE-FS-080​	11.82​	In​	InFFPE-FS-075​	555.54​	11.82​
FFPE-FS-081​	236.4​	In​	InFFPE-FS-075​	791.94​	236.4​
FFPE-FS-082​	-47.28​	Out​	OutFFPE-FS-075​	791.94​	0​
FFPE-FS-083​	-70.92​	Out​	OutFFPE-FS-075​	791.94​	0​
FFPE-FS-084​	-70.92​	Out​	OutFFPE-FS-075​	791.94​	0​