# How to get average cost and running sub summary

Question asked by egbaumier on Jun 10, 2014
Latest reply on Jun 11, 2014 by egbaumier

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