Inventory Average Cost Calculations
The software provides for the selection of any of four methods of costing for its inventory: LIFO, FIFO, Average and Standard. The average cost method used by
the software is called “rolling weighted average.” The following examples show the benefit of using this method for average cost calculations.
Basic Weighted Average method
A (non-rolling) weighted average calculation provides a simple snapshot of the current inventory value based on the purchase cost and quantity remaining in each “cost bucket”
for an item. The average cost is calculated on each outbound transaction. An example:
Quantity Cost
5 @12.00
15 @12.50
10 @12.00
Average for these cost buckets: 12.25
Value of inventory: 367.50
Sell 6 @12.25 = 73.50
Using this method of calculation for costing purposes would result in a different cost for each transaction that was processed. Using the example above, a sale for a quantity of 6
items would yield a cost of 12.25. However, a second sale of 6 items would yield a cost of 12.2917, presuming that no other transactions took place between the two sales
transactions.
Quantity Cost
14 @12.50
10 @ 12.00
Average for these cost buckets: 12.2917
Sell 6 @ 12.2917 = 73.75
Continuing this progression, without any additional purchases:
Quantity Cost
8 @ 12.50
10 212.00
Average for these cost buckets: 12.2222
Sell 6 @ 12.2222 = 73.33
Quantity Cost
2 @ 12.50
10 @ 12.00
Average for these cost buckets: 12.0833
Sell 6 @ 12.0833 = 72.50
Quantity Cost
6 @ 12.00
Average for these cost buckets: 12.00
Sell 6 @ 12.00 = 72.00
Now we have sold our inventory quantity to zero. But, if we add the total cost of the product sold:
Sell 6 @ 12.2500 = 73.50
Sell 6 @ 12.2917 = 73.75
Sell 6 @ 12.2222 = 73.33
Sell 6 @ 12.0833 = 72.50
Sell 6 @ 12.0000 = 72.00
Total 365.08
Our total beginning inventory value was 367.50, but we have only credited 365.08 against inventory for the sales, thus we have a 2.42 balance in our inventory account in
GL.
This discrepancy is magnified as more purchases and sales occur, such that the GL will never be in balance with the IN valuation.
Rolling Weighted Average method
The key difference between the weighted average and the rolling weighted average is that in the rolling weighted average method, the average cost of an item is only calculated at
the time of purchase (when quantity is added to stock). Therefore, all sales transactions that occur between purchases will be accounted for using the same average cost. Using
this method, the selling of stock to a zero quantity should always result in the proper GL credits to offset the value in IN.
It is relatively simple to see that, if all sales transactions use the same average cost, the total value credited to inventory will match the beginning value of IN. An example:
Quantity Cost
5 @12.00
15 @12.50
10 @12.00
Average for these cost buckets: 12.25
Value of inventory: 367.50
Sell 6 @12.25 = 73.50
Sell 6 @12.25 = 73.50
Sell 6 @12.25 = 73.50
Sell 6 @12.25 = 73.50
Sell 6 @12.25 = 73.50
Total 367.50
We can create a more complex example by purchasing a quantity of 12 with a unit cost of 12.10 after two of the sales cycles. After two cycles, this is the status of the inventory
buckets:
Quantity Cost
8 @ 12.50
10 @ 12.00
Rolling weighted average: 12.25
Adding our new purchase, and recalculating the average cost:
Quantity Cost
8 @ 12.50
10 @ 12.00
12 @ 12.10
Rolling weighted average: 12.19
Value of inventory: 365.70
The calculation for the new average cost is ((current on- hand * current standard cost) + (transaction quantity * transaction unit cost)) / (current on-hand + transaction quantity).
Therefore, in this example ((18 * 12.25) + (12 * 12.10)) / (18 + 12) = 12.19. If we continue the sales progression using the new average cost, we will still have a zero
balance when our quantity on-hand becomes zero.
Sell 6 @12.19 = 73.14
Sell 6 @12.19 = 73.14
Sell 6 @12.19 = 73.14
Sell 6 @12.19 = 73.14
Sell 6 @12.19 = 73.14
Total 365.70
The rolling weighted average cost method provides the most accurate representation of costs to the GL, and minimizes the need for many of the cost of goods sold (COGS)
adjustments that may be required when using just a weighted average.