• Home
  • About
  • Contact
  • German

Dynamics 365FO/AX Finance & Controlling

Dynamics 365FO/AX Finance & Controlling

Monthly Archives: September 2015

Pitfalls of the inventory value report and the potential conflicts report

17 Thursday Sep 2015

Posted by Ludwig Reinhard in General Ledger, Inventory

≈ Comments Off on Pitfalls of the inventory value report and the potential conflicts report

Tags

Dynamcis AX, Inventory reconciliation, Inventory value report

After explaining details of the inventory value report in the previous posts, I will now put a focus on the major pitfalls of the inventory value report and the related potential conflict reports based on the following sample transactions that have been entered for a specific inventory item (“L1000”) in January 2015:
EN_30_0005
After recording the sample transactions in Dynamics AX, I run the inventory value report and got the following outcome:
EN_30_0020
The total inventory quantities and values shown in the inventory value report (highlighted in yellow color) can be explained by the following yellow-highlighted inventory transactions:
EN_30_0025
As you can identify from the yellow-highlighted lines in the previous screenshot, not all item transactions are included in the inventory value report. The on-hand inventory form possibly provides a better view on the sample item transactions and allows a straightforward reconciliation with the inventory value report data. Please see the following screenshot.
EN_30_0030
As you can identify from the previous two screenshots, only physically and/or financially updated inventory transactions are included in the inventory value report. Inventory transactions that do not have a related physical and/or financial value, such as purchase order registrations or sales order picks are excluded from the inventory value report.

To demonstrate the major problems of the inventory value report and the related potential conflicts report in the following sub-sections, I recorded the very same transactions that have been recorded for item “L1000” in January 2015 for a second test item “L1100” in February 2015 and got the following result:
EN_30_0035

 

Problem 1: Date interval
One of the first things that I tried to do was analyzing the transactions of the first test item (“L1000”) only. As all transactions for this item have been recorded in January 2015, I run the inventory value report for the following date interval.
EN_30_0039

Outcome:
With this date restriction in place, the inventory value report showed the following data:
EN_30_0040
What you can identify from this inventory value report is that the ledger transactions that are shown at the end of the report are limited to January 2015 and that a variance of 14250 EUR arose. This variance is caused by the inventory transactions for the second test item “L1100” that were recorded in February 2015.

This outcome is very important for reconciliation purposes as only the General Ledger transactions but not the inventory transactions are influenced by the date interval entered for the inventory value report. You can identify this by the blue-highlighted line in the upper part of the inventory value report. This line shows the item transactions for the second test item “L1100” that have been recorded in February 2015.

A direct result of this outcome is that you have to run the inventory value report at month-end before any future inventory transactions have been entered for the next month if you want to reconcile your General Ledger with your inventory balance for the end of a specific month. That is because the inventory value report does not show inventory values as of a specific date in the past.

Please note that I tried to get around the date interval problem by applying the following additional filter on the inventory transactions:
EN_30_0041
Unfortunately, this second filter on the inventory transactions is not picked up by the inventory value report and results in the same outcome as before.

Remark:
All transactions and results presented in this blog post were executed on a Dynamics AX 2012 R3 “Contoso” demo system that can be downloaded from Partnersource. Numerous additional tests showed that the identified first problem with the date interval does not exist in each and every system but rather depends on the Dynamics AX 2012 version used and the hotfixes implemented. Whether or not you will face the first problem described in your system can thus not be answered conclusively here.

In order to understand the identified problem better, I executed a search for problems with the inventory value report on the Lifecycle website and got the following result:
DEEN_30_0140
What you can see from the previous screenshot is that several unresolved issues with the inventory value report seem to exist and that you have to take utmost care when using this report.

 

Problem 2: Setup summary ledger accounts
A second problem that I observed with the inventory value report is that no guidance/white paper or something alike is available for the setup of the summary ledger accounts that are linked to the inventory value report. As a result, you always run the risk that you miss the one or the other ledger account resulting in a reported variance that actually does not exist.

Example:
For testing purposes I setup a new inventory summary account (“149999b”) where I excluded the ledger account that is used for recording packing slip transactions for the item group “Audio” and that is included in the original summary account “149999”. Please have a look at the next screenshot.
EN_30_0045

Outcome (1):
With the newly setup inventory summary account in place, the inventory value report shows a discrepancy of 900 EUR between the inventory and ledger balance. This amount is identical to the ledger balance of account 140201 that was explicitly excluded for testing purposes. Unfortunately, the inventory value report does not give any hint that this account is missing.
EN_30_0050

Outcome (2):
As the inventory value report did not include any hint on the missing ledger account, I run the report on potential conflicts between inventory and General Ledger and got the following result:
EN_30_0055
As you can identify from the previous screenshot, the potential conflicts report provides some additional information on the underlying transactions that caused the discrepancy. Yet, this report does also not give any hint why the discrepancy arose; that is, which ledger account is left out.

 

Problem 3: Report filters
The next test that I did was trying to filter the inventory value report on a specific item (“resource”) as shown in the next screen-print.
EN_30_0060

Outcome:
The inventory value report does indeed apply the resource (item) filter but does now show a discrepancy between General Ledger and inventory that does not exist and is only caused by the fact that the ledger transactions are not filtered correspondingly.
EN_30_0065
This outcome and the previously identified problem with the date filters illustrate that filtering transactions in the inventory value report based on date intervals, items, item groups and alike needs to be done with utmost care and does not necessarily provide you with the outcome you expect.

 

Problem 4: Report filters potential conflicts report
After trying to filter transaction in the inventory value report, I tried the same with the potential conflicts report. Please see the next screenshot.
EN_30_0070

Outcome:
The result of this test was the following report that shows a discrepancy between inventory and General Ledger but does not provide any additional information.
EN_30_0075
I find this outcome very confusing as the discrepancy illustrated indicates that something must be wrong but that no additional details for the identified discrepancy can be provided. The issue here seems to be once again that the report filter for the item “L1100” did not similarly filter the corresponding ledger transactions.

 

Problem 5: Subcontracting service items (1)
The next issue that I experienced with the inventory value report and the potential conflicts report is related to service items used for subcontracting. According to the Dynamics AX manuals and TechNet, subcontracting items shall be setup as “stocked service items”. See for example this Website.

To test how subcontracting service items are incorporated into the inventory value report, I setup a basic finished product “L6000” that consists of an item “L6001” and a subcontracting service item “L6002”. The next screenshot provides an overview on the finished product and its components.
EN_30_0080
Please note that the subcontracting service item is setup with an item model group that has the physical and financial ledger integration parameters activated. If those parameters are not activated, the ledger transactions generated when recording production postings are wrong / incomplete from a finance & controlling perspective.
EN_30_0085
With this setup in place, the inventory value report shows a variance of 10 EUR after the production order is ended but before the purchase order for the subcontracting item is invoice updated.
EN_30_0090
The underlying reason for the discrepancy shown in the inventory value report is because service items are by default excluded from the inventory value report. As long as those items are not invoice updated, the ledger postings are incomplete with the result that the inventory value report shows a discrepancy between the General Ledger and inventory values. Please note that the potential conflicts report shows the same discrepancy.
EN_30_0100

Once the purchase order invoice for the subcontracting item is posted, the discrepancies illustrated in the inventory value and the potential conflicts reports disappear.

Note that the difference illustrated here does not arise if the purchase order invoice for the subcontracting item is invoice updated before the production order is ended.

Irrespective of the last qualification, the finding that the inventory value report shows a variance unless the purchase order invoice for the subcontracting item is invoice updated is a major disadvantage – especially in companies with a continuous production – as the inventory value report and the potential conflict report will most likely always show a discrepancy between the inventory and General Ledger balances.

 

Problem 6: Subcontracting service items (2)
A second issue with subcontracting service items is that the difference between the ledger balance and the inventory balance does not disappear if (a) the production order is ended before the purchase order invoice for the subcontracting item is invoice updated and (b) the original purchase order price for the subcontracting item differs from the price the vendor finally charges. The following illustrations exemplify this problem.

Example:
The subcontracting item vendor charges our company a 2 EUR higher price than originally calculated.
EN_30_0115
After the production order is finished and the purchase order is invoice updated, the 2 EUR price variance remains as a difference in the inventory value report as illustrated in the next screenshot.
EN_30_0120
This difference disappears with the recalculation of the inventory.
EN_30_0125
Note that the difference illustrated here does not arise if the purchase order invoice for the subcontracting item is invoice updated before the production order is ended.

 

Summary:
The various problems exemplified in this blog post allow concluding that the inventory value report and the potential conflict report are not suitable for the reconciliation of inventory and ledger balances especially in companies with a permanent “around the clock” production. That is because of the identified problems with the report filters and the problems identified in relationship to subcontracting service items.

What is more, even if the inventory value report is run in batch mode, the report often runs – depending on the number of items you use in your environment – around 10-15 minutes. This fact and the lack of drill-down functionalities do not allow executing detailed inventory value analyses.

In the next blog posts I will provide you some hints on how to avoid that differences between inventory and ledger arise in the first place and what other reconciliation tools exist that you might use for reconciling ledger and inventory balances.

Inventory reconciliation (Inventory value report) – Part 3/3

11 Friday Sep 2015

Posted by Ludwig Reinhard in General Ledger, Inventory

≈ Comments Off on Inventory reconciliation (Inventory value report) – Part 3/3

Tags

Dynamics AX, Inventory reconciliation, Inventory value report

Block 4:
Within this setup section of the inventory value report, users can chose among the following four parameters:
EN_22_043
(Note that the first and last parameter are mutually exclusive)

If you only select the first parameter, the inventory value report will only show you a total quantity and amount for the resources (items) and inventory dimensions selected. Example:
EN_22_044

If you chose the print total quantity and value parameter instead, you will get a breakdown of the total inventory values (quantity and amount) in a physical and financial section as illustrated in the next screenshot.
EN_22_045

Activating the parameter “calculate average unit cost” results in the illustration of an additional column that divides the inventory values by their quantities.
EN_22_046

Finally, the parameter “include not posted to ledger” ensures that also transactions that have not been posted to ledger accounts are incorporated into the inventory value report.
EN_22_047
An example for “not posted to ledger transactions” are purchase order related packing slip postings provided that the following AP parameter has not been selected.
EN_22_048

Block 5:
Within the last setup block, you can select one or more of the following parameters:
EN_22_049

  • Material:
    This parameter is always selected and cannot be deselected.
  • Labor/indirect cost/direct outsourcing:
    As illustrated before, the selection of one of these parameters allows reconciling WIP transactions that are for example generated when production routes are posted.
  • Level:
    The level parameter allows you selecting either “totals” or “transactions”. Please note that the selection of “transactions” does have a significant influence on the time the report runs and should only be selected for a limited number of inventory transactions that need to be analyzed in detail.
  • Include beginning balance:
    The last parameter “include beginning balance” can only be selected if you selected “transactions” at the detail Level. Selecting this parameter allows you incorporating beginning balance inventory transactions from earlier years.

 

I hope that the blog posts on the inventory value report were able to give you some additional financial insights into the inventory value report and the inventory reconciliation process in general and look forward to seeing you in the next post on inventory reconciliation.

Inventory reconciliation (Inventory value report) – Part 2/3

11 Friday Sep 2015

Posted by Ludwig Reinhard in General Ledger, Inventory

≈ Comments Off on Inventory reconciliation (Inventory value report) – Part 2/3

Tags

Dynamics AX, Inventory reconciliation, Inventory value report

Block 3:
The column section is probably the most important setup block of the inventory value report. Due to its importance, the different elements that can be setup in this block will be explained in detail based on several examples.

Inventory parameter
EN_22_012Activating the inventory parameter ensures that financial and physical inventory quantities and amounts are incorporated into the inventory value report. Please note that – from a finance & controlling perspective – the total inventory value is usually made up of the sum of the physical and financial amounts, as the risk of loss by chance and transfer usually shifts once goods enter or leave the company.

In respect to the inventory value parameter you might experience situations where the inventory value report shows you negative physical and/or financial amounts. This often happens when transactions are backdated or entered (too) late. The following example shows you an example of this phenomenon.

Example:
A company purchases 1000 pcs of an item that has a current cost price of 10 €/pcs for this very price. Due to delays in the warehouse and the accounts payable department, the purchase order packing slip is posted in September and the purchase order invoice is posted in October. In the meantime 500 pcs of the item have been shipped to a customer. The shipment occurred in July and the sales order invoice was posted in August. The following timeline summarizes the sequence of inventory transactions that were recorded in Dynamics AX.
EN_22_018

Outcome:
In July, after recording the sales order packing slip, the inventory value report shows a negative physical quantity and amount.
EN_22_019
In August, after posting the sales order invoice, the inventory value report shows a negative financial quantity and amount.
EN_22_020
In September, the inventory value report still shows a negative financial quantity and amount but a positive physical quantity and amount resulting in an overall positive inventory value.
EN_22_021
In October, the inventory value report finally shows the remaining positive financial quantity and amount.
EN_22_022


WIP Parameter
EN_22_023

Activating the WIP parameter allows you reconciling the following two production WIP accounts that are setup in the inventory posting form.
EN_22_024

Please note that the activation of the following row parameters does also allow you reconciling other WIP Transactions such as labor cost transactions, indirect cost transactions and direct outsourcing cost transactions.
EN_22_026
As an example, given the following production parameter setup (“item and category”), …
EN_22_025
… the inventory value report does also allow you reconciling the WIP accounts that are setup at the cost category level.
EN_22_027

Note: For reconciling WIP accounts, you do not have to use the inventory value report but can also use the production control balance reports illustrated in the next screenshot.
EN_22_028

Deferred COGS & COGS Parameter
Within this subsection, I will explain the deferred COGS and COGS parameters together as they are related from a process flow perspective.
EN_22_029

Activating the deferred COGS and COGS parameter allows reconciling the ledger accounts that have been setup in the following sections of the inventory posting form.
EN_22_030

Example 1:
After activating the COGS parameter, the inventory value report shows a total value of 10 TEUR for the financial COGS amount.
EN_22_031
If you compare this amount with the balance that can be identified in the General Ledger you will notice that there seems to be a difference of 300 EUR between the ledger transactions and the inventory transactions as illustrated in the following screenshot.
EN_22_032
The underlying reason for this variance is that project related transactions are not included in the inventory value report.
EN_22_033

In addition to project related inventory transactions, the following inventory transactions are not included in the inventory value report if you only activate the COGS / deferred COGS parameters:

  • Transfer orders,
  • Purchase order related transactions,
  • Sales order picking list transactions.

Example 2:
The previously illustrated inventory value report does also show a deferred COGS amount of 314885.84 EUR.
EN_22_034
As before, if you try finding this value in the General Ledger you will probably notice a “variance” (see the following screenshot).
EN_22_035
The reason for this obvious difference is that the inventory value report shows values that are not limited to one single financial period. If you use the ledger transaction list report and not the trial balance list page, you are able to reconcile the amount shown in the inventory value report as illustrated in the following screenshot.
EN_22_036

Note that you can also use the “shipped, not invoiced” Accounts Receivable report for reconciling the deferred COGS value shown in the inventory value report.
EN_22_037
Unfortunately, the “shipped, not invoiced” report is not generally available for every country but can easily be made available through a change of the following country code parameter:
EN_22_038

Best practice:
As illustrated in the examples before, for reconciliation purposes it is best setting up separate ledger accounts for each transaction type in the inventory posting and project posting form. Using the same ledger account for different groups – as illustrated in the next screenshot – can easily result in reconciliation problems.
EN_22_039

Profit and loss Parameter
EN_22_041

Activating the profit and loss parameter allows you reconciling the accounts that are setup in the profit and loss section of the inventory posting form (see the next screenshot). As a result, an inventory value report with this parameter activated allows identifying and reconciling inventory transactions that have been recorded through inventory adjustment and inventory counting journals.
EN_22_040

Please note that activating the profit and loss parameter does not allow you identifying those inventory transactions that have been recorded through inventory movement journals as those journals do not make use of the profit and loss accounts that have been setup in the inventory posting form.

Hint: Analyzing inventory transactions that have been recorded through inventory adjustment journals can offer you a first sign whether or not something is possibly wrong with your warehouse processes.

A last remark on the ledger accounts that can be setup in the column section:
EN_22_042
As illustrated before, setting up a ledger account in the yellow highlighted section of the inventory value report allows you a direct comparison of the inventory value amounts with the general ledger amounts. A prerequisite for this comparison is that summary accounts are setup in the Chart of Accounts as only summary accounts can be selected here.

…to be continued in the next post.

← Older posts

Communities4Future

Dynamics UserGroup Deutschland

Project Accounting Book – Part 2

Project accounting book

Categories

  • Accounts Payable
  • Accounts Receivable
  • Bank Management
  • Book reviews
  • Budgeting
  • Cost accounting
  • Fixed Assets
  • General Ledger
  • Inventory
  • Management Reporter
  • Miscellaneous
  • Podcast
  • Project
  • Sustainability
  • Uncategorized

Tags

Advanced bank reconciliation Allocations Bank reconciliation Budgeting Controlling Cost accounting Cost accounting module Cost center accounting customer D365 D365FO Dynamics AX Dynamics AX 2012 Electronic reporting Email Environment Fixed asset statement General Ledger journal Global Warming indirect costs intercompany Inventory Inventory reconciliation invoice invoice recording IOT Management Accounting Management Reporter Modern Finance MS Flow MT940 PowerApps PowerAutomate PowerPlatform Project Project module Purchase Order Resource scheduling Sensor settlement SharePoint Sustainability Sustainability Accounting Tax time recording timesheet Vendor invoice recording Vendor payments WBS workflow

Important Websites

  • Dynamics AX/365FO Links

Legal

  • Disclaimer

Subcribe

  • RSS - Posts
  • RSS - Comments

Enter your email address to follow this blog and receive notifications of new posts by email.

Archives

  • January 2023
  • December 2022
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • September 2019
  • August 2019
  • July 2019
  • June 2019
  • May 2019
  • April 2019
  • March 2019
  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • January 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015

Blog at WordPress.com.

  • Follow Following
    • Dynamics 365FO/AX Finance & Controlling
    • Join 569 other followers
    • Already have a WordPress.com account? Log in now.
    • Dynamics 365FO/AX Finance & Controlling
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...