mibuso.com
Home› NAV/Navision Classic Client
Options

Item cost changed at physical inventory journal posting

sajidhanifkhansajidhanifkhan Member Posts: 182
edited 2009-11-05 in NAV/Navision Classic Client
Hi ,

I noticed very strange thing,

We were doing Physical inventory by using physical inventory journal , we created seprate batches for different locations,
When we update the physical inventory column I run the test report before posting the inventory I saw noted the total adjustment amount and evry body of inventory team agreed on that amount as soon as I posted they asked me to print the report for the adjustment I ntoiced that system was showing different amount.

Please note as setup we marked automatic cost posting to G/L.

Can any one explain the reason of the cost amount differences?

Regards,

Comments

  • Options
    KowaKowa Member Posts: 918
    What costing method are you using? Did you run the item cost adjustment?
    The item journal line only shows you the current average cost. This applies only to positive adjustments created when posting the journal. The real valuation depends on your costing method. The negative adjustments will be adjusted according to this method, all items inventories where the counted and calculated quantity coincided will remain untouched.
    Kai Kowalewski
  • Options
    sajidhanifkhansajidhanifkhan Member Posts: 182
    Hi Kowa,

    We are using FIFO costing method and no I did not run the cost adjustment but it applies the cost automatticaly while posting since I have marked Automatic cost posting in inventory setup and automatic cost adjustment is always. Average cost period is zero in inventory setup.

    I still did not get the reason. Please suggest if the inventory setup is correct as I mentioned above?
  • Options
    Alex_ChowAlex_Chow Member Posts: 5,063
    You still need to run the Adjust Cost process before you do any costing analysis irregardless of the costing method you use.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • Options
    KowaKowa Member Posts: 918
    sajidhanifkhan wrote:
    Hi Kowa,

    We are using FIFO costing method and no I did not run the cost adjustment but it applies the cost automatticaly while posting since I have marked Automatic cost posting in inventory setup and automatic cost adjustment is always. Average cost period is zero in inventory setup.

    I still did not get the reason. Please suggest if the inventory setup is correct as I mentioned above?
    If you have automatic cost adjustment set to "Always", the adjustment takes place when the journal is posted, that means the FIFO valuation is available immediately. You can run the manual cost adjustment just to make sure that everything has been adjusted (some older entries may have stayed unadjusted, especially if the automatic cost adjustment was set to some other option than "Always" at some time).

    The average cost period is only relevant if you use "Average" as your costing method. (BTW:The NAV standard has no "zero" setting, you select periods. Up to version 4 , it was always a daily average, but Version 5 offers longer periods too).

    You can't determine the FIFO valuation before posting. All you get from the journal is a more or less accurate assumption. Just one example:
    NAV calculated 3 items with an average unit cost of $10 . These were purchased in 3 separate purchases at $9,$10 and $11. You counted only 2, so the average valuation would be $20. NAV posts a negative adjustment. FIFO chooses the $9 purchase, and so the FIFO valuation for the remaining entries is $10 + $11 = $21. In this case, it is higher, in other cases it may be lower. That depends on how the purchase prices have fluctuated. It can also be combination, if the negative adjustment entry was not applied to a single purchase but to several different purchases. In the latter case, the FIFO valuation for the adjustment is a weighted average calculated from the applied quantities for each purchase and the corresponding cost amounts.
    Kai Kowalewski
  • Options
    sajidhanifkhansajidhanifkhan Member Posts: 182
    Kowa wrote:

    the FIFO valuation for the adjustment is a weighted average calculated from the applied quantities for each purchase and the corresponding cost amounts.

    Based on your statment above at time of running the test report with my earlier sstated inventory setup which seems correct as per your suggestion, I was having as total value of adjustements for example 10000 in the test report before posting the physical inventory batch. As soon as I posted the total value changed to 12000. My point is that if system is calculating the weighted average for the applied quantities and showing me the value in test report why it changed after posting?
  • Options
    KowaKowa Member Posts: 918
    sajidhanifkhan wrote:
    My point is that if system is calculating the weighted average for the applied quantities and showing me the value in test report why it changed after posting?
    The average unit cost from the item or SKU card (this is copied to the journal line) is calculated
    by the standard formula :

    (Beginning Inventory + Net Purchases - Cost of Goods Sold (COGS))/ Quantity

    If SKUs are used, this will be location-specific or variant-specific.

    The Beginning Inventory is always 0, because NAV sums up everything starting from the first value entry. Net Purchases and COGS should be considered in a broader sense as any cost amount increase and any cost amount decrease.

    That is your overall average, but that has nothing to do with the weighted average FIFO uses if there is more then one item application entry involved. This is calculated solely for this particular application to determine the "Cost amount (actual)" for the adjustment entry.
    Kai Kowalewski
  • Options
    KowaKowa Member Posts: 918
    edited 2009-11-05
    An Example for a negative adjustment being applied to two purchases with different prices :

    3 Purchases :
    3 for $ 5 = $ 15
    4 for $ 6 = $ 24
    3 for $ 4 = $ 12

    Total : $ 51 / 10 = $ 5.10 -> this average unit cost is shown on the item card

    Phys Inventory Journal:
    Calculates Qty of 10 but you counted only 4. That results in a negative adjustment for a Qty of 6.

    Inventory average valuation before posting : 4 x $ 5.10 = $ 20.40
    You post the journal. The initial valuation for the neg. adjustment is the average coming from the journal line:
    6 x $ 5.10 = $ 30.60.
    Then the cost adjustment compares this value to what is calculated from the applied quantites.
    FIFO takes out all 3 from the first purchase and 3 of the 4 from the second purchase.
    3 for $ 5 = $ 15, 3 for $ 6 = $ 18
    TOTAL : $ 33 -> this is the real cost amount decrease. So an adjustment value entry for ($ 30.60 - $ 33) = $ -2.40 is created by the cost adjustment batch.

    $ 33 / 6 = $ 5.50 -> this is the weighted-average unit cost for the negative adjustment item ledger entry (not $ 5.10, that was the overall average in the journal line).
    All of these amounts have a negative sign in the ledger entries.

    $ 51 - $ 33 = $ 18 -> this is the ending inventory for this item with FIFO valuation (not $ 20.40 as assumed before posting)
    $ 18 / 4 = $ 4.50 -> this is the new average unit cost shown on the item card.

    If you sum up the remaining open purchase entries, you get the same result:
    1 for $ 6 = $ 6, 3 for $ 4 = $ 12 -> Total $ 18
    Kai Kowalewski
  • Options
    sajidhanifkhansajidhanifkhan Member Posts: 182
    Hi Kowa,

    Thank you very much for your detailed explaination. It really helped to understand the concept.

    Best Regards,
Sign In or Register to comment.