Hi,
Stock status report works fine. How are you calculating previous day's average
cost?
To use E9 BAQ, it would be tough. But you can start with PartWhse (which
includes aggregated on hand qty and average material cost by warehouse). Left
join parttran table. Filter the parttran table to only include transactions
after the cut-off date. To decide which trantypes to use, if they change
onhandqty or the avgerage material cost in the partwhse table, you will need to
include this trantype and reverse the transaction. Below is example of Pur-Stk:
If you average cost .90 and you receive a PO at $1.00 – the transaction is for $1.00 – although the average cost has been re-calculated.
Let Current Average Cost $.90
Let Current On Hand Qty: 100
Current Total Material Cost ($.90 x 100) = $90
Let cut-off date be December 31, 2014
Let current date be January 31, 2015
Transaction: (from parttran):
Let Transaction Type = Pur-Stk
Let transaction date be 1/1/2015
Purchased 50 qty at $1.00.
Assuming that there is no other transaction in the Parttran for this part, 50 qty is part of current on hand qty. Current Average Cost includes cost of this purchase. To calculate the average qty and average cost at end of December 31, 2014, this transaction must be removed from the Total Material Cost and Current On Hand Qty. Average cost will be calculated as Total Material Cost (Dec 31, 2014) Divided by Total On Hand Qty (as December 31 2014).
To calculate average cost, first calculate On Hand qty at December 31, 2014 and calculate Total Material Cost as December 31, 2014. Then divide Total Material Cost over Total On Hand Qty.
Total Material Cost as of December 31, 2014:
Current Material Cost – [purchase unit cost x purchased qty] =
$90 – [$1.00 x 50] = $90 - $50 = $40
Total Qty on Hand as of December 31, 2014:
Current On Hand Qty – Purchase Qty =
100 – 50 = 50
Average Material Cost as of December 31, 2014:
$40/50 = $.80/Unit
