Microsoft Dynamics AX (Archived)

Incorrect Inventory re-calculation

(0) Share

Report

Posted on by Community Member

Dears,

I have a strange behavior after running inventory re-calculation!

Notes to help in understanding the problem:

- I have BOMitems with 3 levels

- I have a huge amount of transactions and BOM assembly per month.

- BOMs are reported as finished automatic once sold

- Journal is as follows:

Dr. Inventory BOM

Cr. Inventory Raw

Dr. COGS

Cr.Inventory BOM

(Accordingly the Inventory BOM balance is zero)

After inventory recalculation I noticed the following:

1- A balance appeard in the Inventory BOM account

2- Some BOM items did not have any amounts in the adjustments field although the raw items were adjusted.

3- The recalculation was Run with 10 Maximum Throughputs, I ran it 3 more times increasing it each time and each time the balance of the BOM inventory account slightly decreased!!!!!

4-When I add the Financial value and the adjustments from the inventory transactions inquiry, they are not equal to the COGS value?!

How come the amount changed without posting any new transactions?!, how come the amount of recalculating the raw items was not equal the amounts affecting the BOM?!

Appreciate your feedback / opinion.

Thanks,

Sherine.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

You haven't included enough information to dig into your problem, but here are some possibilities for you to consider. This all assumes some average cost inventory model, otherwise your question wouldn't make sense anyway.

Reversing report as finished operations don't work like normal report as finished operations. Whereas a normal report as finished operation will see a matching adjustment on the receipt for any adjustment on the issue, thereby keeping the values in sync, a reversing report as finished can see an adjustment on the finished part which is an inventory issue, and that will not be translated into its components which are inventory receipts. In other words, all reversing report as finished operations end up creating residual values in the BOM account.

Inventory transfers within the same warehouse have serious bugs in recalculation in AX 4 and 2009. The cost values of the issue and receipt should always be equal, but I bet if you ran some SQL you would find some that are not. This is another source of residual value after recalculation.

If you used any sort of minimum adjustment amount, i.e. $1.00, then this is an obvious source of residual value. For example, a BOM receipt with 2 component issues where the components have individual adjustments of $1.56 and -$1.26, the receipt would not be adjusted for $0.30 because that's less than the minimum adjustment amount. This is supposed to prevent "jitter" of recalculation due to rounding, etc., but it's a source of error also.

Finally, if you have multiple warehouses, and you have any sort of bleed back and forth between them, i.e. transfer from A to B, and then from B back to A for the same item, where cost loops are created, these can create situations where the inventory recalculation process "approaches" an ideal value with each cycle, like a limit function in calculus, but never gets there.

Hope some of this helps you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Brandon,

Thanks for taking the time and giving me this detailed answer!

First I am using a FIFO costing model not average, why are you saying this does not make sense?

For reversing do you mean credit notes, because there weren't any.

For inventory transfers, I have AX2012 R3, is this applicable here?

For the minimum adjustment throughputs I ran it once with one and once 0.5 I don't think I need to go for less!

Though I did not cancel the one with 1 before running the one with 0.5 would this make a difference?

For the last point I don't totally understand it!, yes I have multiple warehouses with transfers between them of raw materials only not the finished.

If you need more detailed information as you mentioned I did not give enough please tell me.

Thanks,

Sherine.

Was this reply helpful? Yes No
Verified answer

Brandon Wiese 17,788 on at

Like (0)

Report

I meant to say "some actual cost inventory model", not "average", my bad. FIFO, average cost, weighted average cost, are all forms of actual cost inventory models, as opposed to standard cost where inventory recalculation does not apply.

When you mentioned that increasing the Maximum throughputs actually decreased the residual value in the BOM account, that strongly suggests to me that you have a lot of cost loops across financial boundaries, i.e. transfers of items back and forth between two or more warehouses. This complicates the recalculation process, and Maximum throughputs is designed to limit the amount of work that will be done so it doesn't run forever. The Minimum adjustment does much the same thing. If you increase the Maximum throughputs and decrease the Minimum adjustment and the results keep getting better, than you simply have to draw a line somewhere between performance and accuracy and live with it.

You haven't given us a sense of scale of the problem. Is your BOM account left with a few dollars of value, a few hundred, a few thousand, and out of how much inventory? Some residual can be unavoidable with a complex recalculation. I have been doing inventory recalculations for more than 10 years, and if I end up with $5 or $10 of residual value per million of inventory, frankly I'm quite pleased.

The issue in your original post that gives me the most concern is that your COGS does not equal the financial value plus the adjustment value. This should always be true across all customer invoice transactions. I don't mean the Main account for your COGS, because lots of things can post to this Main account including manual adjustments. But certainly a margin report or other cost report should be accurate per the adjustments that are made.

I'm not sure this is a good or appropriate place to provide the level of detail that would be required to arrive at a root cause for even a single specific adjustment that concerns you. It sounds like you have quite a lot of data and a high transaction volume. You may need to seek the assistance of a partner or consultant who is highly familiar with inventory recalculation and can work directly with your system and your data.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Brandon,

Sorry for my late reply, as I was in my weekend!

You are right it is a very detailed case to be solved this way, i just wanted to get ideas from others experience here.

I would like to ask you:

- Do you know if the cancellation of recalculation works properly in AX2012 R3? as I heard it did not in earlier versions!

- Would it make any difference if I cancelled the recalculations I did, and make only one and increase the throughputs and decrease minimum adjustment from the begining or it will be the same effect (As I have ran 4 recalculations increasing the throughputs each time did not start from the first one!)

- Would you please explain if possible why does the transfer between multi warehouses and the bleed back cause a problem with the costing? (Is this applied only if the transfer in BOM item or even raw items)

- As a scale of the problem:

The inventory Raw balance is 8.5 million and the BOM inventory after 4 recalculations is 100K

The COGS of items from GL of a certain warehouse (From Financial dimenions filter) is 555K While the Financial value plus adjustments is 565K

Thanks for your patience!

Would appreciate your feedback.

Thanks,

Sherine.

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

I haven't noticed a problem in AX 2012 with canceling a recalculation, but I haven't done that many yet. Yes, in previous versions it was not 100% reliable and could easily leave things out of whack. I recall in 4.0 I did everything I could to avoid canceling a recalculation it was so bad.

In theory, just running recalculation again should just make incremental adjustments on top of all previous adjustments, to the same end result as canceling and starting over with a single recalculation, at least provided the same parameters are in play. Obviously if you ran 4 times with a Maximum throughputs of 10 each time, you would probably have to use 40 to achieve the same result in one pass.

100k seems like a lot. I recall $10 on 4 million in inventory.

There's no reason for COGS to differ at all. I would find a specific single case of that and drill into that problem until I figured out what was going wrong. That's unacceptable in my opinion.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Brandon,

Thank you very much, I will try analyzing the COGS issue as much as I can, and I guess will have to open an incident for this.

You have been so much helpful and patient in your replies...

Thanks again.

Sherine.

Was this reply helpful? Yes No

Community site session details

Incorrect Inventory re-calculation

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Incorrect Inventory re-calculation

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates