Single Formula to acheive FIFO Costing

[jim may]

The left table (Columns A:E ) is my recent Purchase History of a product. Next, Columns G and H are my day-end Inventory Qtys. I'd like a formula in Column I that
would begin by searching the History table, in the example shown since I want the value as of 5/31/2015, I should first select (from the history table) the first date = to, or less than 5/31/2015, and in this case the 5/28/2015 row. And keep going up accumulating the full extended values, BUT no more that the Qty on Hand. In the # 5 layer I only need 586 of the 600 bought. I'm at a loss as to how to beging this formula. Can anyone assist? TIA, Jim

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2										From Layer/Series # >>	5	4	3	2	1
3	Layer or	** PURCHASING HISTORY **					** DAY END ON HAND INVENTORY **			Qty in Layer/Series >>	500	500	600	400	586	<< Limit total to 2,586
4	Series #	Date	Qty	Extension	U/Price		Date	Qty	Value	U/Price Layer/Series >>	$ 4.10	$ 3.90	$ 3.90	$ 3.85	$ 3.90
5	1	05/10/2015	600	2,340.00	$ 3.9000		05/31/2015	2,586	$ 10,165.40	<< Need Formula to Produce	2050	1950	2340	1540	2285.4	$ 10,165.40
6	2	05/15/2015	400	1,540.00	$ 3.8500		06/01/2015	2,398		which I have computed here >>
7	3	05/20/2015	600	2,340.00	$ 3.9000		06/02/2015	2,398
8	4	05/25/2015	500	1,950.00	$ 3.9000		06/03/2015	2,236
9	5	05/28/2015	500	2,050.00	$ 4.1000		06/04/2015	2,686
10	6	06/04/2015	500	1,936.97	$ 3.8739		06/05/2015	2,560
11	7	06/07/2015	450	1,777.12	$ 3.9492		06/06/2015	2,530
12	8	06/09/2015	30	130.00	$ 4.3333		06/07/2015	2,860
13	9	06/14/2015	320	1,643.07	$ 5.1346		06/08/2015	2,700
14	10	06/18/2015	260	950.93	$ 3.6574		06/09/2015	2,531
Sheet1

Cell Formulas
Range	Formula
I5	=SUM(K5:O5)
K3	=C9
K4	=E9
K5	=K3*K4
L3	=C8
L4	=E8
L5	=L3*L4
M3	=C7
M4	=E7
M5	=M3*M4
N3	=C6
N4	=E6
N5	=N3*N4
O3	=H5-SUM(K3:N3)
O4	=E5
O5	=O3*O4
P5	=SUM(K5:O5)

 

[sheetspread]

Can this be done with only worksheet functions? A VBA solution would be to resize each corresponding range until the qty sum just exceeds what you have in column H, pass that to an array, subtract the excess from the last term, then sumproduct with column E.

 

[jim may]

Yes, I'm leaning towards a UDF, utilizing like you say Resize, Array, Offset and Sumproduct ... Jim

 

[z168]

What industry are you in Jim?

I work in Logistics - and we average all our SKUs for the fiscal year so all our analyses are immune from price increases or payment patterns

 

[jim may]

I'm an independent consultant, working with small businesses. I current client is a convenience Store and I'm after tracking their cigarette Inventory and also their Fuel sales.

 

[z168]

I'm an independent consultant, working with small businesses. I current client is a convenience Store and I'm after tracking their cigarette Inventory and also their Fuel sales.

I see.


Question: is 6/9/2015 = $9,992.99?

If I got the calculation correctly, you will need 6 layers. By June 4th your last 5 purchases will not cover the quantity on hand so you will need to tap on to the 5/10 purchase.

 

[z168]

These are the calculations I got:


If you noticed the Qty layer and Price layer rows are empty and instead nested the formula in each cell. If the calculations are correct, PM me your email and I will just send you the workbook. hth

 

[sheetspread]

Yes, I'm leaning towards a UDF, utilizing like you say Resize, Array, Offset and Sumproduct ... Jim

I think that can work, the first number I got matched yours and z168's (but differed from the next 8):

Excel 2010
	A	B	C	D	E	F	G	H	I
3	Layer or	** PURCHASING HISTORY **					** DAY END ON HAND INVENTORY **
4	Series #	Date	Qty	Extension	U/Price		Date	Qty	Value
5	1	5/10/2015	600	2,340.00	$3.90		5/31/2015	2,586
6	2	5/15/2015	400	1,540.00	$3.85		6/1/2015	2,398
7	3	5/20/2015	600	2,340.00	$3.90		6/2/2015	2,398
8	4	5/25/2015	500	1,950.00	$3.90		6/3/2015	2,236
9	5	5/28/2015	500	2,050.00	$4.10		6/4/2015	2,686
10	6	6/4/2015	500	1,936.97	$3.87		6/5/2015	2,560
11	7	6/7/2015	450	1,777.12	$3.95		6/6/2015	2,530
12	8	6/9/2015	30	130	$4.33		6/7/2015	2,860
13	9	6/14/2015	320	1,643.07	$5.13		6/8/2015	2,700
14	10	6/18/2015	260	950.93	$3.66		6/9/2015	2,531
Sheet1 (3)

Sub fifotrack()
Dim x%, lr%, y%, qtyarray(), z%, qtyarray2(), w%
lr = Cells(Rows.Count, 1).End(xlUp).Row
For x = 5 To lr
ReDim qtyarray(0)
y = 0
z = Application.Match(Cells(x, 7), Range("b5:b" & lr), 1)
Do Until Application.Sum(Range("C" & z + 4 - y + 1 & ":C" & z + 4)) > Cells(x, 8).Value
ReDim Preserve qtyarray(y)
qtyarray(y) = Cells(z + 4 - y, 3).Value
y = y + 1
Loop
qtyarray(y - 1) = qtyarray(y - 1) - (Application.Sum(qtyarray) - Cells(x, 8).Value)
ReDim qtyarray2(y - 1)
For w = 0 To y - 1
qtyarray2(w) = qtyarray(y - 1 - w)
Next w
Cells(x, 9).Value = "=sumproduct({" & Join(qtyarray2, ";") & "}," & Range("e" & 4 + z - y + 1).Resize(y).Address & ")"
Next x
End Sub

Excel 2010
	A	B	C	D	E	F	G	H	I
3	Layer or	** PURCHASING HISTORY **					** DAY END ON HAND INVENTORY **
4	Series #	Date	Qty	Extension	U/Price		Date	Qty	Value
5	1	5/10/2015	600	2,340.00	$3.90		5/31/2015	2,586	$10,165.40
6	2	5/15/2015	400	1,540.00	$3.85		6/1/2015	2,398	$9,432.20
7	3	5/20/2015	600	2,340.00	$3.90		6/2/2015	2,398	$9,432.20
8	4	5/25/2015	500	1,950.00	$3.90		6/3/2015	2,236	$8,800.40
9	5	5/28/2015	500	2,050.00	$4.10		6/4/2015	2,686	$10,542.35
10	6	6/4/2015	500	1,936.97	$3.87		6/5/2015	2,560	$10,050.95
11	7	6/7/2015	450	1,777.12	$3.95		6/6/2015	2,530	$9,933.95
12	8	6/9/2015	30	130	$4.33		6/7/2015	2,860	$11,247.59
13	9	6/14/2015	320	1,643.07	$5.13		6/8/2015	2,700	$10,631.59
14	10	6/18/2015	260	950.93	$3.66		6/9/2015	2,531	$9,992.99
Sheet1 (3)

Cell Formulas
Range	Formula
I5	=SUMPRODUCT({586;400;600;500;500},$E$5:$E$9)
I6	=SUMPRODUCT({398;400;600;500;500},$E$5:$E$9)
I7	=SUMPRODUCT({398;400;600;500;500},$E$5:$E$9)
I8	=SUMPRODUCT({236;400;600;500;500},$E$5:$E$9)
I9	=SUMPRODUCT({186;400;600;500;500;500},$E$5:$E$10)
I10	=SUMPRODUCT({60;400;600;500;500;500},$E$5:$E$10)
I11	=SUMPRODUCT({30;400;600;500;500;500},$E$5:$E$10)
I12	=SUMPRODUCT({310;600;500;500;500;450},$E$6:$E$11)
I13	=SUMPRODUCT({150;600;500;500;500;450},$E$6:$E$11)
I14	=SUMPRODUCT({551;500;500;500;450;30},$E$7:$E$12)

z168: I only matched your first and last results. I'm assuming you calculated FIFO and perhaps more carefully looked at the changes in previous layers than I did, but why wouldn't we get the same results on 6/01/2015 for example?

 

[jim may]

z168, thanks; I'm currently away from my office, but as soon as I can I will look into your question. Jim

 

[jim may]

OK Guys, I think I have it. I went the UDF Route (without usage of Arrays).

Thanks for both your input on this. Jim

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	Layer or	** PURCHASING HISTORY **					** DAY END ON HAND INVENTORY **				Manual Proof
4	Series #	Date	Qty	Extension	U/Price		Date	Qty	Value
5	1	05/10/2015	600	2,340.00	$ 3.9000		05/31/2015	2,586	$ 10,165.40		10,165.40
6	2	05/15/2015	400	1,540.00	$ 3.8500		06/01/2015	2,398	$ 9,432.20		9,432.20
7	3	05/20/2015	600	2,340.00	$ 3.9000		06/02/2015	2,398	$ 9,432.20		9,432.20
8	4	05/25/2015	500	1,950.00	$ 3.9000		06/03/2015	2,236	$ 8,800.40		8,800.40
9	5	05/28/2015	500	2,050.00	$ 4.1000		06/04/2015	2,686	$ 10,542.37		10,542.37
10	6	06/04/2015	500	1,936.97	$ 3.8739		06/05/2015	2,560	$ 10,050.97		10,050.97
11	7	06/07/2015	450	1,777.12	$ 3.9492		06/06/2015	2,530	$ 9,933.97		9,933.97
12	8	06/09/2015	30	130.00	$ 4.3333		06/07/2015	2,860	$ 11,247.59		11,247.59
13	9	06/14/2015	600	3,080.76	$ 5.1346		06/08/2015	2,700	$ 10,631.59		10,631.59
14	10	06/18/2015	700	2,560.20	$ 3.6574		06/09/2015	2,531	$ 9,992.99		9,992.99
15							06/10/2015	2,400	$ 9,482.09		9,482.09
16							06/11/2015	2,250	$ 8,897.09		8,897.09
17							06/12/2015	2,100	$ 8,312.09		8,312.09
18							06/13/2015	2,000	$ 7,922.09		7,922.09
19							06/14/2015	2,450	$ 10,417.85		10,417.85
20							06/15/2015	2,300	$ 9,832.85		9,832.85
21							06/16/2015	2,200	$ 9,442.85		9,442.85
22							06/17/2015	2,000	$ 8,646.85		8,646.85
23							06/18/2015	2,500	$ 10,387.04		10,387.04
Sheet1

Cell Formulas
Range	Formula
D13	=E13*C13
G16	=G15+1
I5	=ROUND(GetValue(G5,$B$5:$B$14,H5),2)
K5	=SUM(D$6:D$9)+((H5-SUM(C$6:C$9))*E5)

My UDF...

Function GetValue(InvDate As Range, HistDate As Range, Qty As Long) As Single
LL = Application.Match(InvDate, HistDate, 1) + 4
Do Until AccumQty > Qty
    AccumQty = AccumQty + Cells(LL, 3).Value
    AccumSum = AccumSum + Cells(LL, 4).Value
LL = LL - 1
Loop
RAccumQty = AccumQty - Qty
AccumSum = AccumSum - (RAccumQty * Cells(LL + 1, 5))
GetValue = AccumSum
End Function