Stock Valuation - FIFO Calculation in Excel | General Excel Questions & Answers

Stock Valuation - FIFO Calculation in Excel

biplab4444

New Member

Members

Forum Posts: 1

Member Since:
June 24, 2016

Offline

June 24, 2016 - 10:36 pm

Can any one help me to find out the closing stock valuation and profit under FIFO method. Please find the attached file.

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

June 28, 2016 - 11:04 pm

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

starshines

Member

Members

Forum Posts: 4

Member Since:
March 16, 2017

Offline

March 16, 2017 - 5:35 am

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

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

March 22, 2017 - 1:10 am

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

starshines

Member

Members

Forum Posts: 4

Member Since:
March 16, 2017

Offline

March 23, 2017 - 11:26 pm

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

Philip Treacy

Admin

Forum Posts: 111

Member Since:
October 5, 2010

Offline

March 24, 2017 - 10:47 am

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

starshines

Member

Members

Forum Posts: 4

Member Since:
March 16, 2017

Offline

March 25, 2017 - 6:45 am

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

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

March 25, 2017 - 2:14 pm

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.

starshines