Dynamics 365 for Finance and Operations Inventory Cost Model, Part 1: Core Concepts

This week I was encouraged by fellow networker, Wali Ullah Khan to share my insights on the inventory cost model in Dynamics 365 for Finance and Operations. On reflection, I agree that this topic is probably of interest to a quite a few people. Since this topic is relatively broad and complex, I have decided to divide my post into five parts, namely:

• Part 1: Core concepts.
• Part 2: Configuration.
• Part 3: Cost management.
• Part 4: Landed cost.
• Part 5: Cost controlling.

The core inventory cost model has actually changed surprisingly little since the days of Damgaard Axapa 1.0. Actually, some of the core concepts of the inventory cost model go all the way back to Damgaard XAL. I have worked with this core model since 1995 (in Concorde XAL), but even so, I will not pretend to know everything, so if I get something wrong, please feel free to correct me. But this I know: we still have an inventory cost model that is tightly coupled to the application logic and data model.

In the following, I have tried to explain some of the core concepts in the D365FO inventory cost model.

Inventory Model

A released product in D365FO is associated with an inventory model through the Item model group. The inventory model determines how the inventory value for that product is calculated. D365FO supports a number of different inventory models, but some common scenarios are:

• Traded products = FIFO.
• Manufactured products = Standard cost.
• Retail merchandise = Moving average.

Normally, the inventory model for a product is determined by the company’s accounting policy.

The inventory model for a released product is fixed for the entire legal entity, so it is not possible to change it by site or warehouse.

Inventory Transactions

The whole inventory costing system in D365FO revolves around the inventory transactions. Every time a product is bought, sold, manufactured, shipped etc. the system creates an inventory transaction (or updates an existing one). The same transaction may have a different status depending on where it is in the life-cycle. Some of these status values do not influence inventory costing, but some do. These are:

• Received = The goods have been received and put away in the warehouse. Normally, this is referred to as goods-received-not-invoiced (GRNI).
• Purchased = An invoice for the goods has been received and posted.
• Deducted = The goods have been shipped to the customer, but not yet invoiced. Normally, this is referred to as goods-shipped-not-invoiced (GSNI).
• Sold = The goods have been invoiced to the customer.

All other inventory transaction status value do not influence the cost.

Physical and Financial Value

Another important aspect of the inventory cost model in D365FO is the distinction between physical and financial value. Physical value is the value of goods received (status = Received) or goods shipped (status = Deducted). Correspondingly, financial value relates to status Purchases and Sold respectively. Historically, physical value was sometimes referred to as “floating value” in earlier versions (maybe it still is on some reports).

As the following screenshot shows, physical and financial value is recorded directly on the inventory transaction together with the physical and financial dates.

Average Value Issue

As the above example shows, the financial cost value for these goods is 188,790.00. However, depending on the product’s inventory model (see above), this may change when the inventory is closed (see below) on a periodic basis. If the product’s inventory model is set to Standard cost, the cost value is fixed and will not change when the inventory is closed. However, if a dynamic principle such as FIFO or Weighted average is chosen, the financial value may change. The change will be recorded in the Adjustment field on the transaction.

Closing

Ah yes, this lovely feature has been with us since Concorde XAL, so I guess it must be celebrating its silver jubilee. To calculate the true inventory cost value of products, you must close the inventory on a regular basis.

This also brings us to a very singular core concept of D365FO (and all its predecessors): issue at average value. When creating an outbound inventory transaction, for instance by creating a sales order, the inventory will not get a financial cost value until you invoice the sales order line. The invoice routine finds the cost value for the transaction by looking up the average cost value for the set of dimensions associated with the transaction. Let’s make an example:

Product 51B66 has a cost price €100 per piece. The storage dimensions for 51B66 are set to site and warehouse.

On warehouse WEST the company holds 50 pieces and the total cost value is €5,100. Therefore the average cost value for warehouse WEST is €102.

On warehouse EAST the company holds 25 pieces and the total cost value is €2,450. Therefore the average cost value for warehouse WEST id €98.

If the user invoices a transactions on warehouse WEST, the system will use €102 per item as cost of goods sold (COGS). If instead, the user invoices a transaction on warehouse EAST, the COGS is €98. In part 2 of this series, we will explore how to set up storage dimensions for inventory costing in more details.

As the following screenshot shows, you can always find the average cost price in the Cost price field on the On-hand inventory screen:

When closing the inventory, the system will recalculate the value for alle open inventory transactions. Open inventory means (financially) outbound inventory, which has not been matched with (financially) inbound inventory. If the product inventory model is FIFO, it will, as it says on the tin, calculate the cost value using a FIFO algorithm.

This calculation creates settlements. These settlements are in effect an audit trail for each inventory transaction that shows, in detail, how the inventory cost value is calculated. The accumulated adjustment in cost value is stored in the Adjustment field on the inventory transaction itself. We will be looking more into settlements and cost exploration in part 3.

Once an outbound inventory transaction has been fully settled, it is closed and the cost value is fixed (at last).

Charges

All inventory transactions with financial value will have a corresponding entry in the general ledger. In part 2 we will be exploring the relationship between the inventory module and the general ledger. However, a core concept worth highlighting at this stage is this: if you would like to adjust the inventory value, it is done through the inventory module – not by making manual postings to the general ledger accounts. At least, if you need to make manual adjustments, create a separate adjustment account and leave the automated postings on accounts where manual postings are not allowed.

As a principle, you can only influence the cost value on an outbound inventory transaction by changing the cost value of the matching inbound inventory transaction.

So how do you do that? Mainly by applying charges to the inbound transaction. Charges can be applied in a number of ways, but normally they are applied on the purchase order and subsequently on the supplier invoice. Charges are fully configurable and can be applied automatically or manually. Examples of common charges are freight and duty. However, an automated (percentage) charge for warehouse overheads can also be applied and included in the inventory cost value.

It is worth noting that the cost value on the inventory transaction is always in the base currency for the legal entity. Therefore, exchange change fluctuations can be quite hard to carry over into the inventory value.

Standard Costing

Since standard costing is probably the most straightforward inventory cost concept in D365FO I will not be dealing with it in this series. In future, I will dedicate a separate series on this topic instead. Stay tuned…

Closing remarks

In the above, I have tried to introduce some core inventory cost model concepts that apply to D365FO. In the many projects I have been involved with, the concept most people struggle with is this: the fact that COGS is not fixed when you invoice the customer. In part 3 we will discuss how to mitigate this issue as far as possible. Also, many people find reconciling inventory and the general ledger difficult. Again, if best practice is applied when setting up the solution, it should not be too bad. We will be taking a look at this in part 2 and 5 of this series.

A word of caution to end this first post in the series: if this goes wrong, it is usually very difficult to reconcile the ledgers and get back on the right track due to the sheer volume of transactions generated by the system. In past projects, I have always tried to make sure we would simulate an inventory closing procedure with reconciliation to iron out any issues before going live. It is definitely worth the effort.