Inner-workings of Dynamics AX: 2015

Different inventory costing/valuation methods in AX especially how sales cost is calculated and adjusted after an inventory close in different methods.

Inventory Model Groups

(Inventory management> Setup> Inventory> Inventory model groups)

Inventory model groups include settings for the valuation method and for item handling. They are important for inventory valuation and ledger integration.

Selecting an inventory model (FIFO, LIFO, average or standard cost) on the tab Inventory model, you specify the inventory valuation method, which is the way Dynamics AX links issue transactions to receipt transactions in terms of valuation.

If you change the Inventory model or Ledger integration settings on the tab Setup of an inventory model group after posting item transactions, reconciliation of inventory and finance may become very difficult.

Inventory Valuation

The basis of inventory valuation is a simple principle:

Receipt costs - cost amounts are provided by the receipt transaction

Issue costs - cost amounts are calculated according to the valuation model

The cost amount of the issue transaction derives from the receipt transactions, which refer to the issue according to the valuation model (FIFO, LIFO, average). It is not possible to enter the cost price and cost amount in an issue transaction, therefore.

Exceptions to this rule are moving average valuation (for issue transactions keeping the average cost price shown at the time of posting) and the standard cost price valuation, for which Dynamics AX provides two different options:

– Fixed receipt price (This is a checkbox on the Item model group form)

– Standard cost (This is seen in the Inventory model dropdown)

The option “Fixed receipt price” is available/enabled for valuation methods FIFO, LIFO and weighted average cost. Applying this option fixes the receipt cost price in advance, preventing to change it when recording a transaction.

“Standard cost” provides true standard costs, applying the standard cost price of an item for all issue and receipt transaction. The difference between the methods “Standard cost” and “Fixed receipt price” shows, when changing the standard cost price of an item. Whereas the standard cost method immediately posts an adjustment of inventory value, the fixed receipt price method does not directly post a difference. In this method, the new price only applies to new receipts – existing inventory will issue to the old price until it is consumed completely. As a prerequisite for applying the standard cost method, the multisite functionality needs to be active in the company account concerned.

Valuation Method

Dynamics AX provides following valuation methods – available in the field Inventory model of the inventory model group – to calculate the cost amount of issue transactions:

– FIFO

– LIFO

– LIFO date

– Weighted average

– Weighted average date

– Standard cost

– Moving average (I have used this for AX Retail)

Valuation of Item receipts

Receipt transactions get their financial value when you post the related financial transaction (invoice). Except for the standard cost method, the different transaction types therefore provide the receipt cost price and amount as follows:

– Purchase order receipts

Amount of the invoice line

– Sales item return

Original value of the returned item; return cost price entered in the return/sales order line, if not assigned to an original sales order

– Other receipts

Cost amount entered in the journal line

Valuation of Item issues

The cost price and amount of item issues always complies with the average cost price when posting the transaction. The valuation method applies when closing the inventory determining the assignment of item receipts to item issues according to the inventory model (FIFO, LIFO or average).

Inventory closing calculates the cost price and amount of an issue transaction based on assigned receipts. The issue price and amount therefore is not final until you have posted the financial transaction (invoice) of all assigned receipts and inventory closing is finished.

As an exception, individual assignment of issue transactions to receipt transactions does not apply to following valuation methods:

- Standard cost price – This method (standard cost or fixed price) immediately applies to issue and receipt transactions.

- Moving average – Keeps the posted cost price of issue transactions and does not require inventory closing.

Standard cost price

For items of the inventory model “Standard cost”, inventory closing is not required because all receipts and issues immediately post the standard cost price available in the item price form.

When activating a new standard cost price, Dynamics AX immediately applies an adjustment of inventory value for the current stock, which posts in inventory and in the general ledger. The new standard cost price therefore may apply to issues of that stock right away.

Fixed receipt price

The checkbox “Fixed receipt price” in the inventory model group applies in combination with the valuation methods FIFO, LIFO or average cost. When selecting this option, the cost price entered in the released products form or the item price form specifies a fixed cost price for receipt transactions.

The cost price and cost amount calculates according to the valuation method and therefore always complies with the standard cost price of the item, as long as you do not change the item cost price.

When you change the item cost price, consumption of existing stock in inventory will comply with the old cost price calculating the cost amount of issue transactions according to the valuation method. Therefore, inventory closing is also required for the option “Fixed receipt price”.

Moving average price

For items of the inventory model “Moving average”, receipt transactions are posted with the price provided by the transaction (vendor invoice). When posting an issue transaction, the average cost price at the time of posting applies. This cost price does not change through inventory closing.

If a vendor invoice refers to a purchase receipt, which is not completely on stock any more, for the quantity not on stock, a difference between the physical cost amount and the financial cost amount of the purchase transaction is posted as an adjustment to a price difference account. For the quantity still on stock, posting the purchase invoice posts the total amount – including possible differences – to the financial cost amount.

Following table shows an overview of the different valuation methods available in Dynamics AX:

Inventory models controlling the valuation methods in Dynamics AX

· FIFO (First In First Out) - Item issues refer to the oldest item receipt still on stock

· LIFO (Last In First Out) - Item issues refer to the newest item receipt on stock that is available when closing inventory

· LIFO date - Like LIFO, limiting the assignment of issues to receipts before the particular issue

· Standard cost - The cost price of item issues and receipts is equal to the active standard cost price of the item

· Weighted average - The cost price of item issues in a period is the average cost price of all receipts (including the beginning balance) in this period (inventory closing period)

· Weighted average date – The cost price of item issues is the average cost price calculated separately for each day.

· Moving average - The cost price of item issues is the average cost price of the inventory quantity at the time of posting the issue.

Below example shows cost price calculation for the different valuation methods. Three receipt transactions with different cost prices and an issue transaction in between.

Posted transactions for comparing valuation methods

Date Transaction Quantity Cost amount

July 1 Receipt 10 100

July 2 Receipt 10 200

July 3 Issue 10 (to be calculated)

July 4 Receipt 10 300

After inventory closing, the cost amount of the issue shows following figure depending on the valuation method:

Valuation of the item issue

Model Amount Explanation

FIFO 100 From receipt on July 1

LIFO 300 From receipt on July 4

LIFO date 200 From receipt on July 2

Weighted average 200 Average of all receipts

Weighted average date 150 From receipt on July 1 and July 2

Moving average 150 Current average when posting the issue

Inventory Closing and Adjustment

When you post an issue transaction, Dynamics AX always applies the average cost price (except for the standard cost model). In order to calculate the correct cost price and amount according to the valuation method (Inventory model) of the item, you need to close inventory. Only items assigned to inventory model “Standard cost”, or “Moving average” do not apply inventory closing.

You need to close inventory periodically – usually as part of the month closing procedure in finance – in order to show correct item costs in finance and to close inventory transactions. After closing inventory, it is not possible to post inventory transactions in the closed period any more.

Need for inventory closing

The main task of an inventory closing is to calculate the actual cost price of an inventory issue. At the time of inventory issue posting, system does not calculate an exact issue cost price according to the inventory model. Instead, system uses some estimated value for the issue cost price, which looks like a good approximation for the actual cost price. Then, during the inventory closing, the actual issue cost price is calculated with respect to inventory model settings. After that, system calculates the difference between the initial (estimated) issue cost price and the true issue cost price, then system writes this down into inventory transactions and inventory adjustments. Then GL postings for these adjustments are created. Why this approach has been chosen? In general, during posting of an initial inventory issue, system might not have all information required to calculate the true cost price. For example, if negative inventory is turned on, we can post issues before corresponding receipts; In the Weighted Average model, it is impossible to calculate an issue cost price before all the receipts for a period has been posted; For FIFO/LIFO models, the receipts might be posted out of chronological order and so on.

Changing an inventory model

As long as there are no open transactions for an item, we can change its model group. Changing model group to Standard cost however, is not straightforward and requires a conversion tool to be run.

Inventory recalculation

Recalculates the inventory as of a given date without closing the inventory. This process matches inventory receipts and issues for the transactions that are not financially matched through inventory dimensions in order to determine the real value of the on-hand inventory. Unlike inventory close, it does not settle issues or close transactions. Inventory recalculation can be performed on a subset of inventory items whereas Inventory close must be performed on all inventory items. It is not as accurate as an inventory close and should not be relied on to replace inventory close.

This process is batchable, with options to select items. There is no from date option only an upto date and option to select period. But as the volume of data grows it’s going to get slow.

Past inventory report

If you want to know the physical inventory on a date in the past, you may select a report in the menu path Inventory management> Reports> Status> Physical inventory.

Examples
In this case, purchase and sales data is imported via external files and processed date wise. Effects on Financial cost amounts, on hand inventory and cost price is noted.

1. Initial purchase file imported;

a. 19/12/2014 Purchase for a product; Qty = 7; total purchase $ = 17.50 ($2.50 each)

b. On hand inventory updated = 7; Financial cost amount = $17.50; Cost Price = 2.50

2. Next purchase file imported:

a. 29/12/2014 Purchase for a product; Qty = 5; total purchase $ = 11.50 ($2.30 each)

b. On hand inventory updated = 12; Financial cost amount = $29; Cost Price = 2.42

3. Sales File imported with sale qty of 1 both for 20/12/2014 and 30/12/2014.

a. On hand inventory updated = 10; Financial cost amount & Cost Price in different cases as below

Moving average

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.42	2.42	26.58	11	2.42
Sale	1	30/01	2.42	2.42	24.16	10	2.42

FIFO

Before recalculation

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.42	2.42	26.58	11	2.42
Sale	1	30/01	2.42	2.42	24.16	10	2.42

After recalculation

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.50	2.50	26.50	11	2.40
Sale	1	30/01	2.50	2.50	24.00	10	2.40

Weighted average by date

Before recalculation

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.42	2.42	26.58	11	2.42
Sale	1	30/01	2.42	2.42	24.16	10	2.42

After recalculation

Sale/Purch	Qty	Date	Each cost	Total cost	NewFinancial Cost Amt	Resulting stock	Cost Price
Purchase	7	19/01	2.50	17.50	17.50	7	2.50
Purchase	5	29/01	2.30	11.50	29	12	2.42
Sale	1	20/01	2.50	2.50	26.50	11	2.41
Sale	1	30/01	2.42	2.42	24.08	10	2.41

Inner-workings of Dynamics AX

Wednesday, July 15, 2015

AX crash analysis

Monday, May 25, 2015

Inventory valuation methods