Skip to content

Inventory Valuation by Location Report for Average Cost Item shows Zero on Hand but has a Value

ArcherPoint How-To Blog: Step-by-step instructions on how to perform specific tasks in Microsoft Dynamics NAV

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.

<p>If you are interested in any of these reports, or for more information on another Dynamics NAV costing topic, please <a  data-cke-saved-href="http://www.archerpoint.com/contact-us" href="http://www.archerpoint.com/contact-us" target="_blank"><strong>contact ArcherPoint</strong></a>.</p> <p><a  data-cke-saved-href="http://www.archerpoint.com/blog/bob-bergman" href="http://www.archerpoint.com/blog/bob-bergman" target="_blank"><strong>Read more blogs by Bob Bergman</strong></a> for practical advice on using Microsoft Dynamics NAV.</p>

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.

Purchase order showing two different locations and different unit costs using Average Cost

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.

Inventory Valuation Report showing correct values 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. 

Sales Order to ship 10 pieces all from one location

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.

Inventory Valuation Report now shows zero on hand and a negative value

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.

 Changing the Average Cost Calc. Type to Item & Location & Variant in Inventory Setup

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.

After changing the Inventory Setup, the Inventory Valuation Report yields expected results

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.

nventory Valuation Report for just location BOB 1 shows zero on hand and zero value…as expected

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.

Blog Tags: 
Read ArcherPoint's Blog Follow us on Twitter Follow us on Facebook Follow us on LinkedIn Link to our RSS feed Join us on Google+ Watch us on YouTube
Get Help Now