The running average cost price and serial numbers in D365FO (or AX)

A couple of weeks ago I wrote an article about the 'Running average cost price' in D365FO (or AX). This article has been widely read and I got more responses on this article then regular. Apparently this topic is interesting for a lot of readers.

Therefore in this article I give a follow up on this topic. In this article it goes about the running average cost price again, but now from the focus on working with the running average cost price on the tracking dimension 'Serial number' to see how it works when you track the financial inventory on a deeper level then the item.

To have an example in this article I use this with an item related to FIFO (First in, First out).

Setup on the Tracking dimension groups

To be able to have the inventory valuation method working on a dimension level, you have to select the option 'Financial inventory' on that dimension. In this case I have created a Tracking dimension group 'TD' where I selected this field on the active tracking dimension 'Serial number':

When finished, I have created a new released product with this tracking dimension. Also I have linked this released product to the Item model group FIFO where (not surprising you) the inventory valuation method FIFO will be used:

In this case the FIFO principle (First in, First out) must be used: not on the item transaction level, but on the serial number transaction level.

The transactions:

To make this example I have created purchase transactions for this released product. For each serial number I have created the same transaction values:

(If you don't have the serial number in the transactions form, you can add this dimension via 'Display dimensions' and selecting the tracking dimension 'Serial number'):

So for each serial number the inventory value is as following:

• 2 pieces for $ 8,-
• 3 pieces for $ 22,50
• 5 pieces for $ 25,-
• Total 10 pieces for $ 55,50.

So the running average cost price is $ 5,55 ($ 55,50 / 10). If I open the On-hand form and click on the released item to see the details, this is indeed the running average cost price value presented in D365FO:

Now I am going to sell the items:

• For serial number A I sell 5 pieces
• For serial number B I sell 3 pieces

Based on the running average cost price of $ 5,55 both cost amounts are given to these transactions:

• 5 x $ 5,55 = $ 27,75
• 3 x $ 5,55 = $ 16,65

Inventory closing

As already written in the previous article about the running average cost price, the inventory valuation method FIFO will be used when running the inventory closing.

Based on FIFO (First in, First out) on item level, the first sales order would consume the total cost price of Purchase order 00000127 (because 4 items are purchased here and the sales order has a quantity of 5). But after running the inventory closing, you can see that the settlement of receipt and issues is creating different cost prices on the sales order lines:

And this is the effect of selecting the field 'Financial inventory' on the Tracking dimension 'Serial number'. Based on this option, FIFO isn't calculated for the item in total but FIFO is calculated for all transaction related to one serial number. Therefore the cost price values of the sales orders presented above are based on the following order, the red line presenting serial number A and the blue line presenting serial number B:

(in this situation I could better not post purchase order 00000126 and 00000125 on the same financial date because this can be confusing because the transactions are presented in a different order then the FIFO order, my excuses for that). The calculation is as following now:

• Serial number A: quantity 2 and value $ 8,- used from Purchase order 00000127 and quantity 3 and value $ 15,- used from Purchase order 00000125. In total quantity 5 and value $ 23,-.
• Serial number B: quantity 2 and value $ 8,- used from Purchase order 00000127 and quantity 1 and value $ 5,- used from Purchase order 0000125. In total quantity 3 and value $ 13,-.

Based on the different selling quantities for each serial number, the inventory value for each serial number is also different now. This also means that both serial numbers has a different running average cost price now:

Serial number A has a running average cost price of $ 6,50:

The clarification for this amount:

• Purchase order 00000125: 2 pieces left for $ 10,-
• Purchase order 00000126: 3 pieces for $ 22,50
• Total: 5 pieces for $ 32,50 = ($ 32,50 / 5) $ 6,50

Serial number B has a running average cost price of $ 6,07:

The clarification for this amount:

• Purchase order 00000125: 4 pieces left for $ 20,-
• Purchase order 00000126: 3 pieces for $ 22,50
• Total: 7 pieces for $ 42,50 = ($ 42,50 / 7) $ 6,07142857, rounded in D365FO at $ 6,07.