Floating cost prices in Navision / Business Central

Nav123: Navision, Showare, OrderApp

Print Friendly, PDF & Email

Estimated reading time: 12 minutes

Why do you need a stock regulation? Why is it so slow? Why can't you just correct the cost price once it changes? I've been asking myself the same question ever since inventory regulation was introduced and made worse and worse.

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

Sure, the first answer: Because it's Microsoft. True. OK, the second answer: Because people tend to prefer adding something more complicated to something more complicated. This is what I experience in every single project.
But let's get to the factually correct, if not 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 takes a lot of database transactions to do it - Posting and paying takes more time.
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 Cost Price later not just change. And any price correction requires a lot of effort over the cost price correction. The inventory disposal method cannot be corrected at all. Microsoft recommends here in all seriousness to replace this item with a new item that has been set up correctly.

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

Since 1993, I have had only one customer - among well over a hundred project customers - who wanted exactly this contribution margin calculation. Even a lengthy discussion that he was deluding himself with an accuracy that he could not achieve was of no use. He understood my calculation example and had to agree with me - but he insisted on a correct application of the inventory control - WITH a simple correction of the cost price. He then got the desired function from me - and a final invoice with completion of the project. I like to earn money - but not with nonsensical activities.

I found the customer's reasoning for this insanity particularly remarkable, and must therefore share it here. The field and inside sales people should determine exactly how much you have earned with a particular order. Especially and precisely because these people could select different input batches specifically for an explicit sales transaction. And each batch should show an item-specific contribution margin at the end, i.e. even after cost prices have changed subsequently. Sounds clever at first and therefore commendable. Or? But what is the consequence? Of course! The sales staff naturally selected exactly those batches which enabled the most favorable sales price and/or the highest contribution margin. The other, too expensive purchased batches were simply not booked. Subsequently, of course, new purchases were also made once in a while, if these promised more favorable procurement costs than the articles 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 not! In the warehouse, the products were not recorded and/or managed in batches. It was sold what was just picked. At least for most products. The cent-exact contribution margin calculation was a bulwark of complexity, welded with a process-fixed self-deception. And worst of all, the customer agreed with me. He had to agree with me after we did a warehouse inspection and asked the warehouse manager about the issue.
Simple is good. And: Simple is practically always better. That's why it was so easy for me to part with this customer after this project.

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 exactly this calculation of purchase price I build back into your Navision on request. The only directly visible change you get is a new field in the article card: Moving purchase price.

This moving cost price can now be corrected at any time as you expect or perhaps are used to from your old Navision. You don't need any stock revaluation, no stock adjustment. In the sales line, this value is automatically drawn here as the cost price. Alternatively, if you wish, the cost price (fixed) if filled in, and only if this is empty, the moving cost price.

Cost of goods purchased, incidental costs of purchase

Since about the logical 2009 version, there are the item surcharges and item discounts. These are exactly made to include copper surcharges, raw material surcharges and other costs of goods issue in the cost price in sales, and thereby reduce the contribution margin of an article.
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 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

Especially for overseas business, additional procurement costs arrive late, such as freight & customs.

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.