One warehouse, 2 cost models

Around 14 years ago I designed a feature called the “Dual warehouse”. The feature is scarcely documented (https://technet.microsoft.com/en-us/library/jj733298.aspx) and barely understood. It was intended to maintain 2 warehouse cost prices in parallel in countries suffering from hyperinflation. In 2002 I was even given a chance to present this feature to Erik Damgaard, at that time known not as a Danish TV celebrity but the Damgaard Data co-founder and the father of Dynamics AX. His verdict was “he understood the benefit but the approach was a bit too radical” to his taste.

Indeed, the feature was not only tracking inventory cost in 2 currencies (the local accounting one and in the reporting i.e. secondary currency) but also calculating it in accordance with 2 different cost calculation models and posting the financial impact into the general ledger in an isolated GL layer to make it visible on IFRS vs. local account statements. Every inventory transaction was given an additional set of fields CostAmountSecCur* to store the value in the secondary currency. The feature was blamed for bugs, but after a while it made it into the Dynamics AX core application. A decade later the bugs are gone and the feature is still there in Dynamics AX 2012 R3 for Russia and other Eastern European countries, it has been kept on a par with the latest developments at the AX logistics core.

What have been the reasons to keep it in the application?

• Initially it provided a stable inventory value in the ‘hard’ currency USD, in a country where retail prices in rubles RUB were updated daily and sometimes grounded in a pseudo-currency tied to the dollar. The COGS in RUB was reflecting the past purchase price whereas the revenues were driven by the inflated sales price, resulting in a disproportionally huge profit in RUB. Only the COGS in USD was comparable with the revenues in USD and provided a more solid basis for a business decisions in an economy ravaged by hyperinflation. With the Russian economy “in tatters” my dual warehouse feature may gain back some popularity :)

• Not that long time ago I was asked by a CEO: “We buy in euros, we sell in euros, why on Earth would we track our stock and assets in the Polish zloty?!” Indeed, IAS 21 says “an entity is required to determine a functional currency … based on the primary economic environment in which it operates”, but auditors are generally less tolerant and expect the balance to the produced in the national currency.

• Costing models in accounting and in the cost accounting sometimes differ. For example, on the balance sheet the average price may be applied to the product stock, whereas in the cost accounting the product may be recorded at a standard price. Here we may utilize the “dual warehouse” with the reporting currency same as the local one but with 2 different cost valuation models.

The emphasis is put here on the last requirement as it seems to be pretty common across the globe.

Example 1

I am using the standard Dynamics AX 2012 R3 demo database, the RUMF company. “RUB” is both the local currency and the reporting currency there. The license configuration key Country regional specific features / Multiple countries regions / Russia / Dual warehousing must be active. Contrary to AX2009, local compliance features in Dynamics AX 2012 cannot be re-used in a “foreign” legal entity by just turning the configuration key: the primary company address must be in Russia. I leave it to the reader to hack AX and to ‘jailbreak’ this feature. Do not expect it to be very easy.

Let me first highlight the business case the feature was initially intended to support. Imagine a product valued at a monthly average price. It is delivered in 2 batches at the same effective price in USD but a different equivalent in RUB:

Date	Business case	Price USD	Rate USD/RUB	Primary cost price RUB (Weighted avg.)	Secondary cost price USD (Weighted avg.)
15.01.2015	Purchase 100 kg	10,00	66,10	66,10	10
17.02.2015	Purchase 100 kg	10,00	62,66	64,38	10
16.05.2015	Sale 50 kg	15,00	50,01	64,38	10

The contribution margin in the ‘hard’ currency USD is 33.33%, while the contribution margin in RUB is just 14.17%. Which is the “right” one depends on the cost structure of the company. Technically, the one cannot be deducted from the other without knowing the full transaction history. In Dynamics AX a separate inventory closing run is required. This is exactly what the “Dual warehouse” feature does: it allows for 2 parallel closing runs, 2 settlement data pools and a secondary GL ledger.

Example 2

Now let us explore my major case with one currency but 2 models:

Date	Business case	Price USD	Inventory cost price USD (Weighted avg.)	Inventory cost price USD (Standard)	Cost variation USD
15.01.2015	Purchase 100 kg	10,00	10	10
17.02.2015	Purchase 100 kg	12,00	11	10	+2
16.05.2015	Sale 50 kg	15,00	11	10

To test it, set up a new item model group. Pay attention the secondary inventory model group and the Post secondary financial parameters. The latter activates the “dual” GL posting layer.

Create a new product with the tracking groups None-None. Assign a default site and a warehouse. Use the button Manage costs / Item price on the released product ribbon to assign a standard price to the item. Do not enter the price of 10.00 on the first tab page of the “Pending price” grid but on the second, in the field Secondary cost! Leave the primary standard cost to be zero. Activate the price. In the item group of the product, set an account for the “Rounding variance”.

Open a purchase order to the “Vortex corporation” and post 2 PO invoices with different prices as outlined above. Open a sales order and post an AR invoice with 50 kg.

Now check the inventory transactions and look at the awaited, but still stunning result, or as we say at Microsoft – awesome. Just awesome:

Note the column Cost amount (cur.) meticulously tracking our standard cost as opposed to the average cost in the ordinary Cost amount column. Now open the GL voucher for the second PO invoice:

Examine how the system recorded the cost variance in a separate posting layer.

You may also perform the Inventory management / Periodic / Closing and adjustment vs. Closing and adjustment in currency. Here nether of the procedures is going to make any cost adjustments, since within the primary cost model the momentary cost price happens to be the same as the total average, while the secondary model should not produce any settlements at all for our product valuated at a standard cost.

Conclusion

The examples above have been deliberately kept simple; in reality different currencies and models lead to collisions where an inventory transaction may be settled in one model but remain ‘open’ in the other; where manual adjustments and write-offs are only applied to one of the models; where the ‘physical’ price affects the calculation. I still hope this short introduction has given you an idea what this feature can do if applied creatively.

Comments

• Anonymous
  September 16, 2015
  круто, круто )

• Anonymous
  October 25, 2015
  Is this functionallity described more detailed than in TechNet?

• Anonymous
  October 28, 2015
  No, I'm afraid my blog entry is the best what we have in English.

• Anonymous
  December 26, 2015
  The comment has been removed

• Anonymous
  December 27, 2015
  The comment has been removed

• Anonymous
  January 27, 2016
  The comment has been removed

  • Anonymous
    October 14, 2016
    The comment has been removed
    • Anonymous
      October 24, 2016
      The comment has been removed