+ Reply to Thread
Results 1 to 11 of 11

FIFO Inventory- COGS & Inventory Valuation

  1. #1
    Registered User
    Join Date
    12-07-2005
    Posts
    72

    FIFO Inventory- COGS & Inventory Valuation

    Excel & VBA Gurus,

    I had been trying really hard to get some solution on COGS valuation & inventory valuation on FIFO basis. my daily transactions are typical sales & purchases.
    Attached is the inventory in/out movement from Quick Books. in the column "Num" type bill is the entry from purchase bill whihc always has a reference as P/O####. This is how i will capture the landed cost against a PO., another type "Inv Adj" is inter warehouse transfer. Name is cusotmer, Inventory is my item number. in some cases it is like "2000", in some cases it is 10000:10121, and in some cases it is 10000:10200:10201 that is why they fall in different columns when i export them.
    What I would lilke to do: 1) Run a report by month, by customer showing cost of goods sold on FIFO basis, I can capture sales amount by running another report.
    Please let me know if you need any other info.

    Thanks
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-07-2005
    Posts
    72

    Re: FIFO Inventory- COGS & Inventory Valuation

    Hi All,
    I am trying to value inventory on FIFO basis (atleast to nearest possible accurate level). I have exported & doctored few reports from Quick Books (accounting s/w). On attached sheet on FIFO details tab I caputred on hand inventory, received between 7-1-09 to 8-18-09 PO by PO details on PO cost tab with other cost overhead items broken down. what i would like to do.. on FIFO details tab- for on hand inventory- find rate or average rate starting from the last received PO. see example shown on line 11 on FIFO Details sheet. can any one provide any code or formula to achieve this? # PO received column does not have much significance, since i was trying to check how many po's are making the total received qty's. Please help
    SD
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-07-2005
    Posts
    72

    Re: FIFO Inventory- COGS & Inventory Valuation

    Can someone please help me with this? even any part achievements or referencing links will do. But i really need this urgently
    Thanks
    SD

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: FIFO Inventory- COGS & Inventory Valuation

    Hello aromaveda,

    What your asking for is something that you should discuss with your accountant. I don't think you will find anyone here willing to take on the legal burden of helping you with this, especially for free. I have seen my small business owners get into trouble using QuickBooks. If you don't have an accountant then you should find one.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  5. #5
    Registered User
    Join Date
    12-07-2005
    Posts
    72

    Re: FIFO Inventory- COGS & Inventory Valuation

    Hi Leith,

    I dont think there is any legal implication here. All I want to do is compute the inventory valuation based upon FIFO method which QuickBooks is currently not capable of doing since it computes it on average cost basis. I am only asking if anyone has developed a code or if someone could provide me a formulae to do the computation. Since manual calculation is very time consuming a code will help to automate it. No one will be legally held responsible for the output of the code (which i will be also testing before using it, hence it will be my sole responsibility)

    thanks
    SD

  6. #6
    Registered User
    Join Date
    12-07-2005
    Posts
    72

    Re: FIFO Inventory- COGS & Inventory Valuation

    I found the below code on the internet. But it gives me an error "Run Type Mismatch 13. Can anyone help fix this? Can some one suggest how the table structure should be so i could make this work for FIFO valuation?

    Option Explicit
    Sub Update_Inventory()
    Starting_Inventory
    Dim endrow As Long 'last row in range
    Dim Title As String 'book title
    Dim Mycell As Range 'Cell with sold item title
    Dim MyCell2 As Range
    Dim qtySell As Long 'Sell volume in current row
    Dim i As Long 'row counter
    Dim BuySell As Range
    Dim MyRow As Long
    Dim RemInv As String
    Dim invred As Long
    Dim Lastrow As Long 'row above sell transaction
    endrow = Range("A" & Rows.Count).End(xlUp).Row
    Set BuySell = Range("B1:B" & endrow).Find("Sell", LookIn:=xlValues)
    If BuySell Is Nothing Then
    Exit Sub
    End If
    For i = Range(BuySell.Address).Row To endrow Step 1
    Title = Range(BuySell.Address).Offset(0, -1).Value
    qtySell = BuySell.Offset(0, 2).Value
    Lastrow = BuySell.Row - 1
    For Each Mycell In Range("A1:A" & Lastrow)
    Do While MyRow < BuySell.Row
    If Mycell.Value <> Title Then
    GoTo nextmycell:
    ElseIf Mycell.Offset(0, 1).Value = "buy" Then
    RemInv = Range(Mycell.Address).Offset(0, 6).Address
    If Range(RemInv).Value = 0 Then
    GoTo nextmycell:
    ElseIf qtySell <= Range(RemInv).Value Then
    Range(RemInv).Value = Range(RemInv).Value - qtySell
    qtySell = 0
    MyRow = BuySell.Row
    GoTo NextSale:
    ElseIf Range(RemInv).Value < qtySell Then
    invred = Range(RemInv).Value
    Range(RemInv).Value = 0
    qtySell = qtySell - invred
    GoTo nextmycell:
    End If
    End If
    Loop
    nextmycell:
    Next Mycell

    NextSale:
    On Error GoTo ErrorHandler:
    Set BuySell = Range("B" & i & ":B" & endrow). _
    FindNext(BuySell)
    Next
    ErrorHandler:
    End Sub

    Sub Starting_Inventory()
    Dim endrow As Long
    Dim Mycell As Range
    endrow = Range("A" & Rows.Count).End(xlUp).Row
    For Each Mycell In Range("A1:A" & endrow)
    If Mycell.Offset(0, 1).Value = "buy" Then
    Mycell.Offset(0, 6).Value = Mycell.Offset(0, 3).Value
    End If
    Next Mycell
    End Sub
    Thanks
    SD
    Last edited by aromaveda; 08-29-2009 at 01:59 AM.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: FIFO Inventory- COGS & Inventory Valuation

    Please edit your post to add code tags.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    12-07-2005
    Posts
    72

    Re: FIFO Inventory- COGS & Inventory Valuation

    i dont know how to do that. let me figure out

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: FIFO Inventory- COGS & Inventory Valuation

    Hello armomaveda,


    How to wrap your Code
    1. Select all your code using the mouse.
    2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

    OR you can also do this manually by placing the tags
    [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.

  10. #10
    Registered User
    Join Date
    12-07-2005
    Posts
    72

    Re: FIFO Inventory- COGS & Inventory Valuation

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-24-2009
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: FIFO Inventory- COGS & Inventory Valuation

    Hello aromaveda

    Have u found solution to this?


    EM

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1