Inventory valuation models

inv-val-funcs.bas

Function LIFO(dataRange As Range) As Variant
    Dim data As Variant
    Dim result() As Variant
    Dim cumIn As Double
    Dim cumOut As Double
    Dim cost As Double
    Dim i As Long

    ' Convert input range to a variant array
    data = dataRange.Value

    ' Resize result array
    ReDim result(1 To UBound(data, 1), 1 To 1)

    ' Initialize cumulative variables
    cumIn = 0
    cumOut = 0
    cost = 0

    ' Iterate through data
    For i = LBound(data, 1) To UBound(data, 1)
        ' Check if output units are not empty (assumes Column C holds output units)
        If Not IsEmpty(data(i, 3)) Then
            cumOut = cumOut + data(i, 3)
            ' Iterate backward to find matching input units
            For j = i - 1 To 1 Step -1
                ' Check if input units are not empty (assumes Column B holds input units)
                If Not IsEmpty(data(j, 2)) Then
                    cumIn = cumIn + data(j, 2)
                    ' Check if cumulative input is greater than cumulative output
                    If cumIn > cumOut Then
                        Exit For
                    Else
                        ' Calculate and update cost based on unit cost and input units
                        cost = cost + data(j, 1) * data(j, 2)
                        data(j, 2) = Empty
                    End If
                End If
            Next j
            ' Calculate and update cost based on conditions for the final row
            If cumIn - cumOut > 0 Then
                cost = (cost + (data(j, 1) * (data(j, 2) - (cumIn - cumOut)))) / cumOut
                data(j, 2) = cumIn - cumOut
            Else
                cost = cost / cumOut
            End If
            ' Store cost in result array and reset variables
            result(i, 1) = cost
            cumIn = 0: cumOut = 0: cost = 0
        End If
    Next i

    ' Output result array
    LIFO = result
End Function

Function AVR_COST(dataRange As Range) As Variant
    Dim data As Variant
    Dim result() As Variant
    Dim Bal As Double
    Dim Debit As Double
    Dim AVcost As Double
    Dim i As Long

    ' Convert input range to a variant array
    data = dataRange.Value

    ' Resize result array
    ReDim result(1 To UBound(data, 1), 1 To 1)

    ' Initialize cumulative variables
    Bal = 0
    Debit = 0
    AVcost = 0

    ' Iterate through data
    For i = LBound(data, 1) To UBound(data, 1)
        ' Check if input units are greater than 0 (assumes Column B holds input units)
        If data(i, 2) > 0 Then
            Bal = Bal + data(i, 2)
            Debit = Debit + data(i, 1) * data(i, 2)
            AVcost = Debit / Bal
        ElseIf data(i, 3) > 0 Then
            ' Store AVcost in result array (assumes Column C holds output units)
            result(i, 1) = AVcost
            Debit = Debit - data(i, 3) * AVcost
            Bal = Bal - data(i, 3)
        End If
    Next i

    ' Output result array
    AVR_COST = result
End Function

Function FIFO(rng As Range) As Variant
    Dim a As Variant, cost As Double, sumIn As Double, sumOut As Double, _
            i As Long, ii As Long, n As Long
    
    On Error Resume Next ' Ignore errors temporarily
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    With rng.Parent
        rng.Resize(, 1).Offset(, 4).ClearContents
        a = rng.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
        FIFO = Application.Index(a, 0, 5)
        Erase a
    End With
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
    On Error GoTo 0 ' Reset error handling
End Function

inventory-valuation.bas

' Source: http://www.mrexcel.com/forum/excel-questions/167756-inventory-fifo-lifo-average-cost.html
' Author: jindon
' Date: Oct 14th, 2005, 05:38 PM


'Assume the following:

' 1) that Column("E") holds Unit price
' 2) that Column("F") holds input units
' 3) that Column("G") holds out units

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