|
Ок, первая часть готова. Предоставляется по принципу open source. Это значит, если бросятся в глаза ошибки - сообщайте.
What it’s all about?
I have been talking to consultants and developers working with DAX and I know that the issues regarding inventory closing and stock cost calculation have become one of the most severe at the majority of the implementation projects. One treats the inventory closing as a ‘black box’ delivering various results in different times depending on a phase of the moon. In my opinion, this is caused by the lack of information, which is dispersed across the whole documentation. Furthermore, some tricks of this procedure are not described at all and need to be discovered on the cut-and-try principle or by studying the source code of the inventory closing.
In this article I’m trying to explain in detail how this procedure works and to guide through some other issues related to cost calculation. One part of the content has been collected from different documents about DAX; another captures my own experience gathered while working with Axapta 3.0 and reverse-engineering its source code. I haven’t verified everything in the version 4.0 but - according to my analysis of the source code – the algorithm hasn’t been changed significantly.
This document is addressed to experienced consultants and software architects with DAX (former Axapta) who have questions about the topic being discussed. The reader should comprehend the basic ideas behind the Trade and Logistics module and should have already tried to complete an inventory closing.
Why do we need inventory closing?
The primary goal of inventory closing is to calculate the true prime cost of an outward inventory movement (a so-called inventory issue). DAX works with the cost price the following way: at the moment of an inventory issue the system doesn’t try to derive the precise cost of this movement in accordance with the inventory model. The system books instead an estimated value which more or less resembles the real prime cost (read more about it in the chapter ‘Instant cost of issues’).
After all, the real cost is figured out during an inventory closing session according to the inventory model settings. Having calculated the cost, the system derives an adjustment which is a difference between the previously estimated cost and the real one. This adjustment is saved in the inventory transaction table and booked in the General Ledger.
Why it’s done this way? Generally speaking, at the time of an outward movement the system doesn’t necessarily have all data needed to compute the true cost. For example, with the ‘negative inventory’ turned on we can sell items even before they come to our warehouse. Moreover, with the model ‘Weighted Average [per period]’ it’s impossible to calculate the cost price before all stock arrivals have been booked; with the model ‘LIFO’ we cannot assess an issue cost until the last arrival in the period hasn’t been registered, and so on.
Several times novice consultants came to me and asked to customize the system, to let accountants enter a cost price directly into a voucher and to prevent AX from interfering and re-calculating costs in such operations. Well, this approach contradicts carelessly the ideology and the architecture of the inventory closing in DAX. Cost price must always be determined by reconciliation of item issues (outward movements) with receipts (inward movements).
After the system has calculated inventory cost for a particular period any subsequent movements are prohibited in that period. It makes sense because the insertion of new receipts and issues in behindhand might lead to inconsistency in existing settlements and corrupt the prime cost. If we only need to momentarily estimate the cost price of issues in their current state without preventing new inventory movements from being posted, we can just start a recalculation procedure instead of a closing.
The inventory closing as it is consists of the following steps:
1. Reconciliation of issues and receipts, calculation of an initial issue cost price;
2. A run through the graph of all inventory movements with adjustments being worked out;
3. Booking in the General Ledger.
Let’s consider each of these steps in detail.
Reconciliation of receipts with issues
Calculation of true issue cost price is always done in AX on the basis of settlement of issue inventory transactions with receipt transactions. Settlement may not be applied across different item codes or across different ‘financial’ inventory dimensions.
Legitimacy of this approach for the models FIFO and LIFO can be easily proven. For example, if we bought 2 pieces per 10 Euro and 5 pieces per 14 Euro each and than sold 3 pieces, the prime cost of the issue comprises [on the FIFO model] 2 pieces from the first receipt (20 Euro in total) and one piece from the second (14 Euro more). Consequently, the total issue cost of 3 pieces equals 34 Euro.
I will proceed using the terms settled quantity and settled amount. A settled amount is a multiplication of a receipt’s cost price and a settled quantity from a receipt. Of course, the settled amount is rounded according to the setting in the currency master table.
The principle of reconciliation on the model FIFO is trivial and doesn’t require further explanation. The only feature to mention is that the system ignores history of issues and receipts. I. e. if wares were bought on the 15th and sold on the 5th, the two movements would be mutually settled by FIFO in assumption that there were no other receipts. Usually it doesn’t make sense. This phenomenon of a ‘negative on-hand inventory’ is explained at greater length in the chapters ‘Why do we have a negative on-hand’ and ‘Cost price run’.
The models ‘LIFO’ and ‘LIFO date’ in DAX should be better called LILO (Last In – Last Out). They act like the FIFO but exactly the opposite way around. The system goes through a list of unsettled receipts beginning from the very last and reconciles them with last issues, respectively. The ‘LIFO date’ differs in the way how the receipts are chosen. It takes only those receipts which existed at the time of a particular issue movement. Co-existence of these two models can be explained on the following example. Imagine you buy wares on the 1st, 5th and 31st and sell on the 10th and 18th. On the one hand, according to the strict accounting principle of LIFO you must sell 31st‘s wares because the last receipts are to be taken first. On the other hand, the sale from the 18th is going to be settled with the purchase from the 31st, which is somehow illogical. That’s why developers of the system have implemented the both principles. The first one is correct from an accountant’s point of view; the second is in line with common sense.
Now let us consider the reconciliation models ‚Weighted average’ and ‚Weighted average date’. Long time ago, in the ages before the version Axapta 3.0 SP2, EACH issue in a period was reconciled with EACH open receipt (or with each receipt before and including the issue’s date in case of the model ‘Weighted avg. date’). That is, if we had 2 unsettled receipts by the beginning and got 6 more in the course of a month, each issue would be settled against all the 8 receipts. Given 40 issues, this would result into 8*40*2 settlement transactions (the factor of 2 is determined by the architecture of the module which needs 2 transactions per one pair of settled movements). It’s not surprising that this exponential growth didn’t favour the performance of the closing routine. As a result, in the Axapta 3.0 SP2 they implemented some compromise algorithm of weighted average. Thresholds for the settled quantities and amounts were introduced. The system takes bigger receipts first in order not to settle less than the given threshold quantity. If a particular receipt or issue still has an open quantity less than the threshold, it is settled regardless of the threshold.
The threshold quantity can be applied in several ways:
1. You can specify a ‚Minimum percent’ directly in the form of the closing procedure. The system tries to settle not less than the given percent part of the receipt’s quantity.
2. You can enter a ‘Minimum average quantity in the item master table.
3. You can pre-define a ‘Minimum average quantity’ in the Inventory module parameters for those items which don’t have any setting in the master table (see the option 2).
There is another implicit method to limit the settlement quantity by specifying a ‘minimum average amount’ in the inventory closing form. This will encourage the system not to make settlements with an amount less that the given threshold.
This model does well in usual business cases, which means, if the purchase price doesn’t vary too much and if purchases happen relatively rarely compared to sales. In this case the approximating algorithm produces very insignificant deviation from the real cost price computed, for example, in Excel by dividing the whole cost of the unsettled receipts by their total quantity. Nevertheless, assume we procure on regular basis 2-3 batches a month with 1000 pieces each and sell thousands of batches per 1-2 pieces each. If we’ve suddenly got additional 5 pieces twice as expensive as usual, there is a chance to get a higher cost for a couple of sales which happen to be settled against this extra purchase.
At the end I have to point out that receipt transactions are valued on the closing date. If we bought wares on the 15th and allocated some miscellaneous changes on the 25th, an inventory closing performed on the 20th won’t take the correction into account and will use original price from the purchase.
|