4 Replies Latest reply on Jun 11, 2014 7:02 AM by egbaumier

# How to get average cost and running sub summary

I'd like to get some directions how to solve this issue:

There is source table with:

ProductID

Date IN

Quantity IN (Purchased)

Cost of Purchase

Date OUT

Quantity OUT (Sold)

There are 400 different ProductID's and 1100 registers that include Purchased and Sold items.

I need a Report to show these information by Month:

ProductID

DateIN

Quantity IN

Date OUT

Quantity OUT

Quantity Balance (Running)

Average Cost

The issue is how to handle Average Cost until Balance Qt is <> 0 for next register and update it when you have a new entry value when your balance is 0.

The table below just represent 01 item but there are 400 items in this source table that should be summarized.

 Date ProductID Qt IN Cost IN SubTotal Cost Qt OUT Cost OUT Balance Qt Average Cost 01/01 ABC 10 100.00 10x100=1000.00 10 1000/10 = 100.00 01/02 ABC 10 110.00 10x110=1100.00 10+10=20 1000.00+1100.00/20 =105.00 01/04 ABC 15 15*105.00 20-15=5 105.00 (REPEAT LAST VALID AVERAGE COST) 01/10 ABC 5 5*105.00 5-5=0 105.00 01/12 ABC 10 115.00 10x115=1150.00 10 1150/10 = 115.00 01/14 ABC 8 8*115.00 10-8=2 115.00

Regards,

Edison Garcia

FM13

• ###### 1. Re: How to get average cost and running sub summary

See if the attached file helps you.

• ###### 2. Re: How to get average cost and running sub summary

Thank you for suggestion.

There are some issues yet.

1. costOUT should be a calculation using last valid average cost for that Product.

2. If QtyRunning = 0, it means that all Qty from Purchase products are sold, Average Cost should be 0. Try to get OUT 8 pcs and you'll see that cAverageCost remain as last register.

But when you sell all parts Average Cost shpud be 0 until new Purchase (QTy and CostIN) and make a new calculation.

3. For a new Product (not ABC) Average Cost should be related just for this new product, for example try to add Product TTC.

When I add a new product such as TTC, Average Cost got from last valid average cost from Product ABC .

I tried to use some variable to get this Last Average Cost from Product but I failed.

Regards,

Edison

• ###### 3. Re: How to get average cost and running sub summary

Edison Garcia wrote:

There are some issues yet.

1. Well, then make it so; GetNthRecord - 1 etc., and remember that this all hinges on the sort order by date.

2. I didn't understand that – unless you were saying the same thing twice, which always confuses me.

Do you mean that if after a sale the running quantity is 0, the average price should be 0, too?

Then you only need to wrap the OUT part of cAverageCost in a Case(), too, and define as first check 'not sQuantity' (or 'sQuantity = 0', if you prefer); if so, return 0, otherwise the value from the last record.

3. If the different products don't impact each other (as they shouldn't), why not sort by product and date, and use running with restart (when sorted by product) for quantity and cost?

If you want to only sort by date AND still look at the most recent entry of a product (and use its summary values), you should probably use a self-join by product or some SQL magic instead of summary fields, because this could get really messi.

Edison Garcia wrote:

I tried to use some variable to get this Last Average Cost from Product but I failed.

That happens when one uses any old variable, instead of the right one …

• ###### 4. Re: How to get average cost and running sub summary

Because I'll import all registers from spreadsheet to FM with Invoices, Dates, Amounts, etc ... there is a last one information that I would like your help.

At SubSummary I can get Product, Running Qty and LAST Average Cost from all products. I did a new calculation field that works for all sorted products ... but for product that contain register #1 I couldn't get this information.

What is my missing point?

Edison