At the end of a fiscal period, the balances from subledgers are reconciled with general ledger (GL) values. This is to ensure that the subledger and the GL are synchronized. When there are discrepancies between the subledger and the GL, it’s important to identify the reason for the discrepancies, so that suitable actions to correct the issue can be taken. To help identify the variation in subledger and GL and take further action to reconcile both ledgers, Microsoft Dynamics AX 2012 has the Inventory Value Report.
The Inventory Value Report is a framework with a form for configuration of multiple versions (layouts) of the report to reconcile inventory with GL. Accounting managers and supervisors can use this report to reconcile physical inventory and financial inventory.
Navigate to Inventory Management ? Setup ? Inventory ? Inventory Value Reports or Inventory and Warehouse Management ? Setup ? Costing ? Inventory Value Reports.
Inventory Value Report Configuration
At least one report configuration is required before using the report. The configuration tool allows you to make several report versions. Examples of various configurations:
- All item groups for a “total” reconciliation that only prints the details for each item group.
- Items for a “total” reconciliation that prints the details for each item ID and includes subtotals by item group.
- Each item group that prints the details of each transaction for each item.
- Additional versions that do not include any totals and can export data for Microsoft Excel.
Note: There could be as many configurations as per the business requirements.
1. Inventory Detail by Site, Item, Item group including the inventory values (Financial Quantities, Physical Quantities along with their amounts):
Below: A sample Inventory Value Report
2. Inventory Valuation Method Moving Average:
Below: A sample Inventory Value Report
You can optionally select the “Print cumulative account values for comparison” check box, and then specify the main account to print balances for. If this option is selected, make sure that you carefully specify the main accounts to print. If you use multiple inventory accounts for example, you might need to create several versions of the report, or create a main account of the type total that sums all of the inventory accounts.
Tips for Inventory Value Report configuration:
- General FastTab:
- Date interval: Set to default a pre-defined date interval every time the report is executed (e.g. current year, last year, current period, etc.). This date interval can be overridden at the time the report is executed.
- Range: Choose ‘Posting’ Date or ‘Transaction Time’ 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 + Department
- Columns FastTab for columns required in the report:
- Financial positions:
- Inventory: Check to display the financial, physical quantities and amounts that are posted to inventory accounts.
- Financial quantity is physical quantity of on-hand inventory (in a unit, e.g. LBS, EA) that has been financially updated (i.e. invoiced or production ended).
- Financial amount is monetary Value amount of on-hand inventory that has been financially updated in Company Currency.
- Physical quantity posted is 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).
- Physical amount posted is monetary value of on-hand inventory that has been physically updated but not financially updated.
- WIP 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.
- Deferred COGS and COGS 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.
- Profit & Loss displays the financial amount posted to P&L for inventory.
- Compare On-hand inventory value to cumulative accounting values to print account balances on the bottom of the report for reconciliation purposes
- Summarize to give and total inventory quantity and inventory amount.
- Average Unit Cost for average unit cost of each item that has inventory on the report.
- Print Total 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 selected, for example “site”. Then site-wise value will be displayed.
- Resource ID: To view Item wise column in inventory, 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: Column for each item group and the value of the group’s 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 selected, a report with the total value of inventory per dimension is displayed
- Financial positions:
- Rows FastTab: To select rows to be displayed in the report.
- Resource Type: Displays rows for inventory value of each of the resource types selected. Material is most commonly viewed in this report and is mandatory.
- Detail Level: For the details on report required
- Totals: Total value of inventory on the date of execution with all the closed transactions for each resource or resource group.
- Transactions: Each transaction for each resource or resource group will be displayed
Tricks for Inventory Value Report configuration:
- ‘Weighted average – date’ is a complex valuation method to reconcile because the close process does NOT create ‘Weighted Average Inventory Close’ inventory transactions if there is a matching receipt and issue transaction to close instead of multiple receipts and issues. It can be very tedious to find the last close cost.
- Check the Production Control Parameter ‘Report as finished – Use estimated cost price’ checkbox, otherwise Production Report as finished books physical at the latest cost price from the item.
- If unchecked, the ‘Latest cost’ checkbox will use old cost in case of inventory is consumed in later date and price of the product has varied. The cost of production may not give a correct picture of cost of manufacturing.
- When setting up Storage Dimension groups for Site/Warehouse/Location, consider carefully whether to check the ‘Financial inventory’ checkbox checked. If you do not check the checkboxes, and products are held in inventory for a period of time in different sites/warehouses./locations, Microsoft Dynamics AX groups transactions across sites/warehouses/location together for inventory valuation and close purposes.
- Customer should be billed and the invoices posted immediately after shipment so that physical value of inventory matches with financial values, otherwise it can be a mammoth task to reconcile.