Inventory Valuation by Location Report for Average Cost Item shows Zero on Hand but has a Value
We often get the question from clients using the Dynamics NAV Average Cost Method, “Why is there a dollar value when there is zero on hand in a location, but when I run the report for all locations combined, it has a correct value?”
First, we should look at the Inventory Setup. We can see that the Average Cost Calc. Type is set to “Item”.
What this means is that the average is calculated for all locations combined.
Figure 1 – Inventory Setup showing Average Cost Calc. Type set to “Item”
To illustrate, here is an example. Create a purchase order for an average cost item with 10 pieces going into to Location BOB 1 and BOB 2. The unit cost for those going into BOB 1 is 10.00 and those going into BOB 2 100.00.
Figure 2 – Purchase order showing two different locations and different unit costs using Average Cost
We then post and invoice the receipt and run the inventory valuation report for each location. We can see that the values are correct for each location.
Figure 3 – Inventory Valuation Report showing correct values for each location
We then created a Sales Order to ship the 10 pieces in Location BOB 1.
Figure 4 – Sales Order to ship 10 pieces all from one location
Now, when we run the Inventory Valuation Report, we get the unexpected results of zero on hand in location BOB 1 with a value of -450.00.
Figure 5 – Inventory Valuation Report now shows zero on hand and a negative value
If you would like to maintain an average cost for locations, you must select Average Cost Calc. Type to be Item & Location & Variant.
Figure 6 – Changing the Average Cost Calc. Type to Item & Location & Variant in Inventory Setup will maintain an average cost for locations
Now, when we run the Inventory Valuation Report we get what we might expect.
Figure 7 – After changing the Inventory Setup, the Inventory Valuation Report yields expected results
When we run the Inventory Valuation Report for just Location BOB 1, we can see that the valuation for zero on hand is equal to zero.
Figure 8 – The Inventory Valuation Report for just location BOB 1 shows zero on hand and zero value…as expected
For more information on this or any other Dynamics NAV costing topic, please contact ArcherPoint.
Read more "How To" blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV.