Understanding the Inventory Value Report in Microsoft Dynamics AX 2012

AX’s Inventory Value Report is a very powerful tool…IF you know how to build it to display the information that your business requires and IF you know how to interpret this information.  So, what is the inventory value report anyways?  The inventory value report is a tool for you to look at the total quantity and value of your inventory posted to the inventory subledger by item number or resource group.  It is a great tool for helping you reconcile inventory value with GL balances, which we will discuss in part two of this series. 

In this post we will focus on setting up Inventory Value Report IDs so that the report will show you exactly what you need to see, no more no less.  It is a good idea to set up several inventory value report IDs to show different information depending on who is running the report and why it is being run. 

Building Inventory Value Report ID’s

1)      Navigate To: Inventory management > Setup > Inventory > Inventory value reports

2)      Click ‘New’ and type in a unique ID and descriptive name.

3)      Expand the General fast tab to choose where the data for the report is coming from:  

·        Date interval: Set if you want your report to default to a pre-defined date interval every time it is run (ex: this year, last year, current period, etc…).  This date interval can be overridden at the time the report is run.

·        Range: Choose whether ‘Posting’ Date or ‘Transaction Time’ is the range for the data

·        Dimension Set: Choose what dimension set you want to run the data for.  For example, you can run the data by Main Account, or by Main Account + Business Unit.

4)      Expand the Columns fast tab to choose what columns you would like to see in your report:

·        Financial positions

o   Inventory: If ticked, report displays the financial and physical quantities and amounts that posted to inventory accounts – explanation of financial and physical quantity/amount below.

-  Inventory: Financial Quantity = physical quantity of on hand inventory (in a unit e.g. EA) that has been financially updated (i.e. invoiced or production ended)

-  Inventory: Financial Amount = dollar amount value of on hand inventory that has been financially updated

-  Inventory: Physical quantity posted = physical quantity of on hand inventory that has been physically updated but not financially updated (i.e. PO Receipt, SO Packing Slip, or Report as Finished posted to ledger)

-  Inventory: Physical amount posted = dollar amount value of on hand inventory that has been physically updated but not financially updated

o   WIP: If ticked, report displays physical quantities and amounts of inventory in WIP status – WIP is anything that has been picked for a production order or reported as finished for a production order, but not yet ended.

o   Deferred COGS and COGS: If ticked, report displays physical quantities and amounts of inventory that should post to Deferred COGS and financial quantities and amounts that should post to COGS. Deferred COGS are physical quantities and amounts because they offset picking list quantities and amounts whereas COGS are financial quantities because they offset invoice quantities and amounts.

o   P&L: Displays the financial amount posted to P&L for inventory.

·        Compare On-hand inventory value to cumulative accounting values: Check this box to print account balances on the bottom of the report for reconciliation purposes – will cover this in more detail in Part 2 of this series.

·        Summarize: If this is ticked, you will not see both inventory physical and inventory financial values, instead the two values will be added together to give and overall inventory quantity and inventory amount.

·        Average Unit Cost: If this is ticked in the last column of your report you will see a value for the average unit cost of each item that has inventory on the report.

·        Print Total: If this is ticked the inventory physical and financial quantities will be added together in an additional column on the report and the inventory physical and financial amounts will be added together in an additional column on the report. (Can only be checked if summarize is not checked.)

·        Inventory Dimensions: Displays a column for each inventory dimension that is ticked– for example if you tick site, you will see separate inventory values for each site. 

·        Resource ID: If view is selected, you will see a column for each resource (i.e. item) in inventory and the value for each dimension tracked, if there is inventory across multiple dimensions a separate row will be displayed for the value in each dimension tracked. If total is selected, a row will display directly beneath showing the total value across all dimensions.

·        Resource Group: If view is selected, you will see a column for each resource group (i.e. item group) and the value of the groups inventory, if there is inventory across multiple dimensions a separate row will be displayed for the value in each dimension tracked.  If total is selected, a row will display directly beneath showing the total value across all dimensions. 

(If none of the Resource ID or Resource Group boxes are checked you will simply get a report with the total value of inventory per dimension.)                                                                                                                                    

5)      Expand the rows tab to choose what rows you want to see on your report:

·        Resource Type: Displays rows for inventory value of each of the resource types that you select.  Material is what is most commonly viewed in this report and is mandatory.

·        Detail Level: This allows you to choose how detailed you want your report to be.  If you select totals you will view only the total value of inventory on the date you run the close for for each resource or resource group.  If you select transactions you will view each transaction for each resource or resource group.  This is not recommended if you are looking at a large range of resources.  Include beginning balance is only available for the transactions level of detail.

Now you have the knowledge and tools to be able to set up your inventory value reports based on what is right for the people in your company.  Maybe someone in finance only wants to see a high level overview of the total value of actual inventory for each resource group, so we would simply set up an inventory value ID that only shows the totals for each resource group.  Maybe your inventory cost controller wants to see the totals for each resource include the values that are in WIP, so we would set up an inventory value report ID that includes totals for each resource and has columns for the value of WIP inventory.  Once the setup for the report IDs has been complete we can now start to run our reports, understand our reports, and utilize them to make our jobs easier.