Return on FIFO Basis

[Michaels]

Hi all !


I want to create Sales Returns functionality (UDF) for FIFO. When I enter Sales Returns, the sales quantity must be negative indicating returns on Sales sheet in quantity sold column. Now, these returns must be valued (cost added back to inventory) on the basis of same principles, at which they went out of inventory (so in other words, cost out cost in). So it has to reverse the cost out, implying that the actual sale has not taken place in the first place.

The current sheet set up is as follows:

Purchases:
A: Date of Purchase
B: Product
C: Quantity Purchased
D: Cost

Sales:
A: Date of Sale
B: Product
C: Quantity Sold
D: FIFO Cost


Example:
Date Product Quantity Cost
[TABLE="width: 353"]
<tbody>[TR]
[TD="align: right"]01/03/2014[/TD]
[TD]Test-01[/TD]
[TD] 68.00[/TD]
[TD] 15.00[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/03/2014[/TD]
[TD]Test-01[/TD]
[TD] 140.00[/TD]
[TD] 15.50[/TD]
[/TR]
[TR]
[TD="align: right"]11/03/2014[/TD]
[TD]Test-01[/TD]
[TD] 40.00[/TD]
[TD] 16.00[/TD]
[/TR]
[TR]
[TD="align: right"]16/03/2014[/TD]
[TD]Test-01[/TD]
[TD] 78.00[/TD]
[TD] 16.50[/TD]
[/TR]
</tbody>[/TABLE]

Qty Purchase: 326 units
Qty Sold: 272 units
Total Cost of Purchases: $5,117
FIFO Cost: $4,226

Now if -272 is entered in Quantity sold column on Sales sheet, the FIFO cost should be 0.



Thank you for your help.

Regards

 

[mole999]

devils advocate, could you not have a returns column on the sales line, thereby you have all the associated costs already on the line, you could show a percentage of sales that were returned and be ale to maintain a running total, sort of like double entry book keeping

 

[Michaels]

devils advocate, could you not have a returns column on the sales line, thereby you have all the associated costs already on the line, you could show a percentage of sales that were returned and be ale to maintain a running total, sort of like double entry book keeping

Nope. A Reverse-FIFO is required in this case. I have asked this question (new thread) from another perspective by adding a helper column and doing the reverse sumproduct with formula if possible.