FIFO P&L calculation

cyrilbob

New Member
Joined
Aug 8, 2018
Messages
3
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

Code:
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sorry for the duplicated post (I had an error).

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


Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top