Multiple rows in IV10200 for one receipt line - inventory cost layers in Dynamics GP
Explaining how inventory cost layers work for multi-currency purchasing transactions in Dynamics GP
Table IV10200 - Inventory Goods Received Work
Purchase receipts for goods in the Inventory Goods Received Work table, may have two rows for a single receipt line.
For multi-currency transactions, where goods have been purchased in another currency to that of the system functional currency then, one line of a receipt can show up as two rows in the IV10200 table.
Example posting of a receipt line for qty=2000.
The first row is for all but one of the qty receipted (1999)
The second row is for the remaining qty (1).
So the total of the two line quantities will match the quantity on that purchase receipt (2000), but the unit costs will differ between the two lines (£0.49 and £11.79).
Explained
Lets explore why this is so and to do this we are best setting out an example:
- Functional currency: GBP (£)
- Vendor/Supplier currency: EUR (€)
- Quantity purchased: 2,000 units
- Unit price: €0.57 per unit
- Exchange rate: €1.00 = £1.15
- Extended cost in euros: 2,000 × €0.57 = €1,140.00
- Extended cost in pounds: €1,140.00 ÷ 1.15 = £991.30
Root of the problem:
Deriving the unit cost from the converted extended cost by diving by the number of items...
£991.30 ÷ 2,000 units = £0.49565 per unit
GP must store unit costs in pence (two decimal places), so it cannot store £0.49565. To honor the decimal places, it must choose between £0.49 and £0.50, and neither divides perfectly into £991.30:
| Result | |
|---|---|
| 2,000 × £0.49 | £980.00 (£11.30 short) |
| 2,000 × £0.50 | £1,000.00 (£8.70 over) |
Neither works. So GP takes as many units as possible at £0.49 and loads the remainder onto the last unit:
| Cost Layer | Qty | Unit Cost (£) | Extended Cost (£) |
|---|---|---|---|
| Layer 1 | 1,999 | £0.49 | £979.51 |
| Layer 2 | 1 | £11.79 | £11.79 |
| Total | 2,000 | £991.30 ✓ |
What the £11.79 Actually Consists Of
| Element | Amount (£) |
|---|---|
| True cost of 1 unit at £0.49 | £0.49 |
| Rounding remainder from 1,999 units at £0.49 | £11.30 |
| Total on Layer 2 | £11.79 |
So £11.30 of the £11.79 is purely the exchange rate rounding difference that GP could not distribute across the batch. Only £0.49 of it is the actual cost of that unit.
What This Means for Valuation
Under FIFO, the first 1,999 units consumed will each show a COGS (Cost of Goods Sold) of £0.49 - understated by £0.00565 per unit against the true cost of £0.49565. Then when the 2,000th unit is sold or used:
COGS spikes to £11.79
Gross margin on that transaction collapses
Any pricing, margin or stock reporting referencing that unit looks completely wrong and is impractical and does not represent reality.
The total £991.30 across all 2,000 units is correct - it is purely the FIFO (First IN First Out) layer distribution that is distorted. Hence we apply average costing to this whole transaction.
What happens with finance postings?
FIFO perpetual costing absorbs the £11.30 rounding difference across all stock on hand, giving a blended unit cost of approximately £0.4957 rather than a distorted £11.79 layer, rounded to £0.50 cost. The costing layer is collapsed for the sake of sanity in costing.
The £11.79 cost will flow through to the invoice or inventory transaction that ends up taking that stock item.
Summary
With an exchange rate of 1.15 and a receipt total of £991.30, the true unit cost is £0.49565 - is a figure that GP simply cannot store to two decimal places.
The line is split with most items assigned the actual cost and the second row taking on the exchange rate rounding variance as a cost in addition to the cost of the item.
The £11.79 on Layer 2 is carrying £11.30 of batch rounding difference that has nowhere else to go. Cost in GP, is taken as £0.50, as it is averaged over the cost layers, keeping the costing sane and practically manageable.