Skip to main content

Notifications

Small and medium business | Business Central, N...
Answered

Cost amount on sales invoice does not match actual cost of materials

Posted on by 10

We have a weird situation where the cost amount listed on the sales invoice does not match the actual cost of the materials by a significant amount (about 80x).  As a result it looks like our inventory is lower than it should be and our cost of goods were higher than they should be.  We were able to trace this issue to two shipment entries that have an actual cost much higher than they should be.

Simplified Example

  • On a production order we record output of 100 units and consumption of $500 worth of materials giving us a unit cost of $5.  This then decreases our raw material inventory by $500 and increases our finished goods inventory by $500, so a net change of $0.
  • We then shipped the 100 units.  Our this then debited our interim cogs account and interim FG inventory by $500
  • Lastly we invoiced the sales order.  This debited our interim fg inventory account and credited our interim cogs account by $500 undoing the shipment entries.  It then debited our cogs account and credited our fg inventory accounts by $40,000.  Now our cogs and fg inventory appear to be off by $39,500.

Other details

  • This increased cost of materials seems to only be affecting 1 lot this item
  • This lot was made on two different production orders but it seems to only be affecting the shipments using items from the first production order
    • There are no entries on this production order that are out of the ordinary and the costs are as expected
  • This issue appears on two different sales shipments.  The shipments were invoiced separately and part of separate orders

We are struggling to understand 1) what caused this and 2) how to fix it. As far as we can tell there is no entry that increased the cost of materials by this amount but we know that NAV wouldn't just change numbers for no reason.

Any help is appreciated.

  • Blake Burnley Profile Picture
    Blake Burnley 10 on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    We found a solution to this and wanted to share it.  It ended up being much simpler than expected.

    We confirmed that, for some reason, no Adjustment Entries were not posted to correct the Cost Amount (Actual) on the shipment lines.  This does not explain why the Cost Amount was so much larger to begin with but why it remained.  We see lots of other shipments that started with a Cost Amount higher than expected but subsequent Adjustment Entries corrected.

    What ended up working was using the revaluation journal on the output entry for the item we shipped.  We revalued the output entry by the smallest amount possible.  This then forced the shipment lines to be revalued as well.  After the revaluation the shipment entries now had a Cost Amount (Actual) that matched what was output.  Then, to prevent further mistakes, we revalued the output back to its original value so that it matched what had recorded in consumption.  After combing through all the ILE and GLE it looks like everything lines up and appears as expected.

    When going through the revaluation process it seems like this prompted NAV to post the correct Adjustment Entries.

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    Blake,

    OK. Good luck.

    Steve

  • Blake Burnley Profile Picture
    Blake Burnley 10 on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    Steve,

    I appreciate the offer but I think we have exhausted our options.  I spent some more time working on this with our partner and they have not been able to find the cause of this issue.  They believe that they need to do a data correct to fix this issue.

    Thanks

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    Blake,

    I am not sure your time zone but if you want to connect via Teams or Zoom I have no problem helping you and looking at the posting and entry and telling you what I see. If you would like this, feel free to write back and I can send a message to you with information on how to connect with me.

    Thanks,

    Steve

  • Blake Burnley Profile Picture
    Blake Burnley 10 on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    Steve,

    Do you mean Sales Order?  As far as I can tell the Sales Order for this item is close out and item was invoiced completely.  I found other entries in the system that do not have a corresponding adjustment but that appears to be because the initial price was correct and they did not need one.  It feels like NAV for some reason thinks this is the correct price.

    It can't hurt to share the screen shots but I don't think this is something you will be able to recreate without using our database.  We sold this item everyday for a year with no other issues so I don't think this is going to be a setup issue.  I think the cause is going to be some onetime event that occurred that we can't identify.  Without knowing what triggered this event I think it'll be hard to reproduce.

    Thanks

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    Blake,

    A thought here... The Item you sold showing $40K vs. $500 expected, is this Item showing the Purchase Order is still Open and Invoice not posted? Meaning Received Not Invoiced? I am trying to narrow why it has not been Adjusted within the Adjust Cost-Item Entries process and cleared, as you have mentioned others have been.

    Can you look check. I attempted to recreate the issue in Cronus with NO Routing just a BOM and simple components with Expected Cost on. I am not getting the difference you are when I sell the Item. My FIFO Costing Method takes the Unit Cost Expected and records the accrual and upon Invoice updates with the layer. I ran Adjust Cost to make sure adjustments are completed and no additional entries.

    Do you want me to take screen shots of my process and email them back on this forum?

    Thanks,

    Steve

  • Blake Burnley Profile Picture
    Blake Burnley 10 on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    Steve,

    Correct, all of our items are FIFO costing and have no indirect and overhead values setup yet.  We do not use routings, machine centers, or work centers.  There do not seem to be any direct or indirect entries that account for all the difference.

    I looked through all the values entries for this item and found that after this invoice was posted several other invoice were posting with the same high unit cost but subsequently received an adjusting entry.

    Thanks

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    Blake,

    Each of the Components on the BOM are FIFO and have no indirect or overhead values? Also, do you have a Routing and what does that look like for the Work Center or Machine Center? Lastly, from the Finished Production Order, the ILE, Value Entries, and Capacity Entries do they show any direct or indirect entries causing the difference?

    If you want me to test this in my database, I can but will need to setup the items and BOM and routing and will need your images. This massive difference can only be caused by unexpected changes to the Consumption or Output Entry. Please export the entire screen for ILE, Value Entries, and Capacity so we can see all the columns and Unit Cost and Qty.

    Thanks,

    Steve

  • Blake Burnley Profile Picture
    Blake Burnley 10 on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    Steve,

    No, the item does not have any indirect or overhead values.

    The values entries show a similar story to what we are observing elsewhere.  The Cost Amount (Expected) is the cost of materials used ($500) but the Cost Amount (Actual) is the increased cost ($40,000).  Some thing interesting I noticed is that there does not appear to be an adjustment entry for this item on this invoice.  All the other items invoiced have adjustment entries to correct the Cost Amount (Actual) value.  I am made an example table below.  

    pastedimage1616074540313v1.png

    Thanks

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cost amount on sales invoice does not match actual cost of materials

    Blake,

    The Item you are selling, on the Item Card do you have any Indirect or Overhead values setup? Also, what do the Value Entries show for when you Navigate on the Posted Invoice?

    Thanks,

    Steve

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans