How to calculate COGS on Account Schedules (The CPA Way)

When a purchase of inventory is made, 4 accounts are hit:
Direct Cost Applied

When the items are sold, 4 accounts are hit:

In this case, the COGS is automatically calculated and posted to your COGS account. The COGS posted by NAV is ALWAYS correct. Don't assume otherwise.

Typically, when a CPA (Certified Public Accountants) report COGS, it's calculated and displayed based on the following formula:
Beginning Inventory
+ Purchases
- Inventory Adjustment
- Ending Inventory

It has brought to my attention that this formula gives all sorts of problems with companies using NAV to use the account schedules and give a proper financial statment to the CPA's desire.

This calculation is actually pretty easily defined in the NAV account schedules.

Let's say you have the following Chart of Accounts:
12000 - Inventory
58000 - Purchases
58050 - Direct Cost Applied
58200 - Inventory Adjustment

The way to set the formula to give the proper COGS based on the CPA formula on the Account Schedules is the following:
A010 - Beginning Inventory (Row Type = Beginning Balance)
A020 - Purchases (Row Type = Net Change)
A030 - Inventory Adjustment (Row Type = Net Change) Set it to NOT show
A040 - Direct Cost Applied (Row Type = Net Change) Set it to NOT show
A050 - Inventory Adjustment (Formula = A020 + A030 + A040)
A060 - Ending Inventory (Row Type = Balance at Date)

B010 - COGS (Formula = A010 + A020 + A030 - A050 - A060)

Here's a screenshot of the setup:

What was the point for A040..A060? (Read only if you're interested in the accounting and costing aspect of it)

The reason why we need to set this up is because of purchase returns. When items are returned using the purchase return order (or purchase credit memo), the cost being used to return the product is deducted based on your costing method, NOT what you typed in as the direct unit cost on the purchase return/credit.

Confused? Don't be. Let me explain, when you invoice a purchase order for $10.00 for item A, the following happens:
- $10.00 Accounts Payable
+ $10.00 Inventory
+ $10.00 Purchases
- $10.00 Direct COst Applied

Now, suppose you need to return this product to the vendor and the vendor is only willing to give you credit for $8.00. When you post the credit, the following will happen:
+ $8.00 Accounts Payable
- $8.00 Inventory
- $8.00 Purchases
+ $8.00 Direct COst Applied

Additional, NAV will make these 2 entries:
- $2.00 Inventory
+ $2.00 Direct COst Applied

The extra $2.00 entries are posted because the cost of the item is $10.00. And that's the cost it's should be relieved from inventory. So you can say the sum of Purchases and Direct Cost Applied is the difference between vendor returns and the vendor purchases. For most companies, these amounts are lumped together to be displayed under inventory adjustment on the income statement. You can separate it out as a different line item on your income statement if you wish.

It doesn’t matter if you turn on Exact Cost Reversing or if your client says “this doesn’t happen, we always use the same cost that we bought it at”. ALWAYS do this.

This example assumes that you set direct posting to NO on the G/L accounts described. As you understand the concept behind this, you can easily incorporate Item Charges, WIP, etc into this formula. Basically, everything that gets accrued into Inventory needs to be part of this formula.

Most companies would want to allocate additional line items into the calculation of COGS without accruing the expenses to the inventory. This is fine, you'll just need to add the other costs into the formula. However, the total of your COGS will be different than NAV's COGS.

Separate from the point of this post, as a rule of both thumbs, you always turn off the Direct Posting field on the inventory accounts. Always. ALWAYS. No exceptions.

If you want to make G/L entries to the inventory account, create a separate Inventory G/L accounts and combine them together on your Account Schedules to have them appear as one when you present it to your CPA or auditors. Making G/L entries directly to the inventory accounts is a one way ticket to reconcilation headache betweeen the Inventory Valuation and General Ledger.

If everything is setup properly, you'll find that the COGS formula you setup will match, to the penny, the COGS account on your Chart of Account.

Comment List
  • This is a terrific explanation of the flow of inventory thru to a sale.  I wish my company accounting staff could understand the concept.  Having been on a QuickBooks system he is overwhelmed by the complexity of NAVISION.  

  • Yes I know. And the "how to" is really appriciated. It should really be a "must read" for all NAV consultants. Yes actually for NAV developers also, as understanding COGS is a very important corner stone in understanding NAV.

  • Well, the account schedules hasn't changed much since version 1.x. So the steps described here will apply to version 1.x foward.

    Except instead of the Direct Cost Applied account, there's only the Inventory Adjmt. account.

  • I guess NAV will never have a version where this is getting really easy! Ever since I started with NAV in 1991 inventory adjustments has always been "great fun" and lot of time spend on the account of the customer!

    But thank you for your description.