Basic principles for the valuation of the stocks

Database tables

The management of stocks in Sage X3 is structured around a group of tables:

   The stock itself. This stock is identified in the database by the records in the STOCK table. Each of these records corresponds to a line of stock. The management rules for these lines are detailed in a technical annex. A group of stock lines characterized by various attributes (lot, status, location...) correspond to a given product, in a given warehouse. Each line of stock carries a global quantity (the value, which serves as the basis of the calculation for the financial value of the stock is stored in other tables).

   The stock movement journal. Identified as entries in the STOJOU and STOJOUVAL tables in the database, it is used to recover in a chronological fashion, the movements carried out, whether in quantity (receipt, issue or internal movement) or for value only (depreciations, miscellaneous adjustments : it should be noted that the quantity field is entered even if the movement does not change the quantity in stock, because in the case of valued movements, it is important to know on which quantity the value change has an impact). Each record in this journal carries the characteristics of the stock line moved without formally pointing to it and it also identifies the original movement that justifies it (document and document line at the origin of the movement: packing slip, material tracking, receipt note...). Each movement carries two values: the movement value, determined from the set up rules, which impacts on the global value of the stock carried by the lines and the value arising from the original document. If the valuation method includes two methods then these values are displayed for both methods.

   The lots table. Identified in the database by the records in the STOLOT table, it associated with a lot and a given product, the default characteristics (for all warehouses) and contains a total for the quantities and an indicative value (expressed in reporting currency).

   A table of the product/warehouse totals, called ITMMVT, which contains a total for the quantities by status (A,Q,R), by status (internal, dock, loaned, allocated, in subcontract...) as well as the totals (base, quantity) that are used to carry out the calculation of the moving average price. It also carries the values such as the last purchase price, the last receipt price, the date of the last receipt and the last document.  The information contained is redundant: it can be reconstructed, for a given product at a given warehouse, by taking the last stock count values and by using the stock journal.

   A table containing the product/warehouse total per month, called ITMMVTHIS, which contains the totals by period and by fiscal year. This table is updated based on the allocation date of each movement.

   A table containing the elements necessary for the management of the FIFO or LIFO stack, called STOCOST, containing, for each product and warehouse, a group of lines characterized by a quantity and a global value. It should be noted that this table is used in the FIFO or LIFO valuation, but it has no links with the physical locations or specific lots.

   A table identifying allocated stock, named STOALL, which is used to justify the allocated quantities and the shortages for each product and warehouse.

The rest of this document details the way in which the movements are registered in the database, focusing mainly on the fashion in which the valuation bases used for the moving average price and the FIFO/LIFO are updated. The calculations that are carried out depend on the valuation method used. This method is defined by a group of rules attached to an identifying code. This code is associated with the product-site, the valued being initialized from the product category.

Recording a stock movement

A stock movement is justified by a given line in a source document (that can be for example, production reporting, delivery advice note, receipt note, physical stock count note...). Its entry will create at least one line in the stock journal (there can be several if the line in question involves the movement of several lots or several units or serial numbers....).

This source document in principle carries a quantity and a value (sometimes only one of the two). The following two values are found in the stock journal: the value carried by the source document and the movement value, determined according to the rules that have been set up (amongst which figures the possibility to pick up the value from the source document).

In financial terms, the stock valuation method used can be Standard price, Revised standard, Moving average price, FIFO or LIFO. According to the country and conditions, certain methods can be or not be allowed, these choices must therefore be confirmed by an accounting expert. This valuation rule can vary as a function of the product and even the product-site pair. This can be admissible in exceptional cases (for example different companies), but as a general rule, the most financially appropriate rules will be chosen.

By default, once this financial valuation rule has been chosen, the variation in the value of the stock as a result of a movement follows the simple rule shown below (that exists in the same way for the quantities) :

final value = initial value+ movement value

The movement value is of course supposed to be signed, that is negative for an issue and positive for a receipt. When this rule is followed and the movement values are stored in the journal, there will always be available a true image of the financial value of the stocks in accounting if the stock movements are posted from the stock journal. It should also be noted that this posting can be carried out each time a movement is made or in a more global fashion (end of day, period or on request).

The rule for the calculation of this value is simple to apply in the case of an issue and is applied without exception: the stock valuation method is used to associate a value with the issue quantity and decreases the residual stock value by the same amount. This value is obtained either from the moving average price for the site (the global value of stock is decreased pro rata with the issue quantity), or by decreasing in steps the site price stack according to the order used (FIFO or LIFO), or by using the Standard price or the Revised standard price associated with the site. In order to manage the exceptional cases where a zero price arises from this calculation a Receipt value is planned.

On receipt, the basic principle consists in increasing the stock quantity and value (are updated the FIFO/LIFO stack and the quantity and value of the product-site used in the calculation of the Average Cost from the order amount). The receipt value of the movement is determined as a function of a rule (defined by product-site) that can be Standard cost, Revised standard cost (the receipt values are thus fixed during a given period), or Order price (in this case the value coming from the source document). Here also, a receipt value is planned if the value obtained is blank. But the specific rules for the calculation of the stock value straight after a receipt are a little more complex and for two reasons :

    firstly because the movement value cannot be perfectly identified at the time when the movement is recorded. The movement will nevertheless be passed with an estimated value, but it is possible once the actual value of the movement is known to carryout an adjustment at a later date (described later in this document). These adjustments are converted into additional lines in the stock journal. These lines will also be posted if the accounting stock interface is used.

    then, because it can happen for certain exceptional movements, that a valuation rule is required that is not used by default for the issues. It is possible to set up this thanks to the exceptions table (this rule will then be associated with an issue type, even a sub-type, identified by a code carried by the stock transaction). This exceptional receipt type can have impact on the accounting, in particular if the original movement has a different value to the receipt value retained. Since these two values are stored in the stock journal, it is possible to set up their use in accounting.

The STOJOUVAL table contains all the valuation information of stock movement. It is used, among others, to stock cost breakdown. This is a detail of the various fields that are available:

• O*: Order amounts
• DO*: Order amount delta
• V*: Valuation amount
• DV*: Valuation amount delta
• V2*: Amount of second valuation method
• DV2*: Delta amount of second valuation method
• DEV*: Non absorbed amount
• DEV*2: Non absorbed amount of the valuation second method
• *MATTOT: Material total = total(MATCST)

• *MACTOT: Machine total = total(MACCST)
• *LABTOT: Labor cost total = total (LABCST)
• *SCOTOT: Sub-contract proportion
• *INVDTA: Invoicing elements proportion
• *OVEMAT: Material overall costs proportion
• *OVEMAC: Machine overall costs proportion
• *OVELAB: Labor overall cost proportion
• *OVESCO: Sub-contract overall proportion

Management of the value totals

The value totals existing in the tables are of two types: on one hand the FIFO/LIFO stack, on the other hand the total in value from the ITMMVT table, which is used to calculate the moving average price. In both cases, the totals are up to date site by site. The global valuation for a company is obtained by a total of the values in the different sites and the global Moving Average Price for a company by moving average for the different moving average prices at each site.

On each site, the basis of the moving average price calculation is updated in a very simple fashion :

   on receipt, the receipt value is added to the value of the current stock value (it is the same for the quantities)

   on issue, the quantities are decreased and the stock value is updated pro rata with the issue quantity.

The update of the FIFO/LIFO stack is made for each site in the following fashion :

   on receipt, a entry in the stack is created for each receipt movement, with the accumulated quantities and corresponding prices.

   on issue, the last or first entry in the stack is decreased according to the context.

It is important to note that this management of the stock value is made in a fashion that is disconnected from the physical management. In other words, even if a product is perfectly identified (by its serial number for example) and is not the first receipt when working with FIFO, the issue value is never taken from the original value, but by following the issue valuation rule.

However, in the case of a receipt for the return of goods, the fact of reversing the original movement is used, according to the setup, to pick up the original moving average price ; if the setup of the receipt values is linked to the order price, it will be perfectly coherent. In the same way, a transfer from one site to another creates two identical movements (one an issue and the other a receipt). If the receipts are made based on Order price, then the transfer will be made with a price that is identical on both sides.

Deletion or modification of the movement

Certain movements can be canceled after having been passed. This type of case is processed as follows :

   the deletion of a receipt is only possible if the stock whose characteristics correspond to the receipt is still available. From the point of view of the valuation, the deletion is processed as a negative receipt, that is to say the movement is carried out in the wrong direction. In addition, the entries in the stock journal corresponding to the original movement and its cancellation are marked (a flag named REGFLG is set to 2).

   the deletion of an issue is only possible if there is still space available in the location from where the products were issued. From the point of view of the FIFO stack, it is not necessarily possible to re-establish the exact situation, because the empty stack entries are deleted as when they appear. In the worst case, a FIFO stack entry is recreated taking the value of the issue (that can in addition be a moving average of two stages of the entry having been purged between times).

The modification of a movement (whether this is a quantity or value change) is processed by the cancellation and deletion, taking into account the controls previously defined.

Adjustment of a movement

The adjustment of a movement corresponds to the case where a movement, entered as a given value at a given moment, has its value changed by the entry of another document (and not by the modification of the source document).

Below are several concrete examples:

a supplier receipt has been entered at a given price, the invoice arrives with a different price.

the supplier invoice is entered, a supplemental invoice (customs charges, carrier charges) to be distributed over the products booked to stock is received (this can occur more than once for the same products).

A production reporting entry that has created receipts at a price calculated based on technical data; a production cost calculation establishes the final receipt price, which is different.

In the particular case of over-invoicing (the invoiced quantity is higher than the received quantity), it is considered that other receipts are still expected and that they will absorb the additional amount linked to the additional invoiced quantity. In that case, only the price difference by quantity and the invoicing elements are integrated in the first receipt.

Example:

• An order of 60 products at a unitary price of €11, and €60 of invoicing elements.
• A first receipt of 50 products is made at a unitary price of €10.
• The over-invoicing of this receipt is calculated in the following way:
  - 60 products at €11 make €660
  - an invoicing element at €60 in proportion of the quantities.
  
  The cost price of the line is therefore (660 + 60) / 60 = €12.
  
• The stock valuation processing does not determine:
  - €11 for the material, but €10,80
  - €1 for the invoicing elements, but €1,20.
  Indeed, the whole value of the invoicing elements is applied on the first over-invoiced receipt: 60 / 50 = €1.20. The material is therefore valued at: €12 - €1,20 = €10,80.
  
• A second receipt of 10 products is made at a cost price of €12.
  
  The stock valuation calculation considers only the material at €12 because the invoicing elements have already been integrated to the stocks.
  
  When taking into account both calculations, the 'material / invoicing element' distribution is calculated as follows:
  
  - Material part: (50 * 10,80) + (10 * 12) = 540 + 120 = 660
  - Invoicing elements part: 50 * 1.20 = 60

In all the cases, these adjustments are made by creating additional entries in the stock journals. It should be noted that it is possible to prevent price adjustments for certain products( Adjustment (yes/no) flag is defined in the valuation methods setup). If this adjustment is made, it is carried out as follows:

• The modifications are made on the total value used to calculate the moving average price. If the number of products remaining is low, the variances to be distributed can have a big impact on the moving average price. It is therefore possible to limit the variation in percentage of the moving average price that results in a given value. The MAXABSCOD - Valuation variance absorption parameter (chapter STO, group VAL) defines if this limitation must be used. It can take the values No, absorption base site, absorption base site/lot. If MAXABSCOD is set to No, the variation in the moving average price is not limited. If it is set to absorption base site, the Moving average price is limited for the PMP site. If it is set to absorption base site/lot, the variation for the average price for the lot at the site is limited (and as a consequence that of the Moving average price in a more drastic way). The maximum variation range in percentage, when it is applied, is given by the MAXABSPER - % maximum of over-absorption parameter (chapter STO, group VAL); the value most frequently used is 10, which means that there is a limit on the Moving average price or the Moving average price for the lots is plus or minus 10%. If a variation limitation has been used, the variance that could not be absorbed is stored in the stock journal (It can be posted using an appropriate account).
  
• The value modifications are distributed over all the entries in the FIFO stack in proportion to the quantities that are still available in these stack entries.

For instance:

• A receipt of 36 products is made at a unitary price €10, whilst the previous stock is zero.
• An issue of 12 products is made.
• A receipt of 6 products is made at a unitary price of €18.
• The receipt of 36 products is valued at €11; thus it is necessary to pass on a variance of €36 in total, on the 30 remaining products, that is a revaluation of €1.20 per product.
  
  Because the residual value of the stock before revaluation is equal to 24*10+6*18, that is €348 (PMP=€11.60), if the maximum absorption setup is fixed to 10%, it is not possible to include more than €34.80 to the stock value.
  Therefore the stock value will pass to €382.80, which gives a moving average price of €12.76, and the variance is posted separately (€1.20) in the stock journal.
  
  In the FIFO stack, this variance is distributed in proportion to the quantities.
  Thus, a sixth (6/30) of the absorbed variance is distributed to the last entry in the stack.
  €5,80 is thus distributed over 6 products: this represents an increase in the unitary value of these 6 products of 0.96666  € (it was €18 at the start).
  The remaining (5/6) over the variance, i.e. €29, is distributed over the remaining 24 products, that is an increase in the unitary value of €1.2083 (it started as €10).

Adjusting receipt movements: when breaking down the average cost by cost group, the potential negative values in the calculation are distributed over other cost groups as follows:

• If the negative cost applies to the material cost group, this amount is distributed in the following order of priority:
  - on the other material cost groups
  - on the non material cost groups (machine, labor and sub-contracting costs)
  - on the four overheads (material, machine, labor and sub-contracting)
• If the negative cost applies to the non material cost group, this amount is distributed in the following order of priority:
  - on the other non material cost groups
  - on the material cost groups
  - on the four overheads
• If the negative cost applies to the material overheads, this amount is distributed in the following order of priority:
  - on the other overheads
  - on the other material cost groups
  - on the non material cost groups
• If the negative cost applies to the non material overheads, this amount is distributed in the following order of priority:
  - on the other overheads
  - on the other non material cost groups
  - on the material cost groups

When such an adjustment is performed, the data recorded in the next fields of the STOJOUVAL table are irrelevant. This data is related to the second valuation method, for which no average cost breakdown is required:

·  DV2*: Delta amount of second valuation method

·  DEV*2: Non absorbed amount of the valuation second method

Stock adjustment in case of purchase credit memo on invoices: credit memos in value and in quantity.

• Case of credit memos in value:
  This mainly concerns price corrections.
  A credit memo in value only impacts the stock value. The stock valuation is based on the variance between the credit memo and invoice.
  The order or receipt are always considered as invoiced.
  
  Example 1: credit memo with no quantity
  
  

  	Units	Unit price	Amount	Stock value
  Administrative	10	10	100:	100:
  Invoice	10	9	90	90
  Credit memo	0	6	6	84

  
  The receipt is considered as invoiced and closed.
  
  Example 2: credit memo on overall quantity
  
  

  	Units	Unit price	Amount	Stock value
  Administrative	10	10	100:	100:
  Invoice	10	9	90	90
  Credit memo	10	1	10	80

  
  The receipt is considered as invoiced and closed.
  
  
• Case of credit memos in quantity:
  A credit memo in quantity decreases the amount invoiced on the order and on the receipt. The stock value is updated when the corrected invoice price is different from that of the receipt, or when the credit memo price is different the that of the invoice. The stock valuation is based on the variance between the credit memo and receipt.
  
  Example 1: unit price amount of the credit memo greater than the invoiced amount
  
  

  	Units	Unit price	Amount	Stock value
  Administrative	10	10	100:	100:
  Invoice	10	9	90	90
  Credit memo	1	12	12	88 (1) Remaining to be invoiced: 1

  
  (1)  88 = (credit memo unit price - receipt unit price) * (- credit memo qty) =  12 - 10 * (-1) = -2
  The stock value was 90. It was adjusted through the calculation: 90 -2 = 88.
  
  The full calculation is as follows:
  Stock amomunt = 9 UN (invoiced quantity) * 9 (invoiced price for 9 UN) + 1 UN * 10 (not invoiced receipt price) + (invoice price = 9 - credit memo price =12) * 1 UN (that should be equal to the invoice price).
  Stock amount = 81 + 10 - 3 = 88
  
  Let us assume now that a corrective invoice is received for a quantity of 1 unit and a unit price of 9. The stock value is: 90 - 12 + 9 = 87.
  
  Example 2: unit price amount of the credit memo lower than the invoiced amount
  
  

  	Units	Unit price	Amount	Stock value
  Administrative	10	10	100:	100:
  Invoice	10	9	90	90
  Credit memo	1	6	6	94 (2) Remaining to be invoiced: 1

  
  (2)  94 = (credit memo unit price - receipt unit price) * (- credit memo qty) =  6 - 10 * (-1) = 4
  The stock value was 90. It was adjusted through the calculation: 90 + 4 = 94.
  
  The full calculation is as follows:
  Stock amomunt = 9 UN (invoiced quantity) * 9 (invoiced price for 9 UN) + 1 UN * 10 (not invoiced receipt price) + (invoice price = 9 - credit memo price =6) * 1 UN (that should be equal to the invoice price).
  Stock amount = 81 + 10 + 3 = 94
  
  Let us assume now that a corrective invoice is received for a quantity of 1 unit and a unit price of 9. The stock value is: 90 - 6 + 9 = 93.
  
  Example 3: unit price amount of the credit memo equal to the invoiced amount
  
  

  	Units	Unit price	Amount	Stock value
  Administrative	10	10	100:	100:
  Invoice	10	9	90	90
  Credit memo	1	9	9	91 (3) Remaining to be invoiced: 1

  
  (3)  91 = (credit memo unit price - receipt unit price) * (- credit memo qty) =  9 - 10 * (-1) = 1
  The stock value was 90. It was adjusted through the calculation: 90 + 1 = 91.
  
  The full calculation is as follows:
  Stock amomunt = 9 UN (invoiced quantity) * 9 (invoiced price for 9 UN) + 1 UN * 10 (not invoiced receipt price) + (invoice price = 9 - credit memo price =9) * 1 UN (that should be equal to the invoice price).
  Stock amount = 81 + 10 = 91
  
  Let us assume now that a corrective invoice is received for a quantity of 1 unit and a unit price of 9. The stock value is: 90 - 9 + 9 = 90.

Physical stock count variance

A positive physical stock count variance is processed like a receipt, with a negative physical stock count variance being processed like an issue. In addition to the update of the quantities in the stock and their adjustment in the stock line detail and lot totals, this variance has an impact on the valuation basis. The valuation methods setup is used to define the positive and negative physical stock count variance values in a different way to a normal receipt or issue. It should be noted that the validation of a physical stock count both creates an entry in the movement variances in the stock journal plus entries giving the detail to each stock line.