The average cost calculation for items is giving erroneous values if quantity is between 0 and 1,has anybody else come across this?
I was just going to post a message on this topic, when I saw yours. I have noticed various issues with Average Cost and Unit Cost computed via the FIFO costing method. (1) It is critical you run Adjust Cost regularily. (2) If the Net Invoiced Quantity is negative, the Unit Cost is not updated when the Average Cost is recalculated. (3) If the Invoiced Quantity goes negative, the Average Cost can be very misleading. Right now, I have an item purchased at prices ranging from $29.55 to $29.97. The Average Cost and the Unit Cost are showing $149. The on-hand quantity is 1, but negative quantities have skewed the calculations. (Negative Invoiced Quantity can occur when you receive and use items before invoicing them.) The Adjust Cost batch job helps correct sometimes. I was hoping someone could explain the rules governing the calculations for Average Cost and Unit Cost when cost and quantity vary a lot.
Do you have the latest updates from Microsoft? They have acknowleded the issue with average costing, and have created a few hotfixes on the issue.
Thanks for the tip … I’ll ask our solutions partner.
Even with the latest (v3.70 with all hotfixes installed) version of Navision, average cost problems remain: 1. A negative on-hand amount as of any date creates average cost values which cannot easily be supported or understood. 2. Posting of inbound transactions will affect the average cost of all outbound transactions on or after the date of the inbound transaction, even if the outbound transactions are “closed” and fully adjusted. 3. The average cost method used by Navision is not periodic or perpetual but is actually “daily periodic.” While there is nothing inherently wrong with this method, is it not commonly recognized by accounting professionals until explained. 4. The Average Cost field on the Item card is not updated with each posting, but only after running adjust cost. Unfortunatly, the Average Cost field is copied to outbound transaction lines (sales order line, sales invoice line, item journal line, etc.) and can easily spread weird numbers throughout the system. 5. I am told, but have not verified, that outside of the North American localization the expected costs are not included in the average cost. 6. Use of the Assembly List - Bill of Materials (in Inventory, not Manufacturing) can often result in corrupt Item Ledger Entry / Value Entry data if Average Cost is used. While average cost behaves slightly differently in v4.00, the core issues above remain. Our best practices for using average cost are as follows: 1. You must prevent inventory from going negative. This can be done via a simple code change to Codeunit 22. This infers that it would not be possible to sell items prior to receiving them, if the quantity on-hand is insufficient to handle the sale. While this can be a significant business work flow restriction, it is critical to the effective use of average cost. 2. You must prevent posting of increase transactions (purchase receipt, manufacturing output, etc.) prior to the date of the most recent outbound transaction. Again, a change to Codeunit 22 will force this issue. 3. If you want to have reasonable sales and customer statistics, you must modify the adjust-cost program to update the unit cost field of the sales line, posted sales invoice line, and posted purch. return line tables. Also, if you want accurate customer profit statistics, the customer ledger entry table must be updated. This is tricky code as one must get to the sales invoice line table via the sales shipment line table, and it is possible to delete this table data after printing the document. We include these modifications whenever average cost is required by our end-users and have eliminated end-user dissatisfaction with the average cost issues. Good luck! - David S. Hutchinson
I’m finding a another situation: the on-hand quantity looks fine since it is adjusted upon receipt. However, since only invoiced quantities affect the Unit Cost, we can consume an item before we’ve paid for it. We end up with a situation where the Value Entries table has a negative Invoice Quantity and negative Cost Amount resulting in skewed Unit Costs. In addition, if you transfer an item in this state (positive on-hand, but negative invoiced quantity), Completely Invoiced in the Item Ledger is not checked leading to problems in the Revaluation Journal. Adjust Cost is not updating these transactions after the invoice is processed. Is there anyway around these problems without customizations?
Just to double-check, you are using the v3.70.A NA (North American) database or a 3.70 NA with all available NA Improvements? The North American localization team improves the average cost from the worldwide product version by including expected costs in the average. Do you have “Expected Cost Posting” set to True in the Inventory Setup? - David
In addition, you have to run the adjust cost process EVERYDAY!!
Yeah, pretty much agree with all the sentiments above, we have had no end of problems trying to get this right and finally solved it by doing a lot of development work which included running the adjust cost item entries routine after each posting, but only for those item no.'s that are on the document, otherwise it would take forever. Basically, it is a nightmare to get right and your average cost will vary wildly all over the shop, the only figures you can really rely on are the adjusted ones of your ledgers. Forget about being able to calculate profit percentages from sales orders, quotes etc, unless you are prepared to do a lot of development work.
Another quirk is that if you backdate an issue, the current average cost is used for the initial transaction but is then then adjusted to the calculated average cost at the posting date during the ACIE/"Post cost to GL periodic runs. (Remember to run both of these daily). This has the interesting effect of causing the overall average cost to be recalculated! In most systems recalculation only occurs as a result of positive entries. A back dated inventory valuation report is also an issue as in 3.7 the GRN value entry is physically modified by the purchase invoice entry. (It’s mostly zeroed). If the GRN and PI are in different periods this can affect the period end balance.
Meint, if I understand you correctly, the cost adjustments to G/L are working fine, this means that you stock valuation will be correct and G/L will reflect this values correctly. The main issue is the Unit Cost on the Item Card, that affects all sales statistics and profit calculations in Sales. Am I getting this correct…[?] Here in Colombia, Average Item Costing is mandatory by law, but so far our few customers have not yet complaint as they are not using profit calculcations in sales… Saludos Nils
Hi Nils, Spot on with that on. On the G/L the figures are always right, but unless you do a lot of development work you really have not got any chance of getting reasonably accurate figures into the original documents like sales orders. The unadjusted unit cost copied from the item card might as well be a completely random figure, it can vary that wildly. Also, Navision does not correct these on the original source entry when the purchase invoice has arrived, so your costs and profit percentages will be all over the shop. We really like to know what sort of profit we will be making on an order and therefore this was a real issue for us. We solved it reasonably well by doing loads of hard core development in the complicated posting and cost calculation code units. Anybody knows if there are any improvements in version 4.0 in this area? Regards Meint
BTW, does anybody know why it is not possible to subscribe to this topic? Regards Meint
Meint, thanks for your clarification. From all your input we have started a couple of testing sessions and in fact the inmediate execution of the cost adjustment process is absolutely necessary and does resolve the most important issue. I guess for the moment I will postpone the sales updating part for later [xx(] I keep on wondering, if partners are resolving this issue, why doesn’t MBS offer a reasonable solution to this obvious issue…
BTW, does anybody know why it is not possible to subscribe to this topic?
I was wondering exactly the same thing [:I] Saludos Nils
Meint, I keep on wondering, if partners are resolving this issue, why doesn’t MBS offer a reasonable solution to this obvious issue…
I think if MBS is going to fix this problem, it’s going to be a complete rewrite of Navision. At least the inventory portion.
That’s what it looks like to me, I can’t see how they solve this problem without extensive re-write of inventory. Does anybody have any experience of how competitive systems tackle this, does Great Plains have a better solution for example? For us, the situation was even more complicated because a large chuck of our sales orders are special orders (i.e. back-to-back to the vendor with a special price) and it took us a lot of development time to write stuff for this. Basically, with Navision if all you are interested in are G/L figures then you are in the clear, if you rely on costs and profit percentages on the sales orders and quotes you will find the standard code does not quite serve your needs. But hey, we all knew we bought an application framework and not a finished product, didn’t we Regards Meint
I think it’s basically boils down to how you explain this to the customer. For me, I basically explained how Navision’s costing works and identified 2 different sets of reports, one for the accounting department, and one for the sales department. The one for the accounting department matches to the G/L, basically using the value entry. This is so they can face the bank, auditors, etc. The ones for the sales department comes from the item ledger. However, the cost will not match the G/L and is subject to change based on the receipt of either the vendor invoice or the freight cost. Basically, these reports are for internal use only. So far, the clients that I’ve came in contact with seem to accept this.