Can any one help me to find out the closing stock valuation and profit under FIFO method. Please find the attached file.
Hi,
A formula approach is too complicated. You should use a UDF to calculate those 2 parameters. Try these 2 functions:
Option Explicit
Function FIFOStockValue(ByRef QtyAndCost As Range, ByVal UnitsInStock As Double) As Double
Dim i As Long, Cell As Range
For i = QtyAndCost.Rows.Count To 1 Step -1
If UnitsInStock <= QtyAndCost.Cells(i, 1) Then
FIFOStockValue = FIFOStockValue + UnitsInStock * QtyAndCost.Cells(i, 2): Exit Function
Else
FIFOStockValue = FIFOStockValue + QtyAndCost.Cells(i, 1) * QtyAndCost.Cells(i, 2)
UnitsInStock = UnitsInStock - QtyAndCost.Cells(i, 1)
End If
If UnitsInStock <= 0 Then Exit Function
Next
End Function
Function FIFOUnitsSoldCost(ByRef QtyAndCost As Range, ByVal UnitsSold As Double) As Double
Dim Cell As Range
For Each Cell In QtyAndCost.Columns(1).Cells
If UnitsSold <= Cell.Value Then
FIFOUnitsSoldCost = FIFOUnitsSoldCost + UnitsSold * Cell.Offset(0, 1): Exit Function
Else
FIFOUnitsSoldCost = FIFOUnitsSoldCost + Cell * Cell.Offset(0, 1)
UnitsSold = UnitsSold - Cell
End If
If UnitsSold <= 0 Then Exit Function
Next
End Function
You can see the functions in action in the attached version of your file.
Please test the functions carefully, hope you will not make us responsible for any eventual losses caused by errors in this example 🙂
Catalin
Hi Catalin,
Although the post is quite old but for the benefit of community and general public, I am replying to it.
I' m afraid you code for FIFOUnitCostSold is not correct and it gives wrong COGS. I have explained below first few lines for your consideration.
- If 15 units are sold, the first 10 units should go out @ 1200 and the remaining 5 from next layer @ 1500, which gives us the COGS of 19500.
- The 5 units sold should go out @ 1500, which should give us 7500 (your code gives 6000)
- The next 8 unit should have COGS of 12000
Can you kindly revisit your code and modify it ?
Thank you
Hi starshines,
Indeed, there was a problem, hopefully this version works better, but should be tested anyway, in all situations.
See the attached file.
Thank you for your feedback 🙂
Catalin
Hi Catalin,
Thank you for taking the time to fix the code.
But I'm afraid its still not correct; for first sale, its giving 22500 whereas it should give 19500 🙂
Also, the current code is row dependent, what if someone has separate purchase and sales table. The code is not dynamic for separate table and for more than one product.
So for example, using a separate purchase table approach, In A, we have dates, B has Product Name, C has Qty Purchased, D has Per Unit Cost and E has Total Cost.
Now we have Sales Table starting from G, where we have date, H has Product Name, I has Qty Sold, J has Sales Price, K has Total Sales, and L has COGS for our FIFO Sold Cost and M has Profit.
Thanks for your kind support and cooperation.
Regards
Hi starshines,
as you are pointing out the changes you think need to be made to the code, are you able to provide the code too?
regards
Phil
Hi Philip,
I'm afraid I am not a VBA coder, but there are many FIFO UDFs on the internet, which are not general in nature, they are very specific.
The code above from Catalin is very simple and very general, in other words, it doesn't require us to have specific sheets and data laid out in certain way. I just checked its not row-dependent, so I am testing Catalin's code hard and found mistakes which I pointed out.
What I said in my post 5 can be done with the current code, only the UDF is giving wrong figures, so I just pointed that out.
regards
Hi,
You still have to upload a sample file with your data structure, the code will not work on any data structure, might need important changes.
Hi Catalin,
I have uploaded the file now with the data structure.
Thanks
