Total Cost Allocation for Multiple Outputs
To allocate the total costs posted against a production order towards multiple outputs is a bit tricky in standard Microsoft Dynamics NAV, you more or less have to manually separate the different costs and post them against each of the production order lines (this since the cost calculations in Dynamics NAV is per production order line).
For material and capacity costs this involves dividing the quantities consumed and times spent between the production order lines and then post them individually against each of the lines. And for subcontracting costs it is more or less impossible (although nothing is impossible in Dynamics NAV, but this is close 🙂 ).
If you are in the business of taking things apart or you have processes that creates by-products, co-products or reclaimed material then you could consider modifying Dynamics NAV to do the allocation of the costs based on factors defined through a setup. This would eliminate the need for manually allocating the costs on each production order and increase the accuracy and consistency of the inventory costs. I believe you have this feature in both Dynamics AX and SAP, so why not in Dynamics NAV?
Here is a conceptual design describing how this can be accomplished in Dynamics NAV (which also would work well with functionality like the additional output functionality I have described in a previous blog post) and some scenarios that describes how it will work with different costing methods.
Conceptual Design of Cost Allocation for Multiple Outputs
The code in Dynamics NAV that summarizes the costs for a production order line and forwards it to the output is found in the cost adjustment functionality where it is done in two steps;
1. The different costs related to each production order line are calculated and inserted into the Inventory Adjmt. Entry (Order) table.
2. The costs in the Inventory Adjmt. Entry (Order) table are compared with the costs of the output item ledger entry(s). If a difference exists the output related item ledger entry(s) will be adjusted by creating value entries (the same was as any other cost for an item ledger entry is adjusted).
Knowing the above standard functionality, we can simply modify the code in the functionality for step 1 to consider all the costs posted against a production order and allocate it towards the different production order lines using defined allocation percentages.
Once step 1 above has been modified to allocate the costs, then step 2 will take care of adjusting the cost of the item ledger entry(s) without having to be modified.
An example of how it can be done; The production order line is extended with a Cost Allocation % field that will control how the cost is going to be allocated (most likely defaulted from the item card or from a setup table of some kind, there are as many variations of this as there are manufacturing companies). The total of the Cost Allocation % on a production order should always be 100 %.
Next you add the same Cost Allocation % field to the Adjmt. Entry (Order) table and write code to transfer the field from the production order line to the Adjmt. Entry (Order) table at the time the production order is change to finished.
Once the fields are in the database we can go and alter the cost adjustment function to allocate accordingly. This we do in the Calc. Inventory Adjmt. – Order codeunit (5896), where there is a function that handles the material costs (called CalcActualMaterialCosts) and another function that handles the capacity costs including subcontracting (called CalcActualCapacityCosts).
The code can for example be modified according to below. Where #1 eliminates the filter on the production order line (to get the total costs for the entire production order) and #2 adjusts the total costs according to the Cost Allocation % and the totals.
The same change is done for the capacity costs and the subcontracting costs (assuming we also want to include those costs in the allocation).
That should be it, short and sweet! 🙂
Example of Cost Allocation for FIFO Cost Item
Let’s test it; we create a production order with multiple outputs, in this case four outputs using FIFO costing method (the same example is also applicable to average cost items). The production order in this case simulates a plastic injection molding process where a plastic fork, spoon and knife are produced at the same time. The process also creates some plastic that goes back into inventory to be re-grinded (e.g. the spure, gate and runner, if you are into plastic injection molding). The allocation of the costs is setup to be 30 % towards the forks, 29 % towards the spoons, 31 % towards the knifes and 10 % towards the regrind plastic.
Then we post the output for the different products. In this case we use the output journal where we enter the output quantities, scrap quantities and the times. Note that we now can enter the total time against one of the production order lines and Dynamics NAV will later allocate the costs according to the percentages.
Next we post the consumption. Same here; we can enter the total quantity consumed against the first line and Dynamics NAV will later allocate the costs according to the percentages.
The above creates the following item ledger entries with expected costs for the outputs and actual costs for the consumption (this is all standard Dynamics NAV functionality).
And the following capacity ledger entries with the capacity costs (also standard Dynamics NAV).
The total cost of production is then 189 (26.5 + 5 for consumed inventory and 105 + 52.5 for the capacity).
We finish the production order and run the costs adjustment batch job. After doing this we can review the costs of the outputs which now are as below. The total cost of 189 has been allocated by the cost adjustment batch job according to the percentages (30 %, 29 %, 31 % and 10 % of the total 189).
It works! 🙂
Now, let’s simulate that the costs of one of the consumed components changes and see what happens (better test this, just to be sure). We do this by revaluating the inbound transaction related to one of the consumed items. We use the revaluation journal and revalue the raw material from 0.5 to 0.6 per LBS.
Then we run the cost adjustment batch job again and the cost of the consumed raw materials has now changed which also revalued the outputs accordingly. Nice!
Now we can control the total cost allocation on production orders, and the values are also posted into the general ledger the same way (as in standard Dynamics NAV which integrates the value entries with the general ledger).
Example of Cost Allocation for Standard Item
Let’s also test the same example but with standard cost items and see what happens (since standard cost for produced items are common, especially in the US).
We use a new set of items that has been setup with the costing method as standard and rolled-up with a production BOM and routing. The costs fields for the fork has been roll-up according to below.
Note that the cost allocation is only for the actual production costs posted against a production order, to do a cost roll-up for a standard cost item you also need to have a production BOM and routing for each of the items to have a correct standard cost rolled up.
We then create a new production order with the standard cost items and the same cost allocation percentages as before.
We post the consumption and output the same way as we posted them in the FIFO example, then we look at the item ledger entries. They obviously have the expected costs according to the standard costs.
We finish the production order and run the costs adjustment batch job. Now the expected costs has moved to the actual costs which are still the same as the standard costs.
But when making a drill-down into the value entries we can see that the variances has been adjusted according to the cost allocation percentages and the total actual cost. The material variance is 5.35 ((0.006 * 999) – (37.80 * 30 %)), the capacity variance is 15119.10 ((15.15 * 999) – (42.5 * 30 %)) and the capacity overhead variance is 30238.20 ((30.30 * 999) – (105 * 30 %)). This is the variances that will be posted into the P&L.
So, it also works as expected for standard cost items. Nice! 🙂
Defaulting Cost Allocation Percentages
If using that above approach then there are many ways of which the Cost Allocation % field on the production order lines can be defaulted. It can be according to the weight of the products (if they are all made from the same raw material for example), based on market material prices (if you are separating different metals for example), based on sales prices, etc… So, how to develop that part of the setup will be from case to case depending on the business requirements.
Other things to consider is if the allocation should only be for material and not for capacity or subcontracting costs (or the opposite). In some cases it might even make sense to have multiple cost allocation fields on the production order line to define a percentage per cost type (e.g. one for material costs, one for capacity costs, one for subcontracting costs, etc.). In other cases the costs of only some of the operations should be allocated against certain outputs (if for example a co-product gets created at the initial operation while other co-products are created at later operations), in those cases it might be slightly more complex to define, but the concept still applies.
A modification like this is something you need to be 100 % sure that it is working as expected and that all scenarios are covered. Modifying the cost adjustment routine and create something that is not working perfectly is the last thing you want to do.
Also note that if you use any kind of item tracking (lot or serial numbers) then you will not get the full trace-ability if you consume the complete quantity against only one of the production order lines. This since the trace-ability in Dynamics NAV is by production order line.