Leveraging Dynamics 365 for Oil Commodity Pricing and Costs

By - February 7, 2023

Many energy and manufacturing clients source and consume commodities to manufacture their end products – tracking average daily costs in Excel spreadsheets and running calculations to determine their final sales prices. These organizations require an ERP system that can flexibly track these costs to develop an accurate product sales price and reports for the period and fiscal year closes. To accomplish this, RSM expanded on pre-existing functionality within Dynamics 365 Finance and Supply Chain’s commodity pricing, closing, and adjustments features. As a result, companies can now track, store, calculate, and report on all commodity-based sourcing costs and sales prices for the period they need.

Dynamically pricing finished goods according to commodity costs:

As mentioned, oil and gas companies purchase large quantities of material from refineries to ensure that all three production facilities can meet business demand. As a result, these companies need to price their finished goods accordingly. This implementation leveraged the out-of-the-box commodity pricing functionality to allow companies to utilize their custom finished good price calculation. RSM has built on top of the native functionality to enable flexibility in pricing end products as required for reporting, the business, and the industry. These changes are summarized below:

Oil Industry Process Dynamics Process and Enhancement
End product costing depends on commodity costs and the organization’s pricing model. Enable multiple variables aside from commodity cost/margin/base price to calculate end product cost.
Costing can vary by each manufacturing site procuring and consuming the commodity, which requires each Site to be costed independently. Enable the ability to add item groups or customer groups to the Quantity and margin template with the specific site defined.
Groupings of customers may receive different pricing based on the pricing model they belong to Create multiple cost basis types and run the pricing calculation to reach the cost basis.


Efficiently edit transaction costs by leveraging the closing and adjustment form

Many oil, gas, and polymer companies have fluctuating supplier costs, which impacts the supply chain downstream. As a result, costs are consistently changing for production runs, purchase orders, and inventory adjustments, which requires clients to use Dynamics 365 to update these costs at the time of period close more easily. To update costs, a custom cost workbench was created to allow the system to read an Excel file upload (Figure 1) and automatically create entries more efficiently in the out-of-the-box closing and adjustment form.

An example use case is when oil procurement requires ancillary costs (freight, storage, inspection fees, etc.) to be allocated and reclassed to the correct main account. The updated amounts will be applied to purchase order receipts.

Figure 1: Menu item to upload the Excel sheet containing all adjustments needed to be made – Modification

There are three main benefits to this enhancement.

  • Multiple transaction adjustments on the workbench through one Excel upload.
  • Main accounts and general ledger journal names are entirely configurable and will be defaulted when making adjustments.
  • Detailed reporting of original costs vs adjusted ones. It can show production orders, purchase orders, and updated inventory adjustments by what amount and main accounts were impacted.

Due to the complex nature of sourcing and consuming commodities, oil, and gas companies require an ERP system capable of flexibly considering fluctuating costs, updating commodity-based end items sales prices, and adjusting multiple transactions seamlessly. Leveraging the functionality already applied with the out-of-the-box Dynamics 365 Finance and Supply Chain software, clients can move away from Excel-based calculations and reporting and begin utilizing the Dynamics 365 suite of features. For many clients, this allows for reporting enhancements with real-time reports using Power BI or more efficient data entry using Power Apps.

Please contact us here if you have any questions or visit our website to learn more.

Aidan Harper is a Senior Associate assisting organizations with implementing and integrating their business processes into Microsoft Dynamics 365 Finance and Operations, specifying in Industrials and Consumer Packaged Goods Industries. Aidan’s experience in Supply Chain Management and Financial Management allows him to assist in multiple areas of an implementation project.

Receive Posts by Email

Subscribe and receive notifications of new posts by email.