Hi, Can any of the resident costing gurus explain how this works? The online help (3.70 W1) says that if you selected FIFO, LIFO, Specific or Average as your costing method, the contents of the Unit Cost field are updated when the Average Cost field is updated. I created a positive adjustment in an item journal, the same item twice, once for a value of 10 and once for 20. The average price now correctly reads 15 (unless you have implemented hotfix 9 on 3.70, which is a completely different story). If you now create a sales order for one of these items, change the unit cost LCY for this sales line to 20 and post it, the new average cost will become 10, but the unit cost still stays at 15. Subsequently, when you run the Adjust Cost - Item Entries batch, it does synchronise the two fields again, i.e. they both say 10. I understand that as long as you run the batch routine your G/L will always be corrected, but for us it is important that the Unit Cost in the sales line is also correct (profit calculations/warnings etc.). Anybody have any ideas what is going on here, is the help just wrong/incomplete, is it user error (always possible) or does it just not work as expected? Many thanks Meint
Unit cost on the sales line is informational only, it is not intended, nor will it reflect the true cost of sales. As Navision state very very clearly in traing, costing reports are to be run from ledger entries, not posted documents.
David, I fully understand this and we are not using the figures on the sales line for our real results. Our G/L figures are corrected by the Adjust Cost Routine, so in fact we could post the sales order without any Unit Cost at all and it would still be alright. However, sometimes it takes up to three weeks before we receive a vendor invoice and know the exact cost of our goods. In the meantime it would be useful if our salesguys had some idea what profit margin they are likely to make and we could forecast how we are doing as a business (pipeline, margin %, revenue, discounts). We therefore try our very best to make sure the unit cost on the sales line closely resembles the true unit cost, hence my question. Meint
I am not saying i don’t understand the issue,just pointing out how Navision works. I suggest you write a simple mod to update the cost field on the sales line.
Hi Meint. As you are having a look at 3.70 have you looked at the expected cost option? This is intended to post the expected cost through to the GL to show a real-time representation of your overall position, although of course it wil lpost the expected unit cost and yo uwill still be subject to full and any variance posting when hte invoice is received. However this will only affect the GL - any reporting will use the unit cost, and as David says the unit cost is for information purposes.
Hi Steven, Yes we are using the expected cost option and have built an account schedule that gives the user a nice view of what is happening with predicted invoice value and profits. This all works rather well for the financial bods. However, most of our sales guys never see this, they only see the sales quote screen from which they send the customer a pdf file. The quote can then easily be converted into a sales order, saving everybody time. We have built a very nice forecast screen which shows the sales guys all the info they need (number of deals likely to drop in a period, gross margin, margin %, turnover etc.), but it relies on the sales header and line information. It is not 100% accurate and we accept that, but problems like the one described above do not help 
We would change the design to look at G/L postings, but for quotes there are none… I suppose my big question is, in the item table, under which circumstances does the average cost field get transferred to the unit cost field? Obviously, there is always David’s option of updating the cost field in the sales line and we will consider this carefully. Thanks Meint
Hi Meint The only way to do this in reality is to sit down and process every kind of transaction through, noting when the average cost is updated and when the unit cost is updated. Once you have situations where transaction caused you an issue, report it to your NSC - if this is then standard and not modified code tehy can report it back to Microsoft. However, and in all probabailty, this is not an issue, this is the way it works [:(!]. There have been discussions and hotfixes on logs in the Microsoft Navision support system that your NSC should have access to, perhaps they can look at these for particular scenairos. As I am sure you would expect this is a very complex area [:)]
Hi Meint, since you are reporting off average cost, why not just pull it from the item card, instead of the sales line? Just keep in miond what Steven was saying, that average cost is not correct till the receipt is voucherd, since you don’t know the cost till you know the cost.
Hi David and Steven, I haven’t given you the entire story so better do it now. We have built a very powerful forms based forecasting system based on the sales line table. We purposely built it with forms and flowfields so the users could drill down into quotes, orders, returns etc and use the true power of Navision. We added some extra fields onto the sales line table (e.g. gross margin and probability) and it all works swimmingly. However, in order to get an accurate forecast, we had to try and get the unit cost as accurate as possible, hence my original question. We know this is only a prediction, but it still needs to be a reasonably accurate prediction. After all, how can you give a price to your customer if you cannot rely on the buy prices that the system is displaying? If we used reports everything would be easily (and we would use David’s suggestion above), but with flowfields this is slightly more problematic. However, we have now managed to solve the problem by running the adjust cost routine after every sales/purchase posting. It was a bit slow and cumbersome after that, so the developer changed it so it only runs for those items that are on the order. We have now tested it and it works a treat, average costs and unit cost are always in sync and our forecast is very accurate. Once every week we will then run the entire adjust cost routine to pick up anything we might have missed. Both, thanks for your insights, costing is a bit of minefield, we are now happy with our system. Regards Meint
quote:
Originally posted by meintd […If we used reports everything would be easily (and we would use David’s suggestion above), but with flowfields this is slightly more problematic. …
there is no difference programming in forms or reports, anything that can be done on a report can be done on a form. Is this more a license issue than a technical one? If so, just put the code in a report, and call it from the form. Just have your NSC add the report as a variable to the form.
Hi, Today, at a customer, i found myself in trouble exactly because of this. I was using Navision 3.70 and the average cost was correct, however the Unit Cost was always wrong. So I went to v3.60 to chack if there was a diference. It was just the same as 3.70. So I went to version 3.10 and surprise of surprises, the Unit Cost was always the same as the Average Cost. Here is the background for this test: I created a product and configured the “Costing Method” as ‘Average’. Then I purchased (in the same purchase invoice) 200 units at the cost of 100 euros and 200 units at 80 euros. This way i got an average cost of 90 euros. Then I checked the product card and found this: 3.70 and 3.60: Average Cost = 90 Unit cost = 100 Last purchase price = 80 However on 3.10 i got: Average Cost = 90 Unit cost = 90 Last purchase price = 80 And as far as this customer goes, version 3.10 is correct. Now since the sales line is always going for the Unit Cost to calculate the sales margin, and this customer wants to have their sales margin calculated over the Average cost, I have two options: 1. Either ‘correct’ the Unit Cost on the product card to the same amount as the Average Cost or 2. I change the sales line to go for the Average Cost instead of the Unit Cost. In my opinion version 3.10 got it right and version 3.60 and 3.70 have a ‘bug’. Either way, and putting asside all considerations on what is the right way and all that, my problem right now is to determine where on earth do I have to change the code in order to get the same efect as in version 3.10? I can always go for the solution given in one of the post above, but I would like to avoid going that way and understanding what is ‘wrong’ in version 3.60/3.70. Damn, this was the longest post in my life…I guess it should count for two (at least…lol)
Well, we spent loads of time looking at this issue, but it was quite some time ago now which means I can’t remember the full results of our investigations. And a few pints of lager don’t help the cause either… The issue caused us so many headaches it was untrue. These were the exact issues you describe above, e.g. being able to calculate profit reasonably accurately so you can check it when you create a sales order. If I remember correctly we found that the unit cost was updated to be the same as the average cost when we ran the adjust cost routine. So, we made a small change to automatically run the adjust cost routine after posting a sales or a purchase invoice. This obviously took ages to complete so we then made a further enhancement to only run it for those items that were actually on the order which now means it takes a second or two. We then did the same thing for item journals and now we are pretty confident that our unit cost will always be the same as our average cost. If you ask me though, something is seriously wrong here and it is a definate bug unless my understanding of the inventory costing is completely wrong. Sorry I can’t actually give you a more helpful answer because we never got to the bottom of it. BTW, did you try it hotfix 9 on 3.70, this did some weird things on the costing as well and as we are now happy with what our system is doing we decided not to implement it.
Hi Joao, Just curious if you ever got to the bottom of this issue, did you log it with MBS and if so, did you get any answers? Costing seems to be a nightmare for most customers… Regards Meint
Hi MBS-Navision world… Further to the Item Unit Cost/Average Cost debate… Calculation of the Item Average Cost is from the Codeunit 5804 (3.70 Vesion) function CalculateAverageCost. During execution of the batch job Adjust Cost - Item Entries it also calls the above Codeunit but calls the function CalculateAverageInclExpCost. This new function reads the Value Entries in the same way but then goes on to adjust itself with corresponding Item Ledger Entries that haven’t been completely invoiced. This means that for some of our FIFO items we have the Item Unit Cost not matching the Average Cost calculation. Can anyone explain why the need for the separate functions. Cheers David