Dynamics AX in the Field

Microsoft Dynamics AX from the Premier Field Engineering team at Microsoft.

Reconciling Inventory to GL in Dynamics AX

Reconciling Inventory to GL in Dynamics AX

Rate This
  • Comments 12

This year at ConvergenceI learned that a low percentage of AX customers are aware that new reports have been developed to assist with the inventory reconciling process. I'm hoping this post will help spread the word. Included in this post is the material I covered in my session at Convergence this year: 'Inventory Costing & Reconciliation Tips & Tricks for  Microsoft Dynamics AX 2009'. Truthfully, the content is not specific to AX 2009. Below are the key takeaways from the session: 

  1. A new 'Inventory Value' report was developed for AX 2012, and is also available for AX 2009.
  1. A new 'Potential Conflicts' report was also developed for AX 2012, and is also available for AX 2009.
  1. Physical Inventory by Item Group report - For AX versions where the new reports are unavailable, we recommend this report for reconciliation against GL values.
  1. Inventory Dimension settings have a significant impact on financial values, and should be considered during reconciliation.
  1. New logic was introduced for inventory transfers. This was developed for AX 2012, but is also available for AX 2009.
  1. Tips and Tricks for reconciliation.

 

Inventory Value Report

The new report is configurable and actually allows you to set up multiple versions of the report. You can use the report framework as a one stop for Inventory, WIP, Deferred COGS, COGS, and Profit&Loss. You could also set up separate reports for each. Each of the 'financial positions' can also be compared to their corresponding GL balance on the report.

 

The report can be run using the date intervals that are used in other areas of the system. This is a change from previous inventory reports, which were limited to using an 'as on' date.

  

Potential Conflicts Report

If the Inventory Value report identifies a difference between GL and inventory values, the new potential conflicts report can be used to identify what caused the imbalance. The report was designed to check for the most common issues that cause discrepancies between GL and Inventory values. 

 

The new reports are included in hotfix rollup 5, and later on AX 2009. If you are on AX 2009 SP1 prior to hotfix rollup 5, you can download the package that includes reports and the non-financial transfers functionality (described later in this post). Here's the link for that download:

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-US;982712

Here is the whitepaper that covers the new reports:

https://mbs.microsoft.com/partnersource/deployment/documentation/whitepapers/ax2009_inventoryreconciliationandreporting.htm.htm?printpage=false

 

Physical Inventory by Item Group Report

This is the report that should be used to reconcile against GL balances if you are an AX build that can not leverage the new inventory value report. The report should be run as 'of' a date that an inventory close was executed' in order to have the most accurate values.

(Inventory > Reports > Status > Physical Inventory)

  • Financial Quantity- financially updated quantity
  • Deducted - physically updated quantity of outflow
  • Received - physically updated quantity of inflow
  • On Hand(Physical Inventory) = sum of Posted Quantity + Deducted + Received
  • Financial value (Inventory Value) = Financially updated amount of Posted Quantity
  • Physical Value not posted (Floating Value) - physically updated quantity * Item master cost price.  Only updated if no transaction in the ledger
  • Physical Value Posted (Posted Physical value )- physically updated item transactions that are associated with the a ledger transaction
  • Known Financial Difference(Known value )- physically updated transactions existed at the AS OF date of the report  - but after that date the transaction was financially updated. 
  • Inventory Value (Estimated Inventory Value) = sum of Financial value + Known value + Physical value  (posted and not posted to GL)

 

Inventory Dimension Considerations

Physical on-hand and financial values can be viewed in AX and in reports at any inventory dimension level. However, the only way to get the most accurate numbers is to view on-hand and financial value using the dimensions that are marked as 'financial inventory' on the inventory dimension group. The financial inventory checkbox effectively does two things for us with regards to inventory valuation: 1. Calculates running average cost price at that dimension level - 2. Settles receipts to issues at that financial level when the inventory close is run (separate FIFO layers per 'financial dimension' level, separate weighted average calculation per 'financial dimension', etc.)

 

Here's an example that illustrates the point:

For the test, I created two FIFO items--both track site and warehouse. The only difference is that one has warehouse marked as 'financial inventory' and the other does not have warehouse marked as 'financial inventory'.

 

I entered the same three transactions for both items. Take note of the warehouses specified:

 

5/1/2011 - Purchase 1 qty @ $10 at warehouse 11

5/2/2011 - Purchase 1 qty @ $20 at warehouse 12

5/3/2011 - Sell 1 qty from warehouse 12

 

In AX, the issue transaction is valued at the running average cost price. So, in this example you may expect a value of $15 for the issue on 5/3. This is where the financial inventory setting comes into play.

 

For the item that has warehouse marked as 'financial', the issue transaction is valued at $20. With financial marked, costs are maintained per warehouse, so the only receipt considered when calculating the average cost for the issue transaction was the receipt for $20 at warehouse 12.

 

For the item that does not have warehouse marked for 'financial inventory', the issue transaction is valued at $15. Both transactions were used in the calculation of the average price (warehouse is basically ignored).

 

Why is this relevant to reconciliation? If I want to view on-hand value by warehouse for the two items, we'll see the problem:

The item that has warehouse marked as financial looks fine--

 

--but if you are viewing on-hand value by warehouse for the item that does not track warehouse as financial, we see something strange. Notice that warehouse 12 has no physical inventory, but a financial cost amount of $5. Quantities were removed from warehouse 12, but the value of those quantities was affected by other warehouse costs, which results in this scenario.

 Reconciliation by warehouse should only be done for items that track warehouse as a 'financial dimension'. The same is true for all inventory dimensions.

 

Tips and Tricks for Reconciling

 Posting Types - Ledger entries get stamped with a 'posting type'.  The posting types that will also result in a change in the inventory subledger values are the following: 

 Financial Updates

  • Purchase, Receipt
  • Sales order issue
  • Inventory Receipt
  • Production Receipt
  • Production issue
  • Purchase, Standard cost price offset

 

Physical Updates

  • Purchase, packing slip
  • Order packing slip
  • Production, picking list
  • Production, Report as Finished

 In order to leverage this information, I used the ledger voucher inquiry  (GL > Inquiries > voucher transactions).

Change the selection criteria to look at 'ledger account' and 'posting type'.

Going into this process, you know two things:

  1. The GL accounts that you deem to be 'inventory'
  2. The posting types that will reflect inventory balance

 

Run the inquiry two ways:

1-Identify Non-Inventory accounts that used inventory posting types - note that the selection criteria includes the posting types listed above, and NOT the inventory accounts. To accomplish the 'not', use an exclamation point:

  • Ledger Account = !Inventory1, !inventory2, !inventory3
  • Posting type =  "Purchase, packing slip", "Order packing slip", "Production, picking list", "production, Report as Finished", "Purchase, Receipt", "Sales order issue", "Inventory Receipt", "Production Receipt", "Production issue", "Purchase, Standard cost price offset"

 

2-Identify Inventory accounts that used non-inventory posting types. Notice that I just changed the placement of the exclamation points, removed them from my inventory accounts, and added them to the posting types.

  • Ledger Account = Inventory1, inventory2, inventory3
  • Posting type =  !"Purchase, packing slip", !"Order packing slip", !"Production, picking list", !"production, Report as Finished", !"Purchase, Receipt", !"Sales order issue", !"Inventory Receipt", !"Production Receipt", !"Production issue", !"Purchase, Standard cost price offset"

Anything returned in these queries should be considered a reconciliation item that needs closer analysis.

The exceptions:

Service items - Service items utilize the same posting types, but never result in inventory subledger values. You'll want to review the posting setup for all of your service items to ensure that none of the previously mentioned posting types are pointing to inventory accounts.

Avoid Invalid use of posting types - On your inventory ledger accounts, you can put some restrictions in place so that only the 'inventory' posting types are used on those accounts. To do so, go to the setup tab of the account setup. Change the 'validate posting' field to 'validation list', then, select the validation list button and select 'posting'. Populate the validation list with the posting types that are valid for that account. This is a good way to avoid misuse of the account.

  • Is it possible to generate some SQL Query for reconciliation so that we do not have to do the same in Excel manually after taking the report dump

  • I saw Inventory Receipt is one of Financial Updates, shall Inventory Issue also be a Financial update? Thanks!

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

  • This page is intended to provide you with the resources available for Inventory Costing in Microsoft

Page 1 of 1 (12 items)