correcting negative inventory?

wesleys

Situation:
Navision 3.7
All items are average costing method.
Adjust cost batch is run every morning.

Item A has -2 on hand. There are a few ways this happens, but let's just assume some of item A was lost so it was negative adjusted (at the average cost at the time). Someone at some point must have found them and shipped them to a customers. No big deal. So, what I need to do, now that the qty on the shelf is actually zero, is create a positive adjustment. My big question here, is what value should I give them?

The Item card displays an average cost. The average cost is a positive number, just a little higher than the last direct cost and our purchase price, because of freight and other applied item charges.

I have tried to do the positive entry at zero cost...I don't think this was a good idea, because that increases my shrinkage (due to previous negative adjustment) when it shouldn't be.

My best idea is to do the positive adjustment at either the current average cost as displayed on the item card, or try to find the latest cost in the value entries table. When I try these methods, I end up with 0 qty on hand, and an average cost of 0 after adjust cost is run (all good). But if I receive and invoice a purchase order for 10 of these at $10.00 each, then run adjust cost I end up with numbers like $9.85 or $11.20 for my average cost. Starting from 0 qty and 0 average cost, any purchase should set my average cost to match the purchase price. Why doesn't it?

](*,)

chaswin

I think what may have happened is due to he negative stock the average cost is not the sum of the values divided by the sum of the quantities. After you've brought the stock back to a positive number it is able to do the calculation correctly and then figures out the incorrect cost postings it made while the stock was negative and adjusts them.

wesleys

I think that that is exactly what is going on. The sales that drove the quantity negative still sit as "expected" costs in the value entry table, until the purchase is made, at which point it starts including them as actual costs. I think the only reason I see a 0 average cost when the quantity is 0 is because it can't devide by 0 quantity so just defaults to 0 cost until it has something better to work with.

So what do I do? How do I devine what cost to use for my positive adjustments to bring the item to 0 so that this doesn't throw off my average cost.

Even if my inventory never went negative, how does anyone determine what cost to use for a positive adjustment. The system likes to default to the last direct cost...which is not correct for anyone who has to pay for landing costs.

Help?

chaswin

I think Navision only uses the LDC if you do an item journal line of type "Purchase". For type "Positive Adjustment" it's supposed to use the average cost. (Which is of course zero if it calculates out to a negative amount).

I think the best bet is to use the last good average cost and let the adjust cost sort things out after the next purchase.

I'm afraid we don't allow our customers to go negative! Our standard issue system includes many local mods that we have made, one of which is to stop stock going negative. We've found that prevention is better than cure! If users can go negative then they don't worry about fixing the stock. Blocking the negative means they have to fix the stock immediately.

With regard to what cost to use, can you copy all the value entries into a spreadsheet and do some calculations based on moving the stock back to zero?

wesleys

I've seen our positive adjustments default to the last direct cost, so I know I can't trust navision to give me the right number. Trying to use the last good average cost has failed as per my experiments above, so I can't let adjust cost just sort it out, because this plan does not get to correct average cost.

Not allowing the system to go negative would be very nice, however I would just as soon sell the product if it's sitting on the shelf, then tell my customers that I don't have it. Besides the fact that this method causes the employees to think we as management don't know what we're doing, because the system wont let us sell and ship products that are clearly sitting in the warehouse. Even without the negative inventory issue I question what cost to use for positive adjustments.

I'm not sure how to go about calculating invoiced quantities and actual cost posted with the entries that are currently just expected and which ones will become actual cost entries that will be created by the adjust cost batch.

nostran

hi,

with regards to the average cost of the item not equal to your last direct cost after processing a new purchase order,i would recommend the following.

run the inventory valuation report. id=1001. if the cost posted for items on hand is not equal to qty on hand * average cost, you need to revaluate those items. revaluate your items to the last direct cost.

this happens because when you sold those two last items and went in negative stock, the g/l amount "allocated" to that item was negative.

let me give u an example to illustrate the above point
initially you bought 5 items A at 10 dollars
your average cost is 10 dollars and the unit cost is 10 dollars
the g/l stock account is debited by 50 dollars and stock valuation i.e. cost posted to g/l for item A is increased by 50 dollars
sell 7 items. g/l stock account is credited by 70 dollars and stock valution i.e. cost posted to g/l decreases by 70 dollars and shows a negative cost posted to g/l of 20

next you process a positive adjustment to bring qty to 0. if the cost that you use is not sufficient to offset the -20 dollars on cost posted, then when you purchase item A, the average cost won't definitely be the last direct cost

hope i did shed some light, i would definitely advise against allowing negative quantity. as once you go into negative qty, most of the times, running adjust cost does not work out the average cost correctly and you run out with a unit cost and an average cost which could sometimes be 20 times what it should be.

in addition, it could generate huge difference between stock valuation and g/l balance, one of my customers had as a result to process adjusments worth millions during one financial year just to get the two reports to tally.

nostran

with regards to positive adjustments,
try the following work around
a) sort out your stock valuation and cost first
b) run adjust cost before processing positive adju
c) look up the avg cost on the item card and manually overwrite the LDC that navision suggests

chaswin

I think what is really causing the problem is the way Navision handles average costing on a "Daily Average Cost" basis in version 3 onwards.

This magic is performed in the adjust cost run.

The concept works on the assumption that each posting date has an opening quantity and value.

To this are added all of the positive adjustments and purchases for the day. An average cost is then calculated. (Total value / total quantity). This calculated average (in reality a mythical figure from a traditional average costing viewpoint) is then used to adjust all of the days negative stock transactions.

What this boils down to is that if you have a positive adjustment or puchase during the day, at a unit cost which is different to the opening average cost, then every issue transaction with the same posting date will be adjusted by the adjust cost/post cost to GL periodic runs. This can be a lot of adjustments in certain manufacturing environments.

This is easy to demonstrate in Cronus:

Opening balance 40 at $10 = 400
Issue -30 at $10 = -300
Issue -20 at $10 = -200

At this point total -10 at $10 = -100

Purchase 100 at $12 = 1200

New total 90 at $12.22222 = 1100

So far so good, the average is what we might expect.

But the adjust cost then stuffs things up:

Opening balance 40 at $10 = 400
Purchase 100 at $12 = 1200

Totals 140 at 11.42856 = 1200

The two issues at $10 then have a new value entry created to adjust the cost from 10 to 11.42856. This means that the inventory value is not worth $1100, as you might expect, but is valued at $ 1028.57!! Bet you didn't expect that! By the same dubious logic, the cost of sales has increased by $71.43.

This "logic" applies to all average cost transactions. You don't have to go into negative stock to have the system perform its magic like this.

Bottom line is that your inventory and profit reports are meaningless documents unless run immediately after the adjust cost/post cost to GL. You may also find your database growing at a faster rate if your input costs vary, even if only by fractions of cents.

This is all done so that you can back date issues and have them executed at the "correct" cost. (Although this can change the current average cost as well).

It gets really amusing if you back date a positive adjustment at a cost that is different to the days average. This causes the days average to change, so all isssues are adjusted once again. Problem is that the opening cost of the following day has also changed, so all of its transactions must be adjusted and so on and so on.

In my youth, the average cost followed the transaction flow and only changed in response to additions to the stock. Once a cost had been applied to a transaction it never changed. The way Navision is operating at the moment is about as far removed from this as you can get!

Can't say I like it very much......!

wesleys

Thank you both very much for your input. It sounds to me like I need to add another process to our inventory valuation controls to account for no-vision trying to think to much...again.

I have to go tell my boss now that no-vision is doing exactly what he was afraid of with average costing. I'd be looking for another job at this point if it had been me who made the decision to go with this software.

Thanks again

](*,)