VBA function to Calculate FIFO price

    Hello Experts,
    Wondering if you could help in coming up with an Excel function to determine the FIFO (First in First Out) price of stocks based on consumption.


    Stock ID (string) is uniquely identified for any transaction.


    For example:
    Sales:
    Stock Date Qty Selling Price Cost Price (Excel Custom Function to determine the Cost or VBA code to look for the cost?) Selling Price
    Stock 1 20-Nov-16 70 $ 110 ?? ----Answer based on FIFO: $70 $40
    Stock 1 21-Nov-16 40 $ 100 ??-----Answer based on FIFO: $70 $30


    Purchases:
    Stock PurchaseDate Qty Cost Price Remaining Stock
    Stock 1 01-Nov-16 120 $ 70 ??
    Stock 1 02-Nov-16 100 $ 80 ??


    Greatly, appreciate your help.


    Thanks,
    Pradeep

    Re: VBA function to Calculate FIFO price


    Hi Pradeep_atm


    Your answer will depend on the opening stock. T


    Take a look at this page, it has a couple of working examples of FIFO. Start at the bottom as it is a function and you should be able to manipulate it. If that is not appropriate use the macro. One will help as this is exactly what you are trying to solve. It is just a matter of manipulating the data to suit your needs.


    Hope this helps.



    FIFO Calculator



    Take care


    Smallman

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login