We have done a lot here recently with inventory valuations. Why care about "Moving Average Costs". Consider using receivers as lot numbers and do actual costs of what is there.
We do our valuations on a monthly basis. If weekly or daily costs are needed, consider saving this daily data to a little database. I guess that we could talk about it... --Bill 973.471.7770 x145 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Baker Hughes Sent: Tuesday, August 28, 2007 2:33 PM To: [email protected] Subject: [U2] Moving Average Cost Hey, I have a distribution/manufacturing question. Could some of you share your formula for calculating Moving Average Cost. Consider: Assume you are receiving stock into the warehouse, and recalculating your new average cost upon each receipt (which later serves as basis for your cost-plus price quote, but that's immaterial to the formula). Since you also have negative stock movements (cycle count inventory adjustments, or adjust quantities on- purchase receipts) should you not use the absolute qty and absolute cost of the movement, when calculating your moving avg cost? Formula A: Extended.Cost.Rcpt = Qty.Rcvd * Cost.Ea Total.Inventory.Value = (Qty.OH * Old.Avg.Cost) + Extended.Cost.Rcpt Total.QOH = Qty.OH + Qty.Rcvd New.Avg.Cost = Total.Inventory.Value / Total.QOH Ex. 1 - a positive Qty Received: Extended.Cost.Rcpt = 10,000 * 2.8242 [28,242.00] Total.Inventory.Value = (11,000 * 2.8215) + Extended.Cost.Rcpt [59,278.50] Total.QOH = 11,000 + 10,000 [21,000] New.Avg.Cost = 59,278.5 / 21,000 [2.8227] Ex. 2 - a negative Qty Received (Adjusted): Extended.Cost.Rcpt = -10,000 * 2.8242 [-28,242.00] Total.Inventory.Value = (11,000 * 2.8215) + Extended.Cost.Rcpt [2,794.50] Total.QOH = 11,000 - 10,000 [1,000] New.Avg.Cost = 2,794.50 / 1,000 [2.7945] Formula B: Extended.Cost.Rcpt = ABS(Qty.Rcvd) * ABS(Cost.Ea) Total.Inventory.Value = (Qty.OH * Old.Avg.Cost) + Extended.Cost.Rcpt Total.ABS.QOH = Qty.OH + ABS(Qty.Rcvd) New.Avg.Cost = Total.Inventory.Value / Total.ABS.QOH With Formula B the new.avg.cost would be the same for both Ex. 1 & 2 TIA, -Baker ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
