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

(0) Share

Report

Posted on by Blake Burnley

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.

I have the same question (0)

All responses (12)

Answers (1)

Suggested answer

Community Member on at

Like (0)

Report

Blake,

A few things to ask first:

1. The Item you are selling and manufacturing what is Costing Method?

2. Have you run Adjust Cost - Item Entries?

I am trying to figure out how the Item you sold had a manufacturing cost (components and capacity (labor) of $500.00 and when actually shipped and invoiced had a COGS of $40K.

To help with how entries are recorded I will include the following from the Production and Sales side:

Please reply back and hope this all helps.

Thanks,

Steve

Was this reply helpful? Yes No
Blake Burnley 10 on at

Like (0)

Report

Steve,

1. All of our items use the FIFO costing method

2. No, we have Automatic Adjust Cost set to always in our inventory setup so as far as I understand we do not need to run it.

I understand how all the debits and credits should occur and mapped all of them out starting from the first WIP production order all the way to the shipment. Everything lines up and all the accounts are hit correctly, it is just when invoice was generated that it used the wrong cost. It seems to add some cost amount for no apparent reason.

Thanks

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Blake Burnley 10 on at

Like (0)

Report

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.

Thanks

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Blake Burnley 10 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Blake Burnley 10 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Blake Burnley 10 on at

Like (0)

Report

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

Was this reply helpful? Yes No