Cascade Update of Cost on Inventory Transactions

General Information

The general idea of cascade update of cost on inventory transactions is that the actual cost (invoiced / manufactured) should be considered in the inventory valuation. The cascade update can be triggered in two different ways.

The first way is when a part has the Supplier Invoice Consideration flag set to Transaction, based on inventory part and an invoice is matched for that part. Then the invoice price will be used to update the cost on the purchase order receipt transaction and the change of cost/WA will be cascaded to transactions made after that purchase order receipt. If several invoices are matched for one receipt the cost used to update transactions is a weighted average of the invoiced price on those invoices. Transaction based invoice consideration could be used in combination with either the inventory valuation method standard cost and cost level cost per serial, or inventory valuation method weighted average and any cost level. Note purchase order charges are not included as a part of cascade updates.

The second way is when a manufactured part has valuation method set to either Inventory Valuation method standard cost and cost level cost per serial, or Inventory Valuation method weighted average and any cost level and when a shop order is closed for that part. Then the total work in process on that shop order will be used to calculate the cost that will be used to update the shop order receipt transaction(s) for the part. The change of cost/WA will also be cascaded to transactions made after that shop order receipt.

Observe that if a shop order issue is amongst the transactions that are cascade-updated, the shop order for which the issue is done will be reopened if that shop order is closed. And as a part of the cascade update all reopened shop orders will be closed, and new cascade updates for the manufactured parts on the shop orders will then be created. So if there are shop order issues amongst transactions to be updated, the cascade update will be a multilevel update.

And if any of the transactions that is updated is a transaction that moves parts to another site within the same company (this could be either a via an inventory move or via an internal customer order), there might be a new cascade triggered on the receiving site from the receipt transaction and onwards. Whether a cascade should continue over to another site (within the same company) or not is dependent on whether the valuation method of the part on the receiving site allows cascade updating or not. If the part on the receiving site does not allow cascade updates,  the actions performed will be the same as when moving parts to a site on another company: the difference posted on the issue transactions will be added as revaluation postings on the receipt transaction at the receiving site as well so that the transit accounts for movement of parts is cleared out. If the valuation method on receiving site allows cascade updates, a new cascade will be triggered for the received part from the receipt transaction and onwards.

The use of cost per serial or weighted average is handled somewhat differently. This is described in the example below that gives some examples on how a part that is set to Transaction-based Invoice Consideration is handled. The scenario for a manufactured part that has weighted average is the same when it comes to cascade updating. The only difference being how the cost set on shop order receipt transactions is calculated. It should be noted that the cost calculation will use an estimated cost structure at shop order receipt which will be updated when the SO is closed. This estimated structure is used even if the shop order is closed together with the receipt.

For parts that are moved between sites within the same company, and for cascades that continue over to other sites, a similar cascade is performed.

In Transaction Revaluation Events application form, system lists information on the trigger for cascade update of transactions including the object which triggered the cascade revaluation, execution times, involved user, number of inventory transactions that are updated with the cascade revaluation event etc.

Transaction Based Invoice Consideration – Standard Cost/Cost per Serial

With this setting, each serial number will have its individual value. The value will be first set at purchase order receipt by the price on the purchase order, and then when the invoice is received and matched. The value of all transactions made for that specific serial number (from the receipt and onwards) will be revaluated to reflect the real invoiced cost.

If the part has been issued between arrival and invoice matching, the source of the issue will be charged or credited with a possible price variance, i.e., an additional variance posting will be added to the connected transaction ID. No price variances will be posted on the supplier invoice.

Example:
Before invoice matching:
1. Part A , serial number 1 is purchased. Purchase price = 80
2. The part is moved to another location.
3. The part is issued to a work order.

Step Action Posting Type Debit Credit
1 Register purchase arrival ARRIVAL Original M1: +80 M10: -80
2 Move part to another location INVM-ISS Original M3: +80 M1: -80
    INVM-IN Original M1: +80 M3: -80
3 Issue part to a work order WOISS Original M50: +80 M1: -80

After invoice matching: Invoice price 87

Step Action Transaction Type Debit Credit
1 Register purchase arrival ARRIVAL Original M1: +80 M10: -80
  Variance  ARRIVAL Additional M1: +7 M10: -7
2 Move part to another location INVM-ISS Original M3: +80 M1: -80
  Variance INVM-ISS Additional M3: +7 M1: -7
    INVM-IN Original M1: +80 M3: -80
  Variance INVM-IN Additional M1: +7 M3: -7
3 Issue part to a work order WOISS Original M50: +80 M1: -80
  Variance WOISS Additional M50: +7 M1: -7
4 Match supplier invoice  (APINVOICE) Original M18: +87 IP1: -87

Expressed in T-accounts:

Step   M1   M10/M18   M3   M50   IP1
1   80       80                  
    7       7                  
2     80         80              
      7         7              
  80             80            
    7             7            
3     80               80     80  
      7               7     7  
4         87                   87
  174 174   87 87   87 87   87       87

The job that creates the variance postings is initiated when a matched invoice is saved. The first part of the job decides the type of cost that should be used to update the receipt transactions. This cost is decided by calculating a weighted average of the invoice price for all the invoices that have been matched to the affected receipt. Note that this means that a partial invoice updates the whole quantity received. The reason for this is that the invoiced price, even for a partially invoiced receipt, is the most accurate guess of the final cost. Also note that all serials in a receipt will get the same cost.

In the next phase, the transactions made for the serial number gets revaluated starting with the purchase order arrival. When the arrival transaction is updated, the job will continue with the next transaction for the serial number, and different actions are taken depending on the type of transactions found for the serial number. We have four general types of transactions:

1.      Normal transactions that can be updated
2.      New receipts
3.      Roll back transaction
4.      Transactions not affected by transaction based invoice consideration

A normal transaction will be updated with the price variance, and the updating will continue to the next transaction. If we find a new receipt transaction, we will stop the update process, and the previous transaction for the serial number will be the last one that is updated. The reason for this is that the serial tracked parts are the same as those that are received back into the inventory after having been outside the system for a while. The most likely is then that the serial has a new value and should not be a part of the ongoing cascade update. Examples of new receipts are new purchase arrivals, work order receipts and so on. Note that un-issues and return material authorizations (RMAs) are not considered as new receipts. Some transactions will lead to a situation where we have to roll back all changes made, and post a price difference posting instead.  Also, there are some transactions that are not directly connected to the inventory part value, and will not be considered at all by the variance postings. That could be shop order variance transactions and other non-inventory transactions.

In some cases when an issue transaction is updated, we will also perform some additional actions.

Transaction Based Invoice Consideration - Weighted Average (WA)/Cost per Part

For weighted average handled parts, all transactions made for the part on the receipt site, from the receipt and onwards, will be affected in different ways by the variance. Just as for the serial tracked parts, possible price differences will not be posted on the invoice, but the variance will be used to revaluate the parts in inventory and the issues made. That is, an additional price variance posting will be added to the connected transaction ID. To calculate a new weighted average value throughout the update, the following parameters are used:

Also for the transaction based WA, all transactions will be divided into different categories:

1. Arrival Transactions
2. Issue Transactions
3. New Receipt Transactions
4. Un-issue Transactions
5. Special Issues (Component Part/External Service/Exchange)

Arrival Transaction

The arrival transaction connected to the invoice matching will be updated with the weighted average invoice price. A new transaction value is calculated based quantity and price used on invoices matched to affected receipt, and a new WA value is calculated to be used in future transactions. The formulas used for these transactions are:


New Transaction Value (per unit) = (Invoice Price * Invoice Qty)Invoice1+ Invoice Price * Invoice Qty)Invoice....) / ((Invoice Qty)Invoice1  + (Invoice Qty)Invoice....)
New WA Value = (WA Qty * WA Value + Transaction Qty * New Transaction Value/Unit) / (WA Qty + Transaction Qty)  

The new WA value is then passed on to the next transaction.

Issue Transactions

Issue transactions or transactions that do not trigger a recalculation of the weighted average made for the part on the receipt site after the arrival transaction, will be affected by the new WA value calculated in the prior steps. The system will calculate the price variance that will affect the transaction and then post additional postings to the connected transaction.

Variance = (New WA Value – Transaction Value) * Trans Qty
New Transaction Value & WA Value = New WA Value

New Receipt Transactions

Receipt transactions or transactions that trigger a recalculation of the weighted average made for the part on the receipt site after the arrival transaction, will not be affected by additional postings. However, they should consider the recalculated weighted average, and calculate a new WA value to be used in future transactions. 

WA Value = New WA Value from previous transaction.
New WA Value = (WA Qty * WA Value + Trans Qty * Trans Value) / (WA Qty + Trans Qty)

Un-issue Transactions

Un-issue transactions or transactions that are reversing other transactions, could both be indirectly affected by the price variance, and trigger a calculation of the new WA value to be used for future transactions.

The un-issue transactions should always have the same value as the original transaction to which it is referring. This original transaction might have been updated by the price variance.

First, update the WA value with the new WA value from the previous transaction. Next, retrieve the transaction value per unit from the original transaction. If it has been updated by the invoice, calculate the variance and post the variance on the un-issue transaction. Then calculate the new weighted average to be used in the next transaction. 

WA Value = New WA Value from previous transaction.
Variance = (Transaction Value from Original Transaction – Transaction Value from the Unissue Transaction) * Transaction Qty
New WA Value = (WA Qty * WA Value + Trans Qty * Trans Value) / (WA Qty + Trans Qty)

Special Issues (Component Part/External Service/Exchange)

The delivery of a component (a part sent out for service) will not be handled as a normal issue, but more like the reversal of a receipt transaction. We will not post any additional postings on the PURSHIP or EXCH-SHIP transactions. Instead, we will calculate a new WA value to be used in future transactions.

WA Value = New WA Value from previous transaction.
New WA Value = (WA Qty * WA Value + Trans Qty * Trans Value) / (WA Qty + Trans Qty)

(Note that the transaction quantity will be considered as negative)

Example: Transaction Based WA

Part A is set up with weighted average per part and transaction based invoice consideration.
There are 10 pieces of part A in stock with a WA of 6
10 pieces are purchased on purchase order 1 to a value of 7 (new WA Value = 6,5)
10 pieces are issued to a work order
10 pieces are purchased on purchase order 2 to a value of 8 (new WA Value = 7,25)
10 pieces are issued to a work order

Transactions before invoice matching:

Transaction/Postings Orig Trans Val WA Qty WA Value Trans Qty Transa Value  
ARRIVAL 7 10 6 10 7  
    Debit Original M1 +70 New WA
    Credit Original M10 -70 6,5
WOISS   20 6,5 -10 6,5  
    Debit Original M50 +65 New WA
    Credit Original M1 -65 6,5
ARRIVAL 8 10 6,5 10 8  
    Debit Original M1 +80 New WA
    Credit Original M10 -80 7,25
WOISS   10 7,25 -10 7,25  
    Debit Original M50 +72,5 New WA
    Credit Original M1 -72,5 7,25

Now 5 pieces of the first receipt (PO No 1) is received and matched. Invoice Price = 8. This triggers the following calculations:

For the first arrival:
New Transaction Value (per unit) = (Invoice Price * Invoice Qty)Invoice1 / (Invoice Qty)Invoice1  = 8 * 5 / 5 = 8
New WA Value = (WA Qty * WA Value + Transaction Qty * New Transaction Value/Unit) / (WA Qty + Transaction Qty) = (10 * 6 + 10 * 8) / (10+10) = 7

For the first issue:
Variance = (New WA Value – Transaction Value) * Trans Qty = (7 - 6,5) * 10 = 5
New Transaction Value & WA Value = New WA Value = 7

For the second arrival (new receipt):
WA Value = New WA Value from previous transaction = 7
New WA Value = (WA Qty * WA Value + Trans Qty * Trans Value) / (WA Qty + Trans Qty) = (10 * 7 + 10 * 8) / (10+10) = 7,5

For the second issue:
Variance = (New WA Value – Transaction Value) * Trans Qty = (7,5 - 7,25) * 10 = 2,5
New Transaction Value & WA Value = New WA Value = 7,5

Transaction/Postings Orig Trans Val WA Qty WA Value Trans Qty Transa Value  
ARRIVAL 7 10 6 10 8  
    Debit Original M1 +70  
    Credit Original M10 -70  
    Debit Additional M1 +10 New WA
    Credit Additional M10 -10 7
WOISS   20 7 -10 7  
    Debit Original M50 +65  
    Credit Original M1 -65  
    Debit Additional M50 +5 New WA
    Credit Additional M1 -5 7
ARRIVAL 8 10 7 10 8  
    Debit Original M1 +80 New WA
    Credit Original M10 -80 7,5
WOISS   20 7,5 -10 7,5  
    Debit Original M50 +72,5  
    Credit Original M1 -72,5  
    Debit Additional M50 +2,5 New WA
    Credit Additional M1 -2,5 7,5

Additional Postings

The variance/additional postings described in the sections above will be added to the existing transactions. That means that the original posting will not be affected or changed. Additional postings could be added to the transaction, independent of the status of the original postings on the transaction. The original postings could be transferred to IFS Financials, or included in the inventory statistics update. 

The type of date used on additional postings is defined on site. It could either be system date or transaction applied date. When using transaction applied date there can be exceptions due to for example closed periods and valuation settings used on other site when moving parts, but then the earliest date compared with transaction applied date will be used.