Challenges for trading companies using the project module

01 Tuesday Mar 2016

Tags

Inventory valuation, Project module, Purchase Order, Sales order

Using the project management and accounting module in companies that are not primarily operating in the business project area but rather in trade or manufacturing can be beneficial due to the numerous additional analysis options available in the project module. Those advantages do however come at a price especially in regards to a more complex inventory valuation. Within the following, I will illustrate you those difficulties and provide you some hints on how to overcome them.

The starting point for the next illustrations are time and material (T&M) projects that have been setup with profit and loss (P&L) and balance sheet (BS) posting project groups in order to show you the difference in the usage of the one and the other project group.

Note: For reasons of simplicity the project with the BS posting is referred to as “WIP” project while the project with the P&L posting is referred to as “ordinary” or “non-WIP” project.

From a financial accounting perspective, the following ledger accounts have been setup.

Note: All ledger accounts setup for the “WIP” posting types are Balance Sheet accounts and all ledger accounts setup for the “Cost” posting types are Profit & Loss ledger accounts.

With this setup in place, I created the following two T&M projects that are used for recording an ordinary process cycle of a trading company that starts with the creation of the purchase order and ends with invoicing the customer. This process cycle will be illustrated step-by-step in the subsequent sections.

Step 1: Create Purchase Order
At the time the purchase order is created, Dynamics AX does not create any inventory / ledger transaction. Yet, depending on the setup of the project module, a so-called item requirement is created that allows you identifying and estimating the forthcoming costs.

Step 2: Post product receipt
The next step in my process cycle is recording the product receipt for the items ordered (100 pcs * 10 EUR/pcs).

For the WIP project, Dynamics AX generates the following two vouchers:

The voucher highlighted in red color is the one created at the purchase order level and represents the usual inventory product receipt.
The second voucher highlighted in blue color represents the project voucher created at the project level. Please note that this voucher directly reduces the company’s inventory and transfers those costs to the WIP ledger account (154600).

For the project with the P&L posting, the following vouchers are generated:

The voucher highlighted in red color is the one created at the purchase order level and represents the usual inventory product receipt.
The voucher highlighted in yellow color represents the project voucher created at the project level. As you can identify, this voucher directly reduces inventory and posts all costs on the project cost account (540500).

The next screenshot shows you the inventory value report that was run after the product receipts were posted for the WIP and ordinary T&M project:

Note: The first item (“L13100”) was used for the WIP project while the second item (“L13200”) was used for the ordinary T&M project.
What you can identify from the physical inventory section of the inventory value report (highlighted in red color) is the purchase order packing slip transaction that increases inventory.
At the same time, AX created a financial inventory transaction that reduced inventory by the very same amount resulting in an overall inventory quantity and amount of 0.
In other words, even though the items have not been sold / invoiced to the customer, you cannot identify them in your inventory anymore as they are already consumed for your project.

Step 3: Post purchase order invoice
The next step in my process cycle is posting the purchase order invoice. This posting generates the following voucher for the WIP and the ordinary T&M project:

After the purchase order is invoiced, the inventory value report shows the following outcome:
As you can identify from the screen-print above there is no inventory left despite the fact that the items have not been shipped / invoiced to the customer.

If the items have not been shipped and invoiced to your customer the question arises how you could identify and report on your inventory?

One possibility to do that is by referring to the project WIP report that is shown in the next screenshot.
What you can identify from this report is that it works only for the WIP project but not for the ordinary project with P&L posting.

An alternative is the project actual transaction report that allows you flipping between the different project types by changing the project statement focus.

Yet, in both cases you only get financial amounts but no quantities. Let’s continue by having a look at what happens if you invoice your customer a part of the items purchased.

Step 4: Invoice customer for a bundle of 30 pcs
After the purchase order invoice has been recorded, the customer is invoiced for a part (30 pcs * 100 EUR) of the goods via an ordinary project invoice.

This generates the following voucher for the WIP project:

The invoice for the ordinary project results in the following voucher:

Given those invoices 70 pcs of the item with a total inventory value of 700 EUR remains. The previously used project WIP report shows this amount for the WIP project but not for the ordinary T&M project.

Unfortunately, the second alternative report used before – the project actual transaction report – is not able to show you the inventory value, neither for the WIP, nor for the ordinary project and thus leaves you with the requirement of setting up a specific project related inventory value report.

Summary:
Overall it can be summarized that the use of the Dynamics AX project module can have several advantages for companies not primarily operating in the business project area. Yet, you have to be aware that recording ordinary purchase and sales orders through this module can cause issues when it comes to inventory valuation especially in situations where products are not purchased and sold within the same accounting period. From this perspective, the use of projects with WIP (Balance Sheet postings) seem to be better suited. Notwithstanding this preference of the author, it seems very likely that your auditors will ask for a specialized inventory value report that you need to create yourself to fulfil reporting / auditing requirements. This (cost) disadvantage needs to be weighed against the potential advantages that the project module offers for trading companies.

Parallel inventory valuation

09 Wednesday Dec 2015

Posted by Ludwig Reinhard in Inventory

≈ 2 Comments

Tags

Dual warehouse, Inventory valuation, Item model group

It was back in 2011 when I first noticed that Dynamics AX must have some functionality to do a parallel valuation (e.g. FIFO & LIFO) of a company’s inventory.

My assumption was based on a feature that I saw in the item model group setup window and that allowed selecting a second inventory model group. (Please see the next screenshot that was taken from an AX2012 demo system).

I started to google about this “dual warehouse” feature but could not find much and thus decided to test it with some basic item transactions. The outcome looked very promising but at that time, the functionality was embedded into the Eastern European AX2009 layer and I lacked the knowledge to make this feature generally available.

Earlier this year a friend came up with a question how to do a standard cost and weighted average inventory valuation for an item in parallel. I remembered that functionality and came across the following blog post.

Equipped with an AX2012 demo system and enthusiastic about the possibilities that this feature could offer especially for finance users, I initially tried activating the Russian dual warehouse configuration key, hoping that this key would make the dual warehouse feature generally available in my system.

Yet, after synchronizing my database nothing happened and my item model group window still looked the same as before including only one single inventory model.

I thought ok that would have been too easy and had a look at the available shared projects in my development environment and “bingo” I found a shared project with the term “dual warehousing”.

My next thought was having look at the built in country-region codes and modifying them simply by adding the country-code of the company I was operating in.

This was a bit work but I thought if it does the job it was certainly worth the effort. Yet, unfortunately only some functionalities were available and I permanently got error messages once I tried posting a purchase order, inventory adjustment journal and so on. As I did not want to abandon the field, I continued adjusting all other tables, forms, classes, etc. that were incorporated in the “RU_” shared projects without doing a detailed analysis of the code that I made available for users in my demo country “Germany”.

Finally I got through and recorded the following transactions to test the feature:

After recording all transactions, the on-hand item window showed the following data:

My next step was running the inventory recalculation jobs for my test item.

After those periodic jobs finished, the following adjustments could be identified in the item transaction form:

The inventory value report showed – in line with the identified adjustment – the expected inventory value of 450 EUR.

As no dual warehouse inventory value report is available, such a report still needs development based on the available data to show users the inventory value according to the second inventory model.

Summary & evaluation
The possibility to do a parallel inventory evaluation is very interesting especially in cases where tax authorities require a different inventory model.

Even if no immediate need for a second inventory model exists, the data generated by the dual warehouse feature can be very useful as they offer the possibility tracking inventory values in a different currency that allows e.g. identifying the efficiency of foreign currency hedging instruments.

Despite those advantages, the use of the dual warehouse feature goes along with the following issues:

Users can only make use of one single additional inventory model,
No dual warehouse inventory value report exists,
If the reporting currency differs from the accounting currency a parallel inventory valuation is not possible as currency effects will always distort the outcome,
The major issue in my opinion is, however, the “development footprint” that the provision of the dual warehouse feature leaves in your system. The next screen-print shows you some (!) of the tables and classes that are affected by the dual warehouse functionality.

As you can grasp from this excerpt, your whole AX system is basically affected by the dual warehouse functionality, which considerably affects your system’s upgradeability.

What is more, if you are not doing a detailed code analysis before making the dual warehouse feature available, you will end up with additional setup windows, form buttons and functionalities that you probably cannot setup without further investigation.

The last constraints lead me to the conclusion that the risks and potential problems of the currently available dual warehouse feature do by far outweigh its potential advantages especially for those of you that are only interested in a second inventory value figure based on a different inventory model. For the last requirement it seems much easier developing another inventory value report based on mathematical-statistical procedures that calculates the second, third … inventory value for you.

Incidental Acquisition Costs – Part 2

30 Saturday May 2015

Posted by Ludwig Reinhard in Inventory

≈ Comments Off on Incidental Acquisition Costs – Part 2

Tags

Allocations, Dynamics AX 2012, Freight charges, Inventory valuation

This blog post continues the previous one by illustrating an alternative approach how incidental acquisition costs can be incorporated into the total acquisition costs of a company’s inventory.

The sample data used in the following are the same that were used in the previous post and are illustrated once again in the next screenshot.

As before, I assume that a company purchased various items (cameras, computers, etc.) for 100 mio. EUR in a given year and that the different vendors that supplied the items charged 1.4 mio. EUR for transportation & freight to deliver the items to our warehouse. In addition to the direct freight costs, 600 TEUR indirect freight costs that could not directly be linked to any product / purchase order were recorded in Dynamics AX. In the following year, for a purchase volume of 120 mio. EUR, a total of 2.64 mio. EUR of direct and indirect freight cost were recorded in Dynamics AX. Previously, an automatic charge ensured that the total freight costs were recorded in the inventory accounts in the company’s balance sheet.

Rather than using automatic charge codes, the same result can be achieved by making use of the costing sheet functionality in the inventory module. How this can be realized is illustrated in the following.

Step 1
The first step is to link your products with a cost group.

Step 2
After linking your products with a cost group you can refer to this cost group in the costing sheet simply by selecting this cost group in the corresponding field.

Step 3
Thereafter a surcharge node needs to be setup. This node is linked to the COP node which includes the TG cost group specified before. Once this link is established the surcharge percentage needs to be specified. In this example I use a surcharge of 2%.

Step 4
Finally, the ledger accounts need to be setup in the posting window. The accounts used here are the same that were used in the previous post for the automatic freight cost allocation.

With this setup, Dynamics AX is posting a freight allocation when purchase order packing slips are posted.
This is different from the previous approach where the freight allocation was posted at the time the vendor invoice was posted.

After recording the vendor invoice and the indirect freight costs – as illustrated in the previous blog post – a total of 240 TEUR remains on the freight cost accounts in the profit and loss statement (total of ledger accounts 600720 to 600735).

Yet, by using the same ledger allocation rule as before also this remaining amount is shifted to the inventory accounts in the balance sheets ensuring that all incidental costs are included in a company’s inventory.