Tip of the Week #88 – Quick Trick to Stock Value Error Analysis

Tip of the Week #88 – Quick Trick to Stock Value Error Analysis

Senior Consultant Kit Tomshøj brings us this week’s helpful tip.

If you are one of the lucky persons minding the stock on your company, you will know how painful it can be to suddenly find errors where the stock quantity and stock value do not match up.

NAV keeps track of your stock quantity and your stock value in two different (but communicating) registers.

In most cases I (we) recommend keeping your items on stock in FIFO (first in, first out) as this is the easiest to manage.

Before you start, you will of course create your stock periods as well as your open posting periods – but that’s a subject for a different post.

This post is about an easy trick to find out what went wrong – if something went wrong.

First: Trust me, you are not the first one who has trouble in your item values.

Second: It may not even be your fault…. but it may well be your responsibility to fix it.

Here is how to quickly find the root cause:

Open your Item Card and navigate to the Item Ledger Entries.

Set the sorting order of the lines to Entry number, so you are sure that the lines are represented in the actual order they were created in.

Copy all Item Ledger Entries to a spreadsheet, while having the first columns as in this example (the blue ones):

Add 4 new columns to the spreadsheet, as shown here.

The columns will be:

  1. Items on stock (I)                                  First line=D4 (Actual Quantity) Second line=I4+D5 (previous quantity plus new quantity from this line) copy this to all lines
  2. Value on stock (J)                                  First line=G4 (Actual Amount) Second line=J4+G5 (Previous value plus value from this line) opy this to all lines
  3. Cost per item moved (K)                      All lines= G4/D4
  4. Cost per item on stock (L)                    All lines= G5/D5

The first thing to notice and mark out is every line where D4 and G4 are both = 0.
This marks a point in time where things even out and all is good so far. Color these lines green so you can clearly see where things were OK if you come back to the spreadsheet later.

Then look for anomalies in Quantity on stock (I) and Value of stock (J) columns – for example, quantity is 100 and value is 0 – this may be cause for error. Also, if quantity on stock is 0 and there is still a value of stock, there is an error.

Also look for changes in column L: If the stocked item value changes drastically, there may be an issue with an Item Ledger Entry where the value was wrong.

Errors which can be identified with this method of analysis are for example:

  • Purchase order is invoiced with wrong price.
    This is the easiest error to fix – in a NAV Revaluation journal.
    Follow the instructions.
  • Purchase receipt was made for 1000. But invoice was received for 800. The order remains open, and there is an imbalance between quantity and value.
  • A sale or consumption was made but the unit cost was incorrect.
    This could be caused by a manual posting as the system postings would normally be OK.
  • A quantity deviation found in a cycle count has been adjusted for (negative or positive adjustments) but with an incorrect unit cost.

Example of error:

Error: negative adjustment was posted with 0 as unit cost.

Effect: Quantity and value are now out of proportion, and the unit value is now much higher from this point.

Repair: Use the function Reverse on the wrong line and post again, now with the correct unit cost. Then run the Adjust Item costs report for the item.

Thank you Kit!