FIFO Calculation with multiple products

I am trying to calculate the cost of sales under First-in-first-out method. The cost could be calculated by excel, which is in column N in the "Results" file. However, I am not sure how I could use Alteryx to do the same thing.

Any help is much appreciated. Thanks in advance.

Cheers,

Austin

Input file.xlsx

Total Cost of Sales.yxmd

Results.xlsx

Transformation

Accepted answers

reply_mueller

Hey @AustinLeung , hey @grossal ,

I really liked your problem, since it is quite tricky and intriguing. At a first glance, I thought I could solve this using only standard tools, like @grossal tried.

I actually succeeded in creating a solution for your example data, but shortly before posting I came to realize that this won't work for more complex scenarios.

Therefore I introduced (nearly) the same code in an iterative macro which, for each iteration takes one sale and collects its respective buys.

After calculating the amount of sold units, the remaining stock of the buys is updated and the sale is removed from the list.

The tool box in the lower part of the macro serves mostly to control the macros flow.

I hope this could solve your problem.

Best,

Johannes

(Blue Reply)

fifo_sales_price.yxzp

DavidP

As @reply_mueller said, this was a really interesting problem, possibly even one for @JoeS to have a look at for a weekly challenge! Ever since you first posted it, I've been mulling it over, scribbling notes on random pieces of paper, mumbling away to myself, etc.

As Johannes also realized, I knew that an iterative macro would solve it, but I was obsessed with finding another way.

I finally settled on the following idea. Convert all the buy events and sell event to rows with a quantity of 1. Then the FIFO principle becomes easier to implement.

My solution attached below

fifo with generate rows.yxmd

All comments

grossal

Can you double check the formula in your excel file? I see a basic 'IF' formula in the N-Column and you already showed your ability to do IF in your workflow.

danilang

Hi @AustinLeung

If you group by SNAME in your multi row formulas, it has the same effect as if you treated each product group separately. You can then remove the IF [SNAME] = [Row-1:SNAME] clauses from your formulas

Dan

DavidP

@AustinLeung, I replicated most of your Excel logic, except for the last column - I'm still trying to understand the formula.

Mirroring exactly what you did in Excel is probably not the most efficient way of doing it in Alteryx, but I'm just trying to understand the logic first.

As @danilang mentioned, you can use the grouping functionality (Group by Stock Name) to good effect in some of your multirow formulas.

The Running Total tool is also one that might be useful in your case.

And I always love an opportunity to use the tile tool.

It would really help if you could put into words the logic of the formula in the last column.

I attach the workflow with my progress to date.

fifo.yxmd

AustinLeung

@DavidP and @Danilang, thanks a lot!

The logic of the last formula may not seem intuitive. I would definitely love to see other approach that would be more straightforward to get the results.

I will try to divide the formula into different parts. You may also use the "Evaluate Formula" Function in excel to simplify the formulas.

Let's take the formula in cell M4 as an example:

=SUMPRODUCT(--(-SUM($L$3:L4)>I$3:I4),-SUM($L$3:L4)-$I$3:I4,K$3:K4)-SUM($M$3:M3)

1st part: --(-SUM($L$3:L4)>I$3:I4):

= If sum(L3:L4) > I3, then = 1, else 0

= If sum(L3:L4) >L4, then = 1, else 0

The results of the first part would be {1;0}

2nd part: -SUM($L$3:L4)-$I$3:I4,K$3:K4

= {-SUM(L3:L4) - I3 ; SUM(L3:L4) - I4}, {K3;K4}

= {100-0;100-100}, {K3:K4},

= {100,0},{K3:K4}

= {100,0}, {8,0}

3rd part: -SUM($M$3:M3)

= 0

So, adding all the parts will be equal to SUMPRODUCT( {1,0}, {100:0), {8:0} ) - 0

=( 1*100*8 + 0*0*0 ) - 0

= 800

Using this logic, the formula in cell M9 =SUMPRODUCT(--(-SUM($L$6:L9)>I$6:I9),-SUM($L$6:L9)-$I$6:I9,K$6:K9)-SUM($M$6:M8)

= SUMPRODUCT ( {1;1;1;0} , {150;50;50;0}, {150;0;10;0} ) - SUM(M6:M8)

= ( 1*150*150 + 1*50*0 + 1*50*10 + 0*0*0 ) - 15000

= 8000

Hope that is clearer. The current logic of calculating FIFO is to calculate the total cost of sales starting from the beginning by SUMPRODUCT(--(-SUM($L$3:L4)>I$3:I4),-SUM($L$3:L4)-$I$3:I4,K$3:K4) and then subtract the previous cost of sales with - SUM($M$3:M3)

reply_mueller

Hey @AustinLeung , hey @grossal ,

I really liked your problem, since it is quite tricky and intriguing. At a first glance, I thought I could solve this using only standard tools, like @grossal tried.

I actually succeeded in creating a solution for your example data, but shortly before posting I came to realize that this won't work for more complex scenarios.

Therefore I introduced (nearly) the same code in an iterative macro which, for each iteration takes one sale and collects its respective buys.

After calculating the amount of sold units, the remaining stock of the buys is updated and the sale is removed from the list.

The tool box in the lower part of the macro serves mostly to control the macros flow.

I hope this could solve your problem.

Best,

Johannes

(Blue Reply)

fifo_sales_price.yxzp

AustinLeung

Hi Muller,

I have been trying to use @grossal approach but also find that the logic does not work for more complex scenarios.

I am still trying to understand your work but I have tested a few samples and that works!

I will try to apply the model to my actual data and see if all is fine. Thank you very much for your help!

jvalcareng002

@reply_mueller - I've been searching these discussions for a solution to my FIFO problem, and your solution here seems to be the closest I could find to my case.

I have been trying to find a way to solve this without using an iterative macro since that is above my current experience, but since I am working with thousands of lines of data I think it will be the only way to solve. I was hoping you (or someone in this feed) would be able to advise.

I have two tables with the following data a) Orders Created and b) Orders Received:

company_name_original	Order Created Date	Orders
Customer1	12/11/2020	10
Customer10	11/6/2020	4
Customer10	12/8/2020	6
Customer10	12/10/2020	4
Customer10	12/11/2020	50
Customer10	12/14/2020	25
Customer10	12/23/2020	35
Customer10	1/14/2021	5
Customer100	12/9/2020	5

company_name_original	Order Received Date	Orders Received
Customer1	12/28/2020	1
Customer10	12/8/2020	4
Customer10	12/11/2020	1
Customer10	12/12/2020	4
Customer10	12/24/2020	1
Customer10	1/11/2021	1
Customer10	1/13/2021	1
Customer100	12/14/2020	2
Customer100	12/15/2020	3

I would like the result to look something like the table below, where orders received is matched to orders created on a first in first out basis. That is orders received are shown to come from the first order placed up until the total # order amount. Once the initial order amount is exceeded, the orders received come from the next order created, and so on and so forth. For example, if Customer 1 orders 10 units on Jan-1 and 10 units on Jan-2, then when they receive 15 units on Jan-3, 10 of those units will be from the order created Jan-1 and 5 will be from the order created Jan-2. The remaining orders received are then attributed accordingly up until the total order amount for each order created.

Result:

company_name_original	Order Received Date	Orders Received	Order Created Date
Customer1	12/28/2020	1	12/11/2020
Customer10	12/8/2020	4	11/6/2020
Customer10	12/11/2020	1	12/8/2020
Customer10	12/12/2020	4	12/8/2020
Customer10	12/24/2020	1	12/8/2020
Customer10	1/11/2021	1	12/10/2020
Customer10	1/13/2021	1	12/10/2020
Customer100	12/14/2020	2	12/9/2020
Customer100	12/15/2020	3	12/9/2020

Due to the large data set, I have looked into grouping the orders by week created / week received rather than exact date. I have attached the sample data below and would appreciate any help you can offer. My biggest issue has been splitting the amount of the order received between several created orders when the amount received exceeds the total amount available in the previous order (i.e. as explained in the example above).

Thanks,

Juliana

Sample Data.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703