Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

Saturday, July 13, 2013

Enter/Match Partial Cost Modification - Inventory versus General Ledger Test

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:

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
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.)

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

1 comment:

  1. Thanks So Much for Your Helpful Post , but I'm wondering why you add COGS
    as the items have not yet been Sold ???!!

    ReplyDelete