Simple determination of stock value in Navision / Business Central

What data do I have to add in order to receive a correct storage value?
Why can't Navision simply determine a correct inventory value?
How do I determine a correct inventory / inventory value?
How does Navision or Business Central determine the price?

If you've already browsed my pages here a bit, you probably noticed: I love (and live) Navision. And so little by little also Business Central. But still, there are some things in Navision / Business Central that make you wonder: "Are they crazy? Or am I?" The fact that Microsoft (or the original manufacturer Navision) has not provided any properly set up or prepared access rights since 1993, is one of those points where I really shake my head.

Another is inventory control. What do you have to deal with there !? Inventory regulation. Actual cost prices. Actual purchase prices. Inventory disposal method. "Cost price is regulated". What? Why? Who regulates my cost price here? Are we in communism with state-regulated default prices? And what are these graduation posts? What does the cost price (expected) tell me and what is the difference to the cost price (actual)?

Please also take a look at this article right here, it might also answer one of your questions?

History

“Everything was better in the past”… No, of course it wasn't. Especially with merchandise management systems and financial accounting, the programs / systems have never been easier to use than they are today. But in order to efficiently use the IT resources of the time, some things were simpler and more functional than they are today. For example, the Ø cost price (moving cost price) used to be determined quite simply as follows:

This simple solution was very elegant. For example, an incorrect purchase invoice (e.g. with the wrong unit) could be corrected very easily: Call up the article master, correct the Ø cost price, and everything was fine again.

The stock value was determined accordingly simply: Stock per item x cost price per item, the whole thing added up, and the stock level was ready.

Background

From Microsoft's point of view, this super simple method from earlier had 2 disadvantages:
a) The moving cost price followed the purchase-invoices, not the purchase-deliveries. In the case of additions through production, the sliding cost price was able to adapt to the price development even more slowly. In general, this was not a problem, as the moving cost price averaged over months or over the year was still correct. If it rose (or fell) too slowly, then its descent (or increase) was also delayed. That was more or less true in the year.

It was much more important to correctly incorporate ancillary purchase costs into the cost price. I already have a solution for this, which will be published here soon.

b) There was hardly any practicable way of determining a moving cost price for the past, e.g. as of December 31, 2020. Navision and Business Central have never had problems determining inventory at any given point in time. But an average cost price on any date? Unfortunately, this was not possible with this ingeniously simple solution.

Simply save the old average cost price in the goods item every time you add goods? No. Everyone could understand that, everyone could calculate and understand. It's too easy for Microsoft.

c) If you make it really complicated, then we also include a 3rd "solution"! Problem: A product is sold on 1.2. delivered. Until then, it had a moving (average) cost price of 5 euros. On 2.2. it is sold for 8 euros (delivered & charged). The contribution margin is therefore 3 euros. On 3.2. comes the purchase invoice: 7 euros. The moving cost price in the article master is adjusted accordingly. The sales invoice that has already been posted, however, still saves the original cost price of 5 euros, although the coverage, the contribution margin, has already been reduced.

As mentioned above: It does not matter over a longer period of time, it "averages itself".

Solution Microsoft

Microsoft introduced inventory control, which actually solved the problems mentioned ... at least in theory. In practice, it was unfortunately the case that this approach caused more problems than solutions and continues to do so. If you read this article you will know what I mean.

Solution Thöne

Back to the roots! At least for the inventory valuation, I have therefore created a very simple (!) Report, which simply calculates the same way as before ... in principle.

What is Navision or Business Central doing here now? First of all, determine the stock on the key date (here: July 14th, 21).
Depending on the setting, the purchase price valid on this key date is then taken (from the purchase prices). Or the average cost price from the last 3 purchases before this key date.

"Of course" you can then sort the list as you like, e.g. according to the highest stock value, alphabetically, article type or whatever. If we have to struggle with RDLC, then we want something out of it.

This solution is to be understood as a scaffolding, something must certainly be adapted in detail according to your specifications. But it's just a pragmatic approach for everyone who has been wondering for years whether they will ever get a reliable and verifiable inventory value out of their Navision. Talk to me, if you also want "simple is good". 🙂