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/
