This topic includes the accounting formulas that the Eagle system uses to calculate the following:
average cost
cost of goods sold
SKU life cycle
The following is the formula that the system uses to calculate average cost when receiving merchandise.
[(new cost x quantity received) + (old average cost x QOH before receipt)] divided by
____________________________________________________________
the new QOH.
For example: You received 4 of an item with a cost of $1.88 each, and you currently have 1 with an average cost of $1.75 each. The calculation to figure the new average cost is:
[(1.88×4)+ (1.75×1)]
----------------------------- = 1.854
5
Thus, the new average cost is $1.854.
The following is the formula that the system uses to calculate cost of goods sold for a SKU sold at POS.
Quantity sold x inventory average cost = cost of goods sold.
For example: The quantity sold is 3 and your average cost in Function IMU is $1.70. The calculation to figure cost of goods sold is:
3 X 1.70 = 5.10
The cost of goods sold is $5.10.
The following are two examples of a SKU’s life cycle. The first example is a SKU that maintains a positive quantity on hand. The second example is a SKU that maintains a negative quantity on hand. Each example contains three phases of the SKU’s life cycle. The first phase is when the item is purchased and received through Purchasing and Receiving and the Receiving Report (RRP). The second phase is when the SKU is sold at POS. The third phase is when the SKU is modified through Modify Receiving Documents (MRV) and the Receiving Documents Report (RRV). Each phase displays the perpetual inventory accounting calculations and its effect on general ledger.
The following are accounting formulas for a SKU that has a positive quantity on hand before and after receiving additional stock.
This example follows SKU 100321 that has:
a quantity on hand (QOH) of 1
an average cost of $1.75
an inventory cost value of $1.75 (1 x 1.75 = 1.75)
SKU 100321 has 3 received by the Receiving Report (RRP), Option F, with a replacement cost of $1.70 each.
The new average cost is $1.712 [ (1.70 x 3) + (1.75 x 1)] = 1.712
4
The new replacement cost is $1.70 (extended: 3 x 1.70 = 5.10).
The net G/L effect of this SKU being purchased and received is:
Accounts Payable Inventory Account
_______________ ________________
| $5.10 Credit $5.10 Debit |
| |
| |
SKU 100321 now has:
a QOH of 4
a replacement cost of $1.70
an average cost of $1.712
an inventory cost value of $6.85 (4 x 1.712 = 6.848)
After the Receiving Report (RRP), Option F, ran, SKU 100321 has 3 sold at POS for a retail price of $3.49 each (extended: 3. x 3.49 = 10.47) and an average cost of goods
sold of $1.712 each (extended: 3 x 1.712 = 5.136).
Sales tax is 8% (10.47 x 0.08 = 0.84).
The net G/L effect of this SKU being sold is:
Cost of Goods Sold Inventory Account
________________ ______________
$5.136 | | $ 5.136
| |
| |
Taxable Merch. Cash Account
_____________ _____________
| $10.47 $11.31 |
| |
| |
Tax Liability
____________
| $.084
|
|
SKU 100321 now has:
a QOH of 1
an inventory value of $1.71 (1 x 1.712 = 1.712)
After 3 of SKU 100321 were sold at POS, you later receive your vendor’s invoice and find that you:
physically received 4, not 3 as finalized by the Receiving Report (RRP)
were charged $1.88 each, not the $1.70 finalized by RRP
In the Modify Receiving Documents (MRV) window, you post the change to quantity and cost, and finalize the receiving document with the Receiving Documents Report (RRV), Option F.
The new replacement cost is $1.88 (extended: 4 x 1.88 = 7.52).
The original extended cost posted to the A/P and Inventory accounts was $5.10 (3 x 1.70 = 5.10), therefore the total variance is $2.42 (7.52 - 5.10 = 2.42).
The net G/L effect of this SKU being modified by a receiving document is:
Accounts Payable Inventory Account
_____________ _______________
| $2.42 $2.42 |
| |
| |
The new average cost is $1.854 because
[(1.88 x 4) + (1.75 x 1)] = 1.854.
5
The $1.75 average cost is the original average cost of this SKU (reported as old average cost on RRP, Option F).
Up until this point, this SKU had collected $5.136 (3 x 1.712 = 5.136 ) in costs of goods sold. But, the cost of goods sold would have been $5.562 (3 × 1.854 = 5.562 ) if the actual cost had been correct to begin with (on RRP). Therefore, the finalized receiving document makes an entry to the Daily Journal Report (RDJ), Option Z, for the difference of $0.43 ( 5.562-5.136 = 0.426, rounded to 0.43) to adjust inventory and cost of goods sold.
The net G/L effect of this change is:
COGS Adjust. Inventory Adjust.
________________ ______________
$0.43 | | $ 0.43
| |
| |
SKU 100321 now has:
a QOH of 2
a replacement cost of $1.88
an average cost of $1.854
an inventory cost value of $3.71 (2 x 1.854 = 3.71)
Summary
The following table is a summary of the positive quantity SKU life cycle.
|
SKU 100321 |
Inventory Value |
|
Beginning Data Qty on Hand 1 Replacement Cost $1.75 Average Cost $1.75 |
$1.75 |
|
Receiving Quantity Received 3 New Qty on Hand 4 New Replacement Cost $1.70 Extended Repl Cost $5.10 New Average Cost $1.712 |
$6.85 |
|
Selling Quantity Sold 3 COGS $1.712 Extended COGS $5.136 New Qty on Hand 1 |
$1.71 |
|
Receiving Document New Qty Received 4 New Qty on Hand 2 New Repl. Cost $1.88 New Extended Repl. Cost $7.52 Variance from Original Recvg. $2.42 COGS/Inventory Adjust. $0.43 New Average Cost $1.854 |
$3.71 |
This section contains accounting formulas for a SKU that has a negative quantity on hand before and after receiving additional stock.
Beginning Data
This example follows SKU 105106 that has:
a quantity on hand (QOH) of -10
an average cost of $25.00
an inventory cost value of $-250.00 (-10 x 25.00 = -250.00)
Phase 1— Receiving
SKU 105106 has 5 received by the Receiving Report (RRP), Option F, with a replacement cost of $28.00 each.
The new average cost is $28.00 (when a quantity on hand is negative, average cost equals replacement cost).
The new replacement cost is $28.00 (extended: 5 x 28.00 = 140.00).
The net G/L effect of this SKU being purchased and received is:
Accounts Payable Inventory Account
_______________ _______________
| $140.00 $140.00 |
| |
| |
Advanced Receiving considers the SKU’s entire negative quantity had been sold at POS with the wrong cost of goods sold (old average cost = $25.00). Therefore, the Receiving Report (RRP), Option F, makes a $3.00 ( 28.00 – 25.00 = 3.00) adjustment to the COGS Adjustments and Inventory Adjustments accounts for each negative QOH. The extended COGS and inventory adjustment is $30.00 (3.00 × 10 = 30.00).
COGS Adjust Inventory Adjust.
_____________ _____________
$30.00 | | $ 30.00
| |
| |
SKU 105106 now has:
a QOH of -5
a replacement cost of $28.00
an average cost of $28.00
an inventory cost value of $-140.00 (-5 x 28.00 = -140.00)
Phase 2— Selling
After The Receiving Report (RRP), Option F, ran, SKU 105106 has 3 sold at POS for a retail price of $55.99 each (extended :3 × 55.99 = 167.97) and an average cost of goods sold of $28.00 each (extended:3 × 28.00= 84.00 ).
Sales tax is 8% (167.97 × 0.08 = 13.86).
The net G/L effect of this SKU being sold is:
Cost of Goods Sold Inventory Account
________________ ______________
$84.00 | | $ 84.00
| |
| |
Taxable Merch. Cash Account
_____________ _____________
| $167.97 $181.83 |
| |
| |
Tax Liability
_____________
| $13.86
|
|
SKU 105106 now has:
a QOH of -8
an inventory value of $-224.00 (– 8 × 28.00 = – 224.00)
Phase 3— Receiving Document
After 3 of SKU 105106 were sold at POS, you later receive your vendor’s invoice and find that you:
physically received 15, not 5 as finalized by Report RRP
were charged $27.50 each, not the $28.00 finalized by Report RRP
In Modify Receiving Documents (MRV) you post the change to quantity and cost, and finalize the receiving document with the Receiving Documents Report (RRV), Option F.
The new replacement cost is $27.50 (extended: 15 × 27.50 = 412.50).
The original extended cost posted to the A/P and Inventory accounts was $140.00 (5 × 28.00 = 140.00), therefore the total variance is $272.50 (412.50 – 140.00 = 272.50).
The net G/L effect of this SKU being modified by a receiving document is:
Accounts Payable Inventory Account
______________ _______________
| $272.50 $272.50 |
| |
| |
The new average cost is $27.50 (average cost equals replacement cost when a negative QOH condition exists).
Up until this point, this SKU had collected $84.00 (3 × 28.00 = 84.00) in costs of goods sold. But, the cost of goods sold would have been $82.50 (3 × 27.50 = 82.50) if the actual cost had been correct to begin with (on Report RRP). Therefore, the finalized receiving document makes an entry to Report RDJ, Option Z, for the difference of $1.50 (84.00– 82.50 = 1.50) to adjust inventory and cost of goods sold.
In addition, since Report RRP originally made a $30.00 entry to the COGS Adjustments and Inventory Adjustments accounts (3.00 × 10 = 30.00), the average cost change means that entry was overstated by $0.50 for each negative QOH. Therefore, an additional $5.00 (0.50 × 10 = 5.00) is added to the above $1.50 adjustment to post a net adjustment of $6.50.
The net G/L effect of these changes are:
COGS Adjust. Inventory Adjust.
_____________ _____________
|$6.50 $6.50 |
| |
| |
SKU 105106 now has:
a QOH of 2
a replacement cost of $27.50
an average cost of $27.50
an inventory cost value of $55.00 (2 × 27.50 = 55.00)
Summary
The following table is a summary of the negative quantity SKU life cycle.
|
SKU 105106 |
Inventory Value |
|
Beginning Data Qty on Hand -10 Replacement Cost $25.00 Average Cost $25.00 |
$-250.00 |
|
Receiving Quantity Received 5 New Qty on Hand -5 New Replacement Cost $28.00 Extended Repl Cost $140.00 New Average Cost $28.00 COGS/Inv. Adjustment $30.00 |
$-140.00 |
|
Selling Quantity Sold 3 COGS $28.00 Extended COGS $84.00 New Qty on Hand -8 |
$-224.00 |
|
Receiving Document New Qty Received 15 New Qty on Hand 2 New Repl. Cost $27.50 New Extended Repl. Cost $412.50 Variance from Original Recvg. $272.50 COGS/Inventory Adjust. $6.50 New Average Cost $27.50 |
$55.00 |