Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculate remaining quantity using First in First Out basis

AkisM
10 - Fireball

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.

 

RecordIDNameTypeQuantityPrice per unitRemaining Quantity after FIFOPrice per unit taken from FIFO transaction
1ABCPurchase3000010.1  
2ABCPurchase2250010.3  
3ABCClosing balance52500   
4ABCSale1750010.5010.1
5ABCPurchase500011  
6ABCSale150009.52000010.3
7ABCClosing balance25000   
8ABCSale210009.4400011
9ABCClosing balance4000   

 

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

 

 

7 REPLIES 7
joshuaburkhow
ACE Emeritus
ACE Emeritus

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-...

 

 

 

Joshua Burkhow - Alteryx Ace | Global Alteryx Architect @PwC | Blogger @ AlterTricks
atcodedog05
22 - Nova
22 - Nova

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
10 - Fireball

@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
17 - Castor
17 - Castor

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.

 

DavidP_0-1602152948572.png

 

AkisM
10 - Fireball

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
17 - Castor
17 - Castor

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.

 

 

DavidP_0-1602192219835.png

 

 

AkisM
10 - Fireball

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!

Labels