AX 2012 | moving average costing method - Part 5

This is the last part of the  moving average costing method in Dynamics AX 2012. in this part we will discuss the reports which show how moving average was calculated.

If you sort transactions on the inventory value report according to transaction time, transactions are listed chronologically, and you can view the costs from a moving average perspective. This way, you can verify that the cost of a moving average product has not been changed retroactively, even though the product has, for example, been adjusted by using backdating.

 
In the following  2 examples, you’ll see how the cost of a product changes with the flow of business events. The two examples that follow are based on the same business events, but they are sorted differently.

• The first example  is sorted by transaction time. With this sorting order, the business events are aligned with the moving average perspective, where transactions are listed in chronological order.

• The second example is sorted by posting date. This sorting order shows the financial impact of the business events; however, for a product that is calculated by moving average, the calculation of the average unit cost is not reflected the way you would assume for a moving average product.

To print the inventory value report according to transaction time do the following:

1- Go to Inventory and warehouse management > Setup > Inventory > Inventory value reports to create an inventory value report setup.

2-  In the Inventory value reports form, click New, enter an ID and a name for the report, and then, in the Range field, select Transaction time. The transaction time is the actual date that the transaction is reported and the moving average cost for the product is updated.

3- Click Inventory and warehouse management > Reports > Status > Inventory value > Inventory value.

4- In the ID field, select the ID of the report that you created, Under Date interval, enter your date interval information, In the From date and To datefields, enter a date interval, and then click OK to run the report.

5- please note that there is two transaction was posted in 07/01/2015. but they appear in the report according to the transaction time (08/30/2015)

To print the inventory value report according to posted date do the following:

6- Go to Inventory and warehouse management > Setup > Inventory > Inventory value reports to create an inventory value report setup.

7- Select the ID which you created in step 2 then change the range field toposting date. save and close.

8-   Repeat step 4 and 5 to print the report.

9- please note that transactions are not listed chronologically, so the two transaction was posted in 07/01/2015 is listed on 08/01/2015 as a transfer from the previous month.

AX 2012 | moving average costing method - Part 4

In this part we will discuss production costing with moving average. If you use moving average in production costing, costs can be based on the current costs of raw materials rather than on the cost price that was originally registered for the item master. When the finished good is reported as finished, the cost reflects the estimated product cost calculation that is executed by a release of production orders.

The original cost calculation of the finished good might have happened months ago, and thus it might be outdated. With moving average, the result of a product cost calculation that is executed during the estimation process can be adopted rather than the cost from the original cost calculation of the finished good.



Example: Use current costs in a BOM calculation

In this example, you have a finished product, D0007 (Speaker Pro Kit), and a two raw material products, M0023 (Speaker Cable Banana Plugs 24K ), M0024 (Speaker Cable In-wall 50 Ft) Furthermore, the following details apply:

• the three products are calculated by moving average.
• The D0007 consumes 1 piece of M0023  raw material and 1 piece of M0024  raw material.
• A bill of materials (BOM) calculation has been activated with a unit cost of 10.00 for each piece of raw material.

You create a production order for product D0007 , and you buy 1 new piece of each raw material M0023 ,M0024  for the production order at a price of 20.00 for each item item. which is higher than the cost that is activated in the product (5.25 for M0023 and 19.07 for M0024). During the estimation process of the production order, the cost price of 20.00 is applied, and this is the cost price that is included when you report the product as finished.

To see this example in action please follow the steps below: 

1- First make sure the  three products are calculated by moving average.

2- Check the row material cost for item M0023 in the released item form

3- Check the row material cost for item M0024 in the released item form

4- Make sure that the three products don't have quantity on hand.  

5- now go to  Inventory and warehouse management > Journals > Inventory adjustment, and then create a new line.

6- Select product M0023, M0024  ,select the site, warehouse then  enter 1in the Quantity field and 20.00 in the Cost price field,for each item then post the journal.

7- Go to Production control > Production orders > All production orders. and then create a production order for product D0007.( make sure to select the warehouse. i forgot to do it before the screen shoot)

8- From the All production orders form, start a production of D0007, click Estimate to run an estimation of the BOM, and then click OK.

9- In the Start form, on the General tab, select Always in both the Automatic route consumption and the Automatic BOM consumption fields, and then click OK.

10- In the All production orders form select the production order then click Report as finished, and then click OK.

11- On the View tab, Click the calculate price notice that the adjusted cost of 20.00 per unit has been applied for consumption of each of the two pieces of raw material.

AX 2012 | moving average costing method - Part 3

After we discussed the Handling of price differences between a product receipt and the invoice in part 1 and Revaluation for moving average in part 2. in this post we will discuss the Backdating with moving average.

When you backdate a product receipt or an invoice, it is revalued to the current moving average cost. Also, a backdated issue is posted at the current moving average cost. With backdating, you cannot change the moving average going forward.


Example: Backdate a transaction

In this example, you want to backdate a transaction by adding a quantity of 1, because of a receipt that happened before the current date. The current moving average cost is 15.00, and you set the price of the backdated transaction to 20.00. When you backdate the receipt, the following cost calculations occur:

• Your inventory is updated by the quantity of 1 as of the date of the backdate, but the moving average cost remains 15.00.
•  The additional 5.00 out of the 20.00 is expensed as of the date of the backdate.

to see the previous example in action please follow the steps:

1- Go to account parables >  Common  > purchase orders > create new purchase order with backdate

2- In the purchase order lines enter item no D0007 with quantity of 1, price 20. then confirm > go to product receipt > enter backdate in the product receipt date field to then post (make sure to select the same site warehouse that you used in part 1 and part 2) .

3- to see the voucher go to receive  > product receipt > voucher.

4- as you can see The additional 5.00 out of the 20.00 is posted to the Price difference for moving average account.

AX 2012 | moving average costing method - Part 2

In part 1 we discussed how to set up moving average costing method, then we discussed  how moving average is handling of price differences between a product receipt and the vendor invoice.

Today we are going to learn how to revaluate cost for moving average. To proceed with the next example you must read part 1 of this series or at least do the setup which described there.

If your moving average value is inaccurate because of, for example, a posting mistake, you can adjust the unit price of the on-hand inventory at any time.

The moving average cost can only be adjusted as of the current date, so when you adjust the unit price of an item by using the following procedure, the adjustment date is always set to the current date. You cannot change the date when you post the adjustment.



When you adjust the moving average cost, the adjustment is posted to the Cost revaluation for moving average account. 

To adjust on-hand inventory value with moving average follow the steps below:

1- Go to Inventory and warehouse management > Periodic > Closing and adjustment.

2- Click Adjustment, and then click Revaluation for moving average.

3- Click Select to select the item D0007 (we used this item in part 1) to adjust,select the site check box and then click OK. The query only applies to the Product and Site financial inventory dimensions.

4- Enter 15 in the Unit cost field, the new unit price of the inventory item, and then click Post.

5- To see the accounting entries, close the Revaluation for moving average form, go back to Closing and adjustment form, select the adjustment transaction click ledger then select voucher.

6- as you can see the item moving average cost was updated by debiting the inventory and crediting the revaluation for moving average account with the difference between the the old cost (12) and the new cost (15).

7. please note that the revaluation for moving average account will be debited and the inventory account will be credited. for example repeat the steps from 1 to 4 to reduce the cost to 10. then check the voucher.

AX 2012 | moving average costing method - Part 1

In this series we are going to discuss the moving average costing method, by covering the following topics:

Part 1: Handling of price differences between a product receipt and the invoice.
Part 2: Revaluation for moving average.
Part 3: Backdating and moving average.
part 4: Production costing and moving average.
part 5: View how moving average was calculated.

first let's introduce the moving average costing method. Moving average is an inventory evaluation method that reevaluates inventory value in the chronological order of posting transactions, and it can be used to compute the average cost of the current ending inventory.

When a receipt transactions are included chronologically in the moving average calculation, according to the order in which the transactions are recorded in the system.

When Issue transactions are assigned the current active moving average cost at the time of recording, and the costs on inventory issues do not change even if the purchase costs (price in vendor Invoice ) change.


In the following example we are going to illustrate how we can setup the moving average prerequisites and then how we can Handling of price differences between a product receipt and the Vendor invoice.

Set up prerequisites for moving average as follow:

1- Set up an item model group

1.1- Go to Inventory and warehouse management > Setup > Inventory > Item model groups.

1.2-  Click New to create a new item model group for moving average.

1.3-  On the Setup FastTab, these check boxes are selected by default:

• Stocked product
• Post physical inventory
• Post financial inventory

1.4- On the Inventory model FastTab, in the Inventory model field, selectMoving average. The Include physical value check box is not available. However, when you apply moving average, physical transactions are always included in a cost calculation.

2- Posting types

Proportionally expensed amounts and adjustments must be posted to General ledger. To distinguish the proportionally expensed amounts from other transactions in General ledger, two unique posting types have been introduced:

• Price difference for moving average
• Cost revaluation for moving average

2.1- Go to Inventory and warehouse management > Setup > Posting > Posting.

2.2- On the Inventory tab, click Price difference for moving average, and then click Add. In the Main account field, select the main account to use.

2.3- Click Cost revaluation for moving average, and then repeat step 2.2 for this account type.

3- Set up Production control parameters

3.1 Go to Production control > Setup > Production control parameters.

3.2 On the General tab, under Report as finished, select the Use estimated cost price check box. When this check box is selected, the estimated cost price is used rather than the cost that is registered on the product master.

3.3 Under Posting, select Post picking list in ledger and Post report as finished in ledger.

3.4. Under Estimation, select Price calculation.

4- Set up Product item model group

Since I am using AX 2012 R3 Demo data, USMF legal entity and item No. D0007 i need to do the following:

4.1- Product information management > Common > Released products > edit item D0007 and make sure to assign the moving average item model group to the item.

Scenario : Handling of price differences between a product receipt and the invoice

If some of the items on a purchase order have been received then sold, and there is a difference between the estimated cost of the purchase (Cost when the product receipt was posted)  and the vendor invoice, the full difference between the invoiced amount and the receipt cannot be recognized as inventory cost. In this case, you need to expense the difference proportionally.

Example: Proportionally expense a difference in price

In this example, you post an invoice to a receipt, and there is a difference in price between the estimated price and the price that is actually charged for the product.

After some of the units of the purchase are sold and thus taken out of inventory again, the value of the inventory cannot be based on the full quantity of the purchase.

In this case, the difference between the estimated price and the actual price of the items that were taken out of inventory must be expended.


To perform the example follow the steps: 

1- Go to Account payables > Common > Purchase orders > all purchase orders. then create new order to purchase five pieces of an item No D0007and enter an estimated price of 10.00 per unit. then confirm the Purchase order.

2. To receive the items go to receive tab then process the product receipt 

3  to sell two of the item Go to Account receivables > Common > sales order > all sales order Now create a sales order for two of the item D0007. So two items are taken out of inventory again at the cost of 10.00 each. confirm the sales order then process the packing slip then post the invoice.

4. when you receive the invoice for the purchase order and discover that you are actually charged 12.00 per unit for the item D0007 , not 10.00 as was originally estimated.

5. to create the invoice go back to the purchase order> invoice tab > invoice, change the cost per unit from 10.00 to 12.00, and then post the invoice.

6. The additional cost of 2.00 per unit cannot be related to the sales order, because the sales order is already costed when you receive the invoice for the purchased items. Therefore, the system performs a proportional calculation, and the expense of 4.00 ([12.00 – 10.00] × 2 units) is expensed on the Price difference for moving average account.

7- To view amounts that are posted on the Price difference for moving average account based on a proportional calculation go to the Invoice tab in the purchase order, click Invoice

8-  In the Invoice journal form, click Voucher.

9. In the Voucher transactions form, in the Amount field, view the amount that is posted to the Price difference for moving average account.

10- please note the following:

• The remaining stock quantity ( 3 pcs ) cost will be updated to 12 when the product receipt voucher reversed and the vendor balance voucher registered.

• The Price difference for moving average account could be debited or credited depending on the difference between the receipt cost and the invoice cost.