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.

 


Comments (0)

Skip to main content