In the series “Dynamics AX modules we rarely implement”, imagine a dialogue between a financial controller (C) and a Dynamics pre-sales consultant (S):
- C: I would like to see the contribution of raw materials (direct costs) separately from the energy, machine depreciation and wages (indirect costs) in the cost price of my finished goods.
- S: Nothing is easier: use Dynamics AX’ standard costing for products. It allows for a “cost breakdown” by user-defined cost groups in the BOM calculation sheet.
- C: Fine. And how do my indirect costs propagate this calculation sheet?
- S: You enter them as a constant machine hour cost rate or as a surcharge in percent of the raw material costs.
- C: But my indirect costs vary by accounting periods, don’t they?
- S: Yes, they do. You should verify and update the hour cost rates regularly.
- C: How can I do that?
- S: Hmm… You can use Excel… or unleash the power of BI!
- C: Can you suggest any systemic and automated approach?
In fact, we can: in the German-speaking countries (the so-called DACH area, Deutschland- Austria- Confoederatio Helvetica) there is a long established school of the formal cost accounting. It introduces the cost center (CC) accounting where the actual monthly costs of ‘service’ cost centers (accounting, marketing) are iteratively allocated to the ‘manufacturing’ cost centers (machine shops or single machines). By dividing this cost by the number of hours the machine has been running the past month you get the revised cost rate for the next month. In the cost purpose (CP) accounting direct material costs are recorded against cost purposes (e.g. finished products, groups of products, customers or market segments), where indirect costs booked on cost centers are periodically allocated to cost purposes by a certain basis, which gives the full cost per product on month average.
This scheme was first implemented in Axapta 3.0 for Columbus IT Austria by a Byelorussian near-shore company CS&IE and then acquired by Microsoft. Later Microsoft recognized those “Cost center accounting” and “Cost purpose accounting” modules were a bit crude, and replaced them with the “Cost accounting” solution initially developed by the Swiss partner Circon Circle Consulting. The latter module has been in existence since Dynamics AX 4.0 through Dynamics AX 2012 R3. In the forthcoming version “AX7” this module may re-appear in a later release.
In this blog I am going to show how to leverage the standard capabilities of this "Cost accounting" module.
Setting the scene
In chemical manufacturing or metalworking, it is not always possible to record even the direct material costs against the finished product order, let alone the indirect ones. The product “individualization” begins in the late production stages where for example an addition of a perfume to the basis makes a distinct hair shampoo SKU, or where the steel is alloyed with vanadium and molybdenum to reach the steel grade ordered by the customer. The “semi-finished materials” i.e. intermediate products are manufactured continuously, and immediately transported further in pipes or railway cars.
The smallest “cost purpose” for such an intermediate product may be a daily order or a team shift batch. The batches should preferably have the constant chemical composition, and they are barely distinguishable. The factory does not record which intermediate product (molten iron) batches are consumed by which individual order (steel grade). Only the quantity of the intermediate product is tracked, i.e. metric tons of the iron used, or liters or cubic meters of a gas or a fluid inflow, etc.
To make the things worse, by-products are collected and re-processed and fed to the earlier stages in the production. For example, the accumulating scale – the oxidized crust left from forging - may be collected and fed back to the blast furnace, building a loop in the cost flow.
On a highly simplified example below I am going to show how to use the Cost accounting module in the first 2 stages of the steelmaking process (sintering and smelting) to absorb the cost of the raw materials in sintering (coke, limestone flux, iron ore) into the cost of the smelting product pig iron. For the simplification I do not take the fuel consumption in the sinter plant, or the highly energetic blast furnace gas into consideration.
The wages are allocated to the respective SP (sinter plant) and BF (blast furnace) cost centers. The intermediary product – the sinter - is produced continuously in daily batch orders, measured in metric tons and reported against the cost purpose “Sint”. The molten iron and the slag fraction are tapped and poured into torpedo cars at the blast furnace roughly every 6-8 hours, weighted in tons and reported against the cost purpose “Iron”. The by-product has the cost purpose “Slag”. Every tapping is represented by an individual batch production order in Dynamics AX.
The below table summarizes a very schematic material balance in ferrous sintering and smelting. The quantities have been reduced from the month to one day of production:
|Raw material||Product / by-product||Ref input, t||Ref output, t||CC||CP||Price, USD/t||Avg consumption per day, t||Avg yield per day, t||Total cost per day|
|65% Fe iron ore concentrate||1.3||Sint||55||7800||429000|
|Labor||80 workers in staff||SP||
2000 per person per mth
|60% Fe sinter||1.4||Sint||
8400 per day, aligned with BF
|60% Fe sinter||1.4||Iron||8400||477833|
8 workers in shift, 100 in staff
3000 per person per mth
~2000 every 8h tap-to-tap, i.e. 6000 per day
|Slag||0.3||Iron||15 (sales price)||1800||
Scenario in Dynamics AX
In the standard demo company USPI, create 6 released products measured in tons as shown below, note the production type necessary to activate the Process manufacturing capabilities:
Set the standard cost price as shown in the table above.
Create the new financial dimension CostPurpose, enter 3 values “Iron”, “Sint” and “Slag”, and add this dimension to the P&L accounting structure. Allow for blanks. Add 2 new cost centers: “BF”, “SP”.
Assign the dimension “Iron” to the product “Iron”, “Sint” to “Sinter” and “Slag” to “Slag”. Make sure that in the item groups the production issue offset and receipt offset posting types are bound to the new profit and loss accounts 500111 “Change in inventory - Raw materials” and 500101 “Change in inventory – Products”. The reason is that the Cost accounting module cannot acquire balance sheet GL transactions.
Procure sufficient stock (e.g. 10000 tons each) of the raw materials Coke, IronOre and Limestone. You may use the shortcut Manage inventory > On-hand inventory > Quantity adjustment.
For the products Sinter and Iron create 2 formulas (Engineer > Formula > Lines) using the above table with the reference input quantities. Beware the Formula size (1.4 tons for the sinter, 1 t for the pig iron).
For the pig iron add the by-product “Slag” (0,3 t per 1 t of iron) with the button Co-products. Set the Burden=None: the slug is not an additional cost but a product that can be sold profitably. Approve and activate the formulas.
The ERP orders in the first stages of steel production are not individually planned but entered after-the-fact with the recorded output quantity. In the production control module (Production control / All production orders) create a batch order for 8400 metric tons of Sinter. The Iron batch orders are likely to be entered on every blast furnace tapping, let’s say for 1500 t, 2500 t, and 2000 t:
The formula lines with raw materials are automatically provided with the product cost purposes “Sint” and “Iron”, respectively. In this scenario we do not absorb any planned labor into the production cost; inactivate any anticipated overheard in the costing sheet.
Produce 8400 t of Sinter with the Process > Report as finished button. Use picking list back-flashing to automatically consume the appropriate quantity of the raw materials.
Now produce 1500 + 2500 + 2000 t of Iron along with 450 + 750 + 600 t of the Slag by-product, use back-flushing to quickly record the consumption of Sinter and Coke.
Sell 1000 t of Slag at 15 USD per ton with a sales order to some customer.
Now simulate the monthly salary allocation with a general ledger journal: post 5333 and 10000 USD to the account 600250 "Manufacturing Salaries" and the respective cost center:
We have now collected data sufficient to start applying the Cost accounting module.
Cost accounting setup
Go to Cost accounting / Setup / Dimensions and make the CostCenter and CostPurpose dimensions available to the module. In Cost accounting parameters, choose Cost category identical with account number. Perform the periodic job Account reconciliation; this will synchronize all P&L accounts in the company with the cost accounting primary Cost categories:
You will later add the non-numeric “local cost categories” manually. On the General tab, Transaction control activate the dimensions CostCenter and CostPurpose for the cost categories 401100, 500101, 500111 and 600250. You are now ready to inject the GL transactions into the Cost accounting module: Periodic / Transactions / Process ledger transactions. The result can be verified in Inquires / Cost transactions:
There will be some costs without a cost center or cost purpose, because the data does not exist at the GL source. You may use Cost accounting / Setup / Dimension posting control to selectively filter out the blank CostCenter on 401100, 500101, 500111 and the blank CostPurpose on 600250.
We may now start viewing and aggregating the direct raw material costs in the module. The necessary setup is the Line structure (Cost accounting / Setup / Grouping / Line structures). Ideally, this line structure should resemble the P&L statement. Start with a basic structure with the cost category lines 1_RM, 2_WG, 4_CP, 5_SL and the totals 3_TC and 6_TS, the remaining nodes you may add later:
The effect of this aggregation can be observed in Inquires / Dimensions, Cost balances. Mark the Total cost and Quantity in Display setup and choose your Line structure. The result is an aggregation of the primary [material] costs posted against a financial dimension, here for the CostPurpose = Sinter:
The indirect costs – the wages – are not yet displayed. They wages are absorbed into the product cost by means of a CC-CP allocation:
CC “SP” --> CP "Sint"
CC “BF” --> CP "Iron"
This kind of an allocation is usually performed in Dynamics AX Cost accounting by a variable basis with the so-called service transactions. They are taken from the production route hour data and allow for the CC-CP allocation of the machine cost (CC) over the product cost (CP) if the same machine have been used to make different products. In our case the “SP” staff only works for the “Sint” production, whereas “BF” people only make “Iron”. I.e. the allocation can be constantly 100%.
The Dynamics AX Cost accounting module knows distributions and allocations in a Calculation version. The “distributions” can re-arrange costs from one CC to another or from one CP to another while preserving the Cost category at the inception. The "allocations" move costs from one dimension (CC) to another (CP), but they require separate Secondary cost categories. Now you have to create CC-CP and CC-CPwg secondary categories as shown on one of the screenshots above. Create one calculation version (Setup / Calculation / Calculation version) for the Actual cost, and add first 2 allocations (you will add the remaining ones later):
Choose the “debit” cost category Allocation account = CC-CP, and the “credit” Offset account = CC-CPwg. Mark Calculate fixed costs and the Offset transaction dimension = Cost purpose. The Calculation value type=Total costs. On the tab page Dimension, you can choose the CP dimension value to distribute the CC cost over: “Sint” and “Iron”, respectively. Enter the Reference quantity = 100. The leftmost symbol in the grid must become green if everything has been set up properly.
Now perform the Periodic / Period calculation. The Inquires / Calculation transactions should start appearing, 4 of them, and in the dimension balance for the “Sint” the wages of 5333 should be displayed.
In the next iteration the resulting full cost of the sinter is spread over the iron batches: CP Sint --> CP Iron. The raw material cost in Sinter may be processed with a distribution; here I may spread a whole Line structure node (1_RM) for CP=Sint over the Line structure node=1_RM of CP=Iron while preserving the original cost category 500111 inside:
Unfortunately, this trick is not going to work with the indirect wages allocated to the Sinter, because internally the “distributions” are executed before the “allocations”, i.e. there will be no allocated wages to distribute.
- Remove the CP=Iron (the red one in the table) from the formula line “Sinter” in the iron production batches before the back-flushing. The intermediate product cost is now “orphaned” and doesn’t show up under “Iron”. This would require a customization to automate.
- Book the intermediate product Issue to a separate GL account i.e. cost category and exclude it from the line structure. This is not always possible because the classification of the product as “intermediate” or “final” may be vague: the intermediate products may also be sold to a customer.
- Building on the fact that in a continuous production the intermediate products cannot accumulate, we may subtract the reported-as-finished “revenue” (line 4_CP) of Sinter from the direct costs of the Iron (line 1_RM): the whole 'change in inventory' of sinter will be spread over CP=Iron with an “allocation”.
Now you know that the full cost of your daily pig iron batches was 667833 USD, where the raw material contribution over the whole material chain was 652500 USD and the salary was 15333 USD.
Output(Stage X) * Output_for_A (Stage X+1) / Total_output_A_to_Z (Stage X+1).
To have an understanding of service transactions in the Cost accounting module, let’s book them manually as opposed to using the automatic function Process production transactions. Create a service cost category Output_t and record 2 service transaction against “Sint” and “Iron” with the daily production yield in a journal Cost accounting / Journals / Service transactions.
Yes, you can, but the route cost associated with the machine relies on the estimated cost rates and remains distinctive only in one production stage, while the Cost accounting module takes the full Profit and loss statement and breaks it down to the single product. Consider it a huge calculator to deduct the machine cost rate.