cost of goods sold on FIFO inventory

[mark692]

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Item Out			Item In
2	Date	Item	Out	Cost Of sales	Selling Price	In	Unit cost	amount	No. of Sold	COGS	Remaining Inventory	Stock On Hand
3	23-Dec	Ariel Passion w/ Downy				10	20.00	200.00	3	60.00	140	7
4	23-Dec	Ariel Passion w/ Downy				2	25.00	50.00	0	-	50	2
5	25-Dec	Ariel Passion w/ Downy	1				-			-	0	0
6	26-Dec	Ariel Passion w/ Downy	2				-			-	0	0
7							-			-	0	0
8							-			-	0	0
9							-			-	0	0
10							-			-	0	0
11							-			-	0	0
12							-			-	0	0
13							-			-	0	0
14							-			-	0	0
15							-			-	0	0
16							-			-	0	0
17							-			-	0	0
18							-			-	0	0
19							-			-	0	0
20
Inventory

Cell Formulas
Range	Formula
I3	=IF([@In]="","",MIN(F3,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I2,$B$2:B2,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I4	=IF([@In]="","",MIN(F4,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I3,$B$2:B3,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I5	=IF([@In]="","",MIN(F5,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I4,$B$2:B4,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I6	=IF([@In]="","",MIN(F6,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I5,$B$2:B5,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I7	=IF([@In]="","",MIN(F7,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I6,$B$2:B6,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I8	=IF([@In]="","",MIN(F8,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I7,$B$2:B7,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I9	=IF([@In]="","",MIN(F9,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I8,$B$2:B8,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I10	=IF([@In]="","",MIN(F10,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I9,$B$2:B9,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I11	=IF([@In]="","",MIN(F11,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I10,$B$2:B10,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I12	=IF([@In]="","",MIN(F12,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I11,$B$2:B11,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I13	=IF([@In]="","",MIN(F13,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I12,$B$2:B12,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I14	=IF([@In]="","",MIN(F14,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I13,$B$2:B13,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I15	=IF([@In]="","",MIN(F15,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I14,$B$2:B14,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I16	=IF([@In]="","",MIN(F16,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I15,$B$2:B15,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I17	=IF([@In]="","",MIN(F17,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I16,$B$2:B16,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I18	=IF([@In]="","",MIN(F18,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I17,$B$2:B17,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
I19	=IF([@In]="","",MIN(F19,(SUMIFS([Out],[Item],[@Item]))-SUMIFS($I$2:I18,$B$2:B18,[@Item])+SUMIFS(Table5[Out],Table5[Item],[@Item])))
J3	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J4	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J5	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J6	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J7	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J8	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J9	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J10	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J11	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J12	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J13	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J14	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J15	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J16	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J17	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J18	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
J19	=IFERROR([@[Unit cost]]*[@[No. of Sold]],0)
K3	=[@[Stock On Hand]]*[@[Unit cost]]
K4	=[@[Stock On Hand]]*[@[Unit cost]]
K5	=[@[Stock On Hand]]*[@[Unit cost]]
K6	=[@[Stock On Hand]]*[@[Unit cost]]
K7	=[@[Stock On Hand]]*[@[Unit cost]]
K8	=[@[Stock On Hand]]*[@[Unit cost]]
K9	=[@[Stock On Hand]]*[@[Unit cost]]
K10	=[@[Stock On Hand]]*[@[Unit cost]]
K11	=[@[Stock On Hand]]*[@[Unit cost]]
K12	=[@[Stock On Hand]]*[@[Unit cost]]
K13	=[@[Stock On Hand]]*[@[Unit cost]]
K14	=[@[Stock On Hand]]*[@[Unit cost]]
K15	=[@[Stock On Hand]]*[@[Unit cost]]
K16	=[@[Stock On Hand]]*[@[Unit cost]]
K17	=[@[Stock On Hand]]*[@[Unit cost]]
K18	=[@[Stock On Hand]]*[@[Unit cost]]
K19	=[@[Stock On Hand]]*[@[Unit cost]]
L3	=IFERROR([@In]-[@[No. of Sold]],0)
L4	=IFERROR([@In]-[@[No. of Sold]],0)
L5	=IFERROR([@In]-[@[No. of Sold]],0)
L6	=IFERROR([@In]-[@[No. of Sold]],0)
L7	=IFERROR([@In]-[@[No. of Sold]],0)
L8	=IFERROR([@In]-[@[No. of Sold]],0)
L9	=IFERROR([@In]-[@[No. of Sold]],0)
L10	=IFERROR([@In]-[@[No. of Sold]],0)
L11	=IFERROR([@In]-[@[No. of Sold]],0)
L12	=IFERROR([@In]-[@[No. of Sold]],0)
L13	=IFERROR([@In]-[@[No. of Sold]],0)
L14	=IFERROR([@In]-[@[No. of Sold]],0)
L15	=IFERROR([@In]-[@[No. of Sold]],0)
L16	=IFERROR([@In]-[@[No. of Sold]],0)
L17	=IFERROR([@In]-[@[No. of Sold]],0)
L18	=IFERROR([@In]-[@[No. of Sold]],0)
L19	=IFERROR([@In]-[@[No. of Sold]],0)
G3	=IFERROR([@amount]/[@In],0)
G4	=IFERROR([@amount]/[@In],0)
G5	=IFERROR([@amount]/[@In],0)
G6	=IFERROR([@amount]/[@In],0)
G7	=IFERROR([@amount]/[@In],0)
G8	=IFERROR([@amount]/[@In],0)
G9	=IFERROR([@amount]/[@In],0)
G10	=IFERROR([@amount]/[@In],0)
G11	=IFERROR([@amount]/[@In],0)
G12	=IFERROR([@amount]/[@In],0)
G13	=IFERROR([@amount]/[@In],0)
G14	=IFERROR([@amount]/[@In],0)
G15	=IFERROR([@amount]/[@In],0)
G16	=IFERROR([@amount]/[@In],0)
G17	=IFERROR([@amount]/[@In],0)
G18	=IFERROR([@amount]/[@In],0)
G19	=IFERROR([@amount]/[@In],0)

hello guys can you help me to get the formula of column D? column D is where the cost of sold item will apear in FIFO method

 

[dnorm]

I think clarification on "Cost of Sales" is needed. Populate E with selling price, if your CoS is the commission (just guessing here) then D would equal =SUM(E-G)*C