The formula for average cost calculation with negative quantities in SIMS

What is the formula for average cost calculation with negative quantity?

Negative quantity and average cost calculation

When a product(s) sells more than the quantity in stock, the system will use the current average cost as the cost of sale of the product, both for the inventory reports and also for the accounting transaction. This is the standard formula to account for the cost of sales.

When the next purchase is updated in the system, and the two costs (average cost and new purchase price) do not match, the system will post a journal to the general ledger in the cost allocation account to provide for this discrepancy. At the same time, the system will update the Average Cost Posting Audit within SIMS (Admin > SFM Interface > Average Cost Posting). This does not affect the quantity in stock or the last purchase price.

From negative to positive quantity in stock

When the current quantity in stock is negative and you purchase enough stock to cover the negative amount and more, the system will replace the current average cost with the last purchase price or landed cost of the product.

In this event, the system will post an SFM journal in the cost allocation account for the amount of the negative quantity * [the difference between the current average cost and the new purchase price]. Please refer to Example 1 in the attached sheet.

From negative quantity to still negative quantity in stock

When the current quantity in stock is negative and you do not purchase enough stock to turn the quantity in stock to positive, the system will maintain the current average cost (not the last purchase price or landed costs).

In this event, the system will post an SFM journal in the cost allocation account for the total quantity of the purchase * [the difference between the current average cost and the last purchase price for this consignment]. Please refer to Example 2 in the attached Excel sheet.  


Notes:  
- The above formula was optimised and updated from Version 2.3.31 of SIMS.
- The posting of the journal entry is only applicable to installations with the SFM Interface which includes the posting of the cost of sales.
- The entries in the Average Cost Posting Audit are applicable to all installations.