Balancing General Ledger against Inventory has always been a
tremendous headache. I have always spent several weeks handling
Inventory-GL issues which theoretically would not take more than few
days. The problem in our case is represented with "cost-adjustment"
primarily which results from improper application of certain processes.
This headache has motivated me to create a sort of test-environment in order to detect all the associated factors affecting the IV-GL balance. Aside from the issues resulting from the improper application (which is not my topic now), I have found out that there is a specific case in MS Dynamics GP 10, in which the system contributes to the IV-GL imbalance, which might be called "Enter/Match Partial Cost Change". I will thoroughly explain this point as follows;
Stage 1 - Receiving a Shipment
Receivng Transaction Details:
Stage 2 - Enter/Match Invoice:
On the Enter-Match winodw, I will suppose that part of the shipment will have the same Unit Cost (regardless of the quantity), while the other part of the shipment cost will increase or decrease (same result either in the increase or decrease.)
This test has been applied by MVP Mohammad Daoud on GP 10.0, 2010 and 2013 with the same results returned. The good news is that the new utility in GP 2013 "Reconcile to GL" locates this difference as shown below;
As a result of the explanation above, GP creates an imbalanced Journal entries between GL and Inventory in this case specifically (partial change of the unit cost) because of having different modules behaviors.
Best Regards,
Mahmoud M. AlSaadi
This headache has motivated me to create a sort of test-environment in order to detect all the associated factors affecting the IV-GL balance. Aside from the issues resulting from the improper application (which is not my topic now), I have found out that there is a specific case in MS Dynamics GP 10, in which the system contributes to the IV-GL imbalance, which might be called "Enter/Match Partial Cost Change". I will thoroughly explain this point as follows;
Stage 1 - Receiving a Shipment
Receivng Transaction Details:
Document Number | Type | Item | Quantity | Unit Cost | Extended Cost |
RCT000000889 | Receiving | A | 100 | 39.25 | 3925 |
Inventory Module:
IV30300
Item | TRXQTY | Unit Cost | EXTDCOST | IVIVINDX | IVIVOFIX |
A | 100 | 39.25 | 3925 | Inventory Warehouse | Accrued Purchases |
IV10200
Item | QTYRECVD | Unit Cost | EXTDCOST | ADJUNITCOST |
A | 100 | 39.25 | 3925 | 39.25 |
SEE30303
Item | TRXQTY | Unit Cost | EXTDCOST | IVIVINDX | IVIVOFIX | JRENTRY | Dr | Cr |
A | 100 | 39.25 | 3925 | IV Warehouse | Accrued Purchases | X | 3925 | 0 |
General Ledger :
GL20000
JRENTRY | Account Index | Dr | Cr |
X | Inventory Warehouse | 3925 | 0 |
X | Accrued Purchases | 0 | 3925 |
On the Enter-Match winodw, I will suppose that part of the shipment will have the same Unit Cost (regardless of the quantity), while the other part of the shipment cost will increase or decrease (same result either in the increase or decrease.)
Enter/Match Transaction Details:
Document Number | Type | Item | Quantity Invoiced | Unit Cost | Extended Cost |
RCT000000890 | Invoice | A | 80 | 39.25 | 3140 |
RCT000000890 | Invoice | A | 20 | 50 | 1000 |
Inventory Module:
Inventory module assumes that all the cost
layer is adjusted to have a unit cost of (50).Therefore, it calculates
the difference between the old Extended Cost (100*39.25 = 3925) and the new Extended Cost (100*50 = 5000) and the difference between the two which is (1075) is debited on the inventory warehouse account.
IV30300
Item | TRXQTY | Unit Cost | EXTDCOST | IVIVINDX | IVIVOFIX |
A | 100 | 39.25 | 3925 | Inventory Warehouse | Accrued Purchases |
A | 0 | 0 | 1075 | Inventory Warehouse | COGS |
IV10200
Item | QTYRECVD | Unit Cost | EXTDCOST | ADJUNITCOST |
A | 100 | 50 | 3925 | 39.25 |
SEE30303
Item | TRXQTY | Unit Cost | EXTDCOST | IVIVINDX | IVIVOFIX | JRENTRY | Dr | Cr |
A | 100 | 39.25 | 3925 | IV Warehouse | Accrued Purchases | X | 3925 | 0 |
A | 0 | 10.75 | 1075 | IV Warehouse | COGS | Y | 1075 | 0 |
General Ledger :
On the other hand, General Ledger can
detect correctly that the change on the unit cost is partial, while the
other part of the cost layer still have the same cost. Therefore, it
calculates the cost adjustment as follows;
Old Extended Cost = 100*39.25 = 3925
New Extended Cost = (80*39.25)+(20*50) = 4140
And the difference is 4140 – 3925 = 215
GL20000
JRENTRY | Account Index | Dr | Cr |
Y | Inventory Warehouse | 215 | |
Y | Accrued Purchase | 4140 | |
Y | COGS | 3925 |
This test has been applied by MVP Mohammad Daoud on GP 10.0, 2010 and 2013 with the same results returned. The good news is that the new utility in GP 2013 "Reconcile to GL" locates this difference as shown below;
Reconcile to GL Utility - GP2013 |
As a result of the explanation above, GP creates an imbalanced Journal entries between GL and Inventory in this case specifically (partial change of the unit cost) because of having different modules behaviors.
Best Regards,
Mahmoud M. AlSaadi
Thanks So Much for Your Helpful Post , but I'm wondering why you add COGS
ReplyDeleteas the items have not yet been Sold ???!!