Microsoft Dynamics 365 for Finance and Supply Chain Management provides robust inventory management features in terms of both Physical and Financial inventory. Initially, it looks like a little bit confusing due to a lot of voucher lines posted to General ledger but once we understand the concepts it provides a very sensible way to handle inventory valuations. This is part 1 of a series of blog posts to explain Basic inventory setup, Procurement, Production, Production costing, Sales, Inventory journals.
In this article, we will be configuring basic setup required for posting inventory transactions. To make things simpler initially I have not considered Product dimensions for all the articles and demonstrations.
Configure inventory
Item groups
Item group is the grouping of items from a financial perspective. The basic rule is to create as many item groups as inventory accounts required for financial reporting (Trial balance) like Raw materials, Packing materials, Consumables, Semi-finished goods, Finished goods etc. Item group is the integration point between inventory sub-ledger and the General ledger. The integration with General ledger can also be achieved item-wise (Table), category wise (Category) (except inventory journals). The recommended approach for General ledger integration is per Item group basis.
Nagivagte to Inventory management >> Setup >> Inventory >> Item groups or Cost management >> Inventory accounting policies setup >> Item groups to view/create new item groups.
This article does not cover inventory posting profile setup, which will be covered upcoming blog posts covering module wise inventory posting profile setup and financial impacts for various set of setups.
Item model groups
Item model group definition is the most significant setup which determines the behaviour of related items as explained further. Microsoft Dynamics 365 for Finance supports scenarios like raw material 1 is valued as Weighted average, raw material 2 is valued as FIFO and the Finished goods produced from Raw material 1 and Raw material 2 to be valued as per Standard cost. I have explained the most important setups for Item model group important in terms of item behaviour, ledger integration and valuation.
Item model groups can be viewed / created by navigating to the Inventory management >> Setup >> Inventory >> Item model groups or Cost management >> Inventory accounting policies setup >> Item model groups
Stocked product
This setup determines whether the item is stocked or non-stocked. If Stocked product checkbox is checked it is stocked product which instructs the system to maintain on-hand inventory and inventory transactions, but if this parameter is kept un-checked then the related items are treated as non-stocked and on-hand inventory, inventory transactions are not maintained by the system. Stocked product setup determines if the related items are tangible or intangible.
Include physical value
Keeping this parameter checked determines whether the physical updated transaction costs are also considered along with financially updated transactions for updating item cost price (running average cost). It also determines inventory recalculation/closing behaviour for physically updated inventory transactions in case of FIFO, LIFO, LIFO date valuation methods. In the case of Weighted average, Weighted average date, Standard cost and Moving average inventory models it doesn’t make any difference for physically updated inventory transactions during the Inventory recalculation/closing
When include physical value is selected following formula is used to calculate the estimated running average cost price. Sample transactions along with the estimated price after each transaction is shown in the following table.
| Date | Transaction | Quantity | Price | Amount | Estimated price |
|---|---|---|---|---|---|
| 01-11-2020 | Financial | 10 | 10.00 | 100.00 | 10.00 |
| 02-11-2020 | Physical | 15 | 12.00 | 180.00 | 11.20 |
| 02-11-2020 | Financial | -5 | 11.20 | -56.00 | 11.20 |
| 03-11-2020 | Physical | 25 | 13.00 | 325.00 | 12.20 |
| 03-11-2020 | Financial | 100 | 9.00 | 900.00 | 9.99 |
However, when the Include physical value is set to No following formula is used to calculate the estimated running average cost price. Sample transactions along with an Estimated price after each transaction is shown in the following table.
| Date | Transaction | Quantity | Price | Amount | Estimated price |
|---|---|---|---|---|---|
| 01-11-2020 | Financial | 10 | 10.00 | 100.00 | 10.00 |
| 02-11-2020 | Physical | 15 | 12.00 | 180.00 | 10.00 |
| 02-11-2020 | Financial | -5 | 10.00 | -50.00 | 10.00 |
| 03-11-2020 | Physical | 25 | 13.00 | 325.00 | 10.00 |
| 03-11-2020 | Financial | 100 | 9.00 | 900.00 | 9.05 |
Microsoft Dynamics 365 for Finance uses estimated running average cost price to post all the consumption transactions except in case of Standard cost inventory model. However, the consumption inventory transactions posted at estimated running average cost are subject to change when Inventory recalculation or Inventory costing is executed. Having said that the only exception to this statement is when you are using Standard cost or Moving average inventory model for inventory valuation.
The recommendation here is if there is a time lag between physical update and financial update of the inventory transactions then it is better to keep include physical value option selected on the item model group. Moreover, it helps to better smooth out the estimated running average price, which also helps to avoid huge adjustment on issue transactions.
Ledger integration
Following two parameters determine the ledger integration and financial posting. When the Stocked product is not selected both of these parameters are greyed out.
Post physical inventory
Post physical inventory determines whether the corresponding ledger entries are posted in the General ledger module when inventory transactions are physically posted. Different type of physical inventory transactions includes Purchase Product receipt, Sales Packing slip, Production Picking list, Production Report as finished journal. This is a company-wide master setting. There are module-level settings also which determines whether different physical transactions are posted to General ledger or not. See the following sections for configuring module-level physical transaction posting into the ledger.
- Setup Post product receipt and Packing slip in ledger
- Setup Post picking list and Report as finished in ledger
Post financial inventory
Set the Post financial inventory selected if you want to account financial postings to General ledger for each inventory transaction like Purchase invoice, Sales invoice, Movement journals, Inventory adjustment, Counting journals etc. When Post financial inventory is selected, the approach is called perpetual inventory method whereby you can get inventory balances directly from Trial balance.
However, if Post financial inventory is deselected, the approach is called non-perpetual (dis-integrated) method. Firstly, non-perpetual inventory method doesn’t provide inventory balances directly from Trial balance. Secondly, non-perpetual inventory approach do not post Purchase invoices to the inventory account instead entire purchase cost is posted to the Profit and loss account. In the non-perpetual (dis-integrated) inventory method, you have to post a manual journal voucher at each period end to debit the inventory account and credit the Profit and loss account.
Recognition of cost
Post to Deferred Revenue Account on Sales Delivery
If Stocked product parameter is deselected, Post to Deferred Revenue Account on Sales Delivery is greyed out and you can not override it. In case of tangible (stocked) products, this parameter determines whether to post deferred revenue to the General ledger or not.
Accrue liability on product receipt
Accrue liability on product receipt: If Stocked product parameter is set to Yes, Accrue liability on product receipt is greyed out and you can not override it. In the case of intangible products, this parameter determines whether the accrued liability is posted to a general ledger or not when Product receipt is posted.
Inventory model
Microsoft Dynamics 365 for Finance supports following inventory valuation methods. It is very important to note here that except Standard cost and Moving average valuation methods, no other valuation method provides real-time inventory costing as per set valuation method, real-time transactions are always posted with estimated cost price (running average cost) and the actual cost is determined only after Inventory recalculation or Inventory closing. When the Stocked product is deselected inventory model field is greyed out.
- FIFO
- LIFO
- LIFO date
- Weighted avg.
- Weighted avg. date
- Standard cost
- Moving average
Physical negative inventory
If you want to allow item to be consumed even though it is not having any on-hand stock than set Physical negative inventory parameter to Yes. In most of the scenarios, it is not recommended to allow physical negative inventory. For business processes like a continuous supply of liquid/gases through pipelines, you might need to allow Physically negative inventory.
Financial negative inventory
If you want to consume or sell physically updated inventory like Product receipt but not invoice, Production Reported as finished but not ended then enable Financial negative inventory. It is important to note here that in certain cases the inventory balances in General ledger might show as negative if Financial negative inventory is allowed. For typical manufacturing and retail business processes, Financial negative inventory should be allowed.
Storage dimension groups
Storage dimension group definition determines how the physical inventory stored and how the financial inventory valuation is maintained. You can maintain financial inventory valuation maximum up to the storage dimension activated for physical inventory tracking. Microsoft Dynamics 365 for Finance by default tracks financial inventory to site level.
Storage dimension group also determines if the Advanced warehouse management is activated for related items by selecting Use warehouse management processes parameter on Storage dimension group. When advanced warehouse management is activated all the storage dimensions Site, Warehouse, Location, Inventory status and License plate are set to Active and Physical inventory tracking is also enabled automatically. However, it is up to you to which extent you want to track financial inventory by checking respective checkboxes against each storage dimension in Financial inventory column e.g. Site wise valuation or Site-Warehouse wise valuation.
Create or view existing storage dimension groups by navigating to Product information management >> Setup >> Dimension and variant groups >> Storage dimension groups or Cost management >> Inventory accounting policies setup >> Storage dimension groups
I have pasted the screenshot for Storage dimension group definition to track physical inventory by Site, Warehouse and Location wise. It also defines financial inventory valuation to be maintained at the Site level.
Tracking dimension groups
Using tracking dimension group definition you can track inventory by Batch number and/or serial number. Even if you don’t you want to track inventory by Batch number or Serial number a blank tracking dimension group definition needs to be created and linked to the items.
Tracking dimension is used to track physical inventory by Batch number or Serial number or both. At the same time tracking dimension can also enable us to track financial inventory valuations by Batch number or Serial number or both.
Create or view tracking dimension groups at Product information management >> Setup >> Dimension and variant groups >> Tracking dimension groups or Cost management >> Inventory accounting policies setup >> Tracking dimension groups
Setup Post product receipt and Packing slip in ledger
Activate Post product receipt in ledger and Post packing slip in ledger parameters from Cost management >> Inventory accounting policies setup >> Parameters
Alternately, Post product receipt in ledger and Post packing slip in ledger can be enabled from Accounts payable parameters and Accounts receivable parameters form.
Setup Post picking list and Report as finished in ledger
Setup Post picking list in ledger and Post report as finished in ledger from Cost management >> Manufacturing accounting policies setup >> Parameters form.
Setup Sites, Warehouse and Locations
We need at least one site to perform any inventory transactions in a legal entity. Create a Site from Inventory management >> Setup >> Inventory breakdown >> Sites
Each site must have at least one warehouse underneath it in order to perform inventory transactions. Create a Warehouse by navigating to Inventory management >> Setup >> Inventory breakdown >> Warehouses form.
In the scenarios we will be going through we will be tracking inventory up to Location level and hence create one by navigating to Inventory management >> Setup >> Inventory breakdown >> Inventory locations also as part of this process create one inventory aisle.
Please share your thoughts and suggestions in the comment section.
HI Gopal,
I have a question relating to Inventory value report storage. Our inventory value report storage is not reconciled with closing stock balance in Trial balance. When we are working on the differences we found that some Transfer Orders have issues.
The issue in transfer order is……. After creation of transfer order (but before receiving of unit at another warehouse) we found that we write wrong receiving warehouse so we cancel the Transfer order and delete it.
The financial effect of this TO is zero but we found that in inventory value report storage the canceled TO unit is reversed but having zero cost. So now we have quantity in hand but zero value. This result in wrong CGS and also our inventory value report storage is not reconciled with closing stock value in Trial balance. As we are using moving average model. we also held inventory recalculation but still differences in inventory value report and trial balance stock closing value.
When we check the transaction details of TO from Transactions in inventory management module we found that the zero-cost amount in inventory value report storage is appearing in adjustment column.
Can you guide how can we move value from adjustment column to inventory value report? Or how the transaction in inventory value report having zero cost value can be adjusted???.
we are using the following the below mention version
Dax -365
Application release 10.0.18
Hi gopal,
What a useful article. Can’t wait to read part 2.
Great article Where is part 2? 🙂
Thanks!! Yet to come. Lost in other priorities.
Hi Gopal ,
We are using Weighted Average method for inventory valuation & our purchase rates varies warehouse to warehouse ,so we need warehouse wise weighted average cost for same product .Currently system is not calculating weighted average cost and actual PO value showing in COGS which ultimately effect our profitability & inventory valuation .Can you please advise me that what could be wrong in configuration ..? & how can we get weighted average cost warehouse wise ..? We are using serialized inventory & our each product has its unique serial number .
Check setting on storage dimension group if the Financial inventory checkbox for Warehouse line is selected to tell system to calculate warehouse wise weighted average cost. Also based on your input I assume you are using non-perpetual inventory and to confirm check Item model group if the Post financial inventory checkbox is activated.
Gopal ,
In storage dimension group ,financial inventory checkbox for warehouse line is selected. Further, in Item model group post physical and financial inventory both checkboxes are activated . Our consultant told us that as we are using serialized inventory so weighted average will apply on only those products which have same serial number but practically we have validation check on serial number ,only one product can be registered with one serial number.
Kindly advise me what should I do to value costing at weighted average with same serialized inventory option ..?
Further ,if we change serialized inventory option now then what will be the consequences as we have closed period till Mar-2021 and don’t want retrospective effect on our reporting .Consultant told us to open new item codes and remove serial inventory option to get desired result but this option seems impracticable as it will create mess in inventory .
Hi,
To achieve the expected behavior you must deselect Financial inventory checkbox on Tracking dimension group against Serial number row.
Ideally you should create new items since transactions already exists on the existing items.
However can you let me know about the version of Product you are using?
You might try to update Financial inventory on Tracking dimension group from backend in case of AX 2012 or update it through code if you are using D365 F&O after closing your inventory. I recommend testing this in Sandbox environment first. It will not have any retrospective effect.
If we deselect Financial Inventory checkbox on tracking dimension group ,can we have desired result means WA warehouse wise ..? We are using D365
Gopal,
I did as u suggested above , I created new tracking dimension and removed financial inventory checkbox on serial number line then i created new item and attach that tracking dimension against it and tested behavior of cost ,its giving weighted average cost now . Can u please tell me how can apply this tracking group to all old items , is there any way ..? i don’t wanna open new products .
is there any way to edit tracking dimension of products after previous period getting locked ..?
Please Advise
Mention which version of AX you are using.
Dax -365
Application release 10.0.18
Platform release PU42
Hi,
You can’t edit Tracking dimensions once they Re used on transactions from front-end.
Recommended solution here is to removing the stock for all items, do the inventory closing, update the tracking dimension for all the items using customized code and then upload the stock back on same items for same inventory dimensions. However test this properly in UAT first.
More risky solution will be to Close the inventory, update the Financial inventory checkbox on Tracking dimension group using code. This solution needs extensive testing for any unexpected behavior later on.
Hope this helps.
One last thing I want to know that if we go for Moving Average method , the difference arises due to rate differential between PO & Purchase invoice can be adjusted to existing closing inventory rather than charge to Period cost . As u know that in Moving Average ,when we make rate amendments on financial document differential charges to expense account .
Yes, correct. For Moving average valuation method the Financial cost is equal to Physical cost. Any variances later on due to Purchase price variance, adjustment on posted invoices will be expensed out. Also very important point inventory recalculation don’t impact the Moving average items.
Hi Gopal,
I have a query. Could you please le me know:
We are using FIFO model group.
For example, I created a purchase order and received the product with 500/-. During Invoicing, I changed the priced to 450/- and invoiced it with some approval.
Now, on what account that difference amount of 50/- will be posted? I am not seeing that 50/- at Voucher level of Purchase order invoice
Hi,
When you post the purchase invoice the voucher posted at the time of Product receipt is reversed. So, if you see the posting against posting type Cost of purchased materials invoiced you will find that it is Debited with 450 only instead of 500 because you have reduced the price at the time of invoice and on the other hand Vendor account is Credited with 450. Hence if you are using any inventory model other than Standard cost and Moving average the price variance is posted to the inventory itself as part of Purchase invoice. The price variance is posted to Profit and loss account only for inventory models Standard cost and Moving average.
Product receipt voucher:
Cost of purchased materials received Dr. 500
To Purchase, accrual Cr. 500
Purchase invoice voucher
Purchase, accrual Dr. 500
Purchase expenditure for product Dr. 450
To Vendor balance Cr. 450
Cost of purchased materials invoiced Dr. 450
To Cost of purchased materials received Cr. 500
To Purchase expenditure for product Cr. 450
When you review the Inventory transaction for this particular Purchase order line you come to know that the Physical cost amount is 500 (updated when Product receipt is posted) and the Financial cost amount is 450 (updated when Purchase invoice is posted)