The estimated extended cost in the Sales Order is based on the item location On hand, location total value and transaction quantity. By standard, when the item on-hand quantity in the line level location of the SO becomes 0, the estimated extended cost also becomes 0.
| Estimated Extended cost = Location Total Value/ Location On Hand * SO Quantity Location Total Value = Location On Hand* Location average cost |
As per the standard NetSuite behavior, the average cost in the location level of the item records is calculated differently from the average cost in the main body of the item records.
The body average cost of the item:
- The average cost of a day is arrived by adding the cumulative total value of the previous day and the purchase receipts of the current day divided by the current on hand quantity.
- The cumulative value = [PO rate*quantity for the day] – [IF quantity * item avg cost]
- Hence, if PO and IR are created for a day, the average cost changes and the IF GL impact changes accordingly.
The average cost of 9/28/2023 = [PO 150+50+50+75]/ [10+5+5+5] = 13
The Total Value of 9/28/2023 = [150+50+50+75] – [(2+2+11)*13] =130
Average cost of 9/29/2023 = [TV of previous day 130 + 50 PO] / [10+5+5+5-2-2-11+5] = 12
Total Value of 9/29/2023 = [130+50 – ((2+2)*12)] = 132
Average cost of 9/30/2023 = [Previous day 132+50] / [10+5+5+5-2-2-11+5-2-2+5] = 182/16 = 11.375
In short, the NetSuite calculation is:
Average cost = [Previous day cumulative total value + PO Receipts] / [Previous day ending quantity on hand + PO quantity of current day]
Total Value = [Total Value of previous day + PO Receipts] – [SO Quantity of the current day * average cost]
Location Average cost field in Item records:
The Location Average Cost in an Inventory Item record is based on calculating the location value over the location quantity on hand.
In cases where the location value and quantity on hand are both 0.00, the Location Average Cost retains the value it had before the transaction that brought on hand count to 0.
This is why most of the time when the inventory on hand is 0, the location average cost may reflect an amount whereas the body average cost field would display a null value.
The computation of the average cost field is a standard computation by the system and cannot be altered. Hence, we cannot display the average cost in the standard body field when the quantity on hand is 0.
Thus, when the quantity on hand is 0 of an item, the body average cost will also be 0 but the location average cost retains the location average cost prior to the quantity becoming 0.