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 :


Date BS Product Quantity Cost
05.01.2016 Buy a 950 102.35
26.01.2016 Sell a 800 102.35
11.03.2016 Buy b 600 102.38
06.04.2016 Sell b 200 102.41
14.04.2016 Sell b 400 102.41
09.05.2016 Buy c 300 102.42
11.10.2016 Sell c 150 102.44
06.02.2017 Buy c 800 102.44
09.03.2017 Buy d 200 102.42




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

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel-questions/1066807-fifo-p-l-calculation.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it. Please be patient and do not attempt to post the question again.