FIFO P&L calculation

[cyrilbob]

Hello guys,

I was looking for FIFO profit and loss calculation and despite many FIFO subjects (mostly calculating stock value), I ended up starting from scratch, which I usually avoid.. two subjects helped to understand, but I coudln't have it work for my-self.

This UDF couldn't work :
https://www.mrexcel.com/forum/excel-questions/75145-first-first-out-inventory-database-2.html

Had wrong results with this one :
https://www.mrexcel.com/forum/excel...-assign-unit-cost-each-sales-base-fifo-2.html


So this is my solution to this problem. Hopefully it will help people looking for identical solutions. It seems to work for me, but if you have any trouble with it, I'll gadly have a look. The same way, if you see some room for performance improvement, I'll take it !

"Data" tab goes :

[TABLE="width: 449"]
<tbody>[TR]
[TD]Date[/TD]
[TD]BS[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD="align: right"]05.01.2016[/TD]
[TD]Buy[/TD]
[TD]a[/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]102.35[/TD]
[/TR]
[TR]
[TD="align: right"]26.01.2016[/TD]
[TD]Sell[/TD]
[TD]a[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]102.35[/TD]
[/TR]
[TR]
[TD="align: right"]11.03.2016[/TD]
[TD]Buy[/TD]
[TD]b[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]102.38[/TD]
[/TR]
[TR]
[TD="align: right"]06.04.2016[/TD]
[TD]Sell[/TD]
[TD]b[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]102.41[/TD]
[/TR]
[TR]
[TD="align: right"]14.04.2016[/TD]
[TD]Sell[/TD]
[TD]b[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]102.41[/TD]
[/TR]
[TR]
[TD="align: right"]09.05.2016[/TD]
[TD]Buy[/TD]
[TD]c[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]102.42[/TD]
[/TR]
[TR]
[TD="align: right"]11.10.2016[/TD]
[TD]Sell[/TD]
[TD]c[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]102.44[/TD]
[/TR]
[TR]
[TD="align: right"]06.02.2017[/TD]
[TD]Buy[/TD]
[TD]c[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]102.44[/TD]
[/TR]
[TR]
[TD="align: right"]09.03.2017[/TD]
[TD]Buy[/TD]
[TD]d[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]102.42[/TD]
[/TR]
</tbody>[/TABLE]


Please note that Date should be sorted previously.
Please note that Product list should be in a separated list (A1:A15 in "list" tab here) :
Results are shown in column F and G

Sub FIFO()
    Dim LR As Long

'list of product
Sheets("list").Select
Set rng = Range("A1:A15") 
For Each cell In rng


Sheets("Data").Select
Range("F1").Value = "left"
Range("G1").Value = "PnL"


    LR = Cells(Rows.Count, "A").End(xlUp).Row
    
    For I = 2 To LR
            Cells(I, 6) = Cells(I, 4)
    Next I


For n = 1 To LR
 If Cells(n, 3) = cell And Cells(n, 2) = "Buy" Then
    For I = 2 To LR
    'case 1
        If Cells(I, 2) = "Sell" And Cells(I, 3) = cell And Cells(I, 6) <> 0 Then
            If Cells(I, 6) < Cells(n, 6) Then
            Cells(I, 7) = Cells(I, 6) * (Cells(I, 5) - Cells(n, 5)) + Cells(I, 7)
            Cells(n, 6) = Cells(n, 6) - Cells(I, 6)
            Cells(I, 6) = 0
            End If
        End If
        
    'case 2
        If Cells(I, 2) = "Sell" And Cells(I, 3) = cell And Cells(I, 6) <> 0 Then
            If Cells(I, 6) > Cells(n, 6) Then
            Cells(I, 7) = Cells(n, 6) * (Cells(I, 5) - Cells(n, 5)) + Cells(I, 7)
            Cells(I, 6) = Cells(I, 6) - Cells(n, 6)
            Cells(n, 6) = 0
            'if < = >
            End If
        End If


    'case 3
        If Cells(I, 2) = "Sell" And Cells(I, 3) = cell And Cells(I, 6) <> 0 Then
            If Cells(I, 6) = Cells(n, 6) Then
            Cells(I, 7) = Cells(I, 6) * (Cells(I, 5) - Cells(n, 5)) + Cells(I, 7)
            Cells(I, 6) = 0
            Cells(n, 6) = 0
            End If
        End If
 
    Next I
    End If
Next n
Next cell


End Sub

 

[cyrilbob]

Sorry for the duplicated post (I had an error).

Cannot edit the post, so there is a correction (failed my c/p),

Sub FIFO()
    Dim LR As Long


'list of product
Sheets("list").Select
Set rng = Range("A1:A15") 






Sheets("Data").Select
Range("F1").Value = "left"
Range("G1").Value = "PnL"




    LR = Cells(Rows.Count, "A").End(xlUp).Row
    
    For I = 2 To LR
            Cells(I, 6) = Cells(I, 4)
    Next I


For Each cell In rng


For n = 1 To LR
 If Cells(n, 3) = cell And Cells(n, 2) = "Buy" Then
    For I = 2 To LR
    'case 1
        If Cells(I, 2) = "Sell" And Cells(I, 3) = cell And Cells(I, 6) <> 0 Then
            If Cells(I, 6) < Cells(n, 6) Then
            Cells(I, 7) = Cells(I, 6) * (Cells(I, 5) - Cells(n, 5)) + Cells(I, 7)
            Cells(n, 6) = Cells(n, 6) - Cells(I, 6)
            Cells(I, 6) = 0
            End If
        End If
        
    'case 2
        If Cells(I, 2) = "Sell" And Cells(I, 3) = cell And Cells(I, 6) <> 0 Then
            If Cells(I, 6) > Cells(n, 6) Then
            Cells(I, 7) = Cells(n, 6) * (Cells(I, 5) - Cells(n, 5)) + Cells(I, 7)
            Cells(I, 6) = Cells(I, 6) - Cells(n, 6)
            Cells(n, 6) = 0
            'if < = >
            End If
        End If




    'case 3
        If Cells(I, 2) = "Sell" And Cells(I, 3) = cell And Cells(I, 6) <> 0 Then
            If Cells(I, 6) = Cells(n, 6) Then
            Cells(I, 7) = Cells(I, 6) * (Cells(I, 5) - Cells(n, 5)) + Cells(I, 7)
            Cells(I, 6) = 0
            Cells(n, 6) = 0
            End If
        End If
 
    Next I
    End If
Next n
Next cell




End Sub