Determine the Actual FIFO or LIFO Value of Inventory Using Standard Cost in Microsoft Dynamics NAV
From time to time I am asked if you can you determine the Actual FIFO or LIFO value of your inventory if you are using Standard Cost. There are companies that will value their inventory at Standard for managerial accounting reasons, but for reporting or tax reasons will value their inventory at FIFO or LIFO.
To understand how Microsoft Dynamics NAV calculates Inventory Value, it is important to understand NAV’s Item Ledger Entries and NAV’s Value Entries. When an inventory transaction is recorded in NAV, an Item Ledger Entry and an associated Value Entry are automatically entered into the NAV database. The Value Entry reflects the cost of that transaction, whether positive or negative. Additional Value Entries can be added later by adding an Item Charge or the Adjust Cost program.
When you are using Standard Cost, you can enter two or more Value Entries in the Value Entry Table when a purchase receipt or Production Output is posted—one for the actual cost and another for the “Variance(s)”.
This means that if you want to know the actual cost of procurement or production for the remaining inventory you need to find the Item Ledger Entries that have a remaining quantity, then drill into the Value Entries to find the Value Entries with the Type = Direct, as well as eliminating those that are of the Type = Variance.
I created an item that is coded for Standard Cost and set the cost at $1.00
Figure 1 – Item Card with Standard Cost set to $1.00
I then created a Purchase Order for the item with a cost of $1.50.
Figure 2 – Create Purchase Order for the same item with a cost of $1.50
I posted the receipt and invoice for the purchase order, which created the following Item Ledger Entries and Value Entries:
Figure 3 – Updated Item Ledger Entries
Figure 4 – Updated Value Entries
You can see that, when I drilled down into the Value Entries, I can find the Direct Cost of $1.50.
To get the total cost of the remaining inventory you would divide the cost amount actual by the original quantity to get the unit cost, then multiply the remaining quantity times the unit cost.
Dynamics NAV’s costing capabilities are quite flexible. If you have a unique costing requirement, contact ArcherPoint to discuss it.