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

FIFO Calculation with multiple products

AustinLeung
7 - Meteor

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

 

36 REPLIES 36
grossal
15 - Aurora
15 - Aurora

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
19 - Altair
19 - Altair

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

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

 

DavidP_0-1581804476833.png

 

AustinLeung
7 - Meteor

@danilang @DavidP, 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)

AustinLeung
7 - Meteor

@danilang @DavidP, 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)

AustinLeung
7 - Meteor

@danilang @DavidP, 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)

AustinLeung
7 - Meteor

@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)

AustinLeung
7 - Meteor

Danilang and DavidP, 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) >I4, 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 the same 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

 

The logic is to calculate the cumulative total cost of sales starting from the beginning by SUMPRODUCT(--(-SUM($L$6:L9)>I$6:I9),-SUM($L$6:L9)-$I$6:I9,K$6:K9)  and then subtract the previous cost of sales up to last transaction with - SUM($M$6:M8). So, the remaining balance would be the cost of sales for that particular transaction. Hope that is clearer. 

AustinLeung
7 - Meteor

Danilang and DavidP, 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) >I4, 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 the same 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

 

The logic is to calculate the cumulative total cost of sales starting from the beginning by SUMPRODUCT(--(-SUM($L$6:L9)>I$6:I9),-SUM($L$6:L9)-$I$6:I9,K$6:K9)  and then subtract the previous cost of sales up to last transaction with - SUM($M$6:M8). So, the remaining balance would be the cost of sales for that particular transaction. Hope that is clearer. 

Labels