Recently I’ve been looking into how the “Unit Cost” is calculated for items using the “FIFO” costing method. I’ve found even if the item is set to “FIFO” the Unit Cost still displays an “Average Cost”. What’s more, depending on your “Inventory Setup”, this “Average Cost” might not be an as it seems either.
The answer lies in the role of the “Automatic Cost Adjustment” setting in “Inventory Setup”. (and therefore the “Adjust Costs – Item Entries” batch job)
In this post I’ll walk through a series of postings and check back on the “Unit Cost” while also checking the “Value Entries” to see what costs are being posted to the GL.
Here goes 🙂
Unit Cost when “Automatic Cost Adjustment” set to ALWAYS
In the first test I set the option “Automatic Cost Adjustment” to ALWAYS in my Inventory Setup as per below:
I then created a brand new item with a costing method of FIFO and gave it an initial “Unit Cost” of £10.00
With this configuration I then received and invoiced the following quantities and values:
A Purchase Order with a Quantity of 5 and a unit cost of £20.00
This changes my original “Unit Cost” to £20.00.
A Purchase Order with a Quantity of 5 with a unit cost of £50.00
Now when I check the Unit Cost on the Item page its showing as £35.00.
So why is this, and what cost will be posted when I sell the item?
If you drill back on the “Unit Cost” the answer becomes apparent via the “Average Cost Calc. Overview” page:
This is showing that we have a quantity of 10 in inventory at a total value of £350.00 giving an average cost, and therefore a Unit Cost, of £35.00.
Selling an item
Things get really interesting when I now sell an item.
According to FIFO rules, when we sell an item, the system should post the actual cost from the first receipt (i.e. first in, first out). This cost isn’t £35.00, as is shown in the Unit Cost, it should be £20.00.
To see what happens I’ll ship and invoice a Sales Order for a Quantity of 1 as per below. (as you can see the Unit Cost displayed is £35.00)
Firstly, let’s check the “Unit Cost” on the item.
After posting the sales order if I now check the “Unit Cost” on the item you can see this has *automatically* (more on this later) been recalculated as per below:
Its now showing £36.66667 because we have a total of 9 in inventory at a total cost of £330.00 giving us an “Average Cost” of £36.66667 i.e. 330/9=36.66667.
So, what cost was posted to the GL for the Sales Order? Did the system post £35.00 or £20.00?
For this we’ll dig into the “Value Entries” on the item. As we can see the system posted an initial cost amount of -£35.00, as per the “Unit Cost” and then *automatically* posted an adjustment entry of £15.00 giving the correct FIFO cost of £20.00
The key takeaway here is the reason we get the additional “adjustment” posting of £15.00 *automatically* is because we have the “Automatic Cost Adjustment” setting switched to ALWAYS in the Inventory Setup.
As I understand it having this switched to ALWAYS runs the “Adjust Costs – Item Entries” batch job every time you post a transaction therefore posting the correct FIFO cost *automatically*.
Unit Cost when “Automatic Cost Adjustment” set to NEVER
Now, let’s test what happens to the “Unit Cost” and the FIFO costs when we have the “Automatic Cost Adjustment” set to NEVER.
First, I’ll create a new item as per below using the same initial “Unit Cost” and a costing method of “FIFO” as the previous item:
I’ll raise the same Purchase Orders as before however for brevity I won’t include screen shots of the Purchase Orders on this occasion.
When we now check the “Unit Cost” the system is still showing £20.00 as per the first purchase order we received and invoiced. It hasn’t been *automatically* changed to the correct “Average Cost” of £35.00.
However if I check the “Average Cost Calc. Overview” by drilling back on the “Unit Cost” it shows we have a quantity of 10 at a total cost of £350.00.
So, why hasn’t the system updated the “Unit Cost” on the item on this occasion?
This is because we have switched “Automatic Cost Adjustment” to NEVER. This means the “Adjust Costs – Item Entries” batch job hasn’t ran when we posted the receipt and invoice for the Purchase Orders.
Therefore if we want to update the “Unit Cost” we must run the “Adjust Costs – Item Entries” batch job manually as per below:
Then click OK to run this for all items. (I could add a filter for my item if I’d prefer)
Now when I check the “Unit Cost” the correct Average Cost is now showing
Its also worth noting that if I leave the setting “Automatic Cost Adjustment” set as NEVER, when I post a shipment the “Value Entries” produced will be for the “Unit Cost” on the item i.e. now that is -£35.00 as we have ran the “Adjust Cost – Item Entries” batch job. The additional “Adjustment” Value Entry will only be produced when I next run the “Adjust Costs – Item Entries” batch job.
The “Automatic Cost Adjustment” setting has a direct impact on what is shown in the “Unit Cost” of an item. This determines when the “Adjust Costs – Item Entries” batch job runs which, amongst other things, also recalculates the Unit Cost on items.
Within Dynamics NAV \ Business Central you can switch Expected Cost Posting to G/L both ON and OFF via the option below in Inventory Setup.
In this post I’ve been playing with this feature to see how things work and how the various postings differ to Dynamics GP. I also take a look at how the Value Entries in Inventory play a pivotal role in this. I end by taking a closer look at the SQL tables involved and how things fit together.
Expected Costs in Dynamics GP
When you receive goods via a Shipment transaction in Dynamics GP a Purchase Accrual is automatically created to a General Ledger accrual account to record the expected cost in the General Ledger. This account is generally taken from the Creditor Card as per below:
opposite debit entry is taken from the Inventory Item card as per below:
Let’s add a Purchase Order Shipment transaction in Dynamics GP and see this in action:
As you can
see from the screen shot above, I’m receiving one Inventory item, and this has
created an accrual distribution crediting the 000-2111-00 accrued purchases
account I specified on the creditor card. The balancing debit side is to the
Inventory code that we specified on the Inventory Item card.
see what happens when we invoice the Shipment:
Just as expected the accrual is reversed via a Debit entry to the 000-2111-00 accrued purchases account and the accounts payable is credited. Therefore, the balance in the accrual account is now nil.
There’s no way to disable this behaviour in Dynamics GP. When you post a “Shipment” for some Inventory Items General Ledger entries are always created. (however you can prevent the entries posting through to the General Ledger via the Posting Setup)
Expected Costs in Dynamics NAV \ Business Central
Before we look at Expected Costing in Dynamics NAV \ Business Central we first have to take a step back and look at the various inventory entries that are created when you post inventory transactions.
When you post an inventory transaction in Dynamics NAV \ Business Central the system creates a minimum of two inventory entries: an Item Ledger Entry and a Value Entry. The Item Ledger Entry records the change in quantity and the Value Entry records the change in inventory values. For the purposes of this post we just need to know that when posting Purchase receipts Value Entries are created for Expected Costs, and when you post Purchase Invoices, Value Entries are created for Actual Costs, and Expected Costs are reversed.
Expected Cost Posting to G/L – Switched ON
Unlike Dynamics GP you can switch ON and OFF accrual postings in Dynamics NAV \ Business Central via the Expected Cost Posting to G/L option in Inventory Setup. When you switch Expected Cost Posting to G/L ON interim accounts are used to post the accrual and inventory entries for Purchase receipt transactions.
The equivalent Dynamics GP accrued purchases account is called Invt. Accrual Acc. (Interim) and is specified in the General Posting Setup window and is selected based on the Posting Groups used on the Item and Creditor. (see my previous post for more details on the posting groups). I’ve highlighted this below
The Inventory code for the debit side of the transaction is taken from the Inventory Posting Group and again is based on the combination of posting groups used. I’ve highlighted this below
The key difference here is Dynamics GP doesn’t use an interim Inventory account whereas Dynamics NAV \ Business Central does.
In my Cronus demo data, the option Expected Cost Posting to G/L is currently switched ON so let’s see how this works when I create a Purchase Order for an Inventory item and receive it.
Here’s my Purchase Order and after clicking Post I’m going to choose receive so I receive the items into the Inventory:
When I view the item I can see this has created the following Value Entry (Number 454) which shows the Cost Amount (Expected) and Expected Cost Posted to G/L both populated.
If I highlight the Value Entry and click Navigate > General Ledger I can see the G/L Entries associated with the Value Entry
we can see the 5510 Accrual account is being credited and the debit entry is
posting to the 2111 “Interim” Inventory code.
Now let’s invoice the purchase order and take a look at the G/L entries. First I click Post and select Invoice on the Purchase Order. (Incidentally if I were to select Receive and Invoice the system recognises I’ve already received the items. It doesn’t receive them again)
This has created the a new Value Entry (Number 455) . There’s a few things to note here. Firstly the Cost Amount (Expected) and the Expected Cost to G/L have been reversed. Secondly the Cost Amount (Actual) and Cost Posted to G/L have been populated.
Again if I highlight the Value Entry and choose Navigate > General Ledger we can the G/L Entries associated with this Value Entry.
As you can see the original entries created via the Purchase Receipt have been reversed by posting a debit to the 5510 Inventory accrual account and a credit to the 2111 Interim Inventory account. The system has then posted new entries to the 2210 Resale items inventory account (debit) and the direct cost applied account (credit). (for more info on the direct cost applied account see my previous post).
So that’s it. Although there are a few extra distributions to Dynamics GP everything makes sense. Its also apparent that the Value Entries have a direct relation to the G/L entries that are created.
Expected Costing to G/L – Switched OFF
Now let’s see what happens when we post a Purchase Receipt with the Expected Cost Posting to G/L switched OFF. First I switch the option OFF and then create and post the Purchase Receipt
This has created the following Value Entries (Number 456)
The key thing to note here is that although the Cost Amount (Expected) is populated the Expected Cost Posted to G/L isn’t. This means no G/L Entries have been created. To prove this click Navigate > General Ledger to view any G/L Entries
Let’s now invoice the Purchase Order and see what happens:
This has created the following Value Entry
This Value Entry records the Cost Amount (Expected) being reversed and the Cost Amount (Actual) and Cost Posted to G/L are also populated. Therefore we get the following G/L Entries
As expected no expected cost postings have been created or reversed for this transaction.
Bonus – G/L Item Ledger Relation and Post Value to G/L SQL tables
An unexpected bonus of writing this post was the chance to geek out on some of the Dynamics NAV tables. Unlike Dynamics GP, I don’t have much of a grasp of the SQL tables in Dynamics NAV however while going through the various scenario’s I was curious about how a couple of things worked which encouraged me to dig a little deeper.
The first thing was how I was able to drill down on the G/L Entries from the Value Entries screen? This meant there must be a direct or indirect relationship between the tables.
After some digging I found the link was via the G_L – Item Ledger Relation table. Therefore writing the SQL query below enabled me to join the G/L Entries and Value Entries table to see all the details for Value Entry Number 455
The next thing I was curious about was what would happen if the Expected Cost Posting to G/L was switched from OFF to ON when there were lots of Purchase Receipts that hadn’t yet been invoiced?
I found the answer to this question lay in the two prompts you receive when you switch Expected Cost Posting to G/L from OFF to ON (or ON to OFF). Below are the two messages you get when toggling the setting
As per the first message it seems when you switch the option Expected Cost Posting to G/L ON the system determines if the Actual Costs for the Purchase Receipt have been posted and if not a record is written for that Value Entry to the Post Value Entry to G_L SQL table. This has a link back to the Value Entry so the system knows to create the Expected Cost interim postings for this Value Entry.
To show this in action, I switched the Expected Cost Posting to G/L option OFF and queried the SQL table:
As per the image above the SQL table is currently blank.
I then created
a Purchase Order and received it as per below:
After posting this I queried the Post Value Entry to G/L SQL table again to see if any new rows had been added and the table was still blank
I then checked the Value Entry for my receipt and as per the screen shot below the Cost Amount (Expected) is populated but the Expected Cost Posted to G/L is blank. As this is only the Purchase Receipt the Cost Posted to G/L is also zero.
I then went back to Inventory Setup and switched Expected Cost Posting to G/L back ON and clicked YES to the prompt and now when I check the Post Value Entry to G/L table its populated as per below
After toggling the Expected Cost Posting to G/L option to ON the system has determined that this Value Entry has no G/L Entries for the Expected Costs and has inserted a record into the Post Value Entry to G_L table with a direct link back to the Value Entry that was created when I posted my receipt.
Now if I run the Post Inventory Cost to G/L batch job as instructed in the second message G/L entries are created for the purchase receipt, the SQL table is cleared, and the Value Entry is updated. See below:
The report output
of the “Post Inventory Cost to G/L” shows entries have been created:
Below are the expected cost General Ledger entries created to the interim accounts. (in my previous example these were created immediately because I had Post Expected Costs to G/L switched ON)
And finally the Expected Cost Posted to G/L field on the Value Entry has been updated to show the General Ledger entries have been created and posted.
If I now check the Post Value Entry to G_L table in SQL I can see its been cleared.
Incidentally if I were to switch OFF Expected Cost Posting to G/L before running the Post Inventory Cost to G/L batch job the SQL row is removed from the Post Value Entry to G_L table.
In conclusion I find the way Dynamics GP deals with expected costs to be a much more conventional and simple approach however there’s no doubting that Dynamics NAV \ Business Central gives more flexibility.
Although I don’t know much about the inner workings of Dynamics NAV \ Business Central it also seems to me that G/L Entries are created based on the Value Entries.
In another post I hope to look at how Expected Costs work with Sales Shipments and Sales Invoices.