Microsoft Dynamics AX Commodity Pricing

Introduction

Many businesses have ingredients or components which are volatile and account for a large percentage of the cost. Microsoft Dynamics AX has a function where the sales prices can be indexed directly to the commodity cost.   This function is referred to as commodity pricing.  In this narrative, we will discuss real-life implementations of commodity pricing as well as a step by step example of how to perform the task.

Example

Commodity pricing refers to the ability to set the sales price for ‘commodity’ based end items, using the market replacement cost of the main ingredient commodity. This price will fluctuate on a periodic basis. An example would be in ‘Animal feed’ manufacturing where 90% (or more) of the cost of the end item is composed of one main ingredient, ‘Corn Meal’. Price of ‘Corn’ is determined by Commodity trading markets, typically on a weekly basis. Hence, such frequent fluctuation in the cost of the Commodity affects the price of the end items.  

Setup the Cost basis type

Microsoft Dynamics AX offers the ability to use several types of price analysis.   These functions can cover different types of commodities.   

1. Go to Inventory management > Setup > Commodity pricing > Cost basis type

2. Click New

3. In Cost basis type, enter a reference for the basis. In this example we used NYMEX.

4. In Description, enter a description for the basis. In this illustration, we used New York Mercantile exchange.

5. Close form.

Setup the Pricing template

The pricing template defines the items and the parameters on which sales prices will be simulated due to the commodity price change. You can define the template to mirror a trade agreement. Being familiar with trade agreements is an essential foundation in constructing the pricing template.  The template becomes the foundation for commodity pricing.

1. Go to Inventory management > Setup > Commodity pricing > Pricing template.

2. Click New.

3. In Pricing template, enter a reference for the template. We used the imaginative code of Commodity.

4. In Description, enter an explanation of the template.   We used the narrative Commodity pricing template.

5. Click the Quantity and margin template button. This is where we start to lay the foundation for our pricing model.

6. In Item code, define whether the pricing is for All items (All), an item group (Group) or a singular item (Table).

7. In Item relation, choose the corresponding record based on the Item code. If Table, it will be the item number.  If Group, it would be the item group.   An Item code of All will not need any selection in the Item relation.

8. If the item is a Master item, then the relevant dimensions (Configuration, Size, Color and Style) need to be entered to identify the variant.

9. In Account code, select the relevant detail for the customer. A value of All means all customers. Group in this field is for a specific customer group. Table means an individual customer.    

10. In Account Selection, identify the relevant record based on the Account code. If Table, it will be a specific customer. If Group, it will identify the customer group.   An Account code of All will mean no entry is needed in this field.

11. The Order quantity is the standard batch size of the product.

12. The Inventory unit is prefilled from the item.

13. The From quantity and To quantity are filled in when you are calculating the price for volume pricing.    In the example we have provided we are calculating the price for an order quantity between 1 and 200 kg.

14. The Cost multiplier adds the cost of the item to the price. For example, if you have a cost multiplier of 1 and a cost of $5.00. It adds that $5.00 to the cost to create a sales price of $10.00. So, if the cost multiplier was 1.5 and the cost was $5.00 then the sales price will be $5.00 plus $7.50 (1.5 times the cost). This scenario calculates a sales price of $12.50.  For other examples see table 1. This field can be used  with Margin amount and Margin percentage to cater for complex formulations.

15. The Margin amount is a fixed amount added to the cost for the new cost. For example, a margin amount of $12.00 for an item cost of $5.00 will result in a sales price of $17.00.

16. The Margin percent is the percentage of the item cost which is added to the item cost to define the sales price.   For example, a margin percentage of 20% for an item costing $5.00 will generate a selling price of $6.00.

17. Close form.       

Table 1 – Example prices for the price template

Setup the Inventory management commodity pricing parameters

Setup the defaults used within the commodity pricing function as well as detail needed for the trade agreement which will hold the newly calculated pricing.

1. Go to Inventory management > Setup > Inventory and Warehouse management parameters.

2. Navigate to the Commodity pricing pane.

3. In Cost basis type enter the Cost basis type we just created.

4. In Pricing template enter the pricing template just recently initiated.

5. Check the checkbox Keep BOM/Formula calculations.

6. In Price/discount agreement select the relevant journal name.

7. Keep the Base reference depth at 10.

8. In Dimension set, enter the relevant dimensions needed to post the price/discount journal.

9. Close form.

Executing a commodity pricing update

Once you have setup the commodity pricing, you need to then periodically run the pricing update.  The period can be weekly, monthly, quarterly or whenever an extraordinary commodity price change has occurred.  This is a four step process.

Figure 1 – Diagram showing Commodity price update steps

Setup pricing calculation

A pricing calculation is a unique calculation session.   Each session identifies the cost of the commodity and the dates for which the prices should be calculated.  Different costs can be given in different pricing calculations to ensure future prices can be simulated.  For example, price calculation PC-0001, may have a cost for PARTA of $10 to calculate a price for January.   Price calculation PC-0002, may have a cost for PARTA of $20 to calculate a price for February.

1. Go to Inventory management > Periodic > Commodity pricing > Pricing calculation.

2. Click New.

3. The Pricing calculation reference is generated by the related number sequence.

4. The Cost basis type is defaulted from the Inventory and warehouse parameters.

5. In Run effective date enter the from date of the prices calculated.   In this illustration it’s 01/08/2016.

6. In Run expiry date enter the to date of the prices calculated.   In this illustration its 31/08/2016.

7. Select the Commodity pricing button.

8. Click New.

9. Enter the Item number which is viewed as the commodity.   In this example we used CHEMICAL-B.

10. In New cost enter the anticipated cost of the commodity.    In this example we used $30.00.   If you want to use the current cost, check the Active cost price check box.

11. Enter the other commodity items you want to consider when simulating the price.   There are numerous tools such as a Select boxes and filter fields which aid in the selection of these items.

12. Close form.

Create price and margin data

Uses the Pricing calculation (defines the cost and effectivity dates) and the Pricing template (states the items and the margins used to calculate the selling price) to calculate the appropriate sales price for review.

1. Go to Inventory management > Periodic > Commodity pricing > Create price and margin data.

2. Select the Pricing calculation to be used for the data.

3. The Cost basis type, Site and Run effective date are defaulted from the pricing calculation.

4. Choose the Pricing template used to calculate the pricing.   Defaulted from the Pricing template identified in the Inventory and Warehouse parameters.

5. Click OK.

6. A dialog box confirms the completion of the calculation.

Price margin update

Review the prices calculated and once validated, converts to an unposted price agreement journal.  Numerous tools exist to help you verify the calculations directly from the form.

1. Go to Inventory management > Periodic > Commodity pricing > Price margin update.

2. Filter to the appropriate Pricing calculation.

3. Select Lines.

4. The lines should match the items identified in the Pricing template.

5. The New cost field is the cost of the finished product incorporating the new commodity pricing identified in the Pricing calculation.

6. The New price is the new sales price based on the New cost.   The new price takes into account the margins and cost multiplier identified in the Pricing template.

7. The New margin is the difference between the New price and the New cost.

8. To verify the pricing calculation, click the Calculation button.

9. Click the Complete button.

10. The display shows the formula, with levels, the operations and overhead calculation cost elements.  The Sales price per unit is the detail of this.  The cost entered for CHEMICAL-B on the pricing calculation was $30.00.   The activated cost is $20.00.   Using CHEMICAL-B at $30.00 a kilogram comes out to be (New Cost from the Pricing calculation * Consumption per lot size, $30.00 * 0.12 = $3.60).  

11. Verify the price calculation

Formula

New cost + (New cost * cost multiplier) + (Margin percentage of new cost) = New Price

5.4456 + (5.4456 * 1.05) + (20% of 5.4456) = 12.2526

12. Select all the items you want to transfer by checking the Upgrade trade agreement check box. 

13. Press the Update trade agreement button.

14. A dialog box will be displayed, stating that a trade agreement journal will be created and will need to be posted.   Click OK.

15. The update will be confirmed by a dialog box.

Posting the trade agreement

Use the standard Microsoft Dynamics AX function of price/discount trade agreement journals to make the price effective.  The price is not effective until the journal is posted.

1. Go to Sales and marketing > Journals > Price/discount trade agreement

2. Filter for the journal which holds the commodity pricing from the list of Open journals.   In our example, it’s the last journal created.

3. Choose Lines.

4. The trade agreement will match the customer and item relationship dictated by Pricing template.  The effective quantity breaks are also defined by the Pricing update.  The Amount in currency matches the New price calculated by the Price calculation update.   The effective date is defined by the Price calculation.   Once you are happy with the price, hit Post the make the price active.

5. A dialog box will appear.  Press OK.

6. A dialog box will confirm posting.

7. The price will now be active.

Summary

By utilizing the Microsoft Dynamics AX function for commodity pricing you can:

• Define how prices can be calculated for a trade agreement based on an anticipated margin.
• Define the price for any customer/item relationship available on trade agreements.
• Calculate the prices based on simulated costs of the commodity.
• Review and analyze the price calculated.
• Activate the cost via a trade agreement journal.

In conclusion, commodity pricing is a great tool for any company that deals with ingredients that are impacted by commodity markets and price fluctuations.