Calculate remaining quantity using First in First Out basis

AkisM

Hi, I need a bit of help on finding remaining quantity using the first in first out basis, per period (each period there is a closing balance). Example below

The First 5 columns is the original data, last 2 columns should be the calculated data by the workflow.

RecordID	Name	Type	Quantity	Price per unit	Remaining Quantity after FIFO	Price per unit taken from FIFO transaction
1	ABC	Purchase	30000	10.1
2	ABC	Purchase	22500	10.3
3	ABC	Closing balance	52500
4	ABC	Sale	17500	10.5	0	10.1
5	ABC	Purchase	5000	11
6	ABC	Sale	15000	9.5	20000	10.3
7	ABC	Closing balance	25000
8	ABC	Sale	21000	9.4	4000	11
9	ABC	Closing balance	4000

Explanation of the above:

Each time there is a sale, the first unit that was bought must be disposed of first, before moving on to the next ones. Hence First In First Out

First period closes with no sales. So remaining quantity after FIFO remains Null. Same for Price per unit taken from FIFO transaction. That column will be used to associate remaining quantity with each original price.

In the 2nd period, the first sale of 17500 will be taken out of the 30000 (first purchase). But there is also another sale of 15000, which takes out the remaining 12500 from that transaction and brings it to 0. So Row F4 must be 0 and the Price per unit taken from FIFO transaction should be 10.1, as that's the original price per unit of the batch of stock that is now 0 in quantity.

Now on the second sale, there is still 2500 units still to be accounted for (first 12500 were set off against the first purchase). So these 2500 are set off against the very next purchase, 22500-2500=20000. This amount of 20000 is the amount that must go next to this sale, row F6. And its associated original price, 10.3

The last sale of 21000 is set off against the very next remaining units that were purchased first. So they're set off against the remaining 20000 from the original purchase of row 2, and that transaction is now also left with 0. But there is still 1000 units to be accounted for. Which will be set off against the very next purchase, in row 5. 5000-1000=4000 and this is the amount that goes next to this sale, row F8, and its associated price of 11.

I hope the above sufficiently explains what I'm trying to do. Thanks in advance for your assistance

DavidP

Hi @AkisM 

I think the FIFO method we used before can still work here, but I'm having a little trouble figuring out the logic for the Remaining Quantity after FIFO column.

Here is the adapted FIFO workflow based on your new dataset.

You can see in the highlighted rows that the sales for RecordID's 6 and 8 are split over 2 unit prices each. For the 15000 sale, 12500 have a unit price of 10.1 and 2500 have a unit price of 10.3. Similarly for the 21000 sale, 20000 have a unit price of 10.3 and 1000 have a unit price of 11.

Perhaps the best way to show me how the  Remaining Quantity after FIFO column is calculated is to copy the results of this workflow into Excel and create an Excel formula for Rows 4, 6 and 8, then send me back the Excel file.

FIFO over multiple periods.yxmd