Date weighted average costing by site in Dynamics AX

Here’s a typical setup for a Storage dimension group – if you use the demo hyper-v image you’ll recognise this setup.

I’m tracking Physical inventory by Site, Warehouse, and Location. But I’m only tracking Financial inventory by Site:

I’ve setup my item’s model group inventory cost model as ‘Weighted average date’ and I’ve posted two receipts at different cost into two warehouses. I’ve used an inventory journal in this example because it’s easy to enter – but in the real world these could be purchase order receipts at different purchase prices or with different freight charges; or production orders with different yields, or transfers in from other sites and warehouses.

To save you straining your eyes at that screen shot, I’ve received 10 units into warehouse 13 at a cost of $10.00 each, total $100.00. And I’ve received 10 units into warehouse 12 at a cost of $12.00 each, total $120.00. So site 1 has received 20 units, total cost $220.00, unit cost $11.00.

The ‘Dimension display’ button on the on-hand form lets me select the degree of detail which the on-hand form shows, and by selecting ‘Site’ I see the total for the site:

And the on-hand form shows me correctly that I’ve received 20, at a total cost of $220.00, unit cost $11.00.

Back on the overview I can select ‘Site’ and ‘Warehouse’, and the detail tab seems to show me the correct values for my receipts – well it does show me the correct totals for now, but this will soon end in tears.

Let’s issue some inventory – again for simplicity I’ll issue it all out and of course all my issues from site 1 are made at the current average cost ($11.00).

Now let’s check those on-hand displays again.

For the site everything makes sense:

No inventory left, no Physical cost amount, no Financial cost amount.

Let’s check the display at the warehouse level:

The zero Financial cost amount at the site level is actually the sum of the $10.00 from warehouse 12, and the -$10.00 from warehouse 13!

So the moral is: you can only rely on the cost totals on the On-hand form when you view the form at the level that you are tracking Financial inventory.

Below the level at which you track Financial inventory the cost displays on this form aren’t going to give you meaningful information.

Production order trace ingredients in Dynamics AX

Once in a while we come across a production environment where one of the ingredients or components is being consumed at a very low rate. It may be a trace ingredient (like a vitamin) or it might be packaging material that you buy in bulk (e.g. rolls) but use in smaller units (each or metre).

There are a couple of considerations here (three actually).

Firstly, we can define the number of decimal places that we can enter for each unit of measure. There are units of measure like ‘each’ or ‘piece’ that will always be considered as a whole number, but of course there are other units like kilograms where we will want to reference partial quantities:

As shown above, you set the ‘Decimal precision’ on the unit.

Secondly, we have to remember that when we use an alternate unit of measure (like grams) on our production order bill of material – or purchase order line, or sales order line – the system’s going to record the underlying inventory transaction in the inventory unit of the item. So if we setup BOM line consumption in grams and the item’s inventory unit is Kg, then the inventory transactions will all be recorded in Kg’s.

Finally, although the underlying SQL database is recording quantities to [I think] 12 place of decimal precision, the inventory transaction and on-hand forms display only 2 decimal places by default – and this display is rounded.

So I’ve created a very simple production order – I’m using a batch production order and a formula, but this discussion applies equally to production orders and BOM’s.

To make 1 of my finished item TS-FORMULA-1, I need one each of my two components, and 1 gram of TS-TINY. And the inventory unit of TS-TINY is Kg:

So the inventory transaction of one of my components shows that I’m going to use 1.00:

But I’m not planning to use any of my trace ingredient at all:

You’ll be tempted at this point to say that the problem is that I’ve only defined 2 decimal places on my Kg unit – and of course 1g = 0.001Kg.

So why not just increase the decimal precision on Kg to 3?

This is what you get:

The inventory transaction looks like zero but it isn’t. it’s 0.001 rounded to 2 decimal places.

My rule here is you shouldn’t use more decimal precision on units than you display on your inventory transactions and on hand. You do not want any of these ‘not quite zero’ inventory transactions and on-hand quantities in your system.

If you don’t want to show lots of decimal places on the inventory transaction and on hand displays a simple way round this problem is to use the ‘Rounding-up’ option on the BOM/Formula line:

Although there are three options (four if you count No rounding up) I’ve only ever used ‘Consumption’. Hiding under the drop-list in the screenshot above is the Multiple that you want to round to:

So now I’ve specified that when the production order is estimated, the system’s going to round consumption up to the nearest 10g = 0.01 Kg’s in my example:

Much neater!

The example normally quoted for the rounding up parameter is something like a can of paint or a bottle of ingredient – which once opened can’t be re-used. In that case your rounding up multiple is 1.00, meaning that you’ll always consume a whole number of units into your production order say 5, even though you only need 4.25.

Purchase order approval in AX 2012

I’m not a great fan of purchase order approval. Actually it’s not purchase order approval that I have a problem with, it’s purchase order re-approval. Mostly I’m working with inventory purchase orders and master planning. So, first off there are a couple of exceptions to using purchase order approval for inventory purchasing: first, if you firm a planned order, the order’s automatically approved; and secondly you can’t use purchase order approval with sub-contract purchase orders. Then with inventory purchasing you’re going to want to update the purchase order – you need to record the vendor’s confirmed delivery date, and you might want to change requested delivery dates.

With purchase order approval workflows active you can’t just edit the purchase order to make changes – notice that the Edit button is inactive. You have to click ‘Request change’ and then make your change and then re-submit for approval

So let’s make a change to the delivery date and submit the purchase order for approval:

After the workflow messages have been process the Actions > View history button shows me that the purchase order is waiting for approval:

But the problem is that master planning is still seeing the original date (and warehouse and quantity) so we’re looking at changes that won’t have any major financial implications, but they’re important to master planning, and we want to apply those changes immediately, without having to wait for the boss to approve the purchase order.

The solution of course is to include an automatic approval setup in your workflow. I don’t really want to get into workflow setup – it deserves a whole series of posts all on its own – suffice to say that there are some useful ‘fields’ that you can use to setup conditions for automatic approval in your workflow. There’s an example in the standard demo data which uses the purchase order delta invoice amount percentage:

You can also use the purchase order delta invoice amount (rather than the percentage) and as you can see above there is also an option to test the purchase order ‘requires purchase order re-approval’.

The ‘requires purchase order re-approval’ is triggered by a policy. Go to Procurement and sourcing > Setup > Policies > Purchasing polices:

We need the policy that’s active for our company:

(If you’re working on policies, keep an eye on this setting – too easy to be updating a policy that doesn’t apply to the company you’re working in).

Then we need to find or create the ‘Re-approval rule for purchase orders’:

Access the details of the policy by double-clicking on the effective version of the Policy rule. Then you define the fields which, if changed, are going to trigger re-approval:

You can view the fields in a simple list or more complex tree view. For this simple example I’m setting this up so that I can change anything on the purchase order except price and quantity.

Now we’re ready to ‘Request change’ on our purchase order, edit the purchase order, and re-submit for approval. Once the workflow has run we can see that the purchase order’s approved and the workflow history shows that the automatic approval was triggered as required:

But if I change the price or quantity, the purchase order requires manual approval:

Job done.