Become a MacRumors Supporter for $25/year with no ads, private forums, and more!

Numbers for Mac: FIFO calculation?

NO INDEX

dirk29

macrumors newbie
Original poster
Apr 24, 2018
1
0
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?
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.