Inventory: FIFO, LIFO and Average Cost

[pegbol]

.
.

Hello,

I need to compare and calculate the Unit Cost Price of my Inventory based on the 3 methods of inventory valuation: FIFO (First In, First Out), LIFO (Last In, First Out) and Average Cost.

Next, I enclose 3 snapshots of each method with the results required (columns color yellow).

Thanks in advance for your help.

regards,
Pedro
.
.

 

[pegbol]

.
.

First-In, First-Out (FIFO) -Assumes that the first unit making its way into inventory is the first sold.

inv.xls
	A	B	C	D	E	F	G	H	I	J	K	L
5					QUANTITY				COST
6	N	DATE	DOC.	DESCRIPTION	UNIT PRICE	IN	OUT	BALANCE	UNIT COST	DEBIT	CREDIT	BALANCE
7	1	01/09/2005		BEG. BAL.	10.00	100		100		1,000.00		1,000.00
8	2	12/09/2005	C001	IN	20.00	300		400		6,000.00		7,000.00
9	3	12/09/2005	C002	IN	25.00	170		570		4,250.00		11,250.00
10	4	12/09/2005	V001	OUT			50	520	10.00		500.00	10,750.00
11	5	23/09/2005	V002	OUT			370	150	18.92		7,000.00	3,750.00
12	6	27/09/2005	C003	IN	30.00	700		850		21,000.00		24,750.00
13	7	29/09/2005	V003	OUT			450	400	28.33		12,750.00	12,000.00
14	8	29/09/2005	V004	OUT			250	150	30.00		7,500.00	4,500.00
15	9	02/10/2005	C004	IN	25.00	320		470		8,000.00		12,500.00
16	10	03/10/2005	V005	OUT			50	420	30.00		1,500.00	11,000.00
FIFO

.
.

 

[pegbol]

.
.

Last-In, First-Out (LIFO)- Assumes that the last unit making its way into inventory is sold first. The older inventory is therefore left over at the end of the accounting period.

inv.xls
	A	B	C	D	E	F	G	H	I	J	K	L
5					QUANTITY				COST
6	N	DATE	DOC.	DESCRIPTION	UNIT PRICE	IN	OUT	BALANCE	UNIT COST	DEBIT	CREDIT	BALANCE
7	1	01/09/2005		BEG. BAL.	10.00	100		100		1,000.00		1,000.00
8	2	12/09/2005	C001	IN	20.00	300		400		6,000.00		7,000.00
9	3	12/09/2005	C002	IN	25.00	170		570		4,250.00		11,250.00
10	4	12/09/2005	V001	OUT			50	520	25.00		1,250.00	10,000.00
11	5	23/09/2005	V002	OUT			370	150	21.62		8,000.00	2,000.00
12	6	27/09/2005	C003	IN	30.00	700		850		21,000.00		23,000.00
13	7	29/09/2005	V003	OUT			450	400	30.00		13,500.00	9,500.00
14	8	29/09/2005	V004	OUT			250	150	30.00		7,500.00	2,000.00
15	9	02/10/2005	C004	IN	25.00	320		470		8,000.00		10,000.00
16	10	03/10/2005	V005	OUT			50	420	25.00		1,250.00	8,750.00
LIFO

.
.

 

[pegbol]

.
.

Average Cost - It takes the weighted average of all units available for sale. Only divide the total amount by the units on hand at a date.

inv.xls
	A	B	C	D	E	F	G	H	I	J	K	L
5					QUANTITY				COST
6	N	DATE	DOC.	DESCRIPTION	UNIT PRICE	IN	OUT	BALANCE	UNIT COST	DEBIT	CREDIT	BALANCE
7	1	01/09/2005		BEG. BAL.	10.00	100		100		1,000.00		1,000.00
8	2	12/09/2005	C001	IN	20.00	300		400		6,000.00		7,000.00
9	3	12/09/2005	C002	IN	25.00	170		570		4,250.00		11,250.00
10	4	12/09/2005	V001	OUT			50	520	19.74		986.84	10,263.16
11	5	23/09/2005	V002	OUT			370	150	19.74		7,302.63	2,960.53
12	6	27/09/2005	C003	IN	30.00	700		850		21,000.00		23,960.53
13	7	29/09/2005	V003	OUT			450	400	28.19		12,684.98	11,275.54
14	8	29/09/2005	V004	OUT			250	150	28.19		7,047.21	4,228.33
15	9	02/10/2005	C004	IN	25.00	320		470		8,000.00		12,228.33
16	10	03/10/2005	V005	OUT			50	420	26.02		1,300.89	10,927.44
AVR COST

.
.

 

[pegbol]

.
.

any help ?

.
.

 

[jindon]

Hi
onto FIFO sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    If .Count > 1 Then Exit Sub
    If Not IsNumeric(.Value) Then Exit Sub
    If Not Intersect(.Cells(1, 1), Range("e:g")) Is Nothing And _
        .Row > 6 Then FIFO
End With
End Sub

onto LIFO sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    If .Count > 1 Then Exit Sub
    If Not IsNumeric(.Value) Then Exit Sub
    If Not Intersect(.Cells(1, 1), Range("e:g")) Is Nothing And _
        .Row > 6 Then LIFO
End With
End Sub

onto AVR COST sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    If .Count > 1 Then Exit Sub
    If Not IsNumeric(.Value) Then Exit Sub
    If Not Intersect(.Cells(1, 1), Range("e:g")) Is Nothing And _
        .Row > 6 Then AVR_COST
End With
End Sub

on standard module

Sub FIFO()
Dim a As Variant, Cost As Double, sumIn As Double, sumOut As Double, _
        i As Long, ii As Long, n As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
With Sheets("FIFO")
    .Range("i7", .Cells(Rows.Count, "i").End(xlUp)).ClearContents
    a = .Range("e7", .Cells(Rows.Count, "g").End(xlUp)).Resize(, 5).Value
    n = 1
    For i = LBound(a, 1) To UBound(a, 1)
        If Not IsEmpty(a(i, 3)) Then
            sumOut = a(i, 3)
            For ii = n To i - 1
                If Not IsEmpty(a(ii, 2)) Then
                    sumIn = sumIn + a(ii, 2)
                    If sumIn > sumOut Then
                        Exit For
                    Else
                        Cost = Cost + a(ii, 1) * a(ii, 2)
                        a(ii, 2) = Empty
                    End If
                End If
            Next
            If sumIn - sumOut > 0 Then
                Cost = (Cost + (a(ii, 1) * (a(ii, 2) - (sumIn - sumOut)))) / sumOut
                a(ii, 2) = sumIn - sumOut
            Else
                Cost = Cost / sumOut
            End If
            a(i, 5) = Cost
            sumIn = 0: sumOut = 0: Cost = 0: n = ii
        End If
    Next
    .Range("i7").Resize(UBound(a, 1)) = Application.Index(a, 0, 5)
    Erase a
End With
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub


Sub LIFO()
Dim a As Variant, Cost As Double, sumIn As Double, sumOut As Double, _
        i As Long, ii As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
With Sheets("LIFO")
    .Range("i7", .Cells(Rows.Count, "i").End(xlUp)).ClearContents
    a = .Range("e7", .Cells(Rows.Count, "g").End(xlUp)).Resize(, 5).Value
    For i = LBound(a, 1) To UBound(a, 1)
        If Not IsEmpty(a(i, 3)) Then
            sumOut = a(i, 3)
            For ii = i - 1 To 1 Step -1
                If Not IsEmpty(a(ii, 2)) Then
                    sumIn = sumIn + a(ii, 2)
                    If sumIn > sumOut Then
                        Exit For
                    Else
                        Cost = Cost + a(ii, 1) * a(ii, 2)
                        a(ii, 2) = Empty
                    End If
                End If
            Next
            If sumIn - sumOut > 0 Then
                Cost = (Cost + (a(ii, 1) * (a(ii, 2) - (sumIn - sumOut)))) / sumOut
                a(ii, 2) = sumIn - sumOut
            Else
                Cost = Cost / sumOut
            End If
            a(i, 5) = Cost
            sumIn = 0: sumOut = 0: Cost = 0: n = ii
        End If
    Next
    .Range("i7").Resize(UBound(a, 1)) = Application.Index(a, , 5)
    Erase a
End With
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
Erase a
End Sub

Sub AVR_COST()
Dim a, i As Long, Bal As Double, Debit As Double
Dim AVcost As Double
With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With
With Sheets("AVR COST")
    a = .Range("e7", .Cells(.Rows.Count, "g").End(xlUp)).Resize(, 3).Value
    .Range("i7", .Cells(.Rows.Count, "i").End(xlUp)).ClearContents
    ReDim Preserve a(1 To UBound(a, 1), 1 To 4)
    For i = LBound(a, 1) To UBound(a, 1)
        If a(i, 2) > 0 Then
            Bal = Bal + a(i, 2)
            Debit = Debit + a(i, 1) * a(i, 2)
            AVcost = Debit / Bal
        ElseIf a(i, 3) > 0 Then
            a(i, 4) = AVcost
            Debit = Debit - a(i, 3) * AVcost
            Bal = Bal - a(i, 3)
        End If
    Next
    .Range("i7").Resize(UBound(a, 1)) = Application.Index(a, 0, 4)
    Erase a
End With
With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With
End Sub

test it hard.

 

[pegbol]

.
.

Amazing jindon !!!!


Let me check and test your fantastic code for a while.


Thank you so much for your kind solution.


best regards,
Pedro
.
.

 

[pegbol]

.
.

jindon,

With the little corrections you kindly provided, it works Perfect!.



Again, thank you so much for your valuable help.

Cheers,
Pedro
.
.

 

[Vlinder_Lentebloem]

Jindon-san, Pegbol,

I need to do exactly the same, so I mimicked the spreadsheet and added Jindon-san's code.
You were referring to some adjustments needed to make the code work properly. Any chance you could post or send me the correctly working code or spreadsheet, as my VBA knowledge is VERY limited, so I was just looking for a simple copy-paste.

Thanks,

My email is nancy.erkens@pandora.be

 

[jindon]

Do you have the worksheet(s) exactly structured as Pegbol posted?

The amendments I made was error trap when accidentally invalid entry made like
Text entry I think?