Hello Experts !
This is my first post on this forum and here is my first question:
I have tried many forums and even tried it myself to create formula for inventory valuation at the end of accounting period using FIFO but to no avail. Finally I saw a code here on this forum in another spread and using the code (given below), it is only giving me the cost of units sold, not the value of ending (remaining) inventory.
Public Function FIFOCost(IssueDate As Date, ProductId As String, IssuedQty As Double) As Double
Dim PurchasesWs As Worksheet, OutputWs As Worksheet
Dim PurCounter As Long, OutCounter As Long, OutLastRow As Long, PurLastRow As Long, _
ToDateCounter As Long, LastAllocationRow As Long, CheckCounter As Long
Dim ToDateIssues As Double, AllocatedPur As Double, UnallocatedQty As Double, _
UnallocatedCost As Double, RemainingQty As Double, IssuedCost As Double, _
ToDatePurchases As Double, AvailableQty As Double
Set PurchasesWs = Worksheets("Input-purchase")
PurLastRow = PurchasesWs.Range("A1").Rows.End(xlDown).Row
Set OutputWs = Worksheets("Output-FIFO consumption")
OutLastRow = OutputWs.Range("A1").Rows.End(xlDown).Row
AllocatedPur = 0
ToDateIssues = 0
ToDatePurchases = 0
For ToDateCounter = 2 To OutLastRow 'determine the total units issued to date
If OutputWs.Range("A" & ToDateCounter).Value < IssueDate And _
OutputWs.Range("B" & ToDateCounter).Value = ProductId Then
ToDateIssues = ToDateIssues + OutputWs.Range("C" & ToDateCounter).Value
End If
Next ToDateCounter
For CheckCounter = 2 To PurLastRow 'determine the total units purchased to date
If PurchasesWs.Range("A" & CheckCounter).Value <= IssueDate And _
PurchasesWs.Range("B" & CheckCounter).Value = ProductId Then
ToDatePurchases = ToDatePurchases + PurchasesWs.Range("C" & CheckCounter).Value
End If
Next CheckCounter
AvailableQty = ToDatePurchases - ToDateIssues
If IssuedQty > AvailableQty Then 'if the issued quantity exceeds available stock end function
FIFOCost = "*"
Exit Function
End If
For PurCounter = 2 To PurLastRow 'determine the last allocation row, unallocated quantity and unallocated cost
If PurchasesWs.Range("B" & PurCounter).Value = ProductId And _
PurchasesWs.Range("A" & PurCounter).Value <= IssueDate Then
AllocatedPur = PurchasesWs.Range("C" & PurCounter).Value + AllocatedPur
If AllocatedPur > ToDateIssues Then
LastAllocationRow = PurchasesWs.Range("A" & PurCounter).Row
UnallocatedQty = AllocatedPur - ToDateIssues
UnallocatedCost = UnallocatedQty * PurchasesWs.Range("D" & LastAllocationRow).Value
Exit For
End If
End If
Next PurCounter
IssuedCost = UnallocatedCost
LastAllocationRow = LastAllocationRow + 1
If IssuedQty < UnallocatedQty Then
RemainingQty = IssuedQty
IssuedCost = UnallocatedCost * (RemainingQty / UnallocatedQty)
Else
RemainingQty = IssuedQty - UnallocatedQty
IssuedCost = UnallocatedCost
For OutCounter = LastAllocationRow To PurLastRow 'determine the cost of issuing the remaining units
If PurchasesWs.Range("B" & OutCounter).Value = ProductId Then
If RemainingQty >= PurchasesWs.Range("C" & OutCounter).Value Then
RemainingQty = RemainingQty - PurchasesWs.Range("C" & OutCounter).Value
IssuedCost = IssuedCost + (PurchasesWs.Range("C" & OutCounter).Value * PurchasesWs.Range("D" & OutCounter).Value)
Else
IssuedCost = IssuedCost + (RemainingQty * PurchasesWs.Range("D" & OutCounter).Value)
Exit For
End If
End If
Next OutCounter
End If
FIFOCost = IssuedCost
End Function
Display More
What I could learn from the code is explained below:
There are two sheets, namely: Input-purchase and Output-FIFO consumption.
But on these sheets, the column headers are not the same as given on the reference sheet attached, which can be found here:
http://www.ozgrid.com/forum/showthread.php?t=150431
Secondly, the above code is only valuing the cost of units sold.
I am getting my head around it but couldn't do it. So I would be glad and thankful if an expert helps me in this.
What column headers should I have based on the above code ?
And how can we value the ending units in inventory ?
I would be thankful to you.
**************************************************************************************************************
[P.S. a simple FIFO example can be found here, which I tried using the code. The cost of goods sold is the same as calculated by the code above.
http://accountingexplained.com/financial/inventories/fifo-method
**************************************************************************************************************
Regards,
Shawn Michaels