The Effects of Inventory Setup in Microsoft Dynamics NAV
I am often asked questions on the effects of Inventory Setup in Microsoft Dynamics NAV. The two most common questions are:
1) How do the settings affect system performance?
2) How do the settings affect what is posted to the General Ledger and when?
In this blog, I will discuss these questions in the context of Automatic Cost Posting, Expected Cost Posting to G/L, and Automatic Cost Adjustment in Dynamics NAV, saving Average Cost for a later blog.
Let’s start by going to Inventory Setup. Go to Warehouse->Inventory->Setup->Inventory Setup:
Inventory Setup Window in Microsoft Dynamics NAV Classic Client
To understand what Automatic Cost Posting does, it’s important to understand Dynamics NAV’s Value Entries. When an inventory transaction is recorded in Dynamics NAV, an associated Value Entry is automatically entered into the Value Entry Table. This Value Entry reflects the cost of that transaction, whether positive or negative. Additional Value Entries can be added later by the Adjust Cost program.
If Automatic Cost Posting is checked, every time a Value Entry is entered into the Dynamics NAV database, the corresponding General Ledger entries are automatically made.
Expected Cost Posting
The key to understanding how Expected Cost Posting to G/L works is being aware that there can be two types of Value Entries. For instance, when a purchase order is received but not invoiced, Dynamics NAV automatically enters a Value Entry with an expected cost. When that purchase is invoiced, NAV automatically enters a Value Entry that reverses the original expected entry with the expected cost and replaces it with one with the actual costs.
By checking Expected Cost Posting to G/L, the system will post expected values to the General Ledger. If this is not done, an accrual General Journal Entry should be posted at the end of an accounting period to bring the General Ledger into balance with the Inventory Sub Ledger.
Inventory Setup Window with focus on Automatic Cost Adjustment
Automatic Cost Adjustment
Automatic Cost Adjustment is a fairly complex process and will be covered in more detail in a later blog, but for now, I’ll explain it this way:
There are two types of inventory transactions: 1) those that add inventory (inbound) and 2) those that remove inventory (outbound). For each transaction type, NAV enters at least one Value Entry to represent the cost of that transaction. Outbound Value Entries get their cost from the Inbound Value Entry that supplied it, for instance, a purchase order lot is received and a sales order is shipped from that lot.
There are times, though, when the original cost that was entered for the purchase order receipt was incomplete and, therefore, the cost transferred to the sales order cost of goods sold is incomplete (i.e., freight costs were added later). When the added cost is made to the purchase receipt, the Adjust Item Entries Cost program finds the outbound entries that were supplied by the purchase order and adjusted to show the additional cost.
To explain Automatic Cost Adjustment, I have included the following from NAV Help:
In this field you can set the program up to adjust for any cost changes automatically every time you post inventory transactions. The adjustment process and its consequences are the same as for the Adjust Cost – Item Entries batch job.
Because potential cost adjustment during every inbound posting could slow down database performance, this setup field includes time options (see below) so that you can define how far back in time from the work date an inbound transaction can occur to potentially trigger adjustment of related outbound value entries.
When setting a time option for automatic cost adjustment, you should therefore select one that balances your requirements for cost accuracy with the performance level of your database. Generally speaking; the shorter the time setting, the less accurate the cost information, but the better the database performance during posting.
In my experience, I have always selected ‘Never’ or ‘Always.’ If you select ‘Never,’ however, you must run the batch job Adjust Cost – Item Entries.
Microsoft Dynamics NAV Financial Management Window
If you are experiencing system performance issues (locked tables), it probably means you have set up Automatic Cost Adjustment to Always and Automatic Cost Posting is turned on. To alleviate these performance issues, change the setting Automatic Cost Adjustment to Never and turn off Automatic Cost Posting. This means, however, that you will need to run the Adjust Cost and Post Cost batch jobs.
For a new implementation, I try to make a determination as to transaction volume. If there is a high transaction volume, then I recommend turning off the automatic features.
As for Expected Cost Posting, it is the choice of the financial team as to whether to use it or not. From a performance point of view, there is not much effect, but it does add a significant number of records to the General Ledger. My choice is to turn it on as it does provide valuable information and it does make reconciling the Inventory Sub Ledger to the General Ledger much easier.
If you are in doubt about which setting to use, the good news is that you can turn Automatic Cost Adjustment on or off without compromising costing accuracy.
If you would like to learn more or have questions about Inventory Setup for your company, contact ArcherPoint.