Looking for a FIFO (First in first out) formula that works with Number for Mac. Found examples for Excel in the web. However, they don't translate to Numbers. Simplified example:
Date - Quantity - Price - Inventory event (sell or purchase)
1/1 - 10 - $1 (purchase)
2/1 - 15 - $2 (purchase)
3/1 - 25 - $3 (purchase)
4/1 - 05 - $4 (sell)
5/1 - 15 - $5 (sell)
7/2 - 20 - $6 (purchase)
8/3 - 40 - $7 (sell)
This simple table shows 70 items purchased and 60 sold. I need help generating a formula to calculate the gain in the FIFO method. First sell on 4/1 is simple: $4 x 5/10 units purchased for $1 = (4-1)*5=$15. Second sell on 5/1 is already a bit more complex: (5-1)*5 + (5-2)*10 = $50. Any additional sell event is getting more and more complex for a Numbers formula.
My csv database has over 500 entries, the inventory events have decimals and their schedule is more complex than in this example.
Any help with a numbers formula? Or is there another simple tool for a Mac?
Date - Quantity - Price - Inventory event (sell or purchase)
1/1 - 10 - $1 (purchase)
2/1 - 15 - $2 (purchase)
3/1 - 25 - $3 (purchase)
4/1 - 05 - $4 (sell)
5/1 - 15 - $5 (sell)
7/2 - 20 - $6 (purchase)
8/3 - 40 - $7 (sell)
This simple table shows 70 items purchased and 60 sold. I need help generating a formula to calculate the gain in the FIFO method. First sell on 4/1 is simple: $4 x 5/10 units purchased for $1 = (4-1)*5=$15. Second sell on 5/1 is already a bit more complex: (5-1)*5 + (5-2)*10 = $50. Any additional sell event is getting more and more complex for a Numbers formula.
My csv database has over 500 entries, the inventory events have decimals and their schedule is more complex than in this example.
Any help with a numbers formula? Or is there another simple tool for a Mac?