Skip to content

Confessions of a Dynamics 365 Business Central Consultant

My journey in the world of consulting using Microsoft Dynamics 365 Business Central

Tag: CPA

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

Overview

Sometimes, how a computer system does accounting will be different than how traditional accountants does accounting.

One of the subjects that always come up for debate is how COGS (Cost of Goods Sold) is calculated.

The Postings

In Dynamics 365 Business Central, when a purchase of inventory is made, 4 General Ledger accounts are hit:

  • Inventory
  • A/P
  • Purchases
  • Direct Cost Applied

When the items are sold, 4 General Ledger accounts are hit:

  • Inventory
  • A/R
  • Sales
  • COGS

In this case, the COGS is automatically calculated and posted to your COGS account. How Dynamics 365 Business Central calculates COGS is based on your Costing Method in conjunction with running the Adjust Cost – Item Entries process.

The COGS posted by Business Central is ALWAYS correct. Don’t assume otherwise!

How Accountants Calculate COGS

Typically, when a CPA (Certified Public Accounts) report COGS, it’s calculated and displayed based on the following formula:

Beginning Inventory
+ Purchases (for the period)
– Inventory Adjustment (for the period)
– Ending Inventory
= COGS (Cost of Goods Sold)

This formula gives all sorts of problems when trying to produce a proper financial statement to the CPA to do their tax returns in Business Central.

What ends up happening is that company will believe they need to purchase all sorts of fancy reporting tools when the out of the box Account Schedules functionality will work just fine.

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

Let’s say you have the following Chart of Accounts:

14300 – Inventory
53100 – Purchases
53700 – Direct Cost Applied
53400 – 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 – A050 – A060)

After you setup the accounts schedule, you can use the Overview functionality to check your work.

In this example, I used the standard Business Central COGS account to check my work.

COGS Account Schedule Check

Boom! The COGS I calculated and the standard BC COGS matches! Good to go!

What was the point for lines A040 to 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 memo, the following will happen:

+ $8.00 Accounts Payable
– $8.00 Inventory (assuming this is what’s on the Unit Cost (LCY) field on the purchase credit memo line)
– $8.00 Purchases
+ $8.00 Direct Cost Applied  (assuming this is what’s on the Unit Cost (LCY) field on the purchase credit memo line)

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. That’s the actual price paid for the item to the vendor so 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.

Conclusion

The formula gets a little more involved when you start involving Item Charges (landed cost assignments). You can play around with the account schedule to see if you’re able to get it going. If not, reach out!

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 Business Central’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 reconciliation headache between 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.

Author Alex ChowPosted on February 15, 2021February 17, 2021Categories implementation, reportingTags account schedule, COGS, CPA, posting3 Comments on How to calculate COGS on Account Schedules (The CPA Way)

Recent Posts

  • Independence with Dynamics 365 Business Central online
  • Extension Development for Non-developers for Dynamics 365 Business Central
  • Export to Excel in Dynamics 365 Business Central v19
  • Permission Setting – Preventing Malice vs Accidents
  • How to Enter Beginning Bank Balance for Dynamics 365 Business Central v18 and Above

Blogroll

  • AP Commerce, Inc.
  • Dynamics NAV Team Blog
  • Dynamics User Group
  • Freddy's Blog
  • Mibuso – Your Favorite Knowledgebase
  • Microsoft Dynamics Community

Categories

  • beginning balance
  • costing
  • database
  • Dynamics 365
  • Dynamics 365 Business Central
  • Dynamics NAV
  • E-Ship
  • ethics
  • implementation
  • Inventory
  • manufacturing
  • Microsoft
  • MPN
  • Other
  • performance
  • permission
  • pricing
  • programming
  • reporting
  • security
  • Self Sabotage
  • Uncategorized
  • upgrade
  • Warehouse Management

Archives

  • March 2022
  • January 2022
  • December 2021
  • November 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • August 2020
  • July 2020
  • May 2020
  • October 2019
  • September 2019
  • July 2019
  • May 2019
  • January 2019
  • November 2018
  • October 2018
  • July 2018
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • November 2017
  • September 2017
  • May 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • October 2016
  • September 2016
  • August 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • December 2015
  • October 2015
  • July 2015
  • June 2015
  • May 2015
  • March 2015
  • January 2015
  • October 2014
  • September 2014
  • June 2014
  • April 2014
  • January 2014
  • December 2013
  • October 2013
  • September 2013
  • July 2013
  • June 2013
  • May 2013
  • January 2013
  • November 2012
  • October 2012
  • September 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • October 2010
  • July 2010
  • June 2010
  • February 2010
  • January 2010
  • November 2009
  • September 2009
  • August 2009
  • July 2009
  • June 2009
  • December 2008
  • June 2008
  • April 2008
  • February 2008
  • January 2008
  • November 2007
  • October 2007
Confessions of a Dynamics 365 Business Central Consultant Privacy Policy Proudly powered by WordPress