Within this addendum I would like to show you how inventory and ledger balances can be reconciled by using the first “financial dimension” reconciliation approach presented in the earlier post.

I will do this based on the following simplified production item (finished product) “L6300” that consists of another item (“L6301”) and a subcontracting service item (“L6302”). The quantities and cost prices that make up the Bill of Materials (BOM) of the finished product can be identified in the next screenshot.

Production step 1: Inventory adjustment Item L6301
In order to produce the production item, I first need to ensure that sufficient parts of the BOM item “L6301” are on stock. I realize this by posting an inventory adjustment journal that generated the following ledger transaction (please see the next screen-print).

What you can identify from this voucher is an increase in inventory value of 40 EUR that is posted against a profit & loss account (“510504”). Please note that all transactions are recorded with the financial item transaction of the BOM item (“L6301”).

Production step 2: Production start
After ensuring that sufficient parts are on hand for production, I started the production order. While doing that, Dynamics AX generated the following voucher:

What you can see from this voucher is an increase in the WIP balance sheet account 150151 and 150153 that are highlighted in green color. The offset transactions are all posted on the inventory accounts 140103 and 140303.

Please note that the voucher for the subcontracting service item that is recorded against the inventory account 140303 could also be recorded against a profit and loss account. In addition, with a slightly different item setup, the voucher for the service item could also have been posted later; that is in later production steps.

Irrespective of the timing of the subcontracting service item posting and the ledger account used, after posting the voucher for the subcontracting item, the inventory value report shows a variance of 10 EUR, which is equal to the value of the subcontracting item “L6302”.

The underlying reason for this variance is that the inventory value report does not incorporate service items.

Now, let’s have a look at the result that can be identified in the trial balance after finishing the first production step.

The following screenshot shows you, the outcome of the first production step (inventory adjustment) highlighted in yellow color and the outcome of the second production step (production start) highlighted in green color. What you can identify from this screenshot is that both financial item dimensions (“L6300” and “L6301”) are balanced and add up to a total value of 0 EUR.

The next screenshot shows the very same balances sorted by main accounts. What I would like to emphasize from this screenshot are the transactions recorded on the green highlighted WIP accounts that incorporate the total value of the finished product.

Production step 3: Report as finished
The next production step is reporting the production order as finished, which results in the following voucher:

As before, the inventory value report still shows a variance of 10 EUR.

The trial balance list page on the other hand does still show a balanced result for the financial dimensions “L6300” and “L6301”.

The WIP accounts are now cleared and show a total amount of 0 EUR (highlighted in green color). At the same time, the preliminary inventory account for the finished item (main account 140204) shows a balance of 50 EUR.

Production step 4: End production
The last production step ends the production order. When ending the production order, Dynamics AX reverses all previous vouchers and posts the increase in inventory for the production item. The next screenshot shows you the voucher generated for my demo item.

As before, even after finishing the production order, the inventory value report still shows a variance of 10 EUR.

The trial balance list page on the other hand shows balanced item financial dimensions.

The same holds for the ledger accounts.

Production step 5: Post vendor invoice for the subcontracting item with a price variance of 2 EUR
Some days after ending the production order, the subcontracting vendor sends his invoice for 12 EUR. The 2 EUR price variance is accepted. After posting the vendor invoice, the following voucher results.

If we have a look at the inventory value report we do now see a variance of 2 EUR that is equivalent to the price difference for the subcontracting item.

An analysis of the trial balance list page shows – as before – a balanced outcome for the financial item dimensions …

… as well as for the main accounts.

Production step 6: Inventory reconciliation
The last “production” step used in this demo is running an inventory reconciliation, which generated the following voucher:

If we have a look at the inventory value report we can now identify that inventory and ledger amounts are finally balanced.

Yet, from a financial dimension perspective this has always been the case as the following screenshots illustrate.

Summary
Within this blog post I highlighted you the specific problems that many production companies face with the inventory value report when they outsource a part of their production chain. Those companies might always see a variance in the inventory value report. Yet, by applying an alternative reconciliation approach – such as using an item financial Dimension – reconciling inventory and ledger balances becomes possible.

Within the previous blog post I showed you several problems of the inventory value report and the potential conflicts report that can cause you some “headache” if you try reconciling ledger and inventory balances. As I do not want to leave you on your own with those problems, I thought it might be useful to show you some alternative approaches for reconciling ledger and inventory values.

Because of the problems with the inventory value report, (as described in the previous blog post), I setup a completely new Dynamics AX demo environment and repeated the previously shown transactions for two newly created test items “L2000” and “L2100”. As before, all item transactions for the first test item “L2000” are recorded in January 2015 and all item transactions for the second test item “L2100” are recorded in February 2015. The next screenshot gives you once again an overview of the inventory transactions executed for the first test item in January 2015.

After recording those transactions, I run the inventory value report without any date restriction and got the following result.

What you can identify from this screen-print is identical to the result for the inventory transactions that I showed you in the previous blog post. Against the background of those transactions let’s now have a look at some alternative inventory reconciliation approaches.

Approach 1: Financial dimension “item”
A first alternative approach for reconciling inventory and ledger balances is setting up all items with a financial item dimension and using standard General Ledger features, such as the trial balance list page, for reconciliation purposes. Example:

What you can identify from the screen-print above (that filters on the first test item “L2000”) is
(a) the total inventory value of 14250 EUR recorded on ledger account 140200, and
(b) the financial inventory value of 14525 EUR that can be calculated by adding the green highlighted lines / values.

Prerequisites:
In order to apply this approach you have to make sure that your account structures incorporate the item financial dimension as exemplified in the following screenshot and…

… that all your items are setup with their corresponding financial dimension.

Note: To make sure that each newly setup item automatically gets linked to its item financial dimension, a minor code adjustment, as illustrated in the next screenshots, might be smart. In my example, I first generated a new method (“SetFDIT”) in the InventTable …

… and made the following adjustment to the insert method.

Note: If you setup inventory posting based on an item group level, it might be sufficient using the item group financial dimension for reconciliation purposes as illustrated in the next screenshot.

Yet, this approach has the disadvantage that it does not help you identifying errors/problems e.g. if a specific item transaction was recorded on a “wrong” ledger account. That is because using the item group financial dimension for reconciliation purposes does not allow you identifying which item caused the problem. For that reason, in an “ideal world”, it would be best working with both, an item group financial dimension and an item financial dimension as they give you the possibility to do your reconciliation at the item group level and “drill down” to the item level if you identify problems.

Advantages/Disadvantages of this reconciliation approach:
This first approach has several advantages and disadvantages. Let’s start having a look at the advantages:

• People working in the finance & controlling department are familiar with using and analyzing financial dimension values via the trial balance list page and other forms/reports in the General Ledger. They are thus familiar with this reconciliation approach.
• The trial balance list page provides some standard drill down functionalities that help identifying problems quickly.
• As Dynamics AX is a fully integrated ERP system, differences between inventory and General Ledger are by definition not possible. That is because each debit transaction requires a corresponding credit transaction. Reconciling inventory and General Ledger is thus a mere question of understanding the Dynamics AX posting framework and having a look at the “right” ledger accounts. As the trial balance list page does possibly give the best and fastest view at ledger accounts, this first alternative approach seems predestined for the inventory-ledger reconciliation job.

Now let’s have a look at the disadvantages of this approach:

• Depending on your system setup and number of items used in your company, the trial balance list page might need more time to open up and is limited to a single year. If you need to analyze data quickly for several years, the Management Reporter might be the better choice for doing the reconciliation job.
• You “lose” at least one financial dimension for reconciliation purposes,
• This approach works best if a code adjustment is implemented that makes sure that the financial item dimension is automatically filled once a new item is setup.

Approach 2: Inventory transaction inquiry forms
A second alternative approach for reconciling inventory and ledger balances is using inquiries forms that allow you analyzing inventory transactions. An example of such an inquiry form is the transactions form that can be found in the inventory management module. Please see the following screenshot.

Prerequisites:
A major disadvantage of this and other standard Dynamics AX forms is that information on ledger accounts cannot be personalized in a way that allows analyzing the transaction data quickly and easily. Please have a look at the next screenshot which illustrates that the inventory transaction form does only show the ledger information for the first record.

For the following illustrations, I thus created a new inventory transaction form denominated “LRE INVRE” that is based on the InventValueTransView that is also used for the inventory value cube. (Please note that you can use several other standard views, queries or tables for setting up this form). As you can identify from the next screenshot, this form allows you identifying the account and offset account for each inventory transaction.

Note: What is important in this respect is that you implement a form that allows you filtering those inventory transactions before the form is opened. Otherwise you might experience some performance issues due to the large number of transactions that need to be loaded when opening the form.

Once the form is implemented you can either analyze the inventory data directly in the form or export the results to Excel for a more detailed reconciliation analysis. Example:

Advantages/Disadvantages of this reconciliation approach:
After illustrating you the underlying principle of this second reconciliation approach, let’s have a look at the advantages and disadvantages of this approach.

Advantages:

• As shown above, this second approach allows you analyzing the ledger account and offset account for each inventory transaction recorded (provided that the transaction is already posted on ledger accounts).
• Different from the first approach, this second inventory reconciliation approach does also allow you analyzing inventory transactions that have not yet been posted to ledger accounts. This is a major advantage as you can e.g. quickly identify items that have been picked or registered but not packing slip updated and/or invoiced.
• A third advantage of this approach is that it can be implemented quickly and easily with a simple system modification. Moreover, users can make use of the standard Excel export functionalities that allow a detailed analysis of the inventory transactions.
• A final advantage of this approach is that you don’t necessarily “lose” a financial dimension for reconciliation purposes.

Disadvantages:

• A major disadvantage of this approach is that transaction level data are analyzed. Analyzing those data can consume lots of time unless you are able to filter those transactions e.g. by date, item or other features.
• When using this approach, users will most likely copy the data to Excel for a detailed analysis. Please remember that also Excel does have its limitations as exporting more than 1.1 mio. data at a time is generally not possible requiring incremental data exports.
• A final disadvantage of this approach is that it is in general more difficult identifying errors, as the data are not pre-aggregated. This means that you have to “slice and dice” the data in a way to identify inconsistent / incorrectly entered transactions. This last disadvantage can be overcome by using the next alternative approach for inventory reconciliation.

Approach 3: Inventory value cube
The third and last alternative method to reconcile inventory and General Ledger balances that I want to present in this post is using cubes. (Please note that I do not make a differentiation between the many BI instruments available for Dynamics AX, such as SSAS cubes, the various Power BI instruments and alike and simply call this approach “cube” solution to keep things as easy as possible).

For the following illustrations, I simply used the inventory value cube that ships with the standard Dynamics AX application. The next screenshot shows the result of an analysis that I did for the demo item “L2000” by browsing the cube in the SQL server management studio.

You can, of course, do the same in MS Excel simply by establishing a connection to this cube.

Example:

Note:
If you compare the data from the inventory value cube and the trial balance list page you will notice some differences in the detailed amounts that make up the total inventory balance of 14250 EUR. In my example you can identify ledger balances on the balance sheet accounts 200100 and 200140 in the Dynamics AX General Ledger that sit on the profit and loss account 600180 in the inventory value cube.

The underlying reason for this outcome is the “design” of the inventory value cube, which currently does not allow reconciling the physical inventory amount shown in the inventory value report. For that reason, you need to make some adjustments to the standard inventory value cube if you want to use it for reconciling purposes in your Dynamics AX environment.

Advantages/Disadvantages of this reconciliation approach:
Now, let’s also have a look at the advantages and disadvantages of using this approach.

Advantages:

• The cube solution is probably the most powerful and fastest solution for reconciling inventory and ledger balances as users can easily analyze the data they want e.g. in Excel.
• Different from the previous approach, the cube solution can easily handle large data volumes as transactional data are pre-aggregated. At the same time, cube “drill through” features allow you taking a detailed look at the transactional data simply by doing a double click on the data.

Disadvantages:

• A first disadvantage of this approach is that you need a cube expert for implementing cubes, as ordinary finance & controlling users regularly do not have sufficient system rights and the expertise to implement those cubes.
• Another disadvantage is that not all the standard inventory value cube measures, calculations and KPIs can be used “out of the box” and need some modification before they can be used in a live environment.

Summary
Because of the many problems of the inventory value report it is prudent to make use of alternative inventory reconciliation approaches.
Within this blog post I showed you three possible alternative reconciliation approaches that can help you overcoming the weaknesses of the inventory value report.
From a finance and controlling perspective, the first approach seems to be the one that should always be implemented as it can be setup by ordinary finance & controlling users without making a system modification.
If differences between inventory and General Ledger arise, using the second and/or third approach seem to be inevitable.

Due to its importance and ease of implementation, I will show you a detailed step-by-step example on how to reconcile production vouchers by using the first “financial dimension approach” within the next post.