FIFO Valuation not Working Properly

[austinandreikurt]

Hi,

I found a FIFO valuation vba that I think is working successfully for some but when I used my figures, it is not. Can you fix this for me?
Original template is from this link: FIFO Calculator — Excel Dashboards VBA

Here is the "ExC" tab from my file:

FIFO_Sample.xlsm
	A	B	C	D	E
1	Date	Product	Qty	Cost	Cost of Goods
2	01-Jan-21	S-TXTL-GRY	50	$ 84.00	$ 4,200.00
3	31-Jan-21	S-TXTL-GRY	20	$ 86.00	$ 1,720.00
4	25-Feb-21	S-TXTL-GRY	60	$ 90.00	$ 5,400.00
5	25-Mar-21	S-TXTL-GRY	300	$ 105.00	$ 31,500.00
6	26-Mar-21	S-TXTL-GRY	100	$ 101.00	$ 10,100.00
7	15-Apr-21	S-TXTL-GRY	320	$ 200.00	$ 64,000.00
8	15-Apr-21	S-TXTL-GRY	30	$ 20.00	$ 600.00
9	21-May-21	S-TXTL-GRY	375	$ 80.00	$ 30,000.00
10	09-Dec-21	S-TXTL-GRY	32	$ 100.00	$ 3,200.00
ExC

Cell Formulas
Range	Formula
E2:E10	E2	=D2*C2

And this is the result I get from my SALES reflected in Column D to F:

FIFO_Sample.xlsm
	A	B	C	D	E	F
1	RESULT
2
3	Month	Product Name	Sell	Cost of Goods Sold	Remaining Inv	FIFO Value
4	Jan	S-TXTL-GRY	53	$ 4,458.00	17	$ 1,462.00
5	Feb	S-TXTL-GRY	56	$ 5,040.00	4	$ 360.00
6	Mar	S-TXTL-GRY	59	$ 6,195.00	341	$ 35,405.00
7	Apr	S-TXTL-GRY	58	$ 11,600.00	292	$ 53,000.00
8
Results

Cell Formulas
Range	Formula
D4:D7	D4	=fifo2(ExC!$A$2:$D$10,A4,B4,C4)
E4:E7	E4	=SUMPRODUCT((MONTH(ExC!$A$2:$A$10)=MONTH(A4))*(ExC!$B$2:$B$10=$B4)*(ExC!$C$2:$C$10))-C4
F4:F7	F4	=SUMPRODUCT((MONTH(ExC!$A$2:$A$10)=MONTH(A4))*(ExC!$B$2:$B$10=$B4)*(ExC!$E$2:$E$10))-D4

But this should be the actual or desired results based on FIFO method:

FIFO_Sample.xlsm
	I	J	K	L	M	N
1	DESIRED RESULT
2
3	Month	Product Name	Sell	Cost of Goods Sold	Remaining Inv	FIFO Value
4	Jan	S-TXTL-GRY	53	$ 4,458.00	17	$ 1,462.00
5	Feb	S-TXTL-GRY	56	$ 4,972.00	21	$ 428.00
6	Mar	S-TXTL-GRY	59	$ 5,880.00	362	$ 37,610.00
7	Apr	S-TXTL-GRY	58	$ 6,090.00	654	$ 96,120.00
8
9			Purchses	Sales	Running Inv
10		Jan	50		50
11		Jan	20		70
12		Jan		53	17
13		Feb	60		77
14		Feb		56	21
15		Mar	300		321
16		Mar	100		421
17		Mar		59	362
18		Apr	320		682
19		Apr	30		712
20		Apr		58	654
21
22
Results

Cell Formulas
Range	Formula
L4	L4	=(50*84)+(3*86)
M4	M4	=M12
N4	N4	=(17*86)
L5	L5	=(17*86)+(39*90)
M5	M5	=M14
N5	N5	=SUMPRODUCT((MONTH(ExC!$A$2:$A$10)=MONTH(I5))*(ExC!$B$2:$B$10=$B5)*(ExC!$E$2:$E$10))-L5
L6	L6	=(21*90)+(38*105)
M6	M6	=M17
N6	N6	=(262*105)+(100*101)
L7	L7	=58*105
M7	M7	=M20
N7	N7	=(204*105)+(100*101)+(320*200)+(30*20)
M11:M20	M11	=M10+K11-L11

And this is the Function vba for the FIFO formula

Option Explicit
Function FIFO2(ByRef Data, ByVal Dte As Long, ByVal Product As String, ByVal Stock As Double) As Double
    Dim ar As Variant
    Dim i As Long
    Const DateCol As Long = 1
    Const ProdCol As Long = 2
    Const QtyCol  As Long = 3
    Const CostCol As Long = 4
    
    ar = Data
    
    For i = LBound(ar, 1) To UBound(ar, 1)
        If Month(ar(i, DateCol)) = Month(Dte) Then
            If ar(i, ProdCol) = Product Then
                If Stock < ar(i, QtyCol) Then
                    FIFO2 = FIFO2 + Stock * ar(i, CostCol)
                    Exit Function
                Else
                    FIFO2 = FIFO2 + (ar(i, QtyCol) * ar(i, CostCol))
                    Stock = Stock - ar(i, QtyCol)
                    If Stock <= 0 Then Exit Function
                End If
            End If
        End If
    Next
     
End Function

Thank you in advance!

 

[BSALV]

Can you add your file (or a reduced example) in a link here ?

 

[austinandreikurt]

Can you add your file (or a reduced example) in a link here ?

Hi! I can't seem to attach files here. I uploaded it first in a gsheet then just have the vba code below in a module:
FIFO_Sample

Option Explicit
Function FIFO2(ByRef Data, ByVal Dte As Long, ByVal Product As String, ByVal Stock As Double) As Double
    Dim ar As Variant
    Dim i As Long
    Const DateCol As Long = 1
    Const ProdCol As Long = 2
    Const QtyCol  As Long = 3
    Const CostCol As Long = 4

    ar = Data

    For i = LBound(ar, 1) To UBound(ar, 1)
        If Month(ar(i, DateCol)) = Month(Dte) Then
            If ar(i, ProdCol) = Product Then
                If Stock < ar(i, QtyCol) Then
                    FIFO2 = FIFO2 + Stock * ar(i, CostCol)
                    Exit Function
                Else
                    FIFO2 = FIFO2 + (ar(i, QtyCol) * ar(i, CostCol))
                    Stock = Stock - ar(i, QtyCol)
                    If Stock <= 0 Then Exit Function
                End If
            End If
        End If
    Next

End Function

 

[austinandreikurt]

Hi,
This kind of Log creation could also help a lot then I can work around to get the Desired Result. My file has a lot of Products and different dates within a year.

FIFO_Sample.xlsm
	A	B	C	D	E
1	Month	Product Name	Sell	Cost	Cost of Goods Sold
2	Jan	S-TXTL-GRY	50	84	$ 4,200.00
3	Jan	S-TXTL-GRY	3	86	$ 258.00
4	Feb	S-TXTL-GRY	17	86	$ 1,462.00
5	Feb	S-TXTL-GRY	39	90	$ 3,510.00
6	Mar	S-TXTL-GRY	21	90	$ 1,890.00
7	Mar	S-TXTL-GRY	38	105	$ 3,990.00
8	Apr	S-TXTL-GRY	58	105	$ 6,090.00
Sales Distribution_Exc

Cell Formulas
Range	Formula
E2:E8	E2	=C2*D2

Thanks!

 

[BSALV]

like this ?
It calculates really day by day, not at the end of the month.
You can add a date and a product without a number to know the stock at that date.
stock

 

[austinandreikurt]

like this ?
It calculates really day by day, not at the end of the month.
You can add a date and a product without a number to know the stock at that date.
stock

Many thanks for your attention on this. Yes! That would be the desired result but will be needing it in a monthly basis since this is for inventory purposes. And would also not need the unit price sold and unit price stock. I think this is for stock market/shares?

 

[austinandreikurt]

I tried running the tool using my original data but it won't give the end of the month results as you have mentioned.

Since there is a total of units for the product by end of January, then the 53 sold units will be utilizing the 50@84 and the remaining 3@86. Then for Feb, with the purchase of 60 units, the 56 sold units will be procured from the remaining 17@86 and 39@90. Not sure what the "Eff.Sold" is? Thank you!

 

[BSALV]

eff. sold = effective sold = if you sell more products (53) then you have in stock (50), then sold 50.

1st row 1/1/21= really that day, not the end of Januari.
Is it always like that, that you summarize all sales for a month and that the last day of the month can be used instead of for example 1/1/21 ?

 

[austinandreikurt]

eff. sold = effective sold = if you sell more products (53) then you have in stock (50), then sold 50.

1st row 1/1/21= really that day, not the end of Januari.
Is it always like that, that you summarize all sales for a month and that the last day of the month can be used instead of for example 1/1/21 ?

Yes, it will always be like that. The sales is based on the total sales at the end of the month vs the total running inventory balance. Thus, whatever day within the month a unit is received or sold, it will always be treated as end of the month purchases/sales. That is also why a Running Balance of Inventory for each month will be needed as part of inventory report. Thanks!

 

[BSALV]

            If aBuy(i1, 2) = aResult(i, 2) And aBuy(i1, 1) <= WorksheetFunction.EoMonth(aResult(i, 1), 0) Then    'same product bought before that date

with this addition, it'll always take the last day of that month.
Link in #5 is updated.

Option Compare Text

Sub Fifo_BS()
     Dim aBuy, aResult(), iSell, MyValueSold, MyValueStock, MyStock, arr()

     Set lo = Sheets("Exc").ListObjects("TBL_Buy")
     With lo.Range
          .Sort .Range("B1"), xlAscending, , .Range("A1"), xlAscending, Header:=xlYes
          aBuy = lo.DataBodyRange.Value2
          .Sort .Range("A1"), xlAscending, , .Range("B1"), xlAscending, Header:=xlYes
     End With

     Set lo = Sheets("Results").ListObjects("TBL_Result")
     With lo.Range
          .Sort .Range("B1"), xlAscending, , .Range("A1"), xlAscending, Header:=xlYes
          aResult = lo.DataBodyRange.Resize(, 3).Value2
     End With

     ReDim Preserve aResult(1 To UBound(aResult), 1 To UBound(aResult, 2) + 4)
     ReDim arr(1 To UBound(aResult), 1 To 4)
     For i = 1 To UBound(aResult)
          'If i = 5 Then MsgBox "1"
          iSell = aResult(i, 3): mysold = 0: MyValueSold = 0: MyStock = 0: MyValueStock = 0     'reset all counters
          r = Application.Match(aResult(i, 2), Application.Index(aBuy, 0, 2), 0)     '1st row with that article in the array aBuy
          If IsNumeric(r) Then                                  'product still in aBuy
               For i1 = r To UBound(aBuy)                       'loop from this row until last row
                    If aBuy(i1, 2) = aResult(i, 2) And aBuy(i1, 1) <= WorksheetFunction.EoMonth(aResult(i, 1), 0) Then    'same product bought before that date
                         x = Application.Max(0, Application.Min(aBuy(i1, 3), iSell))     'min of saldo sells and that action
                         If x > 0 Then
                              mysold = mysold + x
                              iSell = iSell - x                 'saldo quantity
                              MyValueSold = MyValueSold + x * aBuy(i1, 4)     'value products sold
                              aBuy(i1, 3) = aBuy(i1, 3) - x     'remaining from that buy
                              If aBuy(i1, 3) <= 0 Then aBuy(i1, 2) = "~"     'delete that name"
                         End If
                         If aBuy(i1, 3) > 0 Then MyStock = MyStock + aBuy(i1, 3): MyValueStock = MyValueStock + aBuy(i1, 3) * aBuy(i1, 4)
                    Else
                         If aBuy(i1, 2) <> aResult(i, 2) Then Exit For
                    End If
               Next
          End If
          arr(i, 1) = mysold: arr(i, 2) = MyValueSold: arr(i, 3) = MyStock: arr(i, 4) = MyValueStock
     Next

     lo.DataBodyRange.Cells(1, 4).Resize(UBound(arr), UBound(arr, 2)).Value = arr
     With lo.Range
          .Sort .Range("a1"), xlAscending, , .Range("b1"), xlAscending, Header:=xlYes
     End With

End Sub