Reverse SumProduct Criteria

[Michaels]

Hi all !

I am still stuck with returns on FIFO basis. This is my current sales table:

[TABLE="width: 475"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sell Date[/TD]
[TD]Product Name[/TD]
[TD]Qty Sold[/TD]
[TD]FIFO COGS[/TD]
[TD]Avg Tranch Out Cost[/TD]
[/TR]
[TR]
[TD="align: right"]09/03/2014[/TD]
[TD]Prod 1[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]1423.00[/TD]
[TD="align: right"]15.14[/TD]
[/TR]
[TR]
[TD="align: right"]20/03/2014[/TD]
[TD]Prod 1[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]1799.00[/TD]
[TD="align: right"]15.51[/TD]
[/TR]
[TR]
[TD="align: right"]29/03/2014[/TD]
[TD]Prod 1[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]1004.00[/TD]
[TD="align: right"]16.19[/TD]
[/TR]
[TR]
[TD="align: right"]29/03/2014[/TD]
[TD]Prod 1[/TD]
[TD]-272[/TD]
[TD="align: right"]-4226.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now I have tried SumProduct, Offset, and round many times, but couldn't come with the right formula. I tried it in Aggregate function but hard luck.

What I am looking for is, if the negative quantity is entered in Cell C4 (current value is -272), the formula in D5 (current value is -4226), it should do reverse sumproduct of Qty Sold and Avg Tranch Out Cost column matching the product.

If we enter -272, D5 should return -4226. How ?

( 62 x 16.19 ) + (116 x 15.51) + (94 x 15.14) = -4226

If we enter -150, it should return:

(62 x 16.19) + (88 x 15.51) = -2369 (rounded) based on product name in column B. So effectively, we are reversing FIFO cost.

I hope I have explained it correctly.

Thank you for your help.

Note: The Avg Tranche Out Cost is a helper column only.

 

[Michaels]

UDF would be fine too if formula is too complicated. Thanks

 

[GlennUK]

Hi Shawn,

have a look at this UDF and try it, and see if it does what you want:

Function reversesum(target_value, qty_range, COGS_range)
    reversesum = 0
    runningsum = -target_value
    For iloop = qty_range.Cells.Count To 1 Step -1
        amounttouse = Application.Min(qty_range.Cells(iloop).Value, runningsum)
        If amounttouse <= 0 Then Exit For
        reversesum = reversesum + amounttouse / qty_range.Cells(iloop).Value * COGS_range.Cells(iloop).Value
        runningsum = runningsum - amounttouse
    Next
    reversesum = Application.Round(-reversesum, 0)
    
End Function

Your cell entry would look like:
=reversesum(C5,C2:C4,D2:D4)

Cheers,
Glenn

 

[Michaels]

Thank youuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu Glenn....you are the best PERFECT mate

Thanks so much

May God Give You the desire of your heart and fulfill all your dreams.

What can we use with this UDF to make Product Name as criteria because in the Products (Column B), there will be many products and the UDF must calculate returns for that particular product which has been entered in B5.

Thanks

 

[Michaels]

Also, I noticed, keeping it in the same column as COGS (which is being calculated by another UDF) is not working. So I inserted another column, Column E for FIFO returns and tried to use ReverseSum but it returned 0.

So I think 2 small amendments are required.

1. Take the Column B into perspective for matching Product name
2. Modifying the code to make it work in new Column E (as it is not working in the same column as COGS (column D)). I think then it should be like:

=ReverseSum(C2,$C$2:C2,$D$2:D2) copied down

But it should not be including all the range when copied down, it should be -1 row, so $C$2:C2 -1 and $D$2:D2 -1

Thanks

 

[GlennUK]

So, like this then:

Function reversesum(prod_code, prod_list, target_value, qty_range, COGS_range)
    
    reversesum = 0
    If target_value > 0 Then Exit Function
    runningsum = -target_value
    For iloop = qty_range.Cells.Count To 1 Step -1
        If prod_list(iloop) = prod_code Then
            amounttouse = Application.Min(qty_range.Cells(iloop).Value, runningsum)
            If amounttouse <= 0 Then Exit For
            reversesum = reversesum + amounttouse / qty_range.Cells(iloop).Value * COGS_range.Cells(iloop).Value
            runningsum = runningsum - amounttouse
        End If
    Next
    reversesum = Application.Round(-reversesum, 0)
    
End Function

.... entered like:
=reversesum(B2,$B$1:B1,C2,$C$1:C1,$D$1:D1)

 

[Michaels]

Yes Glenn, this is perfect mate. Thank you so much.

So basically Product Code is one cell in Column B, where as Product List is expandable list, target value is the negative sales returns quantity, Qty Range is the expandable Qty Sold/(returned) column and COGS is expandable range from D2 onwards.

Beautiful Glenn, amazing...

Many many thanks

Bye for now

 

[GlennUK]

My pleasure.

Good luck with it!