Category Archives: costing

Common Mistake in Updating Standard Cost in Dynamics NAV

Overview
Recently, I’ve been contacted by one of our readers on why their inventory cost was wrong. This particular company utilizes standard cost and the inventory cost never matched their inventory G/L account. Learning about how they updated standard cost, it was very apparent what went wrong. It was how the user updated their standard cost.

Companies will periodically update their Standard Cost to reflect closer to what the cost of their items. The timing of this really depends on the company and the industry they’re in. I’ve seen companies revaluate their standard cost every month, some do it every quarter, some companies revaluate their standard cost every year.

Updating the standard cost is a must for every company, or else you will see big variances when you run your financials, which accountants and auditors will question every single time.

Update Standard Cost without Changing Existing Inventory on Hand
Depending on how your company wants to recalculate standard cost, you may not want it to change the inventory value on hand.

To do this, all you need to do is just updating the Standard Cost field on the Item Card of the Stockkeeping Unit Card is only part of the process.

The folks at Microsoft made it even convenient for you to do so:

Standard Cost Update

All you need to do is just change the Standard Cost to whatever you like and you’re done. For manufacturing companies they may use the Calculate Standard Cost functionality.

What’s missing is you need to revaluate what you current have on hand to the new standard cost. Just updating the standard cost on the item card will only affect purchase or incoming items going forward. It will not affect historical standard cost values!

Where the Common Mistake Occurs
Not running Adjust Cost – Item Entries aside, the problem is not with the actual update of the standard cost, but the current inventory you have on hand.

Instead of running the Inventory Valuation report or the Inventory to G/L Reconcile report to get their inventory value, most accountants do the following:

  1. Grab the Standard Cost on the item card
  2. Multiply by the standard cost from step 1 by the quantity on hand
  3. Manually adjust the inventory G/L account

The 3rd step is what kills you. Making manual G/L entries will only make reconciliation between the item ledger and general ledger extremely tough and add a tremendous amount of (unless) work. In my career, I have not met a person that wants to do more work that is useless.

Update Standard Cost and Changing Existing Inventory on Hand
If you want to change your current inventory value on hand, you have to run the Revaluation Journal AFTER you update your standard cost.

Microsoft even published an article detailing the proper way of updating standard cost for your Dynamics NAV implementation. Click here for the article.

Conclusion
Properly used, the inventory function in NAV never goes wrong and will never require human intervention. What’s wrong is the information you feed it.

Why Your Inventory Cost is Wrong

The Problem
I hate beating a dead horse to death, but there are a couple of incidences that prompted me to write this article. Navision 1.0 has been out since 1996, yet it always astounds me when I walk into a company claiming that their inventory costing and cost of goods sold is incorrect because Navision is bugged.The first question I always ask is: Have you guys ran the Adjust Cost – Item Entries process? Most of the time, I get the blank stare asking what the hell I’m talking about.

The Mandate
Straight from the Dynamics NAV help:

The Adjust Cost – Item Entries batch job adjusts inventory values in value entries so that you use the correct adjusted cost for updating the general ledger and so that sales and profit statistics are up to date. The cost adjustment forwards any cost changes from inbound entries, such as those for purchases or production output, to the related outbound entries, such as sales or transfers.

Basically, it will update your COGS and the Unit Cost on the item card to the correct cost that the item was brought in. Simply put, you HAVE to run this process BEFORE you do any meaningful financial and costing analysis.

It doesn’t matter what your costing method is. Doesn’t matter what your setup is. You HAVE to run this if you want to do inventory costing analysis in Dynamics NAV (Navision).

Here’s what the process looks like in RTC:

Adjust Cost - Item Entries RTC

 

Here’s what the process looks like in Classic Client:

Adjust Cost - Item Entries version 5.0

Note that the Adjust Cost – Item Entries will look different depending on the what version of Navision you use.

If you do not run this, no analyst in the world can begin to figure out what exactly the cost should be and address any incorrect inventory values you may have.

Side Effects
Note that if your company has never ran this before, or have not ran this in a long time, there may be detrimental affects on your inventory and COGS. Here’s a partial list of what you may encounter:

1. Your COGS on the G/L will go wild. The reason for this is that most companies, when they do not run the adjust cost, will use the general journal to “calculate” what the COGS should be. So when you do decide to let NAV do what it’s suppose to do, you will need to reverse these journal entries.

2. Your Inventory on the G/L will go wild. The reason is the same as above. The journal entries will need to be reversed.

3. Your item unit cost will go wild. The reason, again, is the same as above. A lot of times when you make an inventory journal adjustment to “take out” the inventory with the incorrect cost and positive adjust in the item with the correct cost, you are only doing it on the surface. The way NAV allocates and applies the inventory entries will most likely be different than what you adjusted.

Why Bother Going Though the Trouble?
Looking at the side affects, you may be wondering, why do I want to bother going through this process if I’m running fine as it is? The answer is simple, but it will reduce time and free up your company’s human resources to focus on important things.

Think about the manual spreadsheets and human memories that your salespeople need to figure out what cost the item should be before you can quote a competitive price to your customers. Think about the time it takes your internal accounting staff to figure out what your bottom line should look like on the financial statement. Think about if those times are freed, what other amazing things your employees can come up with to make your business even better.

Conclusion
A lot of companies will give their Trial balance to their CPA and let them make do the calculation. But realize that what they’re doing is just an estimate. It is NOT accurate. The CPA’s priority is to make the number seem reasonable so he/she can do your tax return and having paper trail if there’s an audit. His/her goal is not to get the accurate inventory cost.

Ask any good accountant, reconcilation is one of the most time consuming and useless tasks.

Ask any good salesperson, having a quote rejected because they cannot get the competitive price to the customer is not only a colossal waste of time on the company, but also for the customer. It reflects on the reputation of your company

Ask any good business owner, not having accurate profit and loss for each transaction… Well, you get the idea.

Let Dynamics NAV do its thing so it can help your business can do its thing.

Abnormal Item Charges

Overview
Dynamics NAV is a ERP software that’s built on best business practice. However, that’s not to say that the users of Navision operates on best business practice.

This post describes what I would like to call Abnormal Item Charges. Again, as with what I’ve described with Abnormal Posting Date, don’t bother looking it up on any GAAP dictionary or NAV website. It’s a term that I made up because I lack the vocabulary to think of a better name. English, afterall, is my second language.

What is Abnormal Item Charge?
Abnormal Item Charge are posted item charges that are applied to the open Purchase Order that has not been received or invoiced.

An Example
A lot of times when a company orders some items from factories, they will buy the finishes of the goods that you want to order, i.e. special metallic paint. Because of the competitiveness of factories, they will sometime offer the company to pay only partial (or no payment at all) until the products are successfully manufacturered. The special finishes they will have to pay now.

So at the time of the PO creation, the company will only have paid for the special metallic paint. The company wants to pay for the paint while allocating the cost of the paint to the inventory that they will possiblity receive in the future. So an item charge line is created on the PO and allocated the cost of the paint ot the purchase lines.

The Effects on G/L
As we discussed previously, this item charge will post to the following G/L accounts:
+ Inventory
– A/P
+ Item Charge G/L Account (Purchases)
– Direct Cost Applied

So the additional cost of the item is correctly accrued to the inventory value.

The Problem
The additional cost components of the item has been allocated and correctly posted to the G/L. The problem is, there’s no inventory for the additional cost to apply to.

This means that the value entries will be created, but it will not reflect on the inventory valuation report until the items are received.

The Solution
In version 6.0 (NAV2009), they put out a fix for this in codeunit 90. Now if you try to post an item charge when there are no item ledger entry, it’ll give an error. However, this solution causes problems for the business case described above.

The solution for this was to comment out the code in NAV2009, then create a report that captures the additional cost posted to the G/L that did not have the corrosponding item ledger. We call this report the Abnormal Item Charge report. It’s a pretty simple report. If your company has the same problem, let me know. I’ll send you the report.

EDIT: One of my colleagues reminded me that we can also use the Prepayment Functionality in the US version to handle this. Basically, we would set the item charge line prepayment percentage to 100. Doing this will post the transaction into the prepayment account instead of the inventory account.

Conclusion
Again, NAV is designed to be best practice. This cause in particular forces us to break that best practice because it makes business sense. Note that I don’t usually like to break NAV’s built in best practices, but in this situation, it was a frequent part of the some of our client’s business, especially when factories are competing for orders in a down economy.

Inventory Value to General Ledger Reconcilation in Dynamics NAV

I recently signed up to be a guest columnist on the Microsoft Dynamics NAV community site. I wrote an article with the list of common steps to finding and reconciling the difference between your inventory valuation and the inventory G/L account. The article is posted in the Dynamics NAV Community site hosted by Microsoft.

The article is found here:
https://community.dynamics.com/product/nav/navnontechnical/b/navguestcolumn/archive/2011/09/12/reconciling-inventory-value-to-your-inventory-general-ledger-account.aspx

Again, the focus is to save you time so you can go home on time.

Accounting Cost vs. True Cost

In Microsoft Dynamics NAV, when doing costing and profitability analysis, you need to differentiate between a transaction’s True Cost and Accounting Cost. Don’t bother looking up these terms in the manual, I made them up for a lack of better terms.

To better explain the difference between True Cost and Accounting Cost, we will use this example:

8/15/08 – Item A was received at 10 pieces for $2.00 each.
8/30/08 – 6 pieces of item A was sold for $5.00 per piece
9/1/08 – The vendor invoice for Item A is posted at $3.00 per piece
9/15/08 – The freight invoice came in and item charge is used to allocate an additional $1.00 per piece
9/20/08 – The rest of the 4 pieces of item A was sold for $5.00 per piece

Accounting Cost:
Assuming on 9/30/08, you’re asked to do a sales analysis for the month of August. When the costing is analyzed for the sales made on 8/30/08, the COGS that accounting recognize will be $2.00 per piece. This means that if we’re printing reports based on Value Entry posting date filter from 8/1/08 to 8/31/08, the profit margin would be 60% per piece. Not bad! For accounting, the cost is indeed $2.00 per piece since that’s the only amount that was recognized in that period. (Some companies makes an accrual on the G/L side for the expected cost of good sold, but that’s a separate topic). This number to management, of course, is incorrect.

True Cost:
In actuality, the cost of the item should be $4.00 per piece because each piece came in at $3.00 with an additional $1.00 in freight charges. The margin of the item should be 20%.

Another scenerio is you’re running the sales report from 9/1/08 to 9/15/08, you would show 0 quantities sold, but the cost recognized in that period would be $12.00 ($1.00 in the additional vendor cost + $1.00 in the freight cost * 6 pieces sold). If you did not check this report and you present this report to the management, be prepared field a load questions on the integrity of both you and the numbers.

Solution:
Both Accounting Cost and the True Cost are correct! Do not assume otherwise! It’s just a matter of how the user wants to look at the numbers. For accounting, they need the numbers to be recognized in the proper periods so the previous period numbers does not get changed. For management, they want to see the true cost of sales transaction. What to do?

As a simple rule, the Value Entry stores the accounting cost, the Item Ledger Entry stores the true cost. Since most NAV reports dealing with Contribution Margin uses Value Entry table, we typically remove those field from the report because they are misleading for everyone. We create separate reports using the Item Ledger Entry table taking the Cost Amount (Actual) since it rolls up all the costs associated with the sale transaction.

One thing to note when presenting the report off of the item ledger to the management, depending on when you post the vendor invoice and other landed cost charges, the profitability number will change. This means that, in our example, the profitability report ran on 9/1/08 will be different than the same report with the same filters ran on 9/20/08. However, in my experience, once you properly explain this concept to accounting and management, they will understand.

[EDIT] You can also use the Value Entry table for calculating True Cost. However, just filter on the Valuation Date instead of the Posting Date.

Abnormal Posting Dates

Abnormal posting date entries happens when the Posting Date of the receipt/shipments of inventory is after the Posting Date of the invoice. Why does this happen? You’ll need to ask your CFO or Controller. Typically, I found this more in return orders where the receipt of the product is a certain date, and the accounting department decides to post the credit memo in prior dates.

Either by mistake or intentional, if this happens, your Inventory Valuation will not tie to your G/L.

Here’s a step by step on how to replicate this problem, and how to solve this problem:
1. Create a sales order with posting date of 11/01/08
2. Post the Shipment with a posting date of 11/01/08
3. Change the posting date to 09/01/08
4. Post the invoice
5. Run your inventory valuation report as of 9/30/08
6. Notice that the item is not taken out? But if you look at your G/L, the inventory value is taken out.

This will work for purchase side as well. It doesn’t matter how to do it either using warehouse shipment/receive, the underlying process will net you the same problem.

Usually, problems like this won’t come up until the user is trying to do month end and gets really frustrated on why their inventory doesn’t tie to G/L. Unfortunately, there’s no easy way to reverse this transaction after it’s posted, the only way is to run a report at the end of the period to see what transactions have this “Abnormal Posting Dates”.

Here’s a report that will give you all the transactions that have abnormal posting dates. If you take this amount, add/subtract it to your inventory valuation report, it will be equal to the G/L inventory.

To run this report, just put the period you want to close on the Posting Date field. For example, if you’re trying to close November 2008, then your Posting Date filter should be 11/1/08..11/30/08.

Here’ the link for the report:

http://www.mibuso.com/dlinfo.asp?FileID=920

Why you have Negative Inventory Value with 0 Quantity – Navision 3.7b to Navision 5.0

Here are a couple of reasons why you would get inventory value if you have 0 quantities when you print Inventory Valuation report and Inventory to G/L Reconcile report as of a certain date.

Scenario 1:

If the adjust cost is processed on 7/31/07, and the Allow Posting From was 7/1/07 on General Ledger Setup, the following would occur:

 6/15/07 – Purchase Receipt – $10
6/28/07 – Ship and Invoice – $10
7/15/07 – Purchase Invoice – $12
7/31/07 – Additional Cost for the sale made on 6/28/07 – $2

In this case, when you print the inventory valuation as of 6/30/07, the inventory quantity would be 0 and the inventory value would be 0.

Scenario 2:
If the adjust cost is processed on 7/31/07, and the Allow Posting From was 6/1/07 on General Ledger Setup, the following would occur:

6/15/07 – Purchase Receipt – $10
6/28/07 – Ship and Invoice – $10
7/15/07 – Purchase Invoice – $12
6/28/07 – Additional Cost for the sale made on 6/28/07 – $2

In this case, when you print the inventory valuation as of 6/30/07, the inventory quantity would be 0 and the inventory value would be -$2.00.

Explaination
Navision will automatically post the additional cost to the original invoicing entry because that’s where the cost originally applies to. If Navision detects that the Allow Posting From is before the adjusting date, then it will use whatever the Posting Date is when the adjust cost is ran.

Scenario 3:
If the adjust cost is processed on 8/31/07, and the Allow Posting From was 7/1/07 on General Ledger Setup, the following would occur:

6/15/07 – Purchase Receipt – $10
6/28/07 – Ship and Invoice – $10
7/15/07 – Purchase Invoice – $12
8/31/07 – Additional Cost for the sale made on 6/28/07 – $2

In this case, when you print the inventory valuation as of 6/30/07, the inventory quantity would be 0 and the inventory value would be 0. When you print the inventory valuation as of 7/31/07, the inventory quantity would be 0, but the inventory value would be $2.00.

Explaination
Navision will automatically post the additional cost to the original invoicing entry because that’s where the cost originally applies to. If Navision detects that the Allow Posting From is before the adjusting date, then it will use whatever the Posting Date is when the adjust cost is ran. In this example, since the Adjust Cost process posting date is 8/31/07, it will use this date as the Posting Date for the adjusting entry.