Weighted average inventory accounting in Dynamics AX

I’ve already written a couple of posts about Weighted average inventory costing, but because they say good things come in threes, here’s the third. I discussed the difference between weighted average and date weighted average here, and pointed out a common scenario which shows you a financial cost when you have zero stock on hand here, but I’ve just spent a couple of days investigating an issue where we had a negative financial cost with zero stock on hand, and it wasn’t the simplistic example I posted above (which was just looking at the on hand at the wrong level of detail by storage dimension).

This has lead me to formulate some do’s and don’ts for weighted average costing (and it’s also finally weaned me of my previous preference for date weighted over weighted average).

I’ve been working in AX 2009 – however, for safety I’d assume that these guidelines apply equally to AX 2012.

Don’t have financial dates before physical dates. Here we are principally talking about the posting date you apply to an update > end of a production order (or post a purchase order invoice). The production order report as finish (or purchase order product receipt) is the physical receipt. Do have the financial date on or after the physical date on all your inventory transactions.

Don’t close inventory more than one month late. For instance, close January in February – not in March (or later). Do update > end production orders promptly, and do close inventory within a few days of the actual month end.

Don’t ignore error messages if you have to cancel a close. Unfortunately, inventory closing almost always generates a long Infolog warning you about items where the costs can be fully settled. These will typically be purchased items where you have posted the purchase order product receipt, but haven’t posted the vendor invoice yet; or production orders where you’ve posted the report as finished but haven’t update > ended the production order. So this kind of conditions us to ignore the Infolog from a close. But you don’t normally get an Infolog when you cancel a close. While I was working through my inventory accounting issue I cancelled a close and saw an Error Infolog like this:

Cancel close 31 Dec (ICV000092)
IAD000853
Cancellation
Date closed: 31/12/2015, Voucher ICV000092
Item ****
Could not delete a weighted average inventory closing transaction on item **** on date 9/12/2015.
Could not delete a weighted average inventory closing transaction on item **** on date 9/12/2015.
Item ********
Could not delete a weighted average inventory closing transaction on item ******** on date 10/12/2015.
Could not delete a weighted average inventory closing transaction on item ******** on date 10/12/2015.

OK. I confess. I didn’t notice this the first time I cancelled the close (it was actually on the third time through that I finally took notice of this error).

And finally: Do check the settlements and ledger vouchers posted by inventory closing and revaluation. The simplest way to check the settlements is to go to Inventory management > Periodic > Closing and adjustment > Settlements, and sort by the Adjustment amount (by clicking on the ‘Adjustment’ column heading a couple of times):


Check the ledger voucher by clicking Ledger > Voucher. (It might help to export the voucher to Excel and setup a simple pivot table by Ledger account and Amount if you have a lot of postings in your voucher because of financial dimensions).

Don’t ignore large adjustments. Obviously I have to leave what is ‘large’ up to your judgement, but I’d certainly be asking for help if I saw any numbers that I couldn’t pronounce, like this:


To sum up – if you want to use weighted average costing, keep your postings disciplined; and close inventory every month; and stay vigilant.

If you aren’t prepared to do that, use FIFO (or FIFO + Fixed receipt).


Advertisements

Resource (Work Centre) Efficiency percentage in Dynamics AX

There’s a field on the Resource (Work centre) in Dynamics AX called the ‘Efficiency percentage’.


I must admit I’ve never paid it too much mind. It’s a useful little setting because it’s easy to adjust (because it’s held on the resource and not on the route operation).

Usually in a workshop I just make a throw-away comment like “Set this to say 95% so that you get an achievable schedule”. But this time someone was awake in the workshop and we started to discuss this concept in detail.

Here’s how it works. Let say that my machine in the example above has a plate on the side that says “this machine makes 15 widgets per hour” – and let’s say, for the sake of argument that it runs 24 hours per day. Now my machine only makes 15 widgets per hour when it is running – but I can expect the odd problem, so I’ll set my old friend Efficiency percentage to 95%. Therefore, my setup looks a like this:


Now 15 x 24 = 360. Let’s create and schedule a production order for 360:

My production order is scheduled to start at midnight and finish at 1:15am the following day:


And the capacity reservations are:


So because of my 95% Efficiency percentage I’m only getting 22.80 hours of useful work in my 24-hour day. Another way of looking at this is although my machine is theoretically capable of producing 360 widgets in a 24-hour day, actually in a normal working 24-hour period I’m only expecting to produce 342 (360 x 95%). I’ll just check my maths by changing the production order and re-scheduling it:


Now that’s all perfectly sensible, but the debate was started by the question: “So what’s the standard cost of 1 widget?”. Our machine is powered by huge amounts of unobtainium, and costs $150.00 per hour to run. Is the standard cost of 1 widget $10.00 or $10.53 (24 x 150 / 342)?

I’m not sure I know the answer – it’s a bit of a value judgement. Interestingly, if you look at the way the production order cost is calculated (I’ve estimated my production order) you can see that the system only costs the calculated working time of the machine, and not the total elapsed time that is scheduled:


So if you may want to increase the hourly rate of a machine with an Efficiency percentage of less than 100% according to the additional [non-working] time that’s scheduled onto a production order. Unfortunately, if you do that you lose the advantage of the Efficiency percentage setting percentage which is that it can be adjusted simply and quickly – because if you change it, you’ll have to go and adjust your machine rates.

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.