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