Oversell Automatic Cost Adjustment

When items are oversold and the average costing method is in use, the average cost calculation is distorted when a receipt transaction occurs subsequent to an oversell. The cost of the receipt differs from the item's current average cost. In this scenario, the calculated cost is either under or over-stated, which results in reconciliation issues between the Inventory Valuation and the GL Inventory Control/Cost of Sale Accounts.

To resolve this issue, an automatic cost adjustment transaction occurs to ensure the item's average cost updates correctly. This takes into account the transaction location if costing is being done at a location level rather than a master level.

The automatic adjustment uses the transaction type Total Adj, If your transactions are subject to automatic adjustments (over-sell and average cost in use), you must set up the Total Adj transaction.

The Code/Module/Name and Effect on inventory are pre-defined. The GL account represents the account used as the contra account (to IN Inventory Control) when the automatic adjustment is created. You must ensure this is the correct account for the adjustment (or use wildcards where applicable). You must also assign a tax code that is zero-rated, so the transaction does not attract tax.

The automatically-adjusted receipt transactions are:

AP Invoice Entry
PO Receipt Entry
PO Shipment Entry (where a receipt is processed)
IN Transaction Entry (IN and AP transaction types with an increasing effect on inventory - including stocktake adjustments)
IN Kitset Parent Make-to-Stock Transaction
IN Disassemble Kitset
FO Receipt

An automatic adjustment will be made when a receipt is processed in these circumstances:

The average costs are being used;
The item balance before receipt is negative was previously oversold;
The receipt cost differs from the current average cost for the item.

The automatic adjustment value is calculated after the average cost is recalculated using the receipt/adjustment transaction.

Example 1

The receipt quantity for an item is greater than the absolute value of the item's current quantity on hand.

Pre-receipt quantity on hand balance is -10
Pre-receipt average cost is $25.00
Received quantity is 15
Received cost is $15.00
Post-receipt on hand balance is 5
Post-receipt average cost calculates to $95.00 (pre-adjustment)

The post-receipt average cost is incorrect because the -10 was seen as an absolute value in the calculation: (10 x $25) + (15 x $15) = $475. This value divided by the five currently on hand is $95.00.

The required adjustment will be calculated as:

Post-receipt on hand balance x (Received cost - Pre-receipt average cost)

= 5 x (15 - 25)

= 5 x [-10]

= [-50]

The adjustment value is -$50.00, which results in the average cost being updated to the correct value of $45.00.

This adjustment will credit the IN Inventory Control account, and debit the Cost of Sales Account of the Total Adj transaction type.

Example 2

The receipt quantity for an item equals the absolute value of the item's current quantity on hand.

Pre-receipt quantity on hand balance is -10
Pre-receipt average cost is $50.00
Received quantity is 10
Received cost is $15.00
Post-receipt on hand balance is 0
Post-receipt average cost calculates to $65.00 (pre-adjustment)

The post-receipt average cost is incorrect because the -10 was seen as an absolute value in the calculation: (10 x $50) + (15 x $10) = $650. This value divided by the 10 received (used where on hand balance is zero) $65.00.

The required adjustment will be calculates as:

[-1] x Pre-receipt average cost

= [-1] x 50

= [-50]

The adjustment value is -$50.00, which results in the average cost being updated to the correct value of $15.00.

This adjustment will credit the IN Inventory Control account, and debit the Cost of Sales Account of the Total Adj transaction type.

Enquiries

You can view auto-adjustment transactions in IN Inventory Item Maintenance. They have the same reference as the source receipt transaction and the narration will indicate that the transaction was an overselling cost adjustment.

Reporting

If a receipt transaction has an oversell automatic adjustment associated with it, details of the adjustment displays directly beneath the receipt transaction in the IN Transaction Costing Report, for example:

250807-14

Source Transaction Edits/Deletions:

If an original receipt transaction with an automatic adjustment is edited, the original automatic adjustment transaction will be recalculated based on the update. This takes into account changes made to any of these original transaction attributes:

Unit cost
Quantity
Exchange rate
Discount rate/amount
Document date
Posting date

Information

Regardless of any changes made, the recalculated adjustment value will always be using the item's current quantity on hand. If any transactions occurred subsequent to the original transaction that altered the item's average cost, these transactions are not altered in any way during the re-calculation process.

This ensures that although the recalculated adjustment remains linked to the original receipt transaction, all subsequent inventory movements are considered and the integrity of the inventory valuation and general ledger accounts is maintained.

If an original receipt transaction with an automatic adjustment is deleted, the adjustment transaction is also deleted.

warning

If subsequent transactions have occurred for the item between the original receipt transaction and the time it is deleted, and the deletion results in the on-hand quantity for the item reducing below zero, it is possible that the average cost, last cost, and period summary total cost values will become distorted. A system script is available to correct cost values in these situations. The script is called Adjust average costs.