I need to be able to compute the average cost of a certain item, not sure how to properly design the table as there is a need to subtract from the quantity of the item in First In, First Out order. Here's an example:

Quantity Cost Total
(a) 1000 100 100,000
(b) 500 90 45,000

The first row indicates the beginning balance of the item, and the next one is the next purchase. If we'll sum the quantity then well have 1,500 and the sum for the total is 145,000, and to get the Average Cost we'll do Total/Quantity which would be 145,000/1500 which will yield 96.66 as the Average Cost. Now, for every sales it must be first subtracted from (a) until it gets 0, then proceed to (b), and so on. I could add another column which would indicate the total deductions from the quantity per row but it does not seem to be the best design as I would need to check if it is already 0 before proceeding to the next row (b).

Can anyone recommend a more efficient design such that I could subtract by row (until it gets zero)?

TIA