Revisiting the Average Costing in Dynamics GP

I have received an interesting inquiry from a Dynamics GP customer having concerns about the degree to which Average Costing Calculation is accurate with a special case related to the currency decimals and unit of measurement, which has been thoroughly illustrated in a previous post. In this post, I am presenting a complicated case including both average costing calculation and cost layers split case.

First of all, lets summarize the calculation criteria that the system rely on to calculate the average cost of a specific cost layer, without digging quite deeply into the common “inventory ripple effect” approach which is comprehensively illustrated by MVP Mariano Gomez on Why is my inventory-related transaction posting so slow? The following scenario will shed a light on the standard approach that is considered to calculate the “average cost”

Lets suppose that I am posting 5 inventory receipts on a sequential order in different dates (January, February, March …etc). The image below illustrates how purchase receipts are posted in terms of order and dates.

 

As shown above, all the receipts have the same unit cost, which results in the same unit cost and adjusted unit cost (both are 20). Now, if I enter another receipts proceeding to all the dates above (for instance, 1/6/2017), the average cost will be calculated as follows:

>> Further details regarding the calculation above can be found on Article ID: 923960 Enhancements made to the calculation of average cost in Microsoft Dynamics GP

Now, what If the same scenario above happens to be with a “Cost Layer Split Scenario”. The Cost layer split scenario is the case at which the system has a variance resulting from dividing the unit cost by the equivalent unit of measurement factor to calculate the unit cost of the smallest unit of measurement level such as (50/6= 8.333333 ) >>> See Previous post : Unit cost and Currency decimals setup. In this essence, the system will create two cost layers, both of them will be included in the average cost calculation.

Suppose the I am posting 5 receipts as well and then entering a back dated transaction. See below:

The receipts are posted in the order illustrated under the “Transaction Order” column. Here is receipt 6 details:

* Item Number: Item X
* UOM:  CASE = 6 Piece
* Quantity Received: 1 Case
* Unit Cost (for the Case): 50
* Base UOM unit cost: 50/6 = 8.333333

Therefore, this cost layer will be divided among two cost layer, one of them will handle the rounding variance as shown below:
Cost Layer One | 5 Pieces , Unit cost = 8.33 , Extended Cost = (8.33 * 5 = 41.65)
Cost Layer Two | 1 Piece ,  Unit cost = 8.35 , Extended Cost = (8.35 * 1 = 8.35)
Grand Total = 41.65 + 8.35 =  50

Now, it should be noted that the other layer ( one piece for 8.35) is embedded in the calculation of the average cost for the back dated transactions, this is illustrated below:

What if the system didn’t split the cost layers to handle the rounding and just throw the variance at a specific suspense account ? This means that the cost layer will be something like (6 pieces with a unit cost of 8.33, extended cost = 49.98, variance = 0.02)

Here is the comparison

 

Attached is the excel file illustrating the formulas used to calculating the running average cost per cost layers considering the various scenarios above. >> Excel File (Average Cost Calculation) | Download Link

Best Regards,
Mahmoud M. AlSaadi

Pay attention through Initial Assessment , when it Comes to Inventory Items and Currency Decimals

After an implementation of Dynamics GP for one of our clients, I have received a critical and weird case stating that all the inventory related transactions (Shipment, Inventory Adjustment and Sales) are recorded with a “Zero” unit cost, although, they are entering the correct unit cost when receiving the shipment. Here is the starting point of the case”

                                                               >>  All unit cost fields are zero, on both In and Out transactions

Troubleshooting:

The item number is defined as a sales inventory, FIFP perpetual valuation method and assigned to a KG unit of measurement. Currency decimals on the system level and distribution modules is set to (3) for this selected currency used for this item. The following is the UOM setup of KG:

 

Here is the interesting part, starting with the first transaction on this item (which is a shipment), the following are the transaction details:

• Quantity shipped: 50
• UOM: KG
• Unit Cost (for the KG): 0.44

Now, on the inventory module, the system will break down the unit cost into the smallest unit of measurement, and record the cost layer in the base unit of measurement (IV10200). In case of rounding, the system will consider splitting the cost layers into two cost layers >> Reference: unit cost and currency decimal setup

• Quantity in Base UOM: 50 * (1000) = 50,000 Gram
• Unit cost (for the Gram): 0.44/1000 = 0.00044

The result that the Inventory Module will split into two cost layers, (49,999 Gram) and (1 Gram), the one gram will have the variance which is the total cost in our scenario (22). Here is the purchase receipt smart list again:

By now, it is clear that any (out) transaction withdrawing from this cost layer will have a zero unit cost, which is incorrect. Until, all 49,999 Gram are consumed, the unit cost will be zero. Then when withdrawing the final one gram, the total cost of the whole 50 KG will be consumed.

Correction Criteria

1. Post or delete all pending “saved” transactions
2. Change the currency decimals (on the distribution level, not the system level) to encompass more than the current number of decimals for this specific item, not necessarily all the items.
3. Adjust cost for the historical transactions

 

In order to change currency decimals, go to Microsoft Dynamics GP > Tools > Utilities > Inventory > Change Decimal Places

Helping Note !

• Before running the change decimal places utilities, no user in the company should be working on any of the inventory related transactions (Such as:Inventory Adjustment, Transfer, Receiving, Sales …etc).
• No transaction should be saved or pending on a purchase order, receiving …etc.
• One pressing on “Process”, you will be warned that Manufacturing isn’t updated when you change currency decimals. It’s fine, just proceed

In order to adjust the cost, go to Microsoft Dynamics GP > Tools > Utilities > Inventory > Adjust Cost

After processing the cost adjustment, a report will show all the journal entries created to adjust the cost on the “General Ledger” as well. Please be noted that not only the current cost layer; which is being adjusted, will be affected. ALL related “out” transaction which withdraws from this specific cost layer will be affected accordingly.

The affected transactions will be listed in the Purchase Receipt Update Detail report, after processing the cost adjustment.

Here is the journal entries created on the General Ledger, it will affect inventory and inventory offset accounts (COGS)

 

Best Regards,

Mahmoud M. AlSaadi

Cost Adjustment (2 out of 4) Shipment and Enter Match Invoice

In the previous post of this series ; Cost Adjustment (1 of 4) Override Documents, the first of four scenarios in which cost variance documents are calculated was thoroughly explained. In this post, we will move on with the second scenario of cost adjustment.

When using the shipment – enter match approach, the cost recorded at the shipment could be different than the one on the invoice. For instance, we received one piece of item (X) with cost of 150, then upon receiving the invoice it shows that the cost is different like 160. The following journal entries are recorded;

Business Perspective

Upon Receiving | Purchasing > Transactions > Receiving Transaction Entry (Shipment)

Upon Invoicing | Purchasing > Transactions > Enter Match Invoice

Technical Perspective

Once the shipment is posted, the following records will be written in inventory tables

Now, when an invoice with different unit cost (160) is matched to the shipment, a new cost adjustment record is thrown in SEE30303 (HITB), as well as other modifications in other tables;

• IV30300 records an addition record with the total difference as an extended cost
• IV10200 updates the unit cost field with the new unit cost, while the old unit cost is saved in another field which is ADJUNITCOST, Adjusted Unit Cost
• SEE30303 records an addition record with all the cost adjustment details. Unit cost, extended cost and the associated Journal Entry created on the general ledger level

Helping Note !

This scenario supposes that the shipment is not consumed at all until the time that the invoice is recorded. The third scenarios of this series illustrate how sales documents posted before the invoice is recorded will be updated accordingly to reflect correct cost of goods sold.

Best Regards,
Mahmoud M. AlSaadi

HITB Essentials Series – Zero Quantity and Negative/Positive Cost

One essential part of reconciling inventory tables is to check the Extended Cost for each item within Historical Inventory Trial Balance (SEE30303) versus The Extended Cost in Purchase Receipt Layer (IV10200)

Theoretically speaking, these two values must match for each item number. Although, for several reasons these two values are not the same.

This post is part of the HITB Essentials Series.In this post, I will be providing the script through which we could compare the HITB with the Purchase Receipt Layer only, to get an initial assessment of the Inventory Variances per item.

HITB Essentials Series will reveal several critical inventory issues regarding primarily two issues (Internal Inventory Reconciliation – IV Tables) and (External Inventory Reconciliation – Inventory versus GL)

Tables Included:

• IV10200  | Purchase Receipt Layer Work
• SEE30303 | Historical Inventory Trial Balance

SELECT
   ISNULL(X.[ITEMNMBR],Y.[ITEMNMBR]) [ITEMNMBR],
   X.HITB_Quantity_Available,
   X.HITB_Cost,
   Y.IV_Quantity_Available,
   Y.IV_EX_Cost,
   X.HITB_Cost - Y.IV_EX_Cost AS Cost_Variance
FROM
(
   SELECT  [ITEMNMBR],
   SUM([TRXQTYInBase]) HITB_Quantity_Available,
   SUM([EXTDCOST]) HITB_Cost
   FROM [SEE30303]
   GROUP BY [ITEMNMBR]
)   AS X    ------  Calculate the Extended_Cost per Item [SEE30303]

FULL OUTER JOIN   

(
   SELECT  [ITEMNMBR],
   SUM([QTYRECVD]-[QTYSOLD]) IV_Quantity_Available,
   SUM(([QTYRECVD]-[QTYSOLD])*[UNITCOST]) IV_EX_Cost
   FROM [IV10200]
   GROUP BY [ITEMNMBR]
)   AS Y    ------  Calculate the Extended_Cost per Item [IV10200]

ON X.[ITEMNMBR] = Y.[ITEMNMBR]
WHERE ABS( X.HITB_Cost - Y.IV_EX_Cost) > 1  OR
X.HITB_Quantity_Available<> Y.IV_Quantity_Available

 

Best Regards,

Mahmoud M. AlSaadi

Cost Adjustment (1 out of 4) Override Documents

When inventory items are either purchased, manufactured or sold through the associated modules accordingly; Purchase Order Processing, Manufacturing or Sales Order Processing, these items will be exposed to specifically four different scenarios in which cost variance documents are calculated. Each of these scenarios have its own characteristics and considerations.

Understanding these scenarios comes as an essential part of understanding these modules behaviors and their effects on the inventory module from one side, and the inventory versus General Ledger on the other side.

In this post, I will be shedding a light on the first scenario, which is the override documents.

Setup and Configuration

On the setup level of Dynamics Great Plains, Quantity Shortage option can be mainly setup in one of the following;

• Inventory Control | Dynamics GP > Tools > Setup > Inventory > Inventory Control

Inventory Control – Override Setup

There are three basic options to check which will allow quantity shortage to be handled through override documents. Checking one of these options will allow the quantity on hand to drop to a “negative value” since the required quantity to be withdrawn is bigger than the available quantity

• Sales Order Processing |  Dynamics GP > Tools > Setup > Sales > Sales Order Processing > Sales Document Setup > Fulfillment Order/ Invoice

This option will allow the sales of quantities less than the quantity available, which results with quantity shortage and override documents

Sales Order Processing – Override Setup

Understanding Override Documents – Functional Point of View

Though this section, I will provide a practical example through which an adjustment override is allowed in Inventory to withdraw less than the quantity available resulting with negative quantity on hand.

Transaction Level – Quantity Shortage

• Document Type: Adjustment Out
• Item Number: Chair
• Current Cost: 5
• Quantity on Hand: 0

Adjustment Transaction

Account	Debit	Credit
Inventory	0	50
Inventory Offset	50	0

After the transaction is posted, the quantity on hand will be (-10).

Now, supposing that the after a while, an inventory replenishment occurs to relieve the override document. The replenishment is done through a receiving document (Shipment/Invoice) but with a higher unit cost (7).

The inventory replenishments will result not only with the receiving document, but with a cost adjustment document as well. The following journal entries will be posted;

Receiving Transaction

Account	Debit	Credit
Inventory	70	0
Inventory Offset	0	70

Cost Adjustment Document

Account	Debit	Credit
Inventory	0	20
Inventory Offset	20	0

Understanding Override Documents – Technical Point of View

Inventory tables responds to the override document in the best way to best fulfills data accuracy, by recording all the required fields. It’s worth saying that each of the inventory tables has its own behavior which are all working in consistency and harmony to provide several insights on the same piece of information

Section One | Quantity Shortage and Override Documents

IV10200

As for the purchase receipt layer work table, there is no available purchase layer from which to adjust out, therefore Dynamics GP throws an override purchase receipt layer with a zero quantity received and 10 quantity sold

IV10201

As for the purchase receipt layer details, a detail record for the adjustment out will be thrown and linked through the criteria

• (IV10200.ReceiptSequenceNo = IV10201.SourceReceiptSequence No)

SEE30303

Most importantly, the historical inventory trial balance table, records most of the essential details since it will be later the only inventory table containing cost adjustment document

Database View – Inventory Tables with Override Document

Section Two | Inventory Replenishment and Cost Adjustment
Now, to go with our scenario, a receiving transaction (Shipment/Invoice) for the same item is posted later on. Most importantly, with different cost. See below how tables are updated.

As for the Inventory Transaction Amount History (IV30300), no changes are performed on this table since the behavior of this table is to record historical transaction as is, and keep them in the same historical state regardless of any further changes

As for the Inventory Purchase Receipt Layer (IV10200), changes on the quantity received as long as the receipt sequence number (the link with IV10201)

As for the Inventory Purchase Receipt layer details, the original receipt sequence number is changed accordingly to be linked with the purchase receipt layer. On the other hand, the unit cost is replaced with the new cost while the old cost is recorded in ADJ cost field.

As for the HITB, a cost adjustment record is thrown with the cost adjustment journal that will be posted to General Ledger in order to adjust the previous layer unit cost. And keep Inventory versus GL tied together.

 

Database View – Inventory Tables with Inventory Replenishment

Cost Adjustment – Case 1 out of 4 (Override)

To be Continued …

Best Regards,
Mahmoud M. AlSaadi

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