In Dynamics 365 for Finance & Operations (D365 F&O), in the Cost Price section of Item Model Groups, there's a checkbox to Include physical value. Selecting it can have a significant impact on cost of goods sold (COGS) and margins. I myself have seen Include physical value produce unexpected adjustments to COGS when inventory was closed. At first, I thought the adjustments were wrong—but as I examined more closely, they were indeed correct. The initial postings were at significantly lower cost because Include physical value was enabled without an understanding of its impact.
The option's effect depends on the inventory valuation method. For instance, for standard cost or moving average, there's no impact. But for other available methods, with some differences, the effect of Include physical value can be meaningful.
This blog will explain how the Include physical value parameter works and what you should consider before enabling it. Please note there are other parameters in Dynamics 365 F&O that impact product costing I won't be covering in this post.
We'll start by reviewing some relevant concepts in Dynamics 365 F&O.
The first is physical and financial update on all inventory-related transactions. The concept supports a common scenario where the cost of the item is not known when physically received. The inventory is now available, and the inventory transaction is updated with a physical value which could be considered a best guess of its correct inventory value for financial statement purposes. Similarly, when inventory is moved to work-in-progress or physically shipped to the customer, the inventory transaction is updated with a physical value based on a running average. Examples of physical updates include:
The method used to calculate the physical value depends on the type of transaction and a few other parameters. For example, the purchase receipt is valued based on the purchase order line amount and any applicable charges. The production order receipt is valued based on either estimated value or the current cost price.
The posting of the purchase invoice or sales invoice, or ending of a production order, updates the inventory transaction with the financial value, which is calculated based on all applicable cost components. The difference between physical and financial value reflects the purchase price, purchase charges, bill of materials consumption and route consumption after the physical update.
Running average calculation is a concept in D365 F&O that supports estimating the value of outgoing inventory. The running average is calculated per item in the background based on available inventory.
When Include physical value is enabled, the transactions that are physically updated are included in the running average calculation:
Estimated price = (Physical amount + Financial amount) ÷ (Physical quantity + Financial quantity)
When the option is disabled, only financially updated transactions are included in the calculation:
Estimated price = Financial amount ÷ Financial quantity
The Include physical value parameter has the same impact on running average calculations for these inventory models:
Inventory closing is part of the month-end process to finalize the inventory valuation for the period and prevents posting of additional inventory-related transactions. Outgoing transactions are settled by the system against the receipts, and required adjustments are posted in the subledger and general ledger based on the applicable inventory model. The settlement creates a link between the transactions to exclude them from future inventory closings.
All transactions that are not financially updated are always excluded from the inventory closing when the inventory valuation method is weighted average or weighted average date. When the inventory valuation method is FIFO, LIFO or LIFO date, transactions updated only physically are included in the closing if Include physical value is selected.
There are two main criteria for deciding to select Include physical value:
(1) Are there major differences between physical value and financial value on product receipts?
The physical value is meant to be a best guess for inventory valuation purposes at the time of physical receipt. If the physical value does represent a best guess, Include physical value should be enabled in Dynamics 365.
If the physical value is not a good representation of the final cost price and the option is enabled, the outbound transactions (sales COGS and production WIP) are costed incorrectly. Typically, all purchase invoices are posted, and production orders are ended, before the period close. Generally, inventory value is not final until then. However, COGS and sales margins are often used during the period in reports and for KPIs.
Let's use an example that shows the impact of choosing Include physical value. Product A has a starting inventory of 10 pieces at @ $22.50 each. The starting inventory is financially updated. A new purchase order is issued for 10 pieces @ $15. The supplier ships the goods and the product receipt is recorded by a warehouse worker. Shipping and handling is a significant component of the landed cost of this product, and it is invoiced by a third party. The logistics department adds the actual shipping charges to the purchase order lines before the invoice is posted. In this case, shipping and handling adds up to $7 per piece.
At the time of physical receipt, the shipping charges are not known, and the physical value is based only on the purchase price, which was $15 per piece. The running average is impacted by a physical value that does not represent the actual cost, so COGS is understated. The adjustment at inventory closing results in a jump in COGS, and a sudden drop in gross margin of sold products.
If Include physical value is not selected, only financially updated receipt would be included in the calculation of the running average. The COGS would be more accurate during the period. While there would be an adjustment to the COGS when inventory was closed, it would not be as significant.
When inventory is closed, the COGS which was posted @ $18.75 is adjusted. The adjustment depends on the inventory valuation method.
FIFO: Cost adjustment is $3.75/pc >>> the margin drops from 41.4% to 29.7%
LIFO: Cost adjustment is $3.25/pc >>> the margin drops from 41.4% to 31.3%.
Weighted average: Cost adjustment is $3.50/pc >>> the margin drops from 41.4% to 30.5%.
(2) Is the inventory consumed before the receipt is financially updated?
The second question for deciding whether Include physical value should be enabled in Dynamics 365 entails physical consumption prior to receipt financial updating. It is common that inventory is physically consumed prior to the receipt being financially updated, most usually in drop shipments or cross-docking practices. Other times a supplier may invoice only once per month for all deliveries made during it. When it is common and Include physical value is disabled, the running average calculation can often result in zero cost, because the quantity being used in the calculation is zero or negative. The fallback in this scenario is the cost price defined on the item master. In some cases, the physical value may be more accurate than the cost price on the item master, and so it is better to enable Include physical value.
In the example below, Include physical value is disabled, and the item master has a cost price of $16 per piece. At the time of invoicing the sales order, the running average would result in zero cost and therefore the cost price on the item master is used.
When inventory is closed, the financial value of the sales invoice (COGS) is adjusted using the applicable inventory valuation method.
Use Include physical value when the inventory is often consumed before the receipt is financially updated and the physical value of the product receipt is more accurate than the cost price maintained on the item master.
Just as a reminder, the Include physical value parameter is maintained on the Item Model Group which is assigned to a product. The design provides the flexibility to enable Include physical value on some items while disabling it on others.
Discover more tips & tricks for Dynamics 365 for Finance & Operations with Armanino's team of D365 experts and through the Armanino Dynamics blog.
