Inventory Valuation

[aromaveda]

Hi All,

Finally, a friend of mine put together a code for inventory valuation based on FIFO. In the attached data the purchases are listed on PO_Data sheet & Inventory on hand is on Inventory sheet.
The idea is - Code checks one item of inventory, on hand qty is matched against last received PO and decending order then a weighted average is computed and noted in the WTD_FOB_PRICE.
Problem (1) now is - The code exits after 3 items computed.
Second thing needs to be done - I need to find cost of goods sold for each customer. A sales details sheet is attached with items sold and rate info etc.
How can I fix problem # 1 & achieve COGS. Below is the code :
I am neither a VB nor an Excel guy... Please help..

Class Module # 1 -

Option Explicit

Sub ComputeFOB()
'
' ComputeFOB Macro
' Macro recorded 9/15/2009 by S
'
    Dim wb As Workbook
    Dim ws As Worksheet

    ' Get PO Data and Inventory Data in Ranges
    Dim rInv As Range
    Dim rPO As Range
'
   
    Set rInv = Range("INV")
    Set rPO = Range("POS")
    
    Dim invQty As Integer
    Dim invItem As String
    
    Dim poItem As String
    Dim poQty As Integer
    Dim poPrice As Double
    
    Dim vMatch As Variant
    Dim vPricingData As Variant
    
    Dim finalPrice As Long
    
    Dim iCtr As Integer
    Dim pCtr As Integer
    Dim i As Integer
    Dim bMatchFound As Boolean
    Dim value As Double
    Dim wtdLdp As Double
    
    ReDim vMatch(0 To rPO.Rows.Count, 0 To rPO.Columns.Count)
    
    
    iCtr = 0
    pCtr = 0
    wtdLdp = 0
    
    Dim itemPos As Integer
    itemPos = 0
    
    For iCtr = 1 To rInv.Rows.Count
        
        bMatchFound = False
        'Get ITEM from inv data
        invItem = rInv.Cells(iCtr, 1)
        invQty = rInv.Cells(iCtr, 2)
        
        itemPos = iCtr
        
        'Loop through PO Table
        For pCtr = 1 To rPO.Rows.Count
        
            'Get Item
            poItem = rPO.Cells(pCtr, 2)
                
            'if matching item is found, add it to the vMatch Collection
            If (invItem = poItem) Then
                bMatchFound = True
                vMatch(pCtr - 1, 0) = rPO.Cells(pCtr, 2)
                vMatch(pCtr - 1, 1) = rPO.Cells(pCtr, 4)
                vMatch(pCtr - 1, 2) = rPO.Cells(pCtr, 5)
            ElseIf poItem = "" Then
                Exit For
            End If
            Next pCtr
            
                       
            If (bMatchFound) Then
            
                'set the pricing_data variant to vMatch - this is where we will store the qty and price from
                'table to compute weighted_average_ldp_price
                'vPricingData = vMatch
                ReDim vPricingData(0 To rPO.Rows.Count, 0 To 3)
                ReDim Preserve vPricingData(0 To rPO.Rows.Count, 0 To 3)
                                
                'Now loop through vMatch and compare the PO_Qty to Inventory Qty
                Dim j As Integer
                j = 1
                For pCtr = 0 To UBound(vMatch)
                
                    poQty = vMatch(pCtr, 1)
                    poPrice = vMatch(pCtr, 2)
                    
                    If (poQty > invQty And j = 1) Then
                        finalPrice = poPrice
                        rInv.Cells(itemPos, 3) = finalPrice
                        MsgBox ("Final Weighted LDP Price for :" & invItem & " is :" & finalPrice)
                        Exit For
                    ElseIf (poQty < invQty) Then
                            j = j + 1
                            
                            value = poQty * poPrice
                            vPricingData(pCtr, 0) = poQty
                            vPricingData(pCtr, 1) = poPrice
                            vPricingData(pCtr, 2) = value
                            
                            invQty = invQty - poQty
                            
                    ElseIf (poQty > invQty And j > 1) Then
                            j = j + 1
                            
                            value = invQty * poPrice
                            vPricingData(pCtr, 0) = invQty
                            vPricingData(pCtr, 1) = poPrice
                            vPricingData(pCtr, 2) = value
                            
                            invQty = invQty - poQty
                    End If
                    If invQty <= 0 Then
                    
                        'Calculate Weighted Average LDP based on data in vPricingData
                        Dim v1 As Double
                        Dim v2 As Double
                        Dim jCtr As Integer
                        
                        For jCtr = 0 To UBound(vPricingData)
                            v1 = v1 + vPricingData(jCtr, 0)
                            v2 = v2 + vPricingData(jCtr, 2)
                        Next jCtr
                        wtdLdp = v2 / v1
                        wtdLdp = Round(wtdLdp, 2)
                        'Populate the wtdLdp Price
                        rInv.Cells(itemPos, 3) = wtdLdp
                        MsgBox ("Final Weighted LDP Price for :" & invItem & " is :" & wtdLdp)
                        Exit For
                    End If
                Next pCtr
            End If
    Next iCtr
    'Sheets(1).Select
End Sub

Class - Module # 2

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/21/2009 by S
'

'
    Range("A1:E2500").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range( _
        "A2"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
End Sub

[shg]

Please change the PHP tags to CODE tags.

Thanks.

[aromaveda]

Did it, sorry about that

thanks
sanjay

[royUK]

Your code modules are not Class modules.

Fio is best calculated using a User Defined Functions, search the Forum for examples o here's a template here

http://excel-it.com/workbook_downloads.htm

If you set your Sales sheet as a proper Excel Table you could use a Pivottable for getting Customer data. The table should have a Header Row and no completely empty Rows or colmns between data.