Floating cost prices in Navision / Business Central

Nav123: Navision, Showare, OrderApp

Estimated reading time: 13 minutes

Why do you need a stock adjustment? Why is it so slow? Why can't you just correct the purchase price once it has changed? I have been asking myself this question ever since Microsoft introduced the Stock regulation in Navision Dynamics Attain and Microsoft Business Central BC365 and made it worse and worse. By the way, it did not exist in Navision Financials up to version 4, but essentially the logic described below. When programmers without commercial knowledge program something...

Why has Navision / Business Central's cost price calculation become so complicated?

The first answer is, of course, because it's Microsoft. Right. OK. The second answer is because people tend to add something even more complicated to something complicated. This is what I experience in every single project. Dies wurde auch schon wissenschaftlich untersucht (hier die Studie)
But let's move on to the factually correct, if no longer satisfactory, answer: because a few customers wanted it that way.
The task: We order an item on 1.1.xx at 10 Euro. We get the article delivered on 1.2.xx. We sell the article on 15.2.xx for 30 Euro. We receive the purchase invoice for the article on31.3.xx for 15 Euro.
The sales order from 15.2.xx has now been sold with a cost price of 10 Euro and a margin of 20 Euro.

In the financial accounting, of course, everything continues to be completely OK, since the expenses and the income are all collected completely correctly. Financial accounting is always the highest level of truth.
But in the inventory management we now have a sales transaction with a DB of 20 euros, where in reality only a cover of 15 euros was earned.

The bottom line is that it doesn't hurt anyone. The secret is the moving cost price, often called the average cost or blended price. You simply take the old inventory at the old cost price, add in the inventory value being added, and divide by the new total inventory. Voila: You have a moving cost price that follows the real cost price very well. Sometimes it is a little lower, sometimes a little higher, but on average, it fits quite well.

This was not enough for a few customers. These customers wanted the sales transaction described above to be correctly managed with a cost price of 15 euros instead of 10 euros. The sales transaction, which had actually been completed a long time ago, therefore had to be corrected after the purchase invoice arrived - it was regulated.

This is exactly what warehouse regulation does: it checks sales transactions for the correctness of the cost price used there in each case, and corrects the cost price of a sales transaction if necessary.
This is as complicated as it sounds – and requires a large number of database transactions. Posting and regulation take significantly more time. With some versions of Navision, it was only necessary to invest in more powerful hardware and/or carry out this inventory regulation, for example, at the weekend. The general recommendation at the


And because this is so complicated, you can't mess with Navision in between. The fields Stock Disposal Method (with the options FiFo (First In First Out, e.g. milk cartons that are refilled nice and neat from the back), Lifo (Last In First Out, e.g. sand in a pile, or milk cartons that are simply refilled from the front), Average (e.g. the gasoline in the underground tank at the gas station) and the acquisition price later no longer simply change. And every price adjustment requires a lot of effort via the acquisition price correction. The inventory method cannot be corrected at all. Microsoft seriously recommends replacing this article with a new article that has been set up correctly.

What do you need this "exact" cost price calculation for?

Since 1993, I have only had a single customer who wanted exactly this contribution margin calculation, out of well over a hundred project customers. Even a lengthy discussion, in which I explained that he would be fooling himself into thinking he could achieve a level of accuracy that he couldn't actually achieve, was to no avail. He had understood my calculation example and had to agree with me – but he continued to insist on a correct application of the Stock regulationWITH a simple adjustment to the cost price. Both functions together avoided each other in his Navision version, and could only be combined with a great deal of effort. I like to make money – but not with nonsensical activities. We could have had the required solution in a much simpler way. But it absolutely had to be done through the stock regulation, and that was simply nonsensical. I gave him the desired function – and a final invoice with the completion of the project.

I found the customer's reason for this madness particularly remarkable, and must therefore repeat it here. The field staff and internal sales staff were supposed to determine exactly how much they earned from a particular order. In particular, and precisely because these people were able to select different input batches specifically for an explicit sales process. And each batch should show a precise contribution margin at the end, even after the cost prices have changed. Sounds clever and commendable at first. Right? But what are the consequences? Of course! The sales staff have naturally selected exactly those batches that allowed the cheapest sale price and/or the highest contribution margin. The other batches, purchased at too high a price, were simply not posted. As a consequence, new purchases were of course made if these promised lower procurement costs than the items still in stock. Inventories were thus ramped up,, and inventory capital commitment increased..
Did the sales behavior logically mapped in Navision or here Business Central correspond to the physical stock movement? Of course it did not! In the warehouse, the products were not recorded and/or managed by batch size. What was picked was sold, at least for most products. The cent-accurate calculation of the contribution margin was a bulwark of complexity, welded to a process-fixed self-deception. And the worst thing was that the customer agreed with me. He had to agree with me after we did a warehouse inspection and asked the warehouse manager about this topic.
Einfach ist gut. Und: Einfach ist praktisch immer besser. Deshalb war es auch so einfach für mich, Mich von diesem Kunden nach diesem sehr speziellen Projekt zu trennen.

How Navision's cost price calculation can become incredibly simple again

You forget all this hocus pocus, and install - in simple terms - the purchase price of the "Native Navison", also called Classic Client. This made the cost price calculation until about 2003 as simple as described above.


(Old inventory * Old purchase price) + (Receipts * New purchase price)
===============================================
New inventory

And it is precisely this cost price calculation that I will reinstall in your Navision Financials / Dynamics Attain or Microsoft Business Central BC365 on request. The only directly visible change you will see is a new field in the item card: Moving Cost Price.

This moving cost price can now be corrected at any time as you would expect or as you may be used to from your old Navision or from another previous merchandise management system. You do not need a new inventory valuation or inventory adjustment. In the sales line, this value is automatically used as the cost price. Alternatively, if you wish, the cost price (fixed) is used if filled in, and only if it is empty, the moving cost price is used.

Cost of goods purchased, incidental costs of purchase

Since the logical 2009 version, there have been item surcharges and item discounts. These are designed to include copper surcharges, raw material surcharges and other costs of delivery in the cost price when selling, thereby reducing the contribution margin of an item. Or, for example, to increase it in the case of discounts.
The same function is also available in Purchasing, and is designed to include the cost of goods purchased, freight costs, etc. for items in Purchasing, and to increase the cost price here as well.
This is, typical for Microsoft, incredibly complicated solution, and was initially also full of errors. In the Navision / Business Central versions from about Nav 2015 onwards, this works very reliably, but it is also good to use in the previous versions, as long as nothing goes wrong (invoice corrections, etc.).

What are goods purchase costs?

Mainly freight costs, for example. But also customs duties, warehouse costs, storage costs in general are part of it. Specialists like copper surcharges or material surcharges in general are again extra specialties. But especially copper surcharges can be entered optimally with the line type "Surcharge/Discount (Article)" in the standard Navision, and are therefore excluded here.

Recording of procurement costs

Generally speaking, the purchase costs in most companies differ into 2 types:

  • Direct purchase costs. These can be directly assigned to a purchase invoice. Postage costs are such a classic: When the goods arrive in the warehouse, the exact postage costs are already known. This is the simplest variant, and could even be entered with a little effort or customization with the line type "Addition/Discount (Item)" in standard Navision. Likewise, as mentioned above, copper surcharges and other material surcharges. However, especially individual collective items such as postage can also be recorded more simply with the reference cost function described here.
  • Downstream procurement costs. Freight and customs duties are the classics here. The goods have been in the warehouse for a long time, the purchase invoice has been paid for a long time, and then there are freight costs or customs duties. These should of course also be included in the cost price of the purchased item, and ideally also change the contribution margins of the customer invoices that have been invoiced in the meantime.
    "Actually", this is exactly the domain of the stock regulation. But somehow it has never really worked. And whether it will ever work properly? I don't know. But the bearing regulation definitely causes a lot of problems. It can and has been able to do that from day one.

This is where my solution comes in, especially for the native Navision versions up to version 2009R2. If you need to work on this logic for your RTC Navision (Navision 2013 to Navision Dynamics / Microsoft Business Central 365) or your BC 365 solution, please contact me..

Recording of direct goods purchase incidental costs (postage)

If you have suppliers who specify fixed delivery costs, e.g. 6.90 € transport cost share (TKA) per order, you can enter this directly in the supplier:

Erfassen von fixen Bezugsnebenkosten pro Lieferant (kreditor)
Entry of fixed delivery costs per supplier (vendor)


Alternatively, you can also enter the delivery costs valid for this transaction directly in the purchase order/purchase invoice for each purchase invoice.

Example of a purchase invoice / purchase order without cost entry. The Cost (MW) field in the header is empty. Cost price ("Unit Cost (LCY)") is equal to the unit cost (excluding line discounts).
Beispiel Bestellung mit fiktiven Bezugsnebenkosten (Porto, Fracht oder Zoll)
Example with 100 Euro cost allocation to cost prices. See the Cost (MW) field in the header. Material value of the order 5,291.1 euros. The items with a lower value also receive only a smaller share of the 100 € (Weighted distribution).

Important: These costs (MW) are only included in the cost price when entered in the field Costs (MW)! They do not increase a final invoice amount for this order or for this vendor! This is a purely statistical cost price increase.

Beispiel Bestellung mit echten Bezugsnebenkosten (Porto, Fracht, Zoll)
Example with actual delivery costs incurred for this order, in this case postage. Of course, this could also be freight or customs. Navision first totals all G/L account lines and all item lines in this document, and then distributes (weighted by value of goods) the determined costs to the recorded goods. This can be done by "Recalculate Cost Price" from the Purchase Order menu, but is also done automatically by Navision when posting the document.
Beispiel Rechnung mit bereits umgelegten/umverteilten echten Bezugsnebenkosten (Porto, Fracht, Zoll)
Result of the cost allocation (purchase cost distribution) from the last screenshot: Navision has distributed the actual 50 Euro freight costs (here postage) to the goods values of the individual materials. The Unit Cost (LCY) column (cost price (MW)) has been increased proportionally according to the value of goods.

Both the distribution of the fictitious additional costs from the field Costs (MW) as well as the distribution of the actual order value/invoice value increasing line amounts are also carried out automatically by Navision with each posting of a document, a "forgetting" of this distribution is impossible. Important: Both types of costs (fictitious costs that does not increase the order value/invoice value as well as real costs that increase the order value/invoice value) can be mixed. It is also possible to enter any number of delivery costs per document. All G/L accounts of a document result in the delivery costs, which are distributed to the goods values of the same document.

This logic operates in

  • Purchase orders
  • Purchase Invoices
  • Purchase collective invoices

Subsequent recording of reference costs, e.g. freight

Particularly in the case of overseas business (container goods), additional procurement costs, such as

There is also a solution for this: you enter a new document by entering the freight, forwarding, customs or other extended procurement costs. To this document you assign the original, now higher valued by the additional costs, via "Add. Cost Document to", you assign the original incoming invoice, which is now to be valuated higher by the additional costs.

Enter subsequent delivery costs.

When posting this document, Navision extrapolates the cost price in the original goods receipt, and also increases the cost price in the affected items.