Query on Excel Formula -FIFO Accounting

**sabya** · 06-14-2011, 09:12 AM

Hi ,

I am facing a serious problem while computing the working capital blocked due my excess purchase items , or due to late realisation .

I am looking for a formula which I can use to identify my multi-location and multi-product 1) stock outflow on FIFO basis and 2)date of purchase of the sold materials ,

in other words ,

1)the date difference ( time period ) between item purchased and sold (realisation ) .and
2) also the item unsold at the end of the period ( closing stock) .

Have attached an excel sheet to understand the exact problem

This is a real life issue and would be grateful if you help me .

regards

**incjourn** · 06-14-2011, 10:13 AM

You could use an array formula.

Paste the following into cell K11
=MAX(IF(C11=$C$4:$C11,IF($F$4:F11>0,ROW($F$4:$F11),0),0))
Now highlight the formula in the formula bar and press ctrl+shift+enter.
This is now an array formula (it has curly brackets around it)
This gives the last date when stock was purchased for that item.

The date difference will just be the one data minus the other, format as number to get days.
Now in cell I11 put the following
=INDEX($D$1:$D11,K11)
And format as date.

The match formula will give you the first date for each item and then the
=SUM(Offset(a,b,c,d))
will enable you to sum a particular range if you know the start and end.
So you would end up with something like this.
=SUM(OFFSET(E3,MATCH(C11,$C$4:C11,FALSE),0,4,3))

**shg** · 06-14-2011, 10:50 AM

Perhaps you can adapt the last worksheet in http://www.box.net/shared/7xm2kt408n.

The production and sold quantities are random; push F9 to see different numbers.