Formula/macro for calculation using First in First Out way i.e closing stock valuation using FIFO

[paradise]

Dear Sir,

Pls find enclosed in below attachment excel file,I want the exact result of column G which is highlighted and has shown calculation manually.This column G is based in column F which is a closing qty as I have shown formula there.Column G value has been calculated by taking the rate from column D using FIFO method which means closing stock qty valuation using First in First Out.This is related with accounts and is very easy which you will understand very easily in the respective spreadsheet.

If you require any further info,pls do let me know.This is a sample data as I do have a large data which is not possible to do in that case by manual calculation,hence would like to automate my calculation.

I haven't found any satisfaction result in the web,perhaps this forum might solve this case.

With Best Rgds.
suresh

 

[Hui]

Your cell in G3: =F3*D3
is a LIFO not FIFOas there is still 10 units in the previous row available

FIFO should be equal to =(10*D2)+(10*D3) = 1150

You can use the system I show at: http://chandoo.org/wp/2010/11/18/scheduling-variable-sources/
to do FIFO calcs

 

[r1c1]

May be I am wrong, but

FIFO should be equal to =(10*D2)+(10*D3) = 1150

seems incorrect. As of G3, we had IN 30, Out 20, In 20. So the next OUT will (Out 10) will go from Initial balance.

@Suresh

Please find the attached VBA solution for your problem. It uses named ranges lstIn, lstOut, lstCost to calculate the FIFO value of closing stock. Use the clear button to remove calculated numbers.

 

[paradise]

Hi@r1c1,
Perhaps,you understood what I mean to say,I think I am not wrong what @Hui pointed out regarding G3 is LIFO.It is FIFO which you explained in your comment.You have did the excellent work but you have taken a limited rows name range as in actual data might be large,can you kindly do in the manner as I have enclosed another workbook format which is self explanatory for you now and vba code/macro that contains all the cases FIFO,LIFO and Average Cost.Can you modify accordingly the code in all the cases in the respective format as enclosed herewith.You have to determine the rate in Helper Column I in FIFO Sheet.Similarly,when I copy the same sheet and use macro for LIFO and Average only Helper column rate will be changed accordingly selecting respective macro.

Since in the enclosed in attachment another workbook in which 'manual working' sheet explains in more comprehensive manner.With another method now the column I is required data in 'FIFO' sheet which now will be the helper column and with this helper column rest calculation will be done by a simple formula in subsequent column similar to yours.This column I is how calculated is done in 'manual working' sheet in Column H.

I have enclosed a macro i.e vba code which I found at >> http://www.mrexcel.com/forum/excel-q...rage-cost.html

But in that website example under FIFO,it works .But when I use that code in my data which is slightly different it does not work.As it has considered to some rows only.

If you helps me to modify the code taking the assumption that there may or may not be data in 'IN' AND 'OUT' Column as you did.The explanation in the enclosed sheet is of FIFO only but accordingly kindly modify code for the LIFO and Averge as well as I have not mentioned in it.It would be a great sense of gratitude and appreciation if you help me in this.

With Best Rgds,
Suresh