Introduction
...
Moving Average formula :
( ( Current Moving Average Cost * Current Balance Quantity ) + ( New Transaction Price * New Transaction Quantity ) ) / ( Current Balance Quantity + New Transaction Quantity )
Moving Average Cost is calculated by company. Thus, the Moving Average Cost for an item will be the same across the entire company.
...
- Goods Received Note (GRN)
- Purchase Return (PR)
- Stock Adjustment (ADJ)
- Trade In
- Sales Return (SR) : if it's configured to use Invoice MA Cost
- Supplier Consignment In (SCI)
- Supplier Consignment Out (SCO)
Transactions that consume the Moving Average Cost :
...
Sample Moving Average Cost calculation for Item1 in CompanyA :
| Creation Date | Transaction | Qty | Purchase/Adjustment Price/Unit | Unit Cost | Balance Qty | Balance Amt | MA Cost |
|---|---|---|---|---|---|---|---|
| 2015-10-01 | GRN1001 | 10 | 100.00 | NA | 10 | 1,000.00 | 100.00 |
| 2015-10-12 | INV2001 | 6 | NA | 100.00 | 4 | 400.00 | 100.00 |
| 2015-10-12 | GRN1002 | 20 | 90.00 | NA | 24 | 2,200.00 | 91.67 |
| 2015-10-23 | INV2002 | 1 | NA | 91.67 | 23 | 2,108.33 | 91.67 |
| 2015-10-28 | SR3001 (for INV2001) | 1 | NA | 91.67 | 24 | 2,200.00 | 91.67 |
| 2015-10-28 | PR4001 (for GRN1001) | 1 | 100.00 | NA | 23 | 2,100.00 | 91.30 |
| 2015-10-31 | ADJ5001 | +2 | 91.30 | NA | 25 | 2,282.60 | 91.30 |
| 2015-11-01 | ADJ5002 | -1 | 91.30 | NA | 24 | 2,191.20 | 91.30 |
| 2015-11-01 | Reset MA | NA | NA | NA | 24 | 2,136.00 | 89.00 |
Reset Moving Average Cost (Reset MA)
...
GRN (example shown is based on GRN1001 above + 6% GST)
| Posting Section | GL Code | Debit | Credit |
|---|---|---|---|
| Current Asset | inventory | 1,000.00 | |
| Current Liability | accPayable | 1,060.00 | |
| Current Asset | gst_Input | 60.00 |
PR (example shown is based on PR4001 above + 6% GST)
| Posting Section | GL Code | Debit | Credit |
|---|---|---|---|
| Current Asset | inventory | 100.00 | |
| Current Liability | accPayable | 106.00 | |
| Current Asset | gst_Input | 6.00 |
INV (example shown is based on INV2001 above + 6% GST)
| Posting Section | GL Code | Debit | Credit |
|---|---|---|---|
| Current Asset | accReceivable | 826.80 | |
| Sales Revenue | generalSales | 780.00 | |
| Current Liability | gst_Output | 46.80 | |
| Cost of Goods Sold | inventoryCost | 600.00 | |
| Current Asset | inventory | 600.00 |
SR (example shown is based on SR3001 above + 6% GST)
| Posting Section | GL Code | Debit | Credit |
|---|---|---|---|
| Current Asset | accReceivable | 137.80 | |
| Sales Revenue | generalSalesReturn | 130.00 | |
| Current Liability | gst_Output | 7.80 | |
| Cost of Goods Sold | inventoryCost | 91.67 | |
| Current Asset | inventory | 91.67 |
ADJ (example shown is based on ADJ5001)
| Posting Section | GL Code | Debit | Credit |
|---|---|---|---|
| Current Asset | inventory | 182.60 | |
| Cost of Goods Sold | inventoryVariance | 182.60 |
ADJ (example shown is based on ADJ5002)
| Posting Section | GL Code | Debit | Credit |
|---|---|---|---|
| Current Asset | inventory | 91.30 | |
| Cost of Goods Sold | inventoryVariance | 91.30 |
Reset MA (example shown is based on Reset MA above)
| Posting Section | GL Code | Debit | Credit |
|---|---|---|---|
| Current Asset | inventory | 55.2 0 | |
| Cost of Goods Sold | inventoryVariance | 55.20 |
Back-dated Transactions
MA is always current.
...
Scenario 1 : back-dated Purchase Return
| Txn Date | Creation Date | Txn | Qty | U.Price | U.Cost | Balance Qty | Balance Value | MA | GP |
| 2015-03-01 | 2015-03-01 | GRN1001 | 200 | 30.00 | 200 | 6,000.00 | 30.00 | ||
| 2015-06-01 | 2015-06-01 | GRN1002 | 200 | 100.00 | 400 | 26,000.00 | 65.00 | ||
| 2015-03-01 | 2015-07-01 | PR1001 (for GRN1001) | 200 | 30.00 | 200 | 20,000.00 | 100.00 | ||
| 2015-08-01 | 2015-08-01 | INV1001 | 200 | 110.00 | 100.00 | 0 | 0.00 | 0.00 | 2,000.00 |
As you can see, the MA shot up to $100 after PR1001. This is an expected behavior because as mentioned MA is always recalculated based on current balance quantity and current MA. In this case, the current balance quantity was 200 and current ma was $65.
...
If we were to redo the above transactions all over again, where the creation date and transaction date are the same, as shown below, you will notice that the Cost and GP will be the same.
| Txn Date | Creation Date | Txn | Qty | U.Price | U.Cost | Balance Qty | Balance Value | MA | GP |
| 2015-03-01 | 2015-03-01 | GRN1001 | 200 | 30.00 | 200 | 6,000.00 | 30.00 | ||
| 2015-03-01 | 2015-03-01 | PR1001 (for GRN1001) | 200 | 30.00 | 0 | 0.00 | 0.00 | ||
| 2015-06-01 | 2015-06-01 | GRN1002 | 200 | 100.00 | 200 | 20,000.00 | 100.00 | ||
| 2015-08-01 | 2015-08-01 | INV1001 | 200 | 110.00 | 100.00 | -200 | -20,000.00 | 0.00 | 2,000.00 |