Weighted average vs. Date weighted average

We were just asked to explain the difference between the inventory costing models ‘Weighted average’ and ‘Date weighted average’. In case you’re puzzled, these are the Inventory model set on the Item model group. Inventory management > Setup > Inventory > Item model groups:


There are very detailed explanations on TechNet here and here, but in summary, the major difference is that Weighted average uses the average value of all inventory receipts in the period (plus any on-hand from the previous period), whereas Date weighted average uses the average value of all receipts up to the date of the issue (that is, Date weighted average doesn’t take into account the cost of receipts which take place after your issue).

Whenever I’m asked a question about inventory account I rely on a definition from Geoff McMaster. It’s a bit like Rudyard Kipling’s “I keep six honest serving men” – except that it doesn’t rhyme, and it goes like this: “In Dynamics AX inventory accounting, you specify the cost of receipts – and the system specifies the cost of issues”. Receipts are of course, purchase order invoices (purchase price + miscellaneous charges = landed cost); ‘ended’ production orders (remember when that update used to be ‘Cost’?); invoiced return sales orders (ever been caught out by a missing return cost?); and inventory journals. I guess there are a few more – but let’s move on. Issues are issued at average on-hand inventory cost, and then inventory close and adjust (or recalculation) adjusts the issue cost based on the Item model group’s Inventory model.

I’ve probably offended all of the Standard cost enthusiasts by ignoring Standard cost for so long – but of course standard costing is different. In the Standard cost inventory model all issues and receipt are posted at the current active standard cost, and the system writes a variance away if the actual cost price is different from the standard.

So let’s setup a very simple experiment to illustrate the difference between Weighted average and Date weighted average.

I’ve setup two more Inventory model groups and two new items TS-DWA and TS-WA. See if you can guess which item is using which inventory model. Then for simplicity I’m going to do three receipts of each item, using different costs each time. Again for simplicity I’m going to use an Inventory adjustment journal:


So just now, both of my items are identical. On hand and inventory transactions show:



And:



For both my items I’ve financially updated receipts (Posted) for a quantity of 30.00 and my posted cost is $400.00 so my unit cost price is $13.33

Incidentally when you’re looking at the Cost price on the On-hand form be aware of the inventory dimensions that are active for Financial inventory – and check that you’re using those active dimensions when you check costs.

Now let’s issue the items. Again I’ll use an adjustment journal. When I create the journal I see:


Because this is an issue, this time I can’t enter the cost price. When I created my items I ticked ‘Latest cost price’ on the Manage costs fast tab:


So the system’s updating the item’s cost price for each financially updated receipt, and therefore is now showing the cost price of my last receipt. So back to the journal, and post it:


Let’s take a look at the inventory transactions:


And:


The system has posted the issue at the average on-hand cost price, not the cost price specified on the journal line – and for now, both our item have been issued at the same cost.

So now we’re going to run inventory close for that month, and then we’re going to take another look at our transactions.

Weighted average item:


Firstly the system’s created a pair of Weighted average inventory closing transactions. One is an issue which represents all of the receipts for the period, and is used to calculate the average cost. The other transaction is a dummy summary receipt for the same transactions, and that transaction (and it’s average cost) is used to calculate the cost of all of the issues in the period.

Date weighted item:


The calculation’s slightly different for our Date weighted average item. This time the pair of Weighted average inventory closing transactions are written on the same day as the issue, and this time, because the system’s ignored the receipts after the date of the issue it’s calculating a different average cost, and the system’s adjusted the cost of my issue.

There’s another subtle difference too – the system’s updated the item cost price of my Weighted average item:


(My Date weighted average item’s still showing my last receipt cost).

So the TechNet articles refer to Weighted average as working per month (assuming that you run Inventory close at the end of each month). To test that I’ve done a receipt in one month, and an issue in the previous month and re-run inventory closing.

When I post the issue I see the item being issued at current on hand average value:


But after closing I see:


The system’s adjusted my issue’s cost back to the average from the previous month. There aren’t any issues in July, so as yet there isn’t any requirement for a pair of Weighted average inventory closing transactions for July.

This post is already too long, but before I’m done I just want to mention in passing the Johnny come lately Inventory model ‘Moving average’ With moving average, the product’s cost is determined by the purchase receipt. When the purchase invoice is posted, if there is a difference in cost between the purchase receipt and the purchase invoice, the difference is proportionally adjusted to the current products in stock, and any remaining amount is expensed. Moving average doesn’t support back-dating of transactions and costs in the same way as Weighted average and Date weighted average. More details, as ever, on Technet here

Advertisements

3 thoughts on “Weighted average vs. Date weighted average

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s