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