WAC

Weighted Average Cost - How it is calculated within AX

The Weighted Average Costing (WAC) method in AX looks fairly simple at first, but the calculation is tricky if not clearly understood. This leads to miscalculations and wrong costs being updated in the Item Masters after Inventory Close and Recalculation.

After numerous miscalculations and severe failed attempts at understanding the way the system works, there was a Eureka moment. Here is the final breakdown of the process. 

The system works quite differently while calculating the WAC when compared to the theoretical formulas that describe the calculation and this is where the confusion begins...

We will discuss the complete closing and recalculation process with WAC in this post, including the settlement/ knock off transaction that is posted at closing.

Before we dive into the Calculation and closing process, it is important to understand that the transactions for the calculation are considered based on the Setup of the Item/ Inventory Model Group attached to the item.

If Financial Inventory is enabled at the Site only, all item transactions for the item, at the site are considered while calculating the WAC.

If Warehouse is also enabled for Financial Inventory, separate calculation happens at each Warehouse, for every item. More often than not, this leads to the same item having different costs at different Warehouses.

It is advised not to have breakdown of Financial Inventory at Location Level (Allowed in AX 2009, but disabled AX 2012 onward), as it may lead to the same item having substantially different costs at a single warehouse itself, depending on the number of locations.

The process:

The closing process comprises of 3 major steps:

• Arrive at the WAC
• Adjust Issue transactions to match the WAC
• Arrive at On-Hand
• Settle and Close the inventory

Before we discuss the process, one important note: The system considers only those transactions that are financially posted  - i.e transactions that are Invoiced - for the closing process. Transactions that are only physically posted (received/ issued), will be omitted. For transfer orders, the transactions that are Received/ Delivered are considered.

In case the Include physical value check box is selected, the system will consider physical receipts while calculating and populating the costs for the issue transactions at posting (Sales Order lines and Transfer Order outbound lines), but during closing, it will only consider the financially updated transactions. This is a major cause of confusion. Hence it is suggested to keep the Include physical value check-box un-checked while using WAC.

Arriving at the WAC:

Unlike popular belief, the system considers only the Purchases or the Receipt transactions while arriving at the WAC and the Sales (Issues) are nowhere in the picture.

The formula for arriving at the WAC is:

Where ∑ means sum or addition. It means that every transaction quantity must me multiplied with its respective cost price separately and then added together.

In other words, Weighted average = (Q1*P1 + Q2*P2 +…+ Qn*Pn) / (Q1 + Q2 + … + Qn), where Q denotes the Quantity and P denotes the Purchase cost.

Note: The system considers the positive on-hand quantity from the precious closing as a purchase transaction for the subsequent closing, with the previously calculated WAC. Any negative on-hand balance is ignored while calculating the WAC.

Adjust Issue transactions to match the WAC:

Once the system arrives at the WAC for the item, it checks the issue transactions to see if they have the same cost updated against them. Where the cost is different from the calculated WAC, the system posts a positive or a negative adjustment (as necessary) to arrive at the system calculated cost for the issues.

Where the transaction cost is greater than the WAC, the system posts a negative adjustment and where the transaction cost is lesser than the WAC, the system posts a positive adjustment against the line.

No update is made to the negative on-hand from the previous closing.

Arrive at the On-hand:

After the System has calculated the Cost of purchases and adjusted the cost of sales appropriately, the system calculates the On-hand and a settlement is performed for the Item, before the Inventory is closed.

The On-hand is simply calculated as the Total Purchases - Total Sales. If there is a positive on-hand from the previous closing, it is considered a purchase and if there is a negative on-hand from the previous closing, it is considered a sale.

Negative on-hand is subtracted and Positive on-hand is added to the current on-hand inventory.

Settle and Close the Inventory:

The calculation of on-hand and the Settlement & Closing process go hand in hand and there is no set demarcation of which process happens first. For illustration purposes, the settlement & closing is considered as the final process.

The system creates a settlement to match all the issues with the receipts in the period. This is essentially a matching of the issues to a receipt.

For example, you have the following purchase orders for Item IN001:

Purchase order number PO123 was entered for a quantity of 1 at a cost of USD 10.00.

Purchase order number PO456 was entered for a quantity of 1 at a cost of USD 15.00.

Sales order number SO087 was posted for a quantity 1, with the cost amount USD 12.50.

(12.50 will be the Weighted Average for the Item, considering these are the only transactions against the item.)

When the inventory close is run, sales order SO987 will be settled to purchase order PO123.

This settlement is done to ensure that every issue is mapped to a receipt in the system. Where there is only one receipt and one issue, it is a fairly simple settlement. But in cases where there are a lot of receipts and issues, the system creates one single virtual issue/ receipt closing transfer transaction - which settles all the issues against a virtual receipt in the system, together.

This, however, does not mean that the individual transactions are not settled. The transactions are settled individually, and a closing transaction is created as well. The closing transfer is just a system entry that knocks out because there is a positive line and an exact same negative line that is created.

Once the transactions are physically and financially settled, and the On-hand arrived at, the Inventory is Closed. This means that there can be no more transactions posted in the time frame up to which there is a closing processed for the Item.

While calculating Inventory Closing, the system calculates in the exact same way for all items that have a WAC method.

To adjust the cost in case of wrong cost updates or the like, it is important to understand the working formula of the WAC. The Setups also pay an important role in the consideration.

Happy Daxing :)