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

[joshuaburkhow]

Hey @AkisM 

There are quite a few posts around the community on this topic. I would take a look at these: 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/First-in-First-Out-Matching/td-p/34508...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Creating-a-first-in-first-out-calculat...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/FIFO-Calculation-with-multiple-product...

I noticed that you posted an almost similar question here and got an answer - did that not help you? 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/First-In-First-Out-remaining-quantity-...

[atcodedog05]

Hi @AkisM 

This does seem like a job for Iterative macro. I tried a bit. But the logic for the current is quite complex to implement on tabular format data. Considering row-1 row-2 will not work for yours. Need to see 

[AkisM]

@joshuaburkhow I went through the posts but sadly could not find a case that matches mine closely so I can replicate the logic.

In my previous post with a similar example, @DavidP did provide a great solution that worked with the sample I provided just fine, but after implementing it and going through my data a few times, I noticed that there are more complex cases in my data that I did not include in my first example, and the workflow doesn't calculate those correctly. Hence me posting another thread with a more accurate representation of my data.

The difference is that, rather than calculating remaining quantity after accounting for every single sale, the remaining quantity needs to be calculated per period and stored per period. And sometimes the previous periods will need to be referenced. This complicates the workflow a bit to the point that I couldn't pick up where I left off with the previous solution.

[DavidP · Accepted solution]

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.

[AkisM]

Hi @DavidP , thanks for chiming in again. I checked out the attached workflow, one issue:

-The final output should have the same number of rows as the input, just enriched with the data of columns "remaining quantity after FIFO" and "Price per unit from FIFO transaction". In other awords, row pairs 6,7 and 9,10 are duplicate, and only 1 from each should be kept. I understand you kept both price points, but those are needed only if we're calculating cost of disposal. Which as you mentioned, is already handled nicely by the previous beautiful solution you posted on my previous thread. Here we're not really interested in cost of disposal, just the cost of the oldest stock still remaining in place, at each sale point. I would then use these two amounts (oldest stock still in books + its original price) to calculate the book value of the stock still in inventory after FIFO, at each closing balance (which is my final goal in this exercise).

I'm attaching an excel as you requested to try to make it a bit clearer.

[DavidP]

Hi @AkisM 

I've tweaked the logic in the generate rows for purchases and then used a sample tool to extract the correct values. It works for everything except the 1st remaining quantity after FIFO value in row 4 - the reason being that the logic for this row is different from the rest as it also takes a future sale into account.

Perhaps you can test this logic out with a bigger dataset. If you want to do the same here as we did last time to speed up the workflow for a large dataset, we'll have to do it differently. My suggestion in that case would be to divide all your quantities by 10 or 100 right at the start and multiply the end result by the same factor. Hope this makes sense.

[AkisM]

Thanks for the assistance @DavidP . The rule does not change, it's just that we haven't been able to find a rule that correctly expresses it in alteryx. But it's ok, I came up with a different solution. Since my end goal is just the book value of the remaining inventory, I don't need to actually have remaining quantities next to inventory. I realized that the first workflow you provided which calculates Cost of Disposal is enough for the figure I'm trying to get at.

I will just add the value of the purchases for each period and deduct from it the value of the cost of disposal from each period and the result is the same as if we had remaining quantities from each batch with their original price next to them.

Thanks!