Calculation of the average cost and total value in the item records:
- 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]