Average Cost Calculation 1

[jmccall]

If I have a non-stocked item with 0 qty on hand and an average cost of $2.00 and place an order for 1 item at a cost of $2.50 shouldn't my average cost on reciept change to $2.50 . I seem to have non-stock items in the item location file with a different average cost than the last cost and am wondering how average cost is calculated for a non-stock item with 0 inventory on hand.
Any advice would be appreciated.

 

[crystalreporting]

Not sure if the non-stock factor comes in to play, but using the figures you provided I believe the calculation is correct (using Macola's weighted average cost formula).

Macola takes the QOH x Avg. Cost (0 X $2.00 = 0) and adds it to the Qty. Rec. x Unit Cost (1 X $2.50 = $2.50) for a total of $2.50. It then divides this by the QOH + Qty. Rec (0 + 1 = 1) for a total of $2.50.

Peter Shirley

http://www.srhconsulting.com

 

[jmccall]

Thanks for your response.

That confirms what I would have expected. So wouldn't that imply for a non-stock item with 0 qty on hand the average cost should always be equal to the last cost. And if they are different something else must be effecting average cost.

 

[kscheeler]

I agree with crystalreporting with just one note. Read below about receiving at $0 cost.

Question:

In an Average Cost environment for inventory valuation, if there is zero (0) on hand and an Average Cost in the Item Location file for an Item, is the Average Cost recalculated if a receipt occurs for a quantity at zero (0) cost (a vendor gives us some) ?

Answer:

To minimize the overall long range impact on the inventory value for the item, Progression treats the receipt of items at $0 cost when there is 0 on hand as an anomaly. Any receipt at $0 cost where there is a quantity on hand will be treated normally and the average cost will be recalculated.

Example:

1.) If there is 0 on hand at an average cost of $10, the total valuation of inventory at the General Ledger level is $0.


2.) If at that time a receipt occurs for 10 pieces at no ($0) cost, the Average Cost is still $10 not $0 and at the General Ledger level inventory is still valued at $0. If at this point the question becomes, if the item is issued, what cost is used to relieve inventory;

a: $0 because this is what the value in G/L is

b: $10 which is what the historical average cost is as shown in the I/M Item/Location file.


3.) To take this example one step further, another receipt occurs for 10 pieces at $10 each. At this point there are now 20 on hand at an average cost of;

a: $10 because this is what the historical average has been as well as what the receipt was for

b: $5 because we had 10 at $0 cost and did a receipt for 10 at $10 and calculating 20 pieces at a total value of $100 is $5. In this scenario if the actual viable cost was truly $10, the inventory value at the G/L level could be considered “under valued” until enough receipts occurred over time to return the calculated average cost to the $10 level.



Kevin Scheeler

 

[MacolaHelp]

Point of clarification: we are talking about non-stocked items here. Therefore, the QOH is always 0. Because of this, average cost is never recalculated because we never have a quantity to use to create a weighted moving average. Last cost will always be updated.

The inventory distributions for the part I tested as a receipt in 76300b were debited to inventory and credited to receivings accrual at the last cost (from the PO I created): $2.50 per item. Average cost remained at $2.00 with 0 on hand. I then invoiced. The quantity allocated had increased when I entered the order (item is controlled). After invoicing the item was deallocated, QOH stays at 0. The distributions for the issued item were done at $2.00 per part because my inventory is valued at average cost, which creates a disparity between received last cost of $2.50 and issue average cost of $2.00. You cannot do a cost adjustment on a non-stocked item through inventory transactions to adjust average cost to $2.50 before you enter orders or issue via invoicing. In this case, you would have used the im location maintenance to change the avg cost to $2.50 to keep in sync with last cost before entering on the order. If you hadn't done that, you would need to journal entry the $.50 difference per part to credit inventory and debit COGS. Then your sales history wouldn't match your GL. If you journal entry the $.50 after the fact, you need to go to OE\processes\sales history\sales history load in order to put the sales history reports back in sync with the GL.

It is my recollection that v7x macola has always behaved this way. I think the underlying rationale is that division by 0 is always 0. If there never is on hand, no weighted moving average can be performed.