FIFO matching sale qty to purchase qty

[pulvi2003]

I have a worksheet with columns as follows :
A B C D E F
item batch date qty rate amount
berries p-1 11/11/2010 2300 39.59 91065.97
berries p-2 24/12/2013 2000 22.60 45200.00
berries p-3 26/12/2013 700 22.50 15750.00
berries s-1 01/06/2017 -1000 89.87 89865.65
berries s-2 02/06/2017 -1000 92.02 92020.00
berries s-3 24/08/2017 -1000 93.02 93020.00
berries s-4 26/12/2017 -1000 95.83 95830.00
berries s-5 28/12/2013 -1000 94.73 94730.00
carrots p-1 17/08/2016 1000 21.54 21540.00
carrots p-2 24/08/2016 5350 24.54 131289.00
carrots s-1 06/06/2017 - 850 26.00 22100.00
carrots s-2 09/06/2017 - 500 26.50 13250.00
The positive qty denotes purchase in batches p-1,p2... and -ve qty denotes sale in batches s-1,s2...I want to match the sale qty with purchase qty on FIFO (First in First out) basis and calculate profit / loss in each sale and the remaining stock after all sale adjusted, with its cost value What formula in excel to do the above calculations ?

 

[Gerald Higgins]

Does the batch code prefix of "p" indicate a purchase ?
And "s" indicate a sale ?

For the sale of berries s-1 on 1/6/17, are we meant to somehow identify which of the purchases of berries this came from ?

Can you tell us what the answers SHOULD be for your data sample ?

 

[Eric W]

If I'm correct, on every "sell" line, you want to figure out how many units to take from the first "buy" and how much they cost, how many from the second "buy" line and how much they cost, and so on, and give the total cost. Sell lines lower down would take into account any "sells" ahead of it. If that's true, that would be a really tricky formula. I've played around with it for a couple days now and could not work it out. It can be done with a User-Defined Function somewhat easier.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. Paste the following code into the window that opens:

Public Function FIFOCost(ByVal MyRange As Range) As Variant
Dim i As Long, MyData As Variant, lr As Long, tot As Double, amt As Double, Sale As Double, w as Double

' Eric W, MrExcel.com, 1/11/2019

    Application.Volatile
    MyData = MyRange.Value
    lr = UBound(MyData)
    If Left(MyData(lr, 2), 1) <> "s" Then
        FIFOCost = ""
        Exit Function
    End If
    
    tot = 0
    For i = LBound(MyData) To lr - 1
        If LCase(Left(MyData(i, 2), 1)) = "s" And MyData(i, 1) = MyData(lr, 1) Then tot = tot - MyData(i, 4)
    Next i
    
    Sale = MyData(lr, 4) * -1
    For i = LBound(MyData) To lr - 1
        If LCase(Left(MyData(i, 2), 1)) = "p" And MyData(i, 1) = MyData(lr, 1) Then
            amt = MyData(i, 4)
            If tot > amt Then
                tot = tot - amt
            Else
                amt = amt - tot
                tot = 0
                w = IIf(amt > Sale, Sale, amt)
                FIFOCost = FIFOCost + w * MyData(i, 5)
                Sale = Sale - w
            End If
        End If
        If Sale = 0 Then Exit Function
    Next i

End Function

Now press Alt-Q to close the editor. Now you can use the following formula:

	A	B	C	D	E	F	G	H
item	batch	date	qty	rate	amount	FIFO
berries	p-1
berries	p-2
berries	p-3
berries	s-1	6/1/2017
berries	s-2
berries	s-3
berries	s-4
berries	s-5
carrots	p-1	21540
carrots	p-2
carrots	s-1
carrots	s-2

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]11/11/2010[/TD]
[TD="align: right"]2300[/TD]
[TD="align: right"]39.59[/TD]
[TD="align: right"]91065.97[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]12/24/2013[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]22.6[/TD]
[TD="align: right"]45200[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12/26/2013[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]22.5[/TD]
[TD="align: right"]15750[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]-1000[/TD]
[TD="align: right"]89.87[/TD]
[TD="align: right"]89865.65[/TD]
[TD="align: right"]39590[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6/2/2017[/TD]
[TD="align: right"]-1000[/TD]
[TD="align: right"]92.02[/TD]
[TD="align: right"]92020[/TD]
[TD="align: right"]39590[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]8/24/2017[/TD]
[TD="align: right"]-1000[/TD]
[TD="align: right"]93.02[/TD]
[TD="align: right"]93020[/TD]
[TD="align: right"]27697[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]12/26/2017[/TD]
[TD="align: right"]-1000[/TD]
[TD="align: right"]95.83[/TD]
[TD="align: right"]95830[/TD]
[TD="align: right"]22600[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]12/28/201 7 [/TD]
[TD="align: right"]-1000[/TD]
[TD="align: right"]94.73[/TD]
[TD="align: right"]94730[/TD]
[TD="align: right"]22530[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]8/17/2016[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]21.54[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]8/24/2016[/TD]
[TD="align: right"]5350[/TD]
[TD="align: right"]24.54[/TD]
[TD="align: right"]131289[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]6/6/2017[/TD]
[TD="align: right"]-850[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]22100[/TD]
[TD="align: right"]18309[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]6/9/2017[/TD]
[TD="align: right"]-500[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]13250[/TD]
[TD="align: right"]11820[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=FIFOCost($A$2:$F2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Hope this helps.