AnsweredAssumed Answered

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.

 

 

DateProductIDQt IN
Cost IN
SubTotal Cost
Qt OUT
Cost OUTBalance QtAverage Cost
01/01
ABC
10
100.0010x100=1000.00

101000/10 = 100.00
01/02ABC
10
110.0010x110=1100.00

10+10=201000.00+1100.00/20 =105.00
01/04 ABC



15
15*105.0020-15=5105.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

Outcomes