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!

 

[austinandreikurt]

@BSALV ALMOST THERE! ? Just a fix on EOMONTH scope I think? I tried in a different SKU and the Stock which should have been 275 was only 170 and found that it was due to Mar31,2021 remaining inventories not captured and only until Mar30,2021. I have reupdated the link above. Thank you!

 

[BSALV]

is it working now or do you still need an adaption ?

 

[austinandreikurt]

is it working now or do you still need an adaption ?

Hmm, not sure what's the problem why it don't take the end of the month (March 31) when the code used "<=" ?

 

[BSALV]

the link in #5 contains the last version with the Emonth as described in my reaction #10.
So, i think in #11, you were still working with an older version ?
Can you post your actual problem ?

 

[austinandreikurt]

@BSALV I just used the file in link #5 and download and used my data. When I tried to add another SKUs, I got a problem with the running invemtory or in STOCK in COlumn F because it didn't captured March 31 as part of the remaining inventories but it actually adds up that inventory when computing the COGS in Column E.

 

[austinandreikurt]

Hi @BSALV I now know where the problem is. It is because my raw date has time data (3/31/2021 4:30 or 3/31/2021 19:24) and the "<="&EOMONTH only considers 3/31/2021 0:00 as part of the totals. I just need to clean the dates using DATE formula and it now works perfectly. Thank you for your attention on this. Now I have a FIFO tool for inventory that works. Awesome!

 

[BSALV]

you're welcome