FIFO Calculation with multiple products

[AustinLeung]

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

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

[AustinLeung]

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

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

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

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

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]

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.