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 general ledger 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, 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 when the automatic adjustment is created. You must ensure this is the correct account for the adjustment (or use wildcards if 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 (if a receipt is processed)
IN Transaction Entry (including 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 is 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 or 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 on hand is $95.00.

The required adjustment is 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 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 if on hand balance is zero) is $65.00.

The required adjustment is 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 Inventory Control account, and debit the Cost of Sales Account of the Total Adj transaction type.

Enquiries

You can view auto-adjustment transactions on the Inventory Item Maintenance form. 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 beneath the receipt transaction in the Transaction Costing report.

Source Transaction Edits/Deletions

If an original receipt transaction with an automatic adjustment is edited, the original automatic adjustment transaction is 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

Note: 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 becomes distorted. Run the Adjust average costs system script is available to correct cost values in these situations.