FIFO

  • Thread starter Thread starter Nav
  • Start date Start date
N

Nav

What is the best way to get the average cost on a FIFO
basis when I only have 1 product.

EG.

A 5 @ 1.00
A 5 @ 1.10
A -3 @ 1.30
A 2 @ 1.30


Is it even possible to do using a function?


Thanks in advance for your help.
 
Unfortunately, I cannot use just average Cost, it has to
be average FIFO methodology.

If anyone has any ideas it would be appreciated.


Thanks
 
Nav,

I would be interested in looking at your spreadsheet and seeing if we can
find a solution.

If interested, you can send your spreadsheet to my lastname at ya and hoo
(combined) and you know the rest. Please come back here and leave a message
so I know to check for your file.

I am not making any promises, but I am curious to see if we can solve your
FIFO challenge.

Regards,
Kevin
 
Nav said:
What is the best way to get the average cost on a FIFO
basis when I only have 1 product.

EG.

A 5 @ 1.00
A 5 @ 1.10
A -3 @ 1.30
A 2 @ 1.30

Is it even possible to do using a function?
Click to expand...

Using *one* formula for each sale (presumably the records with negative
numbers to the left of the '@'), I don't believe it's possible unless you
resort to user-defined functions. If you're willing to use 3 additional
columns for each record, it can be done with formulas.

I simulated a bit more data, namely, in A1:C20

A 5 1.0
A 5 1.1
A -3 1.3
A 2 1.3
A -2 1.4
A 2 1.2
A 4 1.3
A -3 1.6
A 4 1.4
A 2 1.4
A -2 1.6
A 1 1.2
A 3 1.7
A 3 1.2
A 1 1.4
A -5 1.3
A -4 1.8
A 3 1.8
A -3 1.9
A 5 1.4

I'm assuming column A contains product IDs. I'm ignoring this. Far more
difficult if this needs to be done for each product all at once. As long as
the records are sorted by product ID, my approach would work for the records
for a single product. I'm also assuming the second column is quantities
(which I'll name Q) where +s mean purchases and -s mean sales, and the third
column is prices per unit (which I'll name P). Finally I'll assume the first
record is always a purchase (2nd column +); otherwise, you tell me how to
figure the basis.

Enter the following formulas.

D1:
1

E1:
=INDEX(Q,1)

D2: [array formula]
=MATCH(TRUE,MMULT(--(ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q)
+SUMIF(OFFSET(Q,0,0,ROW()-1,1),"<0")>0,0)

E2:
=IF(D2=D1,E1+MIN(0,B1),SUMIF(OFFSET(Q,0,0,D2,1),">0")
+SUMIF(OFFSET(Q,0,0,ROW()-1,1),"<0"))

F2:
=IF(B2<0,IF(E2+B2>=0,-B2*INDEX(P,D2),
E2*INDEX(P,D2)+(INDEX(Q,D3)-E3)*INDEX(P,D3)
+IF(D3-D2>1,SUMPRODUCT(--(OFFSET(Q,D2,0,D3-D2-1,1)>0),
OFFSET(Q,D2,0,D3-D2-1,1),OFFSET(P,D2,0,D3-D2-1,1)),0)),"")

Select D2:F2 and fill down into D3:F20. The visible results in column F are
the FIFO cost bases for the sales records.
 
Back
Top