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.
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:
After the settlement phase (with FIFO principle), we have the following picture:
|Code||Qty||Original Cost Price||Price||Adjustment||Code||Qty||Original Cost Price||Price||Adjustment||Qty||Amount||Adjustment|
Take a notice that we are having a loop in our cost movement graph: the issue from the second transfer is settled to the receipt of the first transfer. Then, the receipt of the second transfer is dependant on the issue of the first transfer. Let’s have a look on what will happen on next iterations. I will specify an accumulated adjustment for the given issue/receipt in the adjustment field of the receipt or issue part of the table. Every new table below corresponds to another iteration of the inventory closing.
|Code||Qty||Original Cost Price||Price||Adjustment||Code||Qty||Original Cost Price||Price||Adjustment||Qty||Amount||Adjustment|
|Code||Qty||Original Cost Price||Price||Adjustment||Code||Qty||Original Cost Price||Price||Adjustment||Qty||Amount||Adjustment|
|Code||Qty||Original Cost Price||Price||Adjustment||Code||Qty||Original Cost Price||Price||Adjustment||Qty||Amount||Adjustment|
|Code||Qty||Original Cost Price||Price||Adjustment||Code||Qty||Original Cost Price||Price||Adjustment||Qty||Amount||Adjustment|
|Code||Qty||Original Cost Price||Price||Adjustment||Code||Qty||Original Cost Price||Price||Adjustment||Qty||Amount||Adjustment|
|Code||Qty||Original Cost Price||Price||Adjustment||Code||Qty||Original Cost Price||Price||Adjustment||Qty||Amount||Adjustment|
And so on… If we had not have the issue with the transfer of the item before actual the purchase of the item, then the inventory closing would finish in two iterations. In our example, the actual inventory closing will take around 5-10 iterations. Furthermore, We can estimate that the issue cost price for the sales order will not achieve the correct value of 1200 EUR and a difference will be written-off from transfer transactions as a rounding error.
So, should we have a negative inventory case for one of the time-points in a period of an inventory closing, this would lead to considerable increase in the number of iterations required for the inventory closing and to deviation of the calculated inventory cost from the actual cost.
Besides that, We should remember that for the weighted average model, as a most general principle, every issue is settled to every receipt. Thus, loops in a cost movement graph are natural for the weighted average model and happens even if inventory have not went negative in a period being closed. As of version 2009 it is still true. During an inventory closing every receipt is still being settled to every issue. Even if now it is made via a dummy transfer, not via a direct settlement, it does not change economical meaning of a weighed average inventory closing.
I should mention also, that a cost propagation does not propagate costs through inventory transactions which went through an ‘inventory on-hand adjustment’. If during the cost propagation the system runs into such a transaction, the system adjust it, but then – the system write the adjustment off to a rounding errors GL account, thus effectively stopping a cost propagation via the on-hand-adjusted transaction. It looks quite natural, for the any on-hand-adjusted receipt should be treated as a kind of completely new entity, not connected with the inventory issue from some inventory transfer.
Because it is a frequent case when an inventory is being closed for some long and not completely defined period of time, there are two fields in inventory closing parameters, which allows to limit the number of iterations in the inventory closing:
- “Maximum throughputs”. This is exactly the field which holds the maximum number of iterations allowed. If during cost propagation, an inventory closing procedure has reached the iteration with the number equal to the number specified in this field, then the next adjustment (which came from the last iteration in inventCostListTrans table) would not be propagated to the corresponding issue transaction, but instead will be written-off to a rounding GL account immediately after being applied to the appropriate inventory receipt.
- “Minimum throughput adjustment”. If value of an adjustment for the single transaction, which came from the previous iteration, is below the threshold specified in this field, then this adjustment is not propagated to the corresponding inventory issue, but is written-off to a rounding GL account immediately. I want to emphasize that this parameter stops cost propagation only for those ‘cost flows’ which falls under this threshold. E.g. if we have 3 adjustments from the previous iteration, say, 2,10 and 80 eurocents, and the value of the Minimum throughput threshold is set to 5 eurocents, then only the first of adjustments will be written-off to a rounding account (and the ‘cost flow’ will be stopped effectively). Remaining adjustments will be taken into the following iteration, and corresponding cost flows won’t be stopped.
Procedure for writing-off adjustments is described in more details in the section “On writing-off rounding errors and adjustments during cost propagation“.
There is some odd feature of inventory propagation which often cause user’s confusion. There is a rather typical user’s question: “I had already closed inventory transaction, but it was somehow modified during the inventory closing. Why ?” The answer to this question consists from two points:
- An inventory transaction should fulfill to following conditions to be marked as closed: A quantity in the transaction should be equal to A settled quantity in the transaction(inventTrans.qty==inventTrans.settledQty); A cost price in transaction should be equal to a settled cost price in the transaction(inventTrans.costAmountPosted+inventTrans.costAmountAdjustment==inventTrans.costAmountSettled) (To be absolutely precise – they should not be equal. Actually – a difference between the values must be less then some defined delta).Thus, if cost of a closed inventory receipt was adjusted somehow (may be we just have manually adjusted it through a form, or a cost price was changed during a cost propagation), then this transaction is bound to be open immediately.
- Even before a receipt-to-issue settlement procedure, the system scans all open or partially settled receipt transactions, calculates the difference between a cost price and a settled cost, and should it has one – send the difference to the propagation routine. (It is performed by the inventCostItemDim.updateReceiptAdjustment() method, which is called BEFORE call to the method inventCostItemDim.updateModel() which is responsible for a receipt-to-issue settlement.
I was thinking – could this problem be solved with some different approach ? Perhaps, upon attempt to adjust a closed transaction, instead of pushing an adjustment value to the propagation routine, the system might just write-off the adjustment to a rounding account. Say, we allocate markups to a closed transaction with the posting D Inventory C Transportation costs 50EUR, and then immediately write this adjustment off with the posting D Rounding C inventory 50EUR. Alas, this approach has some gross disadvantage: If our receipt transaction is closed already, it does not mean that the corresponding merchandize has already left our company. It could be simply moved to an another warehouse with a regular inventory transfer journal. Then, the purchase transaction has been closed with the inventory issue of the transfer, but the receipt of the inventory transfer is still open and well. Now if are we trying to book transportation and custom clearance costs to an inventory (and this costs sometimes might be comparable to the purchase cost), with the approach described, these costs wouldn’t be saved into inventory costs as an adjustment, they actually would be immediately written-off to a rounding account. (Which probably would be closed to losses in the end of period). Hence, a marginal profit for the sales order which sells this merchandize would be unreasonable high, because markup costs was not included into a cost price of the merchandize. As a bottom line – it seems that there is no alternative for the current approach.
Finally – to close the theme I want to mention that Dynamics AX 2009 introduced the extra step for cost propagation – an adjustment and calculation of indirect expenses for production orders. I will describe this later in the section “Cost breakdown and indirect costs“.
First, I want to mention, that GL postings are created by an inventory closing only if the Ledger field has been checked in IC’s parameters. Personally – I see no point in un-checking this field, because a time required for creation of GL postings is insignificant, comparing to other steps of an inventory closing. In version 2009 this field is hidden from the parameters form and is always checked.
On previous stages of inventory closing, the system has generated and filled the lines of the inventory settlement table (inventSettlement), which, besides settled quantities, settled amounts and adjustments, contains information required to create GL postings from adjustments performed: a GL inventory account, a GL offset account, posting for inventory and offset GL accounts. So, GL posting creation procedure looks pretty simple. It just sums-up and group amounts from invent settlement by particular accounting parameters. Therefore, there is no necessity in some routine that would reconcile GL postings of an inventory closing to inventory transaction data. If the inventory closing has generated GL transactions with some odd account numbers or dimensions, try to find this dimensions and accounts in the settlement table. Then, from this table, one can drill-down to original inventory transactions and check them for suspicious data.
Furthermore, GL postings creation depends from the particular inventory closing parameter, called “Specification”. This parameter allows to group GL postings not only by accounting parameters (GL accounts,Dimensions, GL Postings), but also on itemId or item group of the item being adjusted.
NB: How does the system knows that for transactions which did not generates GL postings, the inventory adjustment also should not be posted into GL during the inventory closing? During the posting of the originating inventory transaction, the system stores into the inventTransPosting table the information on the inventory account, the offset account, the dimension and, more important, whatever the GL posting has been created for this transaction(isPosted). I want to mention that account and dimension information is always stored, even if transaction has not been posted to GL. Then, during the receipt-to-issue settlement and the adjustment calculation, еру system fetches account and dimension information from the inventTransPosting table, but, If inventTransPosting.isPosted is set to No, then the accounts, dimension and posting of inventSettlement would not be filled, which will cause this record to be skipped during GL posting creation. So – If you are trying to understand meaning of some odd GL postings and You are analyzing a content of inventSettlement table, You can safely ignore any records with the Posting field set to zero (LedgerPostingType:None) because they are ignored during GL posting.
Besides that, in version 2009, on the last stage of an inventory closing, the system calculates and post variations between standard and actual costs. I will look closer at this topic in the section “New standard cost“
A parallel inventory closing from several workstation was introduced in the version 3.0sp2 and has become the most significant improvement of an inventory costing in the history of Axapta. To my opinion, since this release, performance issues of an inventory closing became solvable, whilst in previous versions these issues had been being a real terminal problems…
So – Let’s look at a multi-user inventory closing in more details.
Client (I mean – Dynamics AX reach client process), which originally have started inventory closing, will be referred as a “master client”. Every other client will be referred as a “helper client”. Just to remind – Helper client is started by click to the Calculation->Calculation Help menu in he inventory closing form, whilethe unfinished inventory closing is selected in the grid.
When the inventory closing procedure is started on the master client, it writes the user-specified parameters into the Inventory Closing table (inventClosing) to allow them to be fetched from helper clients. Then, master client writes the list of inventory items, which have open transactions, into Calculation List table (inventCostList). Then, master client, as well as helper clients, begin to perform the receipt-to-issue settlement procedure for items from this table (the routine described in Receipt-to-issue settlement section). This stage considered to be zero iteration. As I’ve already mentioned in Cost propagation section, during a settlement process, the system creates records in InventCostListTrans table, which will be used as an input data to cost propagation iterations. It is worth to mention that if some inventory item has been referenced from inventCostListTrans table, reference to the same inventory item is inserted into the inventCostList table. This is necessary for a coordination of work between multiple inventory closing clients during a cost propagation process.
The similar algorithm is also used on every cost propagation iteration. Every client runs through inventCostList records and for every inventory item referenced, it performs an iteration of the cost propagation, fetching adjustment data, which has came from the previous iteration, from the inventCostListTrans table.
The elementary unit of work for both settlement and cost propagation steps is an inventory item. Settlements creation/iteration of cost propagation for an inventory item is always performed from one client.
Posting of IC results to General Ledger is performed from the master client only.
If Calculation->Pause Calculation menu item is clicked during an inventory closing, then the system will suspend the procedure. The inventory closing session may be resumed little bit later on, by clicking to Calculation->Calculation Help. In this case, the first client which has joined the suspended closing session would became a master client.
If you are using more than 2-3 workstations in a MUIC, it is worth to check utilization of DB and AOS server’s resources. You should check both CPU and disk metrics. (I won’t discuss all necessary metrics in details here). If one of the metrics has achieved some 60-70% of reasonable maximum, one should stop adding new MUIC clients. Also, to my experience, it is a quite normal practice to start 2-3 MUIC clients form the same workstation, because most of the time they do not utilize local resources, but are waiting for a response from AOS or DB servers. It is worth to mention, that a settlement stage usually are bottlenecked by DB Server performance, not by the number of MUIC clients. Hence – Before start of a cost propagation stage, it is not worth to add too many clients to the inventory closing session.
Although MUIC designers intended inventCostList and inventCostListTrans tables to be used only as some internal mechanism for communication between MUIC clients, as a bonus side-effect, thess tables are a perfect tool for an IC monitoring and tracing. But before we proceed to some ideas on IC monitoring, let’s discuss in details the structure of these tables:
|ItemId||Item number for the item to be processed in IC session|
|Voucher||Voucher number for given IC session. It is used to lookup parameter data from inventClosing table by helper clients|
|CostNum, NumOfIteration||Two fields with opposite meanings. NumOfIteration – number of the current cost propagation iteration; CostNum – field with the opposite meaning. Roughly Speaking – Maximum Number of Iterations minus Number of iteration|
|ItemId||Item number to be processed by cost propagation|
|InventTransId||Number of receipt inventory lot to be adjusted in iteration of cost propagation|
|InventTransIdReturn||Number of returned inventory lot. Used if on sales order return (or more generally on any return) number of returned lot (inventory issue lot which is being returned) has been specified. This is special case, because adjustment value is ignored by cost propagation routine. Instead – Returning receipt lot cost price is set to the full cost price of issue inventory lot being returned (with reverse sign indeed)|
|VoucherPhysical||Is used only for quarantine order and for the returns. Is necessary only to refer to originating inventory transaction, which is a precursor for ht current transaction.|
|Voucher||Voucher number for given IC session. Is used to lookup parameter data from inventClosing table by helper clients|
|NumOfIteration||Number of current cost propagation iteration. Is used for coordination between MUIC clients. It prevents the attempt of certain client to proceed with the next iteration, while the most of client still process the current iteration.|
|Adjstment||Adjustment value itself.|
A receipt-to-issue settlement stage considered to be the stage number zero. InventCostListTrans table does not contain records for this stage
In version 2009 technical infrastructure for MUIC has been changed significantly. First of all, this is caused by the transition to the new batch server technology. Although this topic is quite interesting by itself and deserves a separate article, I will briefly enumerate ideas behind the new batch server technology:
- In DAX 2009, batch task complying to the new batch server infrastructure, are performed on the AOS server, not on a dedicated workstation with the regular Axapta’s reach client. This was made not in intention to save customer’s $800 for a dedicated computer, but to support an environment for parallelization of computational intensive tasks. It is clear, that in mid-range prospect, the number of processor cores in a single processor would be quickly increased, while performance of a separate core would increase for some tenth of percents for every doubling of cores per CPU. Hence, if we want to lay some groundwork for further performance and scalability increase, we should provide support for multithreaded execution of the most computational intensive algorithms of Dynamics AX. It is more natural to base such support on the server side (which is already multi-user and multi-threaded), than to develop it on the base of an old client-based batch server. An old batch server is still supported and used by some existing modules, but it is clear, that it will become obsolete in the near future.
- New batch infrastructure supports the API for submitting a batch task into a batch queue (BatchHeader class). It provides means to specify dependencies between tasks or to spawn several tasks to be executed in a parallel.
- New batch infrastructure currently is supported only for a few especially computational intensive tasks. From the first sight, decent parallelization support is provided for an inventory closing and a MRP planning; more or less satisfactory support is provided for a GL journals posting; support for a sales order/purchase order posting parallelization is pretty rudimentary. (If I understood correctly, it works only in the case of posting of multiple invoices for multiple POs/SOs. It would not decrease the time required to post the ONE invoice for ONE sales/purchase order with large number of lines. From the other side – POs/SOs with several thousands of lines is a quite seldom occurrence; May be I just too harsh on these changes to order posting).
In our particular case, to turn on the new parallelization mechanism for the inventory closing, we should specify values for Helpers Batch Group and Extra Batch Helpers fields in Inventory module parameters. I can not provide an exact hint for setting the number of helper processes (I still have not used this mechanism in real implementations for now), but I would start with the number of cores on AOS’s CPU multiplied by two. This implies also, that the number of threads for a batch server specified in Administration->Setup->Server Configuration->Batch Server Schedule is greater or equal to the number of helper processes. Surely – If the same server is also utilized for some routine user operations, we should specify a lower number of helper processes. The effective number of active helpers would be equal to a minimum between the value specified in the inventory module parameters form and the number of active threads specified for the given time interval in the batch server administration form.
The old user interface for starting/stopping helper processes remains in place, but it is used to start/stop helper threads on he batch server, not helper clients as in an old version.
By the way – In version 2009, a cancellation of inventory closing is also performed in parallel. Before that, some implementation (especially with weighted average costing being used) had a non-solvable issues with a cancellation of inventory closing. Due to usage of MUIC they had been performing an inventory closing in reasonable time, but since an IC cancellation had been performed in a single-client mode, it led to unreasonably long time period required to undo closing. Upgrade to version 2009 may become a good relief for this kind of issues.
The most convenient way to monitor an inventory closing is just open another Axapta client and browse through the inventCostListTrans table with thr table browser after inventory closing has been started.
If you sort records by an iteration number, then you can instantly see – what iteration is processed currently. As a rule, if the implementation supports some more or less serious logistics, then inventory can not be closed with less than 5-7 iterations. If your inventory can be closed for the lesser time, then you probably is not interested in IC’s performance and can safely skip the rest of the section. During these 5-7 iterations, number of records in InventCostListTrans table is usually decreased from tens of thousands to several hundreds or even several tens. After these iterations has been finished, it is worth to look more closely on the content of this table. Perhaps-you have noticed several inventory lots, for which amount of cost propagated (in the adjustment field) is being gradually decreased from iteration to iteration somewhat unusually slowly. Say, If the adjustment is 600EUR and it is decreased only for 10 eurocents with every couple of iterations – it is just about time to try to understand – whatever we have some issues with this inventory lot or not ?Most probably, the reason for such behavior is that inventory went negative for the given item in some point of time of the period being closed.
If you are a perfectionist or value of this hanging adjustment is rather high (say – 6000 for the company with monthly revenue of 6000000), then one should write down the ids of lots being adjusted, stop the ongoing inventory closing, cancel it and begin to sort out the lots in question. If you have found some cases of negative inventory, then you have to accurately reverse these transactions, mark reversed transactions to reversing ones (or use returned lot number mechanism) and attempt to run another inventory closing for the same period. (Discussion about the marks and returned lot mechanism follows in the “On reversals” section).
If you find that the hanging adjustment value is inconsiderable comparing to the period’s turnover (say – 50 for the company with the monthly revenue of 50000000), you can just adjust (in the table browser) the value of MinTransferValue of the appropriate record in the InventClosing table. This is exactly the field which is used to store the value of the “Minimum Throughput Adjustment” inventory closing parameter. If you specify the value which is higher then any of remaining adjustments being propagated, then on the next iteration, all remaining adjustments would be written-off to to a rounding GL account and the cost propagation stage would finish. Usage of this technique would not compromise overall precision of company’s financial statements, although it will definitely lead to somewhat inconsistent COGS/Marginal profit values for particular sale orders. So – the basic principle of this technique is the following:
- On the start up of inventory closing we specify some reasonably low value for “Minimum Throughput Adjustment” field (say 1EUR or even 0.50EUR)
- After inventory closing has been run for some 20-300 iterations, we change the value to something considerably high (say 100EUR).
As a result – inventory is closed with high precision for ‘correct’ inventory transactions and with tolerable precision for ‘incorrect’ ones.
That is why it is so important for estimated issue cost not to vary to much from true issue cost. If the cost propagation graph has loops and a propagation adjustment is being decreased too slowly, then every euro of the difference might cost and extra 5-10 iterations of an inventory closing.
Besides that, I advise you to develop some inventory checking functionality which would run through open inventory transactions and warn a user if inventory went negative. If you were to run this functionality before inventory closing, you would not wait for 20-30 iterations to find suspicious inventory transactions. I saw several implementation of this functionality developed by different partners. I do not know, why Microsoft still has not developed it. In version 4.0 some primitive form of an inventory checking was introduced (Close procedure->1.Check open quantities in Inventory Closing form), but it is worthless because it checks only a inventory on-hand quantity for the date of inventory closing and do not check it for every date of period being closed.
Finally, It is worth to mention that for some businesses, negative inventory is a quite regular case. E.G. if some company is selling merchandize ‘on-the-fly’, instantly upon arrival, it is quite natural to sell it and invoice it, several days before purchase invoice is arrived and booked. In this case the only way to improve IC performance is usage of MinTransferValue nudging technique I’ve described already. Sure, In this case COGS and Marginal profit for separate sales would be definitely inconsistent, but for these type of businesses, a cost analysis for separate sales order is usually pointless.
Dynamics AX supports not only inventory closing procedure, which calculates a final issue cost price for every open inventory transaction of a period (blocking the period for further posting of inventory transaction), but also an inventory recalculation procedure. This procedure differs from the an inventory closing in the following way:
- Receipt-to-issue settlement is performed ‘in-memory’, that is – no settlement records are written to the inventory settlement table.
- If a cost price need to be adjusted, an adjustment record is written to the inventory settlement table. This record has the adjustment value filled-in, but settlement qty and settlement amount fields are left empty.
- As a consequence of first two points – the system performs ‘in-memory’ receipt-to-issue settlement before EVERY iteration of cost propagation. It looks quite logical, for if we do not have settlement data stored in a database, we have to rebuild one every time, just to know what issue must be corrected for the cost propagated to the given receipt.
- Recalculation may be entertained for the single item or item group. The only thing that needs to be remembered is that if given item have been issued for the BOM journal or production order, it might result in adjustment of an other item’s transactions, because in this case cost is propagated to other inventory items.
- During inventory recalculation, system does not write-off rounding.
If we think about the 3rd point more deeply, we can draw a conclusion that an inventory recalculation is ALWAYS slower than an inventory closing, because the settlement stage is performed many times. Surely – It can save some time by omitting many inserts to the inventSettlement table, but probably – saved time will be overweight by the time spent for making a settlement.
Finally – I want to discuss the idea, that having an inventory recalculation run every day can significantly improve performance of final inventory closing. Usually – this is not a case. Of course – in ideal world, if recalculation has been run every day, and there was no post factum posting of receipts and issues, then the inventory closing run would not result in many adjustments, and since we do not have so many adjustments, cost propagation would require not so many iteration to finish. But actually – in this ideal case, a simple inventory closing without preliminary recalculations also would have pretty decent performance. Usual case for low performance of IC is a negative inventory and loops in a cost graph, and this can not be resolved with just periodic inventory recalculations. So – inventory recalculation only meant to calculate realistic issue cost prices before the final inventory closing, not to improve the performance of an IC.
Algorithm for IC’ cancellation is simple and quite obvious:
- The system runs through old inventory settlements of IC or inventory recalculation and marks them as cancelled.
- The system copies the old inventory settlements into new ones, reverting values of settled qty, settled amount and adjustment amount.
- The system subtract values of the fields mentioned from corresponding fields of originating inventory transactions.
- New settlement records are posted using the same approach as described in GL postings creation section. Before version 2009, regular settlements and cancellation settlements were posted with the same class (same piece of code). In version 2009 this logic was split into separate classes to post a cancellation and to post regular operation. In version 2009 inventory closing procedure was overloaded with logic to perform a variation posting (for new standard cost mechanism) and a indirect cost posting. Thus result in separation of posting and reverse posting logic
It is also worth to mention that an IC cancellation procedure is used as well to cancel an inventory on-hand revaluation or an inventory transaction revaluation.
There is no separate setting in Axapta to support a per-batch inventory costing. But if we set the “Financial inventory” flag for Batch or Serial inventory dimensions in inventory dimension groups, an inventory closing would calculate costs on per-batch or per-serial basis, regardless of the inventory model specified, because receipts and issues will be settled only inside a single batch (or serial). It is reasonable to set a model for inventory items with per-batch costing to a FIFO inventory model, because it has a fastest processing time.
An architecture of an inventory costing in Axapta implicitly assumes that an inventory lot is the most elementary, atomic unit of cost. So – even if we have several records of inventTrans table with the same lot id, they all are treated as one atomic element of a cost.
From the first sight, this approach seems to be quite logical, but there is also a catch. Say, we have some item with a per-batch costing (The Batch dimension is specified as a financial inventory dimension), and we have two batches – the first batch has 2 pieces for 10 EUR apiece, the second batch has 3 pieces for 15 EUR a piece.
Then – we create a transfer journal for 5 pieces. If the Batch inventory dimension has not been marked as a primary dimension, we can leave this dimension blank in a journal line. Then, during reservation, the system will automatically split issue and receipt inventory transactions for journal lines, substituting available batch numbers into every new line of the inventory transactions table. This is convenient for users, because it allows them to save time required to specify batch number, if they do not care about the batch number for this specific line of the journal.
But after this journal has posted, we will find that all receipt transactions have the same receipt price of 13 EUR. During the posting of the journal, the system use WHOLE LOT’s issue cost as a receipt cost. The lot’s issue cost is 65EUR, then this 65EUR is evenly allocated to every receipt inventory transaction, thus resulting in price of 13EUR apiece. An inventory closing won’t fix this problem, because adjustments for a cost propagation is processed in per-lot basis. Therefore, during inventory closing any adjustment would be again evenly allocated across all receipts. Such a case often lead to a lot of complaint about “Axapta broke my cost price”.
When I first met this issue, I tried to storm it by patching the transfer journal posting procedure and the inventory closing procedure. But it turned out, that the problem seems to be unsolvable for the general case. E.G. we can issue several batches in one transfer line and combine them into one new single batch. And what should we do if the transfer changes not only physical inventory dimensions (like WMS cell), but also financial ones ?How to establish the link from issuing sub-lot and receiving sub-lot ? And what, in most general case, this sub-lot is ? What is a most natural, easy comprehendible, atomic unit of cost ?
This case might also be an example of an attempt to resolve the business issue with an IT-approach. If we look into situation closer, we would run into a requirement conflict: From one side, somebody (most probable – peoples from accountancy/finances) insists on the per-batch costing. Probably, it implies that batch numbers are used to keep track of something (maybe – vendor of the item, may be some special discounted price from vendor, may be it is used to keep track the to-stock/on-order status of an item etc). From other side – some other departments do not care at all about a batch number and trying to make the system to substitute these batch numbers for them. In this case – there are two possible solutions for the problem:
- Insist on a batch number to be specified by a user. The project sponsor should make a SEVERE EXPLANATION to end users about meaning of batch numbers for the company.
- If there is no some implied info associated with batch numbers, It might be useful to push the accountancy into usage of the FIFO costing and discontinuation of the batch number usage. If a batch number is used ONLY to track the exact source of an issue cost price, then it might be much more useful to just run an IC with FIFO model and develop some report which would show the originating purchase transaction for every issue transaction from settlement data.
This section is dedicated to the inventory settlements table (inventSettlement). Strictly speaking, usage of word “Settlement” in the name of this table is misleading, because this table is used not only to store receipt-to-issue settlement information, but also for keeping a track of adjustment made for given inventory transaction. Although for issue inventory transactions, an adjustment is always calculated from settlement data, for receipt transactions an adjustment may be made completely independent from the settlement. Also, if an adjustment of an issue cost price is made during an inventory recalculation, then the settlement would be made ‘in memory’ and the settlement record would actually keep only the adjustment value, but not the settlement data. Let’s have a more closer look at the table’s fields:
|TransRecId||Reference to the RecId of the inventory transaction being settled/adjusted|
|InventTransId||Inventory lot being settled/adjusted|
|ItemId||Item number of the inventory lot being settled/adjusted|
|ItemGroupId||Item group of the inventory log being settled/adjusted|
|TransDate||Date of settlement/adjustment|
|Voucher||Voucher of the settlement/adjustment. Might be used to drill-down to originating inventory closing/markup posting/etc|
|SettleTransId||Surrogate key. Actually – just gradually increased counter. Value of this field is used to fetch receipt-to-issue settlement info. Actually – settlement is stored as TWO settlement records – one for the issue and one for the receipt. This TWO records will share the same SetleTransId|
|QtySettled||Settled quantity. If given record was created as an adjustment (not the settlement), this field is blank.|
|CostAmuntSettled||Settled amount.If given record was created as an adjustment (not the settlement), this field is blank|
|CostAmountAdjustment||Adjustment amount. Field is always not blank for adjustment records. For settlement records this field is has some value only if settlement has resulted in adjustment.|
|BalanceSheetAccount||Inventory account for the adjustment.|
|OperationsAccount||Offset account for adjustment|
|BalanceSheetPosting||GL posting type for inventory side’s GL posting of adjustment|
|OperationsPosting||GL posting type for offset side’s GL posting of adjustment|
|Dimension||Dimension to be used during posting of the adjustment|
|Canceled||Is True if given settlement was cancelled|
|SettleModel||Settlement Model. Roughly speaking – is something like inventory model|
|Posted||True if this record was processed by settlement GL posting procedure.|
The Receipt-to-issue settlement created by inventory closing, is stored in this table as a two records – one for the issue and one for the receipt. These two records share the same SettleTransId.
For adjustments – this table contain only one record per adjustment.
Fields related to GL posting are filled with information from the inventory posting table (InventTransPosting) linked to the inventory transaction being adjusted. If there no record in inventTransPosting has been found by some reason, then the system fetches the inventory account from the inventory issue/receipt settings of the inventory posting setup and offset account – from the inventory profit/loss settings of the inventory posting setup. I should mention also, that the inventTransPosting record is filled during the financial update of the inventory transaction and data remains in this table even if a source inventory document (sales order/inventory journal/production order) has been deleted.
It is worth to mention, that an adjustment to an inventory transaction not always leads to creation of GL postings. Say – If adjustment for a quarantine order transaction was made, this should not lead to GL posting creation, because the originating quarantine order has been posted without GL postings creation. The system keeps track of whatever the original document was posted into GL in isPosted field. If this field was set to false during originating inventory document posting, then system does not fill account fields in the inventorySettlement table and these records are ignored by the GL posting creation subsystem. So, if you are reconciling the inventory settlements to GL, you can safely ignore records with blank account and posting fields. It is also worth to mention that the Posted field in the inventSettlement table does not related to actual posting of the record into the General Ledger. This field is just a flag, which is used to mark records processed by the GL posting creation routine.
It is worth to have a closer look to that fields of inventory transactions, that are related somehow to cost and inventory closing:
|CostAmountPosted||Original receipt cost price for receipt transactions; Estimated issue cost price for issue transactions.|
|CostAmountAdjustment||Adjustment value. Must be always equal to the sum of adjustment from inventSettlement records connected to this invntory transaction|
|CostAmountSettled||Cost amount settled during IC. For closed inventory transactions – is equal to CostAmountPosted+CostAmountAdjustment|
|Qty||Quantity in inventory units (not purchase or sales units)|
|QtySettled||Quantity settled during IC.|
|CostAmountPhysical.||Cost price for physical operations. (E.G – packing slip posting). For receipt transactions, It is usually equal to the price fro purchase order line; For issue transactions – to the estimated issue cost price. Attempt to use the value of this field for any substantial reports is probably sign of bad design. This field only meant for the ROUGH evaluation of potential cost prices of non-invoiced inventory transactions.|
|CostAmountStd||Value of the standard cost used on posting. (Fixed receipt cost – in DAX2009). Is set to zero, if standard cost (fixed receipt cost) is not used for given item.|
|CostAmountOperations||Cost amount posted to operations (not-inventory) account. Actually – only filled-in for purchase transactions of service items.|
|ValueOpen||Closed/Open tag for an inventory transaction. Transaction is marked as closed if CostAmountSettled==CostAmountPosted+CostAmountAdjustment and abs(qty-qtySettled)<0.00000001|
|DateClosed||When transaction is being marked as closed, system sets this field to the date of the last settlement made for the transaction.|
There are two completely different mechanisms to create returns:
- To return inventory issue, the “returned lot number” mechanism must be used. This returned lot number may be specified in, for example, a sales order line or an inventory journal line. Then, this lot number is being copied to an inventory transaction (into inventTransIdReturn field). During an inventory closing, for the transactions with this field set, system finds an originating (being returned) inventory transaction, fetches the original issue cost amount from it, and set cost price of receipt (return) to this value with the sign reversed.
- To return inventory receipts, the “marked inventory” mechanism is used. This mechanism will be described in more details in the rest of the section.
So – The system allows to mark inventory receipts and inventory issues to each other. It means, that the value of an inventory lot number (value of inventTransId) of the first party is written to the inventRefransId field of the second party and vice versa. During an inventory closing or an inventory recalculation, such linked transactions go out of the regular settlement and cost propagation routine. Instead of that, these transactions are settled directly to each other regardless of the inventory model. Consequently – the issue cost price become exactly the same as receipt cost price of marked receipt. It is also worth to mention, that both during a marking and during an inventory closing/recalculation of marked transactions, the system also checks the consistency of financial inventory dimensions between the transactions. That is – If you set the batch number as a financial inventory dimension, an issue and receipt with different batch numbers would not be allowed to be marked to each other. Even if somehow (e.g. by direct write to inventTrans from developed code) this transaction become marked, the system will clear this marking during an inventory closing and just settle this transactions with the regular routine.
Transactions may be marked both before a financial update and after a financial update. It is not allowed to mark an open transaction to a closed one, because during an inventory closing/recalculation this closed transactions are ignored, thus voiding the very idea of marking.
Unfortunately, besides cost calculation, marking in Dynamics AX is also used for some other purposes:
- On reservation in ordered, marking allows to link this reserved issue transaction toa particular receipt transaction in “Ordered” status. Then – on physical update to this receipt transaction, exactly this linked issue transaction would be updated to “Reserved” status. So – We can associate our “Reserve in ordered” to some specific estimated receipt.
- In MRP, marking is used to store the information about covered/covering requirements. Roughly speaking, if two inventory transactions are marked, then corresponding records in Net Requirements (reqTrans) would be considered as covered/covering. Respectively, if we are firming planned order created from Net Requirement, then receipt inventory transactions created would have marked to covered issue inventory transactions.
It is worth to remember about these mechanisms, because often markings created from reservation or MRP planning, results in broken inventory costs. If too much of inventory transactions have been omitted from the regular costing routine because of markings, costing would effectively run in the per-batch costing mode, thus ignoring an inventory model settings for an item.
Consider the following situation: We purchased 3 pieces of a merchandize for 10EUR. Then every piece has been sold (in separate sales orders). Say, we had not had any operations other than the purchase and sales. After an inventory closing, the purchase cost is distributed evenly between issue transactions, thus resulting in the issue cost price of 3.33EUR per transaction. Receipt transaction is much more interesting case. From one side, the settled quantity is 3 pcs (Whole receipt is issued and settled; Receipt must be closed). From the other side – The settled amount is 9.99EUR, thus meaning that the receipt still can not be marked as closed, for the receipt cost price has open balance of 0.01EUR. In such a situation, the system will just revaluate the receipt, writing-off remaining monetary balance to the receipt offset account a profit and loss account from inventory posting setup. (It is worth to mention that a receipt offset account for the purchases is a purchase consumption account from inventory posting setup). That is, after an inventory closing, our receipt inventTrans will have original the cost price of 10EUR in costAmountPosted field and -0.01 of the rounding error in costAmountAdjustment field, and transaction will be marked as closed.
If the inventory closing is about to stop cost propagation (either because maximum number of iterations has been exceed, or minimum throughput adjustment has been achieved) it uses the same approach. Kind of – We received some adjustment from the previous iteration, we adjusted the receipt. Now we normally would apply this adjustment to settled issue transactions. But we won’t do it, because we are due to finish cost propagation. So – To avoid misbalance between the receipt and the settled issue, we just revaluating the receipt to the value of adjustment. Now, when we finally do not have an adjustment to propagate we can safely remove this cost flow from further iterations…
Update: After very initial version of this article was published, I looked into rounding mechanism of version 4.0 and found the following picture: In version 4.0sp1 method initInventSettlement of inventCostItemDim class, has new parameter added (_errorAdjustment). This parameter is set to true if this method is called from the createErrorAdjustment method, which, in turn, is implementing this rounding errors mechanism. It turned out, that service pack 1 introduced changes to the handling of rounding errors: If the parameter mentioned is set to true, then the system uses the profit and loss account from the inventory posting settings as an offset account for an adjustment, not an offset account from the InventTransPosting of the receipt transaction as usual. Before this change, all rounding errors were not, actually, written-off to dedicated GL account, but was hanging indefinitely on the offset account of the original receipt. If you still use any version older than 4.0sp1, I recommend you to backport this change to your version. Furthermore, I consider idea of writing off rounding errors to a P/L account as somewhat dubious. It would be much more convenient for an end-of-period reconciliation, to write-off rounding errors to some dedicated GL account, and then close this account to either profits or loss accounts during an end-of-period balance reformation. To achieve the required effect, you need to modify method inventAdj::errorAcountOperation(). I would use variation account from standard cost setup for this purpose. If we actually use a standard cost mechanism for some items, then this is very account intended for all receipt cost variations; If we do not use standard cost, we can just adopt this existing settings field for our own logic. If this scheme fits you, You would just change the values of InventAccountType::InventProfit and InventAccountType::InventLoss onto InventAccountType::InventStdProfit and InventAccountType::InventStdLoss respectively, in the inventAdj::errorAcountOperation() method.
Some people thinks, that an inventory closing just ignores inventory items having standard cost mode checked in inventory model. This is not a case. Moreover, if standard cost value of an item has changed recently, then the inventory closing for e.g the weighted average inventory model, would average the cost of old receipts (made on old standard cost) and the cost of new receipts (made with new standard cost). That’s why it is highly recommended (not because of some deep technical issues of a standard cost in DAX, but just as a good implementation practice), just revaluate on-hand inventory for standard cost based items, to the new standard cost value multiplied by an on-hand quantity, if standard cost value is about to be changed.
Besides that, I want to mention, that when the system write-off a difference between a standard cost and a purchase cost to a variation account, it proceed with the standard transaction revaluation procedure (It creates the adjustment record in inventSettlement and writies the adjustment value to costAmountAdjustment field). Thus, after an initial financial update of inventory receipt, the field CostAmountPosted will contain the original purchase cost, the field costAmoutStd – the standard cost value and the field costAmountAdjustment will contain the variation between the standard cost value and the purchase cost.
DAX2009 supports a completely new way of work with a standard cost price. The model, I describe here, is renamed to “Fixed Receipt Cost”.
I should make some preliminary point, that Dynamics AX has ambiguous model for setting up service purchase/sales:
- There is a special inventory item type “Service Item”. No records in inventory on-hand table (inventSum) are created for items of this type; These items can not be reserved; Generally – the system prohibits all operations which can not be performed for a non-stockable item.
- There is a check box “Post financial inventory” in an inventory model setup. If this check box is unchecked (which is as case for 99% of service items),then on a financial update of an item’s purchase, the cost is booked to a cost account (Purchase consumption account) instead of an inventory account as usual. (By the way – the cost amount is in this case is written to the costAmountOperations field of instead of the costAmountPosted field). On any inventory operation other than a financial update of purchase, no GL posting is created at all.
An inventory closing for service items is simply meaningless. Actually, an inventory closing is necessary to calculate exact amount of costs transferred from inventory accounts to cost (COGS particularly) accounts. If we already have booked some item’s purchase expenses directly to cost accounts, what is the point in performing an inventory closing for this item ? Why should we spend time and resources for the inventory settlement if we aren’t going to calculate an issue cost price from this settlement? That’s why, starting from version 4.0sp1, service items was excluded from the regular inventory closing routine. For all service item transactions, an inventory closing just sets transaction’s settled qty to total qty, closes transaction, and creates the special settlement for this transaction, which has a “service item settlement” model in SettlementModel field.
It is worth to remember about this new feature if you are upgrading from previous version and you have checked the “Post financial inventory” checkbox for service items in the old version’s implementation.
Probably everybody (even a fresh newbie) knows that Axapta has a mode, which allows to sell some inventory item before a corresponding purchase was posted (Negative physical inventory and Negative financial inventory checkboxes in inventory model).Although, from time to time this mode is turned on (e.g. for service items), generally this mode is turned off for the most of inventory items. That’s why newbie consultants become so confused, when they find that, despite a turned-off negative inventory mode, inventory, somehow, went negative for some time periods.
It is caused by the fact, that during physical or financial update of an inventory transaction, Axapta checks only CURRENT inventory on hand. And if some merchandize arrived yesterday and we updating the issue on date before yesterday, then the system allows us to do it.
Why it was designed that way ? In one of my projects, I redesigned and redeveloped the negative inventory check for financial update of inventory transactions. I had to write the code that checks an inventory quantity for the date of the issue, and then runs through all inventory operations for the given item and combination of inventory dimensions AFTER the date of operation, to check inventory to become negative. If an inventory issue was made on the current date, then this check was performed only slightly slower then regular check. But if operation was performed on the date, which was some 20-30 days from the current – a delay was quite noticeable, around +40-50% to time of a regular inventory update. Moreover, this check also produced a lot of locks for inventory on-hand data. But when I tried to develop the similar check for a physical update of inventory transaction, I ran into the much more serious problem. It was not clear for me- How should I implement the check for a physical update of an inventory transaction in Reserved status. It might happen, that a sales manager reserved some merchandize TODAY, but the system prevents it from being sold post factum, because there had been no enough inventory on hand for the date used. So – In this project I made a new negative inventory check for financial operations, but for physical operations, I left standard check intact. Since post factum posting was a quite seldom occurrence at this implementation, this approach did not lead to some serious problems. But I think it is not reliable enough to be implemented as a standard solution.
As a conclusion – I am afraid that there is no way to provide full support for a negative inventory check on every inventory issue. I think – actually Microsoft should provide the users with some report, which would periodically check open inventory for the occurrence of negative inventory in an open period (from the last inventory closing to now).
As a most general case, the instant estimated cost price is an instant average cost. That is – the current monetary on-hand value for the given item and given combination of financial inventory dimension is divided by the current quantity on-hand. This calculation take into account only financially updated transactions (roughly speaking – invoiced inventory transactions).
Generally speaking, degree of accuracy for instant cost price calculation is not very critical. If a cost movement graph does not contain loops, there is no dependency between an estimated/actual cost variation and a time required for inventory closing. But if the cost movement graph does contain loops, then every additional percent of an estimated/actual cost variation would lead to additional cost propagation iterations. If our inventory has the same item with costs of 400,600 and 700 EUR, and once we have written-off the item with the cost price of 1200EUR, probably, (even with loops in cost movement graphs) increase in inventory closing time would not be drastically increased. But if an item is written-off with the instant cost price of 100000000EUR, then we definitely will have a problem with inventory closing performance. Moreover, if we will use some unreasonably high estimated cost price, our monetary value of inventory on hand would go negative and we would face the situation when in inventory on-hand data we having 3 pieces of the item, with total cost of -600EUR. This situation (if happened) can be relieved with an inventory recalculation or closing.
Now we should discuss several special cases. If Include Physical Value checkbox is checked in inventory model setup, then quantities and amounts from purchase packing slips are also included into an instant average cost calculation. If there is a considerable lag between time of purchase packing slip posting and invoicing, then turning this mode on, might improve the accuracy of instant average calculation.
From some Axapta Sp3 to at least Dynamics AX4.0sp1, this include-physical-value functionality had a nasty bug, which sometimes led to insanely high values of estimated issue cost prices (billions and trillions). So – Be advised against usage of this feature if you have a version/service pack released before summer of 2008.
If during instant average calculation it turns out that inventory on hand does not have an item in question, the system uses the price from inventory table (item details->price discount->base price->price) instead of an instant average. The same value is used for items with a standard cost model. (Renamed to Fixed Receipt Price model in DAX2009).
If system calculates the cost price for a marked issue inventory transaction, it actually calculates the cost price of the marked receipt transaction, and then uses it with reversed sign. This approach decreases an estimated/actual cost price variation for marked transactions.
From newbie consultants I quite often hear the complaint about ‘odd GL postings’ on invoicing of the purchase return. Something like – “Why I’ve got D Inventory C Accounts Payable 1400EUR on invoicing a purchase, and D Accounts Payable C Inventory 1400EUR; D Other Expenses C Inventory 200EUR on invoicing the purchase return?”
Here is the reason for the case: We must post the return amount (1400EUR) to Accounts Payable account. From the other side, if inventory marking is not the case, both estimated and true issue cost prices might differs from the return amount. (In our case – estimated cost is 1200 EUR). DAX tries to resolve the problem with the following approach:
- Return amount (return price multiplied by the quantity) is posted to Debit of Accounts payable
- Estimated issue cost price is posted to Credit of Inventory
- Variation from return amount and estimated issue cost price is posted to Consumption account (specified as a Purchase consumption account in inventory posting setup).
In an end of accounting period, after inventory has been closed, this consumption account contains kind of ‘Financial Result for Purchase Returns”. E.G If we managed to return an outdated merchandize to a supplier with the return prices being higher than cost price, then Consumption account would have negative balance (Profit). If it is not a case – then it would have positive balance (Loss). In the end of accounting period this account would be closed to Other Gains/Other Losses account.
Moreover, if after posting of returns, we have marked them to the transactions being returned, and return prices had not been different from original purchase prices, then after inventory closing, we would have the zero balance on the consumption account, because adjustments made during the IC would reverse the postings to the consumption account.
Although I have not written this before, but usually only financially updated inventory transactions are processed by an inventory closing. (I mean – inventory transactions in Sold or Purchased statuses, turned into these statuses during an invoice posting, inventory journal posting and so on). It is quite reasonable, because during a physical update, an actual cost price of receipt is not known, a physical issue transaction can be returned without being invoiced and so on. That is why, before Axapta 3.0sp2, physically updated inventory transactions were completely ignored by an IC process.
But starting from Axapta 3.0sp2, physical inventory transactions are included into an inventory closing process, but only as a second-class citizens. IC take physically updated transactions into account ONLY IF the inventory item has ‘Include Physical Value’ mode checked in inventory model group. Here is a main differences in costing process for these transactions:
- These transactions are not being firmly settled during inventory closing. I mean – They are settled between each other and with regular (financially updated) transactions but only in memory. No regular settlement record is made in InventSettlement if one of the settled transactions is physically updated.
- An adjustment might be made for the settled physical issue transaction. This adjustment is added directly to the costAmountPhysical field (not to the costAmountAdjustment as for financial transactions). The InventSettlement record for this adjustment has the special settlement model – Physical Value
- On the financial update of physical inventory transactions adjusted by IC, ADJUSTED amount is being reversed.
- Inventory transactions is included into inventory closing, deciding from a physical update date, not financial one. So – If you have a transaction, which was physical updated on 31.09 and financially updated on 03.10, then this transaction would be included into IC for September.
I’ve met quite dubious results of attempt to use this mode on actual implementations. First of all – usually nobody cares about the date of physical update (packing slip update). I witnessed the cases, where inventory transaction was updated with financial date, which was earlier than physical date. Kind of – Merchandise was processed by accountancy about 10 days before it has actually arrived to warehouse. The second – If by some reason purchase packing slip has not been invoiced before end of period, then quite regular sales invoice will be skipped from regular costing and settlement in the current reporting period, but, later on, it will be unexpectedly settled to some out-of-order receipt transaction. All the accountants would be very suspicious, why the August sale was settled to the December’s purchase. 🙂
This feature is not bad or good by itself. It can be turned on without any problems, but You have to guarantee that :
- Physical and financial posting dates agree to each other. Financial update date must be greater or equal than physical update date.
- It is Ok to have unposted purchase invoices during an inventory recalculation. But before an end-of-period period closing, all the purchases must be invoiced.
Generally speaking, DAX2009 has introduced the considerable array of changes and extensions to the costing functionality, primarily aimed at providing a full support of an IAS-compliant standard cost inventory valuation. Some of these changes also have impact on the features, which do not related to the standard costing, but I am going to discuss all these features in a single section, to prevent the information from being scattered across the article.
Honestly speaking, I have not actually used new features in a real implementation, so most information was gathered from the X++ source code analysis. It is quite possible that I have mistaken in some details. Treat this article as a concepts guide, not as a detailed user guide.
Before proceed any further, I want to mention that old standard costing mechanisms are still in place and can be turned on by checking “Fixed receipt cost price” checkbox in Inventory Model groups setup. New standard cost functionality is turned on by specifying the “Standard cost” value in the “Inventory Model” field of Inventory Model groups setup.
DAX2009 has the history of standard cost values implemented. To look at or change the value of the standard cost for an item, the Price button in the inventory details form should be pressed. There are two tabs on the form appeared: The left contains planned, but still not active prices (itemPriceSim table). The right tab contains the already activated standard cost values (ItemPrice table). It is not possible to enter a value into the right tab directly. First, the new price should be entered into itemPriceSim table, and only after that, it might be activated by pressing the particular button. For a BOM inventory item direct editing of cost prices is disabled even in the simulation tab. We will discuss reason for that in the next topic, which will discuss standard cost variations.
I am glad to mention that activation procedure not only changes the value of standard cost in the history table, but also revaluates inventory on hand to the new cost value. Moreover, it is worth to list following facts on the new standard cost mechanism:
- In case of post-factum posting of an inventory receipt, the standard cost which was active for the receipt date is used.
- In case of post-factum posting of an inventory receipt, this receipt goes through the full chain of cost changes, if new cost values have been activated after the posting date. Say, In March we had a standard cost for an item of 70EUR.From the 1 April it has changed to 75EUR. If in an Aril we are posting the item receipt with the date of March 26, it will be received with the cost of 70EUR, then automatically revaluated to 75EUR (with revaluation date 1 of April). It is somewhat untypical for Dynamics AX, but the system will use voucher number of the original receipt (say – a purchase invoice), while the voucher date will be equal to the evaluation date. Although system has provided support for a multiple dates per single voucher number since version 3.0, actual usage of this feature seems to be infrequent.
- The new standard costing is supported for physical inventory transactions. That is – after posting of a purchase packing slip, the value of costAmountPhysical field will be calculated from a standard cost, not from the cost specified in a purchase line. NB: AFTER a financial update is posted, adjustments for physical costs are cancelled. That is – before a purchase invoice posting, costAmountPhsycal contains a standard cost value, after invoice posting – just a cost from the purchase line. Moreover, some standard cost internals calculate a variance as a difference between financial and physical cost prices in an inventory transaction. (costAmountPosted+costAmountAdjustment-costAmountPhysical)
- After every financial inventory operation, an inventory on-hand value is checked for validity (invent on-hand value must be equal to an on-hand quantity multiplied by an active standard cost). If this is not a case, the system proceeds with an automatic invent on-hand value adjustment. This adjustment is not spread across all open inventory transactions as usual, but is linked to the last updated transaction. Besides, because of performance limitations, an on-hand value is checked for the current date, and not for the date of the operation. I beleive, it is not significant, because in case of a standard cost, a rounding error which has occurred in a previous period, definitely would lead to the occurrence of the same error in a current period.
- A manual invent on-hand value adjustment and an inventory transactions adjustment is disabled for the inventory items with the new standard cost inventory model. Moreover, these inventory items are not processed by a regular inventory closing procedure (receipt-to-issue settlement and cost propagation). However, if standard cost item has been assembled from a regular items (having e.g FIFO as a costing principle), then the system will automatically post standard cost variations resulting from he cost propagation routine.
- All the inventory cost adjustments made for variations, rounding errors, automatic stock revaluation made because of standard cost value change, are still made with usage of inventSettlement mechanism.
I’ve witnessed usage of standard costing on my projects several times. Usually, a customer has described principles for choosing this costing model with following wording: “We have a complex production cycle, it would be hard to use FIFO or average, that is why we use standard cost. And speaking off-the-record, our production management is a complete mess, our cost accounting group is completely disconnected from production management group. We do not want to sort out this mess, that is why we use and will use standard costing.” Sure, I am exaggerating a little, but anyway most of enterprises use standard costing only to simplify accounting procedure. Actually, standard costing idea has nothing to do with simplicity of the accounting procedure, but is based on two simple assumptions:
- Only economically justified costs are included into cost price. That is – costs that are necessary and inevitable to produce a given product with required quality. Since it would be very time consuming to check the justification of every cost included into every batch of a production, some kind of cost rationing for the next economic period is implemented. As a good practice, this rationing must be a part of a budgeting process.
- Variations of the cost must be analyzed by a variation kind and by a variation responsible. A principle for the variation analysis is a industry-dependent. I prefer to intake medications made according to GMP and without variations at all. But if fertilizer for my home plants would contain a couple of percent more percents of potassium than it should – I would not care.
Complete review of standard costing goes beyond a scope of the article. If you are interested in the matter – try to read Management and Cost Accounting by Colin Drury. For the further consideration we only need to know the purpose of establishing the support for separate booking of different variation kinds.
If we look closer on the variation kinds posting setup, from the first sight, the meaning of only one posting type is clear: Purchase Price Variance. It is the same variance between a purchase cost and a standard cost which was supported in previous versions of Dynamics AX. Lets review other kinds of variations:
Inventory cost revaluation – an offset account for inventory on-hand revaluation on standard cost change. (Sure – It is not variation from the strict economical point of view, but it is quite understandable, why this setting is placed with settings for regular variations).
Inventory Cost Change – this kind of variance is more or less similar to Purchase Price Variance. Works for all types of receipts, other than Purchase. Also – This variation kind is used to book variations resulting from an attempt to revaluate receipt during an inventory closing/recalculation.
Rounding Variance – During a variations calculation, these variations are calculated with unrestricted precision (actually – limited only to precision of real data type in Dynamics AX). But during posting, these variations are rounded to the currency round-off precision. Designers of the new standard costing mechanism decided not to rely on the standard rounding mechanism of Dynamics AX, which rounds an amount during posting to GL. Instead, they have implemented dedicated rounding mechanism for standard cost variations. (If this were not be a case, amounts from GL postings wound not match data from variations table (inventCostTransVariance). (Please, mention the difference between this rounding variance and rounding postings from invent on-hand rounding which guarantee that invent on-hand value is equal to standard cost value multiplied by on-hand quantity).
Much more interesting matter is a production variance (I suppose these was the main cause for the new standard cost implementation):
Production Price Variance – variance resulting from the difference between the planned and actual costs of one unit of material or work, consumed during a production. Even if our production has consumed the larger QUANTITY of units of work or materials, this variation would not occur.
Production Quantity Variance – variation resulting from the difference between the planned and actual quantities of the material or work consumed during production. This variance is calculated as a variance in the quantity multiplied by a planned cost of one unit of work/material.
Production Lot Variance – it is a little bit odd for me kind of variance related to the batch size of production consumption. DAX supports constant cost of consumption of material on a production route. (I believe – This is a kind of logistical expenses required to deliver material from warehouse to production spot, which in first approximation does not depends on the batch size). Suppose we planned to consume 20 pieces of a material and consume 30EUR of related constant expenses. (1.5EUR a piece). Actually, we have consumed 22 pieces. This results in variance of 30* 22/20 – 30=3EUR. So, Basically speaking, this kind of variance is related to consumption overheads, not to consumption itself.
Substitution Variance – All other kinds of variance. From economical point of view – variation from substitution of materials or route operations.
The next logical question is how the system calculates these variations ? What should be considered as a model of consumption quantity, unit of material/work price and so on ? The answer is quite simple – BOM calculation results are considered as a model. That is – during the last calculation of the BOM, system has saved the model data on quantities and prices of material/work consumed into BOMCalcTrans table. Thus, to calculate variations, it is enough to compare model consumption data from BOMCalcTrans to actual consumption data in ProdCalcTrans (Production order calculation results). The body of variance calculation logic is contained in ProdStandardVariance class. The most interesting methods of the class are findOrCreateVarianceTrans(), which fills a variations table, finding the difference between planned and actual consumption, and calcVariance() which calculate variances themselves.
Now it is becoming more clear, why manual editing of price in ItemPriceSim table is prohibited for BOMs. Imagine that we calculated the BOM, got planned cost of 950EUR and manually updated it to 1200EUR afterwards. Now, if we have an actual cost of, say, 1100EUR, it would be quite unclear how to allocate -100EUR of variance, basing on data which says that variance is +150EUR…
As I’ve said already, Dynamics AX supports 4 kinds of production variation. Experience shows that this level of details is not sufficient for a real life variation analysis. Some mean for more fine-grained setup for variation accounting must exists. This mean is provided by the new Cost Group feature of DAX2009 (Inventory Management->Setup-> Bill of Materials->Cost Groups). By now, only three fields from this table (Cost Group, Name and Cost Group Type) have some interest for us. The groups having a Direct Material cost group type can be associated with an inventory item. The groups having f Direct Manufacturing cost group type can be associated with a Route Cost Category (Other cost group types will be discussed later). At the moment of Production or BOM calculation, cost group data from an item or an operation consumed is saved to the corresponding line of calculation results. When system is calculating the variations (in ProdStandardVariance class), cost group data is also copied into the temporary table with variation data. Since variation posting setup allows to associate GL accounts to a variation kind+cost group combination, the system uses data from this table to define actual GL accounts to be used during posting.
Finally, to complete the discussion on variations and standard costing, we should discuss a standard form used to analyze standard cost transactions (Inventory Management->Inquiries->Transactions->Standard Cost Transactions.) The topmost part of the form displays content of inventCostTrans table. It seems that this table was invented as a mean to separate cost movements from inventory operations. In most cases, this table is updated on physical or financial update of inventory transaction or on attempt to revaluate inventory transactions (which immediately rendered void by writing-off revaluation to cost variations). But sometimes, records are inserted to this table regardless of any inventory operation. E.G. on change to standard cost, a lot of record is written to this table. A half of these records is kinda writing-off old costs. Another half – is kinda accepting new costs. As of now – I see no much sense in this table. Most of the information in it is duplicated from inventory transactions; I have no idea how a similar mechanism would be implemented for non-standard cost based inventory models; Most of the functionality still uses monetary amount fields from inventory transactions; etc. It seems to me, that this table is wrong (or at least – unfinished) step into right direction. In the bottom part of the form there is a grid with data from a much more interesting table (InventCostTransVariance). This table is used to store detailed information on cost variations for a inventory operation (Basically it is a persistent variance roll-up data I’ve mentioned already). This info is stored only if the field Cost Breakdown of the Inventory Parameters form is set to Sub Ledger. The parameter Variance To Standard allows to set the level of details for persistent variance roll-up – Variation Kind or Variation Kind+Cost Group.
And finally – The very last question on new standard costing. Because inventory items with the new inventory model now bypasses regular inventory closing procedure, may be it is not worth to close inventory at all (Of course if all inventory items use the standard cost model) ? The answer is NO! Although the regular inventory closing routine is bypassed for a new standard cost item, other parts of the inventory closing procedure still make some useful job for new standard cost items. In the end of the inventory closing procedure run, the system fills the inventCostTransSum table with summarized values from the inventCostTrans table up to an inventory closing date. Data from this table allows to speed-up a standard cost change value procedure and lower a time required to build some standard cost related reports. Thus, If inventory has not been closed for a long period, performance of these procedures can deteriorate considerably.
In this section we will discuss interesting, promising, but a little bit unfinished feature of Dynamics AX 2009 – a Costing Sheet. Settings for this feature are located in the Inventory Management->Setup->Bill Of Material->Costing Sheet Setup form. In the first approximation, it is a tree of a cost breakdown with cost groups located on a leaf level (The very same cost groups which are used for variation posting setup). Branches of the cost breakdown tree are used for more high level grouping of the costs. If we would play with the an addition of cost groups to the leaf level, we find two new interesting cost types – Indirect Surcharge and Indirect Rate. The first one is a cost calculated as a percent from the value of some other cost node in costing sheet. The second one is some fixed rate multiplied by some ‘cost quantity’. This ‘cost quantity’ are a summed-up quantities from a BOM calculation/Product order calculation belonging to the specified node in costing sheet tree or to one of it subnodes.
Indirect cost calculation principles specified in costing sheet setup are used to calculate a production cost. As for direct production costs, there are two pair of GL account for every indirect cost kind: The first pair is used during posting of picking list, route card/job card – i.e. WIP consumption journals. The second pair is used during final calculation of production order. Also, indirect production costs are posted to prodIndirectTrans table, introduced in DAX2009.
Since indirect costs may depend from a cost of consumed materials, it is clear that they must be also recalculated and updated during inventory closing/recalculation. Thus, If during inventory closing iteration some production issue transactions has been updated, the system proceeds (in inventCostItemDim.updateIndirectCosts() method) with the indirect costs recalculation. If these indirect costs actually were updated, then the system updates prodIndirectTrans data and push the indirect costs amount into the cost propagation routine. Also the standard inventory settlement posting routine was updated to support posting of newly created prodIndirectTrans records.
During an BOM/Production order calculation, the system also creates calculation result lines with indirect costs. And since calculation data are used for standard cost variation calculation, it means that a variation might also occurre from a difference in indirect costs…
Now – Why do I consider this feature as unfinished. It is obvious that any more or less complex production and production costing scheme would require to support different costing sheets for different inventory items. If we would look closer on Dynamics AX internals, we find that actually the system has some prototype support for existence of several costing sheets in parallel. But it is only a prototype as of now. I suppose the more full-fledged implementation of costing sheet will be implemented in future versions of Dynamics AX.