Don't use absolute value, or you'll screw up your average costs. When you're adjusting inventory out, you're REDUCING the total value of your inventory. The average cost for whatever's left should be the new inventory total value divided by the new quantity on hand.
For example: if you receive 10,000 at cost X, then adjust 10,000 out at the same cost, your average cost should be what it was before you did the initial receipt. If you use absolute values, your average cost will go up, and your inventory will be overstated. Larry Hiscock Western Computer Services -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Baker Hughes Sent: Tuesday, August 28, 2007 11:33 AM 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/
