I'd like to get some directions how to solve this issue:
There is source table with:
Quantity IN (Purchased)
Cost of Purchase
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:
Quantity Balance (Running)
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/12||ABC||10||115.00||10x115=1150.00||10||1150/10 = 115.00|