Report the value of physical locations
Key concepts
Inventory dimension
An inventory dimension can have a Physical value and a Financial value. The setting of the Physical value controls whether a dimension is active for Inventory management and Warehouse management. The setting of the Financial value controls whether a dimension is active for Inventory accounting in Cost management.
Note: An inventory dimension can have an active Physical value but no Financial value. However, it can’t have an active Financial value but no Physical value.
Cost object
The term cost object was introduced in Microsoft Dynamics 365 for Finance and Operations, Enterprise edition. It represents a key concept that is used in the management of business costs. A cost object is an entity that costs and quantities accumulated for. For example, a cost object entity can be either a product or product variants, such as variants for style and color.
A cost object is derived from the item ID and the inventory dimensions that have an active Financial value.
There are three groups of inventory dimensions: product, storage, and tracking. Each inventory dimension can have a set of dimensions associated with it. For each dimension, you can set up the following inventory dimension values.
| Product dimension group | Storage dimension group | Tracking dimension group | |||
| Dimension | Value | Dimension | Value | Dimension | Value |
| Color | Financial by default | Site | Financial by default | Batch | Physical and or Financial |
| Size | Financial by default | Warehouse | Physical and or Financial | Serial | Physical and or Financial |
| Configuration | Financial by default | Location | Physical | Owner | Financial by default |
| Style | Financial by default | Inventory status | Physical | ||
| License plate | Physical | ||||
Example: Define a cost object
When an item is created, a set of inventory dimension groups can be assigned to it. The following table shows how you can define a cost object.
| Product dimension group | Storage dimension group | Tracking dimension group | |||
| Color | Financial by default | Site | Financial by default | ||
| Warehouse | Physical | ||||
In the following example, the cost objects are defined by Item + Color + Site.
Examples:
- Speaker + Black + Site 1
- Speaker + White + Site 1
- Speaker + Black + Site 2
The inventory objects can be used to report the physical quantity at any level in the warehouse that is defined by Item + Color + Site + Warehouse.
Examples:
- Speaker + Black + Site 1 + WH 11
- Speaker + Black + Site 1 + WH 12
- Speaker + White + Site 1 + WH12
It’s crucial that you understand the concepts. The configuration and implementation of these concepts have a significant impact on the whole system, especially in Inventory management and Cost management.
After the configuration is implemented, it’s almost irreversible. Any change will require significant resources and will affect system usage itself.
In the rest of the document, we will use the Speaker item as an example. The inventory valuation method is set to Moving average.
Cost object:
- Speaker + Black + Site 1
Inventory objects:
- Speaker + Black + Site 1 + WH 11
- Speaker + Black + Site 1 + WH 12
After a few transactions have been posted, the following inventory transaction entries are generated in the Inventory subledger.
| Color | Site | Warehouse | Financial date | Reference | Status | Quantity | Cost amount |
| Black | Site 1 | WH11 | 1/1/2017 | Purchase order 01 | Purchased | 1.00 | 10.00 |
| Black | Site 1 | WH12 | 2/1/2017 | Purchase order 02 | Purchased | 2.00 | 26.00 |
| Black | Site 1 | WH11 | 3/1/2017 | Sales order 01 | Sold | -1.00 | -12.00 |
The inventory close job was run as of January 31, 2017. Because the inventory valuation method was Moving average, no adjustments were posted.
As part of the fiscal period–end process, an Inventory value report that shows the ending inventory balance in quantity and value is required. To meet this requirement, the inventory value report framework was introduced. The framework lets you create custom reports by including more data points that depend on the type of business. It also lets you define the level of aggregation for cost objects.
Note: The inventory value report is designed to print only the values per cost object or aggregations of cost objects.
You create an Inventory by cost object report, based on the configuration in the following table.
| FastTab group | Field group | Field | Setup value | Setup value |
| General | Range | Posting date | ||
| Columns | Financial position | Inventory | Yes | |
| Resource ID | View | Yes | ||
| Inventory dimensions | Color | View (Column) | Yes | |
| Inventory dimensions | Site | View (Column) | Yes | |
| Inventory dimensions | Warehouse | View (Column) | Yes | |
| Average unit cost | Calculate average unit cost | Yes | ||
| Rows | Resource type | Materials | Yes | |
| Detail level | Level | Totals |
The report will look like this.
| Resource | Color | Site | Warehouse | Quantity | Value | Avg. unit cost |
| Speaker | Black | 1 | 2.00 | 24.00 | 12.00 |
Note: The Warehouse column will remain blank, because the Speaker item doesn’t have any cost object that includes the Warehouse dimension. Inventory dimension Warehouse is only set to Physical.
View the inventory value by physical location Warehouse
Configure the storage dimension group
To meet the customer’s request, you could configure the storage dimension group differently. In this case, the Warehouse dimension is configured so that it has a Financial value.
| Product dimension group | Storage dimension group | Tracking dimension group | |||
| Color | Default Financial | Site | Default Financial | ||
| Warehouse | Financial | ||||
This configuration affects how the Speaker item is handled by the system. The cost object and inventory object now have the same level of granularity.
Cost objects:
- Speaker + Black + Site 1 + WH 11
- Speaker + Black + Site 1 + WH 12
Inventory objects:
- Speaker + Black + Site 1 + WH 11
- Speaker + Black + Site 1 + WH 12
The configuration also directly affects the inventory valuation. In this example, the FIFO, Weighted average, or Moving average inventory valuation method will be applied per cost object, and the overall result will differ.
| Color | Site | Warehouse | Financial date | Reference | Status | Quantity | Cost amount |
| Black | Site 1 | WH11 | 1/1/2017 | Purchase order 01 | Purchased | 1.00 | 10.00 |
| Black | Site 1 | WH11 | 3/1/2017 | Sales order 01 | Sold | -1.00 | -10.00 |
| Color | Site | Warehouse | Financial date | Reference | Status | Quantity | Cost amount |
| Black | Site 1 | WH12 | 2/1/2017 | Purchase order 02 | Purchased | 2.00 | 26.00 |
The result will also differ when the Inventory value report is run by using the same configuration that is described in the previous section.
| Resource | Color | Site | Warehouse | Quantity | Value | Avg. unit cost |
| Speaker | Black | 1 | WH12 | 2.00 | 26.00 | 13.00 |
The Warehouse column now has a value, and the inventory value is 26.00 instead of 24.00.
Note: When you activate the Financial value for the Warehouse inventory dimension, you might affect performance. All transfers between warehouses are now considered financial movements, and financial movements can cause cycles in the Inventory close job. If the Warehouse inventory dimension is used only to physically track inventory, these will be closed as non-financial transfers before the cost calculation begins and the changes of cycles reduced.
Create a custom report that looks at inventory transactions and settlements.
You can create a custom report that sums inventory transactions and settlements by InventDim ID.
The old Physical inventory by inventory dimension report was designed for that purpose. In the report dialog box, users could select any inventory dimensions, regardless of whether they were part of the defined cost objects.
This approach works, provided that the inventory dimensions that you select are part of the defined cost object. However, if you select an inventory dimension that isn’t part of the cost object, the report starts to print incorrect results.
The following table shows the result of printing balances per item and inventory dimensions.
| Resource | Color | Site | Warehouse | Quantity | Value | Avg. unit cost |
| Speaker | Black | 1 | WH11 | 0.00 | -2.00 | 0.00 |
| Speaker | Black | 1 | WH12 | 2.00 | 26.00 | 13.00 |
Note: The inventory cost is calculated at a level above the inventory dimension and warehouse. Therefore, the cost on issue transaction from warehouse WH11 explains why the inventory value per warehouse can become negative.
Report the value of physical locations
If the value of a physical location must be reported, a sum of transactions per location, as described in the previous section, isn’t the solution.
The correct approach is to calculate the value per location by using the following simple formula:
Value = Cost object, cost × physical object, quantity
Cost object:
- Speaker + Black + Site 1
| Resource | Color | Site | Quantity | Value | Avg. unit cost |
| Speaker | Black | 1 | 2.00 | 24.00 | 12.00 |
Inventory objects:
- Speaker + Black + Site 1 + WH 11
- Speaker + Black + Site 1 + WH 12
| Resource | Color | Site | Warehouse | Quantity | Formula | Value |
| Speaker | Black | 1 | WH11 | 0.00 | 12.00 × 0.00 | 0.00 |
| Speaker | Black | 1 | WH12 | 2.00 | 12.00 × 2.00 | 24.00 |
In Microsoft Dynamics AX 2012 R3, a new report that is named Inventory aging was introduced. As the name of the report implies, this report does more than just report the value by physical location. It can also show the age of the current inventory in the user-defined buckets.
In the report dialog box, enter the following information.
| Field group | Field | Setup value |
| As of date | 31-01-2017 | |
| View | Item number | View |
| Color | View | |
| Site | View | |
| Warehouse | View | |
| Aging period | Unit of aging period | Days |
| Aging period 1 | 30 | |
| Aging period 2 | 60 | |
| Aging period 3 | 90 | |
| Aging period 4 | 120 |
The following table shows only the first section of the Inventory aging report, but the user-defined buckets have been omitted.
| Resource | Color | Site | Warehouse | On-hand Quantity | On-hand Value | Inventory value quantity | Inventory value | Avg. unit cost |
| Speaker | Black | 1 | WH11 | 0.00 | 0.00 | 2.00 | 24.00 | 12.00 |
| Speaker | Black | 1 | WH12 | 2.00 | 24.00 | 2.00 | 24.00 | 12.00 |
Conclusion
If your organization must provide inventory value by any physical location, you don’t have to update the current configuration of inventory dimension groups. This change can be very intrusive, and also affects the cost calculation and performance. We also don’t recommend that you build a custom report for this purpose.
The Inventory aging report is designed to calculate the cost per cost object and then can apply it to any physical level that is selected on the report. This report is designed to automatically detect the level that the cost object is defined at per item. It then applies the formula to calculate the value by physical location.
