Difference between revisions of "Inventory: Understanding Average Cost"
Jump to navigation
Jump to search
| Line 1: | Line 1: | ||
| + | ===Simple Case=== | ||
Inventory Simple Case - Avg Cost IS impacted by Sequence!!! | Inventory Simple Case - Avg Cost IS impacted by Sequence!!! | ||
| Line 21: | Line 22: | ||
| S || -50 || 60 || 15.45454545 || -772.7272727 || 927.2727273 || 15.45454545 | | S || -50 || 60 || 15.45454545 || -772.7272727 || 927.2727273 || 15.45454545 | ||
|} | |} | ||
| + | |||
| + | ===Inventory With Reversal - All Positive=== | ||
Inventory with a sale reversal, but keeping it positive! All good, but sequence matters | Inventory with a sale reversal, but keeping it positive! All good, but sequence matters | ||
| Line 74: | Line 77: | ||
|- | |- | ||
| S || -200 || 70 || 16.42857143 || -3285.714286 || 1150 || 16.42857143 | | S || -200 || 70 || 16.42857143 || -3285.714286 || 1150 || 16.42857143 | ||
| + | |} | ||
| + | |||
| + | ===Inventory With Sale/Reversal - Gets Negative=== | ||
| + | |||
| + | Similar to the case where items were positive, the impact of matching Negative and Postive record have no net effect even when the accumlative qty goes negative: | ||
| + | {| class="wikitable" | ||
| + | ! TxType !! EFFCTV QTY !! ACCUMQTY !! COST !! TOTCOST !! ACCUMCOST !! AVGCOST | ||
| + | |- | ||
| + | | B || 100 || 100 || 15 || 1500 || 1500 || 15 | ||
| + | |- | ||
| + | | S || -50 || 50 || 15 || -750 || 750 || 15 | ||
| + | |- | ||
| + | | I || 10 || 60 || 20 || 200 || 950 || 15.83333333 | ||
| + | |- | ||
| + | | S || -200 || -140 || 15.83333333 || -3166.666667 || -2216.666667 || 15.83333333 | ||
| + | |- | ||
| + | | S || 200 || 60 || 15.83333333 || 3166.666667 || 950 || 15.83333333 | ||
| + | |- | ||
| + | | I || 10 || 70 || 20 || 200 || 1150 || 16.42857143 | ||
| + | |} | ||
| + | |||
| + | |||
| + | If a purchase comes between a negative sale and reversal, the ending avg. cost is impacted, but this is still perfectly acceptable and correct handling for the data given. | ||
| + | {| class="wikitable" | ||
| + | ! TxType !! EFFCTV QTY !! ACCUMQTY !! COST !! TOTCOST !! ACCUMCOST !! AVGCOST | ||
| + | |- | ||
| + | | B || 100 || 100 || 15 || 1500 || 1500 || 15 | ||
| + | |- | ||
| + | | S || -50 || 50 || 15 || -750 || 750 || 15 | ||
| + | |- | ||
| + | | I || 10 || 60 || 20 || 200 || 950 || 15.83333333 | ||
| + | |- | ||
| + | | S || -200 || -140 || 15.83333333 || -3166.666667 || -2216.666667 || 15.83333333 | ||
| + | |- | ||
| + | | I || 10 || -130 || 20 || 200 || -2016.666667 || 15.51282051 | ||
| + | |- | ||
| + | | S || 200 || 70 || 15.51282051 || 3102.564103 || 1085.897436 || 15.51282051 | ||
|} | |} | ||
Revision as of 18:14, 25 October 2023
Simple Case
Inventory Simple Case - Avg Cost IS impacted by Sequence!!!
| TxType | EFFCTV QTY | ACCUMQTY | COST | TOTCOST | ACCUMBAL | AVGCOST |
|---|---|---|---|---|---|---|
| B | 100 | 100 | 15 | 1500 | 1500 | 15 |
| S | -50 | 50 | 15 | -750 | 750 | 15 |
| I | 10 | 60 | 20 | 200 | 950 | 15.83333333 |
| TxType | EFFCTV QTY | ACCUMQTY | COST | TOTCOST | ACCUMBAL | AVGCOST |
|---|---|---|---|---|---|---|
| B | 100 | 100 | 15 | 1500 | 1500 | 15 |
| I | 10 | 110 | 20 | 200 | 1700 | 15.45454545 |
| S | -50 | 60 | 15.45454545 | -772.7272727 | 927.2727273 | 15.45454545 |
Inventory With Reversal - All Positive
Inventory with a sale reversal, but keeping it positive! All good, but sequence matters
| TxType | EFFCTV QTY | ACCUMQTY | COST | TOTCOST | ACCUMBAL | AVGCOST |
|---|---|---|---|---|---|---|
| B | 100 | 100 | 15 | 1500 | 1500 | 15 |
| S | -50 | 50 | 15 | -750 | 750 | 15 |
| I | 10 | 60 | 20 | 200 | 950 | 15.83333333 |
| S | 200 | 260 | 15.83333333 | 3166.666667 | 4116.666667 | 15.83333333 |
| I | 10 | 270 | 20 | 200 | 4316.666667 | 15.98765432 |
| S | -200 | 70 | 15.98765432 | -3197.530864 | 1119.135802 | 15.98765432 |
| TxType | EFFCTV QTY | ACCUMQTY | COST | TOTCOST | ACCUMBAL | AVGCOST |
|---|---|---|---|---|---|---|
| B | 100 | 100 | 15 | 1500 | 1500 | 15 |
| S | -50 | 50 | 15 | -750 | 750 | 15 |
| I | 10 | 60 | 20 | 200 | 950 | 15.83333333 |
| S | 200 | 260 | 15.83333333 | 3166.666667 | 4116.666667 | 15.83333333 |
| S | -200 | 60 | 15.83333333 | -3166.666667 | 950 | 15.83333333 |
| I | 10 | 70 | 20 | 200 | 1150 | 16.42857143 |
If sale and reversal are together - it doesn't matter the sequence!
| TxType | EFFCTV QTY | ACCUMQTY | COST | TOTCOST | ACCUMBAL | AVGCOST |
|---|---|---|---|---|---|---|
| B | 100 | 100 | 15 | 1500 | 1500 | 15 |
| S | -50 | 50 | 15 | -750 | 750 | 15 |
| I | 10 | 60 | 20 | 200 | 950 | 15.83333333 |
| I | 10 | 70 | 20 | 200 | 1150 | 16.42857143 |
| S | 200 | 270 | 16.42857143 | 3285.714286 | 4435.714286 | 16.42857143 |
| S | -200 | 70 | 16.42857143 | -3285.714286 | 1150 | 16.42857143 |
Inventory With Sale/Reversal - Gets Negative
Similar to the case where items were positive, the impact of matching Negative and Postive record have no net effect even when the accumlative qty goes negative:
| TxType | EFFCTV QTY | ACCUMQTY | COST | TOTCOST | ACCUMCOST | AVGCOST |
|---|---|---|---|---|---|---|
| B | 100 | 100 | 15 | 1500 | 1500 | 15 |
| S | -50 | 50 | 15 | -750 | 750 | 15 |
| I | 10 | 60 | 20 | 200 | 950 | 15.83333333 |
| S | -200 | -140 | 15.83333333 | -3166.666667 | -2216.666667 | 15.83333333 |
| S | 200 | 60 | 15.83333333 | 3166.666667 | 950 | 15.83333333 |
| I | 10 | 70 | 20 | 200 | 1150 | 16.42857143 |
If a purchase comes between a negative sale and reversal, the ending avg. cost is impacted, but this is still perfectly acceptable and correct handling for the data given.
| TxType | EFFCTV QTY | ACCUMQTY | COST | TOTCOST | ACCUMCOST | AVGCOST |
|---|---|---|---|---|---|---|
| B | 100 | 100 | 15 | 1500 | 1500 | 15 |
| S | -50 | 50 | 15 | -750 | 750 | 15 |
| I | 10 | 60 | 20 | 200 | 950 | 15.83333333 |
| S | -200 | -140 | 15.83333333 | -3166.666667 | -2216.666667 | 15.83333333 |
| I | 10 | -130 | 20 | 200 | -2016.666667 | 15.51282051 |
| S | 200 | 70 | 15.51282051 | 3102.564103 | 1085.897436 | 15.51282051 |
