Costing Expert Needed

synpartners

We've recently had problems with our Inventory Valuation report not matching Inventory to G/L Reconcile. In researching, I was able to use the Inventory Valuation report from v4.0 and modify for 3.6 to fix this problem. These reports now match, but have exposed another problem. We now have some inventory items that have a quantity of zero but values in the thousands of dollars (one is 13k). After looking at the Value Entries I can find issues with how these items were valued back in 2003 (possibly because one user made direct table modifications with the help of an unscrupulous Navision partner). We're now in the middle of an audit and I'm not sure where to start to fix this issue. I know that there are many users experienced in costing on the forum and we'd be happy to pay for a consult. Please respond, PM, or email me if you can help. Thanks!

Alex_Chow

A couple of questions:

1. Have you been running adjust cost process regularly?
2. Have you tried revaluation journal to resolve the item problem?
3. If your valuation is okay, why not just adjust the G/L account to match your valuation report?

nunomaia

Use the method that deadlizard told you to use.

Using revaluation journal indirectly you can post almost any value to item cost ledger entry.

synpartners

Answers to your questions...

>>>1. Have you been running adjust cost process regularly?

I'm not sure how often adjust cost was run in the past (as in, before 2004 when a lot of the odd value entries exist), but since, we typically run it twice a month using a date filter. For example, it was run on 2/19/07 filtering on 02/01/07..02/16/07 and it will be run again after the month closes for 02/17/07..02/28/07.

>>>2. Have you tried revaluation journal to resolve the item problem?

No, I'm not sure exactly how that works. Is it intended to fix issues like we're having?

>>>3. If your valuation is okay, why not just adjust the G/L account to match your valuation report?

The G/L account matches the valuation report, but we're having a hard time convincing our auditors that we don't have major issues since we currently have a few items with zero quantities and values in the thousands of dollars.

Savatage

I never run Adjust costs using any filters. It knows what needs to be done.

-I hope

Youhave been posting Inventory costs to G\L after running the adjusts costs correct?

nunomaia

Adjust cost in 3.60 it isn’t perfect, it has many issues.
When you run Adjust cost with date filters you can lead to incorrect costing. If you make sales leading to negating inventory you can have problems.

Alex_Chow

Like Savatage mentioned, run your adjust cost process without date filters.

When you run your inventory valuation as of today for those items with 0 quantity, is the cost 0? Zero quantity with valuation happens on the timing of when the adjust cost process is ran. There are a lot of topics on this issue if you do a search on this forum.

Navision 3.6 has some problems with the adjust cost process, but it's been address if you upgrade to at least 3.7b (which might be a good investment for you so these problems don't occur again).

synpartners

deadlizard...

We have been running our adjust cost with date filters because that's how we were told to do it by our original implementor. Also, during research in this forum, I found out that adjust cost should never be run as of a previous day. When our accounting department wants to run adjust cost and then post to the g/l as of the end of a period then we would use the filter so that we did not pick up posted transactions that occurred after the "as of" date. From reading here, those transactions are what can throw off the costing. What can we do if we want to run adjust cost up to a certain day but cannot (for whatever reason) physically run adjust cost at the end of that particular day?

If we run the inventory valuation as of today, some of our items that have zero quantity have a cost of thousands of dollars. I've done a lot of research on this forum and have tried to figure it out but all I ever come across are posts with arguments as to whether costing is buggy etc. I can never find anything that can actually give me a solution to this problem. Some posts say to run adjust cost daily etc, and we're willing to do that from now on if need be, but how do we fix these old entries? I'd really appreciate some expert help addressing our issues. Once solved, I'm ready to tackle an upgrade to help resolve this in the future. My issue now is that accounting has put an outside audit on hold until we figure out what has gone wrong with this costing in the past. Thanks to everyone for your help thus far. Please feel free to contact me if you're a freelancer.

Savatage

1) when you run adjust cost without filters - if the cost has already been adjusted for a particular entry it will not adjust again - that's why you don't need filters. It will only do the ones that haven't been done yet.

2) your g/l will never match the inv valuation on a daily basis if you only post the g/l entries at the end of the period.

3) all it takes is one typo of a date and a whole batch of items will not be adjusted and you would never know.

4) if you are still in contact with your original implimentor - ask them what's their reasoning behind their comment.

5)you can always copy the dabase to your local hard drive. connect to that (copy) database and try these suggestions to see what the results would be if you're nervous.

6) our cost used to be way off until we realized we were handling returns and seperate locations incorrectly - once we made the corrections everything has been fine. So the cost errors could also be due to some other way you process your info not necessarily the adjust costs.

synpartners

Savatage...

I guess I should have been more clear when saying that we have items with zero quantity with value as of today. What I really meant was as of our closing period for '06 (12/31/06). Is there a way to know if a value entry has been adjusted? Also, I would guess that we could have an issue as you did in #6 with returns and seperate locations considering we have over 100 seperate locations. Would you mind consulting with me on the best way to handle locations and returns?

nunomaia

You should look at item ledger entry if you have ledger entries with Applied Entry to Adjust checked and Expected cost.

One way to enforce correct document returning is to enforce not null "Applies-to Doc No." and both documents having the same location code.

Alex_Chow

Once the adjust cost is ran, there's no way of reversing the entries in the Value Entries or the Item Ledger.

So if your valuation has 0 quantity with a value, you'll need to ask a solution center to come in and address it for you (it will be a time consuming process).

nunomaia

So if your valuation has 0 quantity with a value, you'll need to ask a solution center to come in and address it for you (it will be a time consuming process).

Time consuming .....
If some values were manually changed by solution partner, then probably will be dificult to correct.

Alex_Chow

nunomaia wrote:

Time consuming .....
If some values were manually changed by solution partner, then probably will be dificult to correct.

Difficult, but not impossible.

Time consuming is an understatement.

Savatage

synpartners wrote:

Also, I would guess that we could have an issue as you did in #6 with returns and seperate locations considering we have over 100 seperate locations. Would you mind consulting with me on the best way to handle locations and returns?

First you have to explain your method of returns.
Second to get some further info in your Inv Setup is your cost type set to "Item-Location-Varient"
Third what costing method are you using? FiFo, Standard, Average.

Our cost when crazy because we made a location called "Damaged" where all returns that were not good enuf to go back into stock went. But Nav doesn't think of it as a place where dead stuff goes it thinks we're filling a 2nd location with goods and therefore adding value where there is none and never clearing it. The last mistake was thinking that when we posting items to the "damaged" location that we should change the cost to Zero so it wouldn't effect any accounts. Well it did the exact opposite. The zero costs along with INV setup as just Item instead of Item-Location-Variant made any item that was sent to the damaged location wrong and each attional entry made it worse. We couldn't figure it out for 3 yrs . We did what our NSC told us to do but thanks to the good people here we figured out on our own.

To keep a long story short I noticed that some of my item ledger enties never closed (see)
http://www.mibuso.com/forum/viewtopic.php?t=11853

So by reversing all the errors all the costs cambe back in line.
the reversing was easy thanks to themave's suggestion (see)
http://www.mibuso.com/forum/viewtopic.php?t=15738

So what have we learned here..
-Mistakes can be corrected & Implimentors aren't always correct.

Sometimes it takes a new person to review a companies business process to see if things are being done the correct way.

I know I went off on a tanget here but what's your biggest concern. The G/L & Valuation not matching or wrong costs. Have you drilled into the item ledger entries for an item with a wrong cost? You should be able to see where the problem started.

nunomaia

I normally recommend using Item costing and not Item-Location-Variant costing. It can generate more errors. If with item tracked item, users tend to give wrong info.…

If you have Item-Location-Variant costing make a backup. In backup db, change the costing method to item costing. Run again adjust cost report (wait, and wait and wait) and see the results.

Check again for open ledger entries.


nunomaia

You should look at item ledger entry if you have ledger entries with Applied Entry to Adjust checked and Expected cost.

synpartners

I can certainly try the change to Item costing and see what I get, but what exactly is the difference and what am I giving up by moving from Item-Location-Variant to just Item?

David_Singleton

The first and most important question is "What costing method or methods are you using".

Basically Navision has three costing mechanisms. FIFO, Standard and Average.

Next what is the EXACT version you are running, and have you done any upgrades. Certain versions of Navision had specific problems with some types of costing. For example Lot numbers Serial numbers and Average costing changed a lot in the versions from 2.6 up to 3.7.

If for example you were running 2.60 and upgraded to 3.70, then your costing could be messed up and completely different if you went:
2.60->3.01->3.70 compared to 2.60->3.10->3.70. So if you have done any upgrades, please let us know which ones. Also if you did come from 2.60, there are issues in costing moving from 2.60 manufacturing.

The third question, is about the "fixes" made directly to the database. Do you know what was done, or if not at least WHY it was done. For example were these hacks made to try to balance inventory out to a specific date. And were the changes made in a 2.xx version (with Item Ledger) or a 3.xx version (with value entries).

nunomaia

In a prevuos post synpartners told that they were running navision 3.60. (My old attain )

I know that navision 3.60 has many hotfixes to solve costing issues. It's important to know the hotfixes that were applied.

synpartners

We use FIFO costing. As for versions. We're running 3.6 client, but most of our objects appear to be 3.1 with the exception of some banking stuff that shows 3.6 with mods by our implementor. Here is some version info...

Inventory to G/L Reconcile NAVUS3.01.02
Inventory Valuation (doesn't match G/L) NAVUS3.10.02.04
Inventory Valution (matches G/L and customized to work in 3.6) NAVNA4.00
Adjust Cost NAVW13.00

Now, you'll notice that in my very first post, I mentioned that originally our Inventory Valuation report didn't match our G/L Reconcile report. After doing some research here, I was able to narrow the problem down to the 'AdjustItemLedgEntryToAsOfDate' function that was different between the two versions of the report. I'm thinking that the differences in this function may help to give some clues into our problem. Under the NAVUS3.10.02.04 version of the Inventory Valuation Report, we get zero quantity and zero value but the G/L reconcile shows a valuation of $13,820.46. With NAVNA4.00 version the valuation shows zero quantity with a valuation that matches the G/L reconcile. If it may help, I can paste up the two different versions of the 'AdjustItemLedgEntryToAsOfDate' function.

Thanks again for your help.

nunomaia

hum.... :-k
I think you have a mess with your objects.

Navision NA 3.60 (without any hotfixes or changes)

Inventory Valuation - NAVUS3.60
Adjust Cost - Item Entries - NAVW13.00
Inventory to G/L Reconcile - NAVUS3.60

synpartners

I'm thinking that we must be running 3.6 client only and that most of the objects are still 3.1? Was there a 3.1 version?

nunomaia

Yes, there is a 3.1 version.

Can you make -> Help About

If your version is 3.1, isn't supposed the costing report to have 3.60 version.

synpartners

Version is US 3.10.B.08 Build 2 (3.60)

nunomaia

Client 3.60
Objects version 3.1 B

Has I have said, you have a mess in your objects.

The lasted version of Inventory to G/L Reconcile in 3.1 it's
NAVUS3.01.02.

It's risky to have objects of item costing of 3.60 in 3.1.
I would only recommend updating to lasted service pack of 3.1 not merging versions. :?

Savatage

Inventory to G/L Reconcile NAVUS3.01.02
Inventory Valuation NAVUS3.10.02.04
Adjust Cost NAVW13.00

These are the exact ones we use in one of our offices.

3.10B with 3.6 executables

I'm sure we have added
hotfix 19 (I think this was for the costs if I remeber correctly)
hotfix 23 (to fix the Error 1355 in Module 19 problem)

Here's all I still have regarding hotfix 23 if you're interested??
http://savatage99.googlepages.com/ChangesinHotfix23.doc
http://savatage99.googlepages.com/Relea ... sionAt.doc

Alex_Chow

At this point, it doesn't matter what version you're using. What's done is done.

The important thing is to get the numbers so the auditors can have confidence on what you're reporting to them.

Contact your local solution center and have them get someone out there to address this for you. If you're not comfortable with your current partner, contact Microsoft or use the Solution Finder on the Microsoft website.

David_Singleton

synpartners wrote:

We use FIFO costing. As for versions. We're running 3.6 client, but most of our objects appear to be 3.1 with the exception of some banking stuff that shows 3.6 with mods by our implementor. Here is some version info...

Inventory to G/L Reconcile NAVUS3.01.02
Inventory Valuation (doesn't match G/L) NAVUS3.10.02.04
Inventory Valution (matches G/L and customized to work in 3.6) NAVNA4.00
Adjust Cost NAVW13.00

Now, you'll notice that in my very first post, I mentioned that originally our Inventory Valuation report didn't match our G/L Reconcile report. After doing some research here, I was able to narrow the problem down to the 'AdjustItemLedgEntryToAsOfDate' function that was different between the two versions of the report. I'm thinking that the differences in this function may help to give some clues into our problem. Under the NAVUS3.10.02.04 version of the Inventory Valuation Report, we get zero quantity and zero value but the G/L reconcile shows a valuation of $13,820.46. With NAVNA4.00 version the valuation shows zero quantity with a valuation that matches the G/L reconcile. If it may help, I can paste up the two different versions of the 'AdjustItemLedgEntryToAsOfDate' function.

Thanks again for your help.

Well the good news is that FIFO can almost always be fixed. As you know now by the replies, you need to resolve the object miss matches that you have, but that is more of a long term issue. In the short term you need to a/ get actually and trustworthy inventory numbers, and b/ fix the data so that you can continue to report accurately.

There were a lot of inventory costing fixes to 3.1 (your version), but these were primarily related to Lot numbers and Average costing. So unless you have lot numbers (or serial numbers) AND use manufacturing, I don't think the hot fixes will help you too much. For sure it may help some of the reports, but I don't think it will address your key data issues. (I think there was also one for sales Return costing, so maybe if you had a lot of returns that might be an issue).

It would be very help full if you could find out from your partner what "fixes and mods" they did to the data. Most specifically did they actually manually insert new POSITIVE Value entries into the system, or did they modify and positive entries.

Generally FIFO costing in Navision is rock solid, it just needs to be done right. Manual adjustments, and wrongly handled returns can be problematic, but can normally be repaired. There are different levels of repair in this case.

A worst case scenario, (which I have only ever done twice) involved deleting ALL GL Entries attached to item/value entries, deleting all values from the item/value entries, and then rebuilding the whole thing. That was extreme, and I doubt you will need to do that. Often the simplest fi is simply to remove all inventory with a Physical journal, and then reenter with correct values. Whilst this often works, I don't like it, since in most cases, the problem just comes back in 6 months time, and you have to do it all over again.

Keep in mind that the adjust cost routine and the Post Cost to GL routines are self correcting, so if you can find the original cost of the POSITIVE ledger entries, you can normally then fix these with a routine, and then run the adjustment routines, and they will self correct. Of course to do that you need to fix the source of the costing errors first.

In many cases I have seen that the fixes made to a system were made unnecessarily. i.e. the "developer" did not really understand why the costing was not what they expected, so the "fixed it" but then Navision went and did its adjustments and fixed the fix, often doubling the error. In these cases, its often just necessary to remove the fixes, and let Navision look after it self.

On the topic of dates, the solution would be to add a new date field that respects forward posting, and use this for reporting. That gets you to todays numbers, and going forward, you modify certain posting routines to block any back dating of purchases and positive adjustments.

Again, in FIFO, negative entries are not all that important, since Navision will adjust them automatically, so if you are looking at detail, start at the beginning, i.e. the first transaction where that FIFO layer came into stock.

By the way, look out for any chains, where you purchased and item, sold it, returned it, BUT didn't post the return credit memo, transfered it to a different location and then sold and invoiced it again. These can mess up FIFO costing. And its a ver. common scenario where you have a RGA or RETURNS stock location separate to your main location.

Anyway, these are just a lot of differnt issues you can start looking at. Without having the actual database its impossible to resolve the exact issue, but this should give you some good starter points where you can then start asking more specific questions.

hant

Dear all,

I have the same issue of zero quantity and amount is not and almost your suggestion is run Adjust Cost/Item entry and Revaluation Journal. My question is how can we use Revaluation Journal with quantity = 0?

Thanks,

hant