From my personal experience of discussions with consultants and developers, implementing Dynamics AX, issues related to costing and an inventory closing became one of main pain-points of an implementation process. Peoples tends to treat an inventory closing as a kind of black box, which, depending from phase of the moon, can produces different result and or require different time to run for a same dataset. To my opinion, it is caused by costing information being scattered across various parts of the documentation. In addition, some peculiarities of costing are not described at all and should be learned in a hard way with trial and error approach or by learning of X++ code of an inventory costing.
In this article I am trying to provide reasonably detailed description of an inventory closing and also I am discussing some other costing related issues.
This document is intended for more or less experienced consultants, who already knew a general architecture of the Trade module and also have some (successful or unsuccessful) experience in usage of an inventory closing.
Almost two years has passed since original version of the article was published. Axapta made considerable progress during these two years (DAX 2009 was released, which introduced some radical improvements in costing area). Besides, I want to describe some subtle details which have been omited from the first version of the article. All additions to the original article are marked out in italics.
Why does an inventory closing is necessary ?
The main task of an inventory closing is to calculate the actual cost price of an inventory issue. DAX works with inventory costs in the following way: On instant of an inventory issue posting, system does not tries to calculate an exact issue cost price according with the inventory model. Instead of this, system uses some estimated value for the issue cost price, which looks like a good approximation for the actual cost price. (To be discussed in the Instant estimated cost price section). Then, during the inventory closing, the actual issue cost price is calculated with respect to inventory model settings. After that, system calculates the difference between the initial (estimated) issue cost price and the true issue cost price, then system writes this down into inventory transactions and inventory adjustments. Then – GL postings for these adjustments are created. Why this approach has been chosen? In a general, during a posting of an initial inventory issue, system might not have all information required to calculate a true cost price. For example, if negative inventory is turned on, we can post issues before corresponding receipts; In the Weighted Average model, it is impossible to calculate an issue cost price before all the receipts for a period has been posted; For FIFO/LIFO models, the receipts might be posted out of chronological order and so on.
When I was working as a DAX developer, from time to time newbie consultants were trying to talk me into customizing Axapta to allow an Accountant to manually enter an issue cost price into inventory documents. Then Axapta was proposed not to touch inventory transactions having manual cost price during an inventory closing. Although this approach looks technically viable from the developer’s point of view, it STRONGLY conflicts with the very idea of the Axapta’s costing system. A true issue cost price SHALL always be calculated during an inventory closing. An on-the-fly cost calculation or even more – a manual costing are impossible on principle.
After actual issue cost prices for a period has been calculated, system disables creation of the new financial inventory transactions in this period. It looks quite reasonable, because a new transaction creation could break the order of issue to receipt settlements thus resulting in incorrect issue costs. If we only want to calculate preliminary issue costs for the current inventory transactions snapshot, we would rather use an inventory recalculation procedure instead of the complete inventory closing
So – Inventory closing procedure consists from the following steps:
- Issue to receipt settlement. Calculation of a initial cost of issues.
- Cost propagation via inventory movements graph
- Posting of adjustments into GL
Let’s look closer on every of these steps:
A calculation of an actual cost price in Axapta is always performed from the settlement between receipts and issues. For FIFO and LIFO principles this approach looks quite natural. Say, We purchased 2 pieces for 10EUR apiece, then 3 pieces for 14EUR a piece. To calculate the cost price for the issue of 3 pieces we must settle it to the first receipt of 2 pieces with the total value of 20EUR, then with one third of the second receipt with the total value of 14EUR,thus resulting in the issue’s cost price of 34EUR. During the settlement procedure, a receipt’s contribution to the issue cost price is calculated as a balance of the receipt transaction on the date of the settlement multiplied to the ratio between the settlement’s quantity and the total quantity from the receipt. Of course – the result is rounded according to rounding rules from the currency table.
I will not describe in details the mechanics of the FIFO model’s settlement, for it is a straightforward process. The only thing that is worth to mention is that the system does not check consistency between the issue and receipt dates. Say – we booked an issue of the item on the 5th of month and a receipt of the same item on the 15th of month (suppose – there are no other movements for the given item). In this case, system would settle the given issue to the given receipt without any warnings, which is contrary to mere common sense. I will look closer on reasons and negative consequences of having negative inventory in sections ‘Why does negative inventory appears? ‘ and ‘Cost propagation ‘.
It would be more correct to call LIFO and ‘LIFO on date’ models a LILO model (Last In Last Out). It is a kind of direct opposite to the FIFO model. System runs through the list of unsettled issues beginning from the last one, and settles them to the last unsettled receipt (ordered by the date of course). A “LIFO on date” model differs from the LIFO in the aspect of finding an appropriate receipt for the given issue. This model settles an issue to the last receipt that have been received BEFORE date of the issue. Say, we purchased some item on the 1st, 5th and 31th days of the month and this item was sold on, say, 10th and 18th of the month. From the strict LIFO point of view, the issue from the 18th should be settled to the issue from the 31th. (Because LIFO suggest the last receipts bound to be written-off first). But the whole idea seems to be a little weird, for we are trying to issue the item long before it was actually received. So it seems that developers have implemented two similar models to satisfy both accounting and common sense approaches to the LIFO principle.
When I was studiyng a DAX2009 source code, it turned out, that actually, usage of the LILO principle, I had described earlier, had been a more programming error than an actual intention. It seems that a developer just copied the piece of code from the FIFO method and substituted the reverse keyword for both issue and receipt selection statements. That resulted in the LILO principle I’ve described above, that is – latest issues was being settled with latest receipts, then earlier issues with earlier receipts and so on. The funny thing is that this error was introduced in at least version 3.0sp2 (summer 2003) and was fixed only 4 years later – in year 2007… It seems that the LIFO principle is not very popular for inventory valuation all around the world.
Now – Let’s have a look at ‘Weighted average’ and ‘Weighted average on date’ principles. In any old time, before Axapta 3.0sp2 was released, run of an inventory closing procedure for these principles led to creation of an every-receipt-to-every-issue settlement. So, If we had a two unsettled receipts from the previous period, 6 receipts on the current period and, say 40 issues in the current period, this led to the settlement of 8 receipts to 40 issues, thus producing 320 settlements and 640 settlement records (For each settlement is stored as two records of inventSettlement table). It is easy to guess that such explosive growth of the invent settlement table also led to significant degradation in inventory closing’s performance. That’s why the more lite approach to weighted average costing was
implemented in version 3.0sp2. In that version, thresholds for quantity or amount of the settlement were introduced. Roughly speaking, while finding an appropriate receipts to settle with a given issue, system begins with receipts having a highest unsettled quantity and tries to settle at least the threshold value. If the unsettled quantity for issue or receipt is below the threshold value, system settles it regardless of the threshold value. The threshold value may be specified in the following ways:
- Minimal percent of of receipt quantity may be specified in the inventory closing parameters. In this case – the system tries to settle at least N percents of a given receipt in each settlement.
- In the field “Minimum Average Settle” of the inventory table for the given item.
- In the field “Minimum Average Settle” of the inventory parameters table. This parameters is effective only for items which do not have the specific value in the field from the previous step.
Finally – there is also an indirect way to specify a quantity to be settled. It is possible to specify a minimum amount to be settled in inventory closing parameters. In this case, the system tries to create settlements which settle a receipt to an issue for at least specified monetary amount, thus limiting a quantity to be settled.
Thus, under regular conditions (purchase prices do not considerable vary during a reporting period; number of issues is significantly higher than number of receipts) results of this averaging algorithm would not differs considerably from a true average cost calculated, roughly speaking, in Excel. But suppose we are purchasing 2-3 batches of item with thousands of pieces in them, and we are selling several thousands of batches with 2-3 item in each. Unexpectedly we are purchasing some strange batch with 5 pieces on double of the regular price. In this case, those unlucky issues, that were to be settled to this receipt, would have a cost price which is deviates significantly from true period’s average.
After the original article was written, a radical change to the weighted average cost calculation algorithm has been introduced. If costing procedure finds that we have only ONE unsettled receipt, then all issues are settled to this receipt and everything is working as usual. But if there are SEVERAL unsettled (open) receipts, then a completely different algorithm is used:
- A dummy inventory transfer is created for a given item id and a given combination of financial inventory dimensions. That is – The inventory transfer operation appears, which is processed by inventory closing as a regular inventory transfer, but it does not actually change the inventory dimension; an inventDimId field value is the same on both issue and receipt inventory transactions of the transfer. All open receipts in a period are settled to the issue transaction of the transfer All open issues in a period are settled to the receipt transaction of the transfer.
Thus actually the receipt of this transfer has a total cost of all open receipts of the period. Then – this cost is propagated to every actual inventory issue of the period by the ratio between the quantity in a given issue and a total summed-up quantity from all open receipts. This algorithm is used for the ‘Weighted average’ principle. The ‘Weighted average on date’ cost is calculated with a somewhat more peculiar algorithm. Actually, the ‘Weighted average on date’ algorithm is reduced to the ‘Weighted average’ algorithm by opening a new averaging period for every receipt. That is, on an occurrence of a new receipt, the system creates the new dummy transfer and settles the new receipt (actually – several receipts sharing the same financial date – should we have them) and the issue from the previous transfer to the new dummy transfer.
I have not personally tested the performance of algorithm described. But taking some general considerations, it looks like an inventory closing for ‘Weighted average’ would work with approximately the same performance as FIFO, and ‘Weighted average on date’ would take around 130-140% of FIFO inventory closing time.
I want to especially emphasize again that for a settlement cost’s calculation, the system uses the balance of inventory transaction on the date of the settlement. So if we purchased a merchandize on 15th, then allocated some additional markup into the receipt’s cost price on 25th, than for an inventory closing on 20th, only the initial purchase cost price, cleared from later markups would be taken into account.
So – The receipt-to-issue settlement is done. The true cost price is calculated. Why does it need some extra steps for a cost propagation ?
Imagine the following picture:
- An item was purchased on the 1st of Jan with the cost price of 2000 EUR
- On the 5h of Jan it was transferred to a neighboring warehouse. It is obvious that the estimated instant issue cost price as well as the receipt cost price for the transfer is 2000 EUR
- On the 10th of Jan the item was sold from the neighboring warehouse. The estimated instant issue cost price is 2000EUR
- On the 20th of Jan some transportation costs of 400 EUR was acquired into the initial receipt cost price (via markup allocation functionality).
- An inventory closing model for the item is FIFO
If we are closing inventory for date of the 31th of January then system settles the issue (to the neighboring warehouse) from the 5th of Jan to the original receipt and calculates the actual cost price of the issue to be 2400 EUR. It is obvious that we should somehow change the receipt cost price for the transfer to the neighboring warehouse and, correspondingly, change the issue cost price for the sales order from the 10 of Jan, to 2400 EUR too.
This task is implemented in the following way:
On the every update of an issue inventory transaction during an inventory closing, the system checks whatever a linked receipt for it exists. (Linked receipts exist for issues of inventory transfer journals, quarantine orders, transfer orders, production orders, BOM Journals and dummy transfers made for average costing). If this is a case, then the adjustment amount is written into the “Lot level adjustments” (InventCostListTrans) table, together with the lot id of the linked receipt and some additional info.
Then on a stage of cost propagation, the system reads this information, adjusts cost price for corresponding receipts, then adjusts cost price for issues settled to this receipts. Should adjusted issues have a linked receipts, then cost propagation is repeated.
Thus, basically – a cost propagation process is iterative be the nature. Through usage of an inventory settlement table, the system builds a cost movement graph which traces cost movements from an initial purchase to a final sale.
How to define a number of iterations required for the inventory closing. It is a complex issue. In a theory, the number of iterations required is calculated as a maximum number of inventory transfers for a given item id plus a maximum nesting level for BOMs with the given item, plus a number of transfers via quarantine order and so on. In a reality, the number of iterations required mostly depends on presence or absence of loops in a cost movement graph. If these loops are absent, then an inventory closing requires 5-7-10-15-20 iterations to be finished. If these loops are present, then the number of iterations required is scarcely predictable.
Why do loops occur in a cost movement graph ?
Imagine the following picture:
- On the 1st day of the month one piece of item was purchased for 200 EUR into the warehouse wh1 (Purch1)
- On the 20th day of the month another four pieces was purchased for 250 EUR a piece into the warehouse wh1 (Purch2)
- Then a transfer of 2 pieces from wh1 to wh2 was posted (post factum) on the 5th day of the month. (Trsf1)
- Then this two pieces was transferred back to wh1 on the 6th day of the month. (Trsf2)
- All five pieces was sold on the 25th day of the month from wh1 (sale1)
- Then on the 27th day of the month extra costs was acquired into the cost price of the original purchase via markup allocation.
- A Warehouse inventory dimension is flagged as a financial inventory dimension.
- We about to close an inventory for the 31th day of the month.
Before an inventory is closed, the inventory transactions has the following costs and quantities: