axinthefield: Reconciling Inventory to GL in Dynamics AX

[Blog bot]

Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx
==============

At Convergence this year, I 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 the post is all of 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 those '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 both reports, as well as the non-financial transfers functionality (described later in this post). Here's the link for that download:

https://mbs.microsoft.com/knowledgeb...b;en-US;982712

Here is the whitepaper that covers the new reports.

https://mbs.microsoft.com/partnersou...rintpage=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 the scenario you see.



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'. We happen to know exactly which posting types will also result in a change in the inventory subledger values. Here they are:

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 use 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:

• The GL accounts that you deem to be 'inventory'
• 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. One exception:



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.




Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx