Help with issues related to negative inventory

(0) Share

Report

Posted on by Heather Roggeveen

9,146

Firstly, let me state, negative inventory is awful and in an ideal world none of us would allow negative inventory. However, now we have found it is not an ideal world, I have some issues at a client that I am hoping for some help with.

Costing method: average cost

When you purchase items that have a low unit value (e.g. packaging), you often get a rounding entry e.g.

64999 units @ $0.22

1 unit @ $323.02

That is because the actual unit cost might be e.g. $0.22031 (don't know if that adds up, but the math is not important).

So the inventory card tracks the current cost of an item. When the above situation happens, the current cost is registered as $323.02 because that is the last cost line. Not generally a big deal - BUT - there are situations where the current cost is the default cost.

Negative inventory is one of these. If there is no stock on hand, GP uses the last Current Cost to do the cost calculation.

In my case, this is packaging that has gone into Bills of Material (standard GP - not manufacturing). When you use tens of thousands of the items, that has a big financial impact. I have no issues with sorting out the costs - I can handle that side of it.

What I want to know is can we stop GP from recording that last cost as the current cost? We want the average cost of the last receipt layer recorded.

Cheers

Heather

*This post is locked for comments

All responses (9)

Answers (0)

Heather Roggeveen 9,146 on at

Like (0)

Report

RE: Help with issues related to negative inventory

Hi Jonathan

No I haven't had a definitive answer on the initial query - what I have taken out of this so far:

- Average Perpetual sounds good in theory, but the practise - particularly with Lot numbers is not that great. The time taken for posting and the continual adjustment of cost layers does cause issues.

With regard to the negative inventory and the cost layer that is used, I haven't had an answer - it may be something we need to escalate to your engineering team. Although, I am sure this will be an issue with other sites so having some public resolution would be good.

I have just worked through some more detailed testing of what is happening.

I have done three PO receipts and invoice matches. First item was in stock already and I added to the stock. Second item was in negative inventory and moved to positive after the receipt. Third item was in negative inventory and remained in negative inventory after the receipt.

All items had the scenario of needing to round e.g. 999 @ 23c and 1 @ 3.85.

Inventory Transaction Enquiry shows the same for each item - i.e. full quantity at the rounded average price with the total cost layer. (1000 @ 23c = $233.62).

Inventory Adjust Costs screen shows the break out (999 @ 23c and 1 @ 3.85)

Item Creditor Maintenance showed 23c in the Originating Invoice Cost for all items.

Item Maintenance showed 23c as the Current Cost for Scenario 1 & 2 - BUT showed 3.85 for Scenario 3. I believe this is the where the crux of the issue is. Current cost should be updated with the average cost of the last batch posted (like Item Creditor Maintenance is) - not the last cost layer.

My next step was to do an Inventory Adjustment (will do a negative one as this replicates using the stock).

Scenario 1 & 2 showed the unit price as 23c - i.e. the current rounded average cost.

Scenario 3 - with the negative inventory - showed the last price 3.85. This is an issue as this is the cost that appears regardless of entering a negative or positive quantity. If I was doing an increase adjustment and didn't know the value was wrong, my increase would be at the wrong price.

For decreases, the unit cost is for information only - in theory it is the average cost that will post.

On posting the decrease adjustment:

Scenario 1 & 2 posted correctly @ 23c per unit

Scenario 3 - posted incorrectly @ 3.85

To me this seems like the logic is incorrect. I know negative inventory is not ideal - but if the system allows it, it should deal with it correctly. Cost should be the last average cost for a batch or zero.

Cheers

Heather
Jonathan Fear on at

Like (0)

Report

RE: Help with issues related to negative inventory

Any update on the resolution? This is a post that is getting a large number of views so we would like to get a confirmed resolution.
Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: Help with issues related to negative inventory

I have never recommended Average Perpetual, a simple example is represented with posting a back-dated transaction which recalculates all the associated inventory cost layers in IV10200 to update the Unit Cost and Adjusted Unit Cost fields. In other cases, posting sales invoices, the document is recorded in IV10201 and the unit cost calculation among IV10200 and IV10201 can take such a long time.

This is usually known as the inventory ripple effect with the Average Perpetual valuation method, several clients have ended up changing the inventory valuation method, I have done it several times same way mentioned above by Ms. Leslie

I have seen several cases related to negative inventory cost which has always been the result of the cost adjustment. I don't have specific answer for the case you mentioned as it is a bit ambiguous to me. The case has been thoroughly illustrated on HITB Essentials Series – Zero Quantity and Negative/Positive Cost. Although, I don't think this is related to your case.
L Vail 65,271 on at

Like (0)

Report

RE: Help with issues related to negative inventory

Hi Victoria,

I've experienced this too. My client brought in transactions from a hundred or so different locations and sometimes it would take overnight to post. Changing the number of physical discs and placing the log/db/swap, etc. files on different discs helped a lot, but in the end I convinced them to change valuation methods.

I zeroed out all of the inventory, changed valuation methods, and imported it back in. Of course, there needed to be some behind-the-scenes manipulation of POP files and what-not, but after it was done their posting zoomed.

Average perpetual sounds like a good idea, but ever since GP fixed the calculation, it's dreadfully slow unless you do all of your inventory postings timely.

Good to know I'm not the only one who has battled this beast!

Kind regards,

Leslie
Victoria Yudin 22,768 on at

Like (0)

Report

RE: Help with issues related to negative inventory

Heather, I don't have an answer for you on the original question, but I wanted to mention that I have also seen some significant posting slowdowns when using average cost. Specifically with returns. Invoices would post instantly. Returns would sometime take several HOURS to complete posting (for a batch of 3 to 4 returns).
Heather Roggeveen 9,146 on at

Like (0)

Report

RE: Help with issues related to negative inventory

Hi

Sorry for the delayed response - extended Easter break - still on leave but very keen to get this issue resolved.

Leslie - I am dealing with lot numbers - but not for the negative inventory - that was related to packaging. Interesting you note about the slow posting - we have issues with the BOM's posting particularly slowly. I had always attributed it to small quantities, sub-assemblies and assigning lot numbers - but maybe average cost is also an issue.

Certainly for one of the cases, this is the scenario - I would have to check the others:

Quantity on hand: 0 at $0 cost

Moved into negative inventory at $0 cost

Goods receipt posted - BUT - not enough to cover the negative quantity

So had 64,999 @ $0.22 and 1 @ $323.02 - but quantity was still in negative.

So now, negative quantity on hand with current cost of $323.02.

Further few thousand quantities taken from the stock that didn't exist.

Increase stock adjustment done for about 90,000 units as client did actually have it in stock (wasn't on the opening stock count) @ $0.22.

Resulting cost adjustment was about $2.7 million :-)

Have adjusted all the underlying cost layers and now the P&L seems to make some form of sense.

It has happened in at least three other instances - but I am unsure whether the stock remained in negative after the initial adjustment or receipt. I will check that and update - probably tonight.

Cheers

Heather
L Vail 65,271 on at

Like (0)

Report

RE: Help with issues related to negative inventory

Hi Heather,

I'm right there with Frank and Mahmoud. 'Current Cost' under the Average Cost Perpetual method is in fact the last moving average cost. I had a lot of trouble with how long it took the invoices to post when a client used average cost and had thousands of transactions per day. And, of course, commonly had negative inventory. Are the items you are dealing with lot or serial numbered items?

Kind regards,

Leslie
Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: Help with issues related to negative inventory

I might be interested as well in further details ...

Your feedback is highly appreciated,
Frank Hamelly | MVP... 46,527 Super User 2025 Season 1 on at

Like (0)

Report

RE: Help with issues related to negative inventory

Heather, using Average Cost as the valuation method, Current Cost should be the weighted moving average cost of stock on hand. Where you've seen an issue with negative inventory, what quantity on hand did you have after the latest receipt? An actual example with real quantities would be helpful.

Thanks,