Average costing Deep Dive in Business Central

For many years Average Costing were extremely difficult to explain to the customers. It helped a lot with the cost breakdown that was launched with Dynamics NAV, where it was possible to click on the AssistEdit on the cost price to get an overview of the movements that affected the price, but that still does not explain all.

Microsoft Learn has this overview of the different costing methods:

This means that the Average Costing method will be based on the total cost of items replenished in a period divided by the total number of items replenished in the same period.

The Microsoft Learn Best Practices section states when to use average cost:

And this might be true, but it is not the whole truth. The Design Details also gives a couple of warnings:

Valuing inventory decreases as a weighted average would be straightforward in several scenarios:

• Purchases are always invoiced before sales.
  
• Postings are never backdated.
  
• You never made mistakes.
  

I would like to add a couple of warnings of my own:

• Consider Average costing if there are a significant number of transactions on each item.
• Using Item Charge with average costing items will generate lots of adjustment value entries
• Running the Adjust Cost – Item Entries automatically can slow down the inventory processes significantly

However, let’s start with setting the system up, and where better than to start in Inventory Setup.

The Default Costing Method only defines, which Costing Method should be suggested to every new item that is created. Except, of course, if a template is being used for creating items, which by the way is almost always.

It is possible to utilize different costing methods for different types of items, but new costing methods cannot be introduced during the fiscal year without advertising it in the yearly fiscal report and for listed companies even the relevant authorities.

Average Cost Calc. Type Defines if the Average cost should be calculated for each item overall or individually for the items in each location or for each variant.

Average Cost Period    Defines the period to be used for the calculation:

• Day
• Week
• Month (which will be the first example)
• Accounting Period

Let’s make an example to show the consequences of these settings.

Firstly, I create a new average cost item:

The item is purchased and sold many times during January:

After purchasing the Items on January 7^st and selling the items on January 9^th the cost price is 10:

Very simple and looking at the transactions is equally simple:

Even the value entries are simple:

Then after the next of purchase and sales have occurred, the cost price is now 11

And the transactions look like this:

But now the Cost Amount actual has changed for my sale on January 7^th, Let’s check the value entry on that:

An adjustment value entry has been added to the original sale.

Adding all purchases and sales shown above, the cost price of the Item is 11,20 the breakdown looks like this:

And the value entries have multiplied:

This of course is made running the Adjust Cost Item Entries always and Post Inventory to G/L automatically.

In order to reduce the number of value entries, the Adjust Cost Item Entries could be changed to run once a month but that would still generate one value entry for each sales transaction.
This might not seem that bad but let me show a real-life example:

As you might realize, this is only the top of the iceberg, and the total number of sales transactions to be adjusted in December 2012 for this one item was 1419.

Then what happens if an invoice with an Item Charge is received at the end of January related to the purchase on the January 7^th:

The Purchase Invoice is simple enough, but the cost of all the sales transactions have been altered as well:

And what happens if the item charge arrived next month e.g., February 15^th:

The costs of the sales are still adjusted with the same date as the sales:

Whereas the value entry of the Item Charge has the correct date:

Why is that and can that be prevented with Inventory Periods or by adjusting the Allow Posting From in the General Ledger Setup?

Or closing the Inventory Periods:

Then the value entries will be moved to the correct month, but they will still affect all the sales:

Average Cost Period per Day

Then what if we use Average Cost Period per Day?

Let me try to change the Average Cost Period to be per Day:

I create a new item:

Then I perform the same purchases and sales than I did previously:

Now the unit cost will reflect the actual cost, also if there are multiple replenishments every day like in production. The Breakdown look like this:

And the number of value entries is reduced significantly:

An Item Charge Invoice for 100 at the end of the months will have this affect:

Which will only affect the receipt on the first date.

Conclusion

Does average costing have a purpose?

       Definitely!

Are there limitations to when you want to use average costing?

       Definitely!

I am not to tell you, if your company should use average costing, that is up to some ones that have more stars on the shoulders than I.

I just say; be aware of the limitations of average cost:

• Consider that average costing generates lots of extra value entries
• Consider that average costing can make the Adjust Cost – Item Entries extremely slow, which again blocks the value entry table so that simultaneous operations are not possible
• Consider average costing at all if running 24/7 operation in the company
• Consider average costing if purchases are often invoiced before sales.
• Consider average costing if postings are often backdated.
• Consider average costing if mistakes are often made.
• Consider average costing if Item Charges are often used.