Valuing Closing Stock using FIFO method of Accounting

{ 24 Comments }

Assume a stock purchase sheet which details the daily stock purchase of various Raw material at various per unit prices.  On another sheet, the closing stock of each Raw material type is entered.  In issuing Raw Material, one may want to follow the First In First Out (FIFO) method accounting which assumes the Raw material issued is from the oldest stock lying in inventory i.e. the inventory left over will be from the recent stock purchased.

The objective is to determine the value of the closing stock of Raw material on FIFO basis.

You may refer to my solution in the attached workbook.

Leave a Comment Cancel reply

Your email address will not be published.

*

  • how to use the Array formula in your sheet ?? and what array formula refers to exactly ???

    Regards
    C.A. Mayank

    • Hi,

      Array formulas are confirmed with Ctrl+Shift+Enter. So after you type the formula in the cell, press Ctrl+Shift+Enter. When you do so, you will see curly brackets surrounding your formula bar.

  • I want to use your sheet.

    I am having number of item to valued. but your sheet is fixed for 20 items only.

    Kindly help me. Do you have any YouTube link ???

    MANOJ CHAUDHARI

    • Hi,

      I do not have a YouTube link. Upload your file on SkyDrive and share the link of the uploaded file here. In the file which you share, please only share the information which I need to solve the problem.

    • Hi,

      You may refer to my solution in this workbook. Please note that since there is a Data Table running in range D3:D580 of “Stock Valuation” worksheet, the file is sluggish and recalculates when the slightest change is made anywhere in the workbook. To verify the figures in range D3:D580 of “Stock Valuation” worksheet, I suggest you create a copy of this file, close the original file and in the newly created file, perform the following on the “Stock Valuation” worksheet:

      1. Copy range C2:D580
      2. Right click > Paste Special > Values

      Since there will be no Data Table formulas now, workbook performance will boost. Now randomly check the Stock valuation.

      For the solution on this worksheet to work, you must always ensure that data on the Stock Purchase worksheet is sorted in the following order:

      1. First by codes (column A) in ascending order; and
      2. Next by Date in descending order

      Please check the result thoroughly and revert for any doubts/clarifications.

      Hope this helps.

  • Thank you very much for File my work will be easier after this… Still i am eager to know about logic you applied to this fill. If it is possible please explain same…

  • Dear Sir,

    I used your excel sheet. My number of transaction are heavy. Therefore system takes lot of time for calcuklation. Is any simple way to speed up the process?

    Regards
    Dineshs

    • Hi,

      The Data > Table is causing a slowdown of the workbook’s performance. After you have got all the correct results, just select the Data > Table range, copy it > right click > Paste Special > Values.

      Hope this helps.

  • Hi Ashish,

    I am getting some error in valuation when I use this file.

    Kindly let me know how to share it with you.

    Regards,

    Milind

    • Hi,

      Upload the workbook to OneDrive/Google Docs or a similar service and share the link of the workbook here. Please point out the mistake clearly and also share the correct result.

  • Hello,
    I want to create FIFO Inventory Template in excel which issue sale quantity from first purchase with the rate of same purchase. could you please advice ?

    Appreciate the help.

  • Dear Sir,

    Your workbook related to calculation of stock valuation under FIFO method was excellent but your workbook has on 580 Rows while i have large number of rows how i can insert the rows,

    please explain.

    • Hi,

      Thank you for your kind words. Where did you get the figure 580 from? In the workbook which I have shared in the original Blog article, there is no 580 row limit. You just need to keep adding rows in the Stock Purchase worksheet and the formulas in the Stock valuation worksheet should recompute. However, please note that since there are lots of formula/array formulas used in this workbook, processing will adversely impact the performance of the workbook.

  • Thanks a lot sir,

    But I have did same thing as you have suggested above still stock valuation sheet working at limited rows & when its cross 580 Rows formula does not working & error showing that ” can not change a part of data table” sir please help in this matter

  • Thanks sir,

    now its working as i have open it through – Go to data table give the ref of Cell F1 in column

  • hi refer to attach work book, I want to know stock which is lying on FIFO basis from whom it was purchased. Let say , I have 1000 units of “A” and have been valued on FIFO basis in this 200 was purchased X vendor , 500 from Y vendor and remaining 300 form Z vendor . So can I get it from my purchase register.

    its Urgent thanks in advanc

  • Hi,

    I am using your sheet, as I Click on Cell D2 “Stock Valuation sheet” & Enter. The result got altered. Please solve the issue.