Average Inventory Costing in Dynamics 365 Business Central

The Silverware Team | December 29, 2020

Dynamics 365 costing methods involves more than just average costing in Dynamics 365; inventory costing methods, standard costing, average cost method, and FIFO inventory costing method all play a part in an ERP system that is driven by accounting standards.

Average Costing in Dynamics 365

In Business Central, the inventory costing method is set on the Costs & Posting FastTab of the Item Card:

Average Costing in Dynamics 365 example

The costing method can be setup to default for new items on the Inventory Setup Page:

Inventory Setup Page example

In addition, there are two setups related to the average costing method on Inventory Setup:

  • Average Cost Calc. Type – This specifies how granular the average cost calculation should be. If the Item option is selected, the average will be based on all items with that item number. If the Item & Location & Variant option is selected, then the item’s average will be broken down by Item/Location/Variant combination.
  • Average Cost Period – This is another measure of granularity. It specifies over what period the average cost should be calculated. The options are Day, Week, Month, and Accounting Period. The Day calculation is the most granular because an average will be computed and applied for each day. Accounting Period is the least granular because the average will be computed based on the entire years’ worth of purchases.

Costing Concepts in Business Central

Business Central separates inventory transactions into two general types:

  • Inbound Entries: establishes the cost basis for inventory held on the balance sheet
  • Outbound Entries: where cost flows to the income statement as cost of goods sold

Examples of Inbound Entries are:

  • Positive Adjustments
  • Purchases
  • Output

Examples of Outbound Entries are:

  • Negative Adjustments
  • Sales
  • Consumption

Business Central keeps two ledgers to track inventory:

  • Item Ledger Entries: tracks item quantity entries
  • Value Entries: tracks inventory value entries

Value Entries are created to support each Item Ledger Entry. The inventory value can be seen on the Item Ledger Entry by adding together the Cost Amount (Expected) and Cost Amount (Actual) fields. Drilling down on either field will open the Value Entries Page.

A separate ledger is needed to track inventory value because there are several factors that can change inventory value, such as revaluations, freight charges or other landed costs, and any difference between expected and actual invoicing costs. All changes to an item ledger entry’s value are tracked as Value Entries.

As soon as an item is brought into inventory, the cost basis is determined. When it leaves inventory, the system leverages the costing method to determine the cost of the outbound entry by applying the inbound entry to the outbound entry.

inventory ledger example
Source: https://docs.microsoft.com/en-us/dynamics365/business-central/design-details-inventory-posting

Average Cost Flow Example in Business Central

Inventory Setup:

Average Cost Flow Example

When a pair of sunglasses is purchased on 4/6/2021 for $100 in Business Central:

  • An item ledger entry is created to track the increase in quantity
  • A value entry is created to track the value of the item ledger entry
  • A vendor ledger entry is created to track the payable
  • The following accounts are hit in the general ledger:
    • Inventory Posting Group: Inventory Account
    • General Posting Group: Direct Cost Applied Account
    • General Posting Group: Purch. Account
    • Vendor Posting Group: Payable Account

Note: Some users choose to set the Direct Cost Applied and Purch. Account to the same account to avoid an ever-increasing debit balance in one account that offsets an ever-increasing credit amount in another account

Item Ledger Entry

Item Ledger Entry example

Value Entry

Value Entry example

Vendor Ledger Entry

Vendor Ledger Entry example

General Ledger Entries

General Ledger Entries example

In order to demonstrate the flow of cost through the system, additional sunglasses are purchased on the following days for the following amounts:

General Ledger Entries examples

Running the inventory valuation report for Item 1010 on 4/14/2021 gives the following inventory valuation for the balance sheet:

inventory valuation report

If a pair of sunglasses is sold on 5/1/2021, the following entries will be made:

  • An item ledger entry is created to track the decrease in quantity
  • A value entry is created to track the value of the item ledger entry. Value is determined based on the costing method. In this case, it will be the Average costing in Dynamics 365 method; therefore, the cost will be averaged between the open ledger entries based on the Average Cost Calculation Type and Average Cost Calculation Period.
  • A customer ledger entry is created to track the receivable
  • The following accounts are hit in the general ledger:
    • Inventory Posting Group: Inventory Account
    • General Posting Group: Cost of Goods Sold
    • General Posting Group: Sales Account
    • Vendor Posting Group: Receivables Account

When the Sales Order is created, it will bring in the Unit Cost that was on the Inventory Valuation Report:

Inventory Valuation Report

The value populated in the Unit Cost ($) field for the Item is used as an initial expected cost for the value for Cost of Goods Sold. The costing method is applied upon posting. When the document is posted, the system will immediately adjust the cost to apply the correct Average Cost valuation if Automatic Cost Posting is toggled On (“True”) and Automatic Cost Adjustment is set to a value other than Never in Inventory Setup. Otherwise, the cost will be adjusted when the Adjust Cost – Item Entries process is manually run by an end user or by a scheduled job process. It is recommended to manually run Adjust Cost – Item Entries process as a part of month-end close to ensure any pending cost adjustments are posted in the appropriate period.

Item Ledger Entry

Item Ledger Entry

Value Entry

Value Entry example

Customer Ledger Entry

Customer Ledger Entry example

General Ledger Entries

General Ledger Entries example

Cost of Goods sold initially posted as $105.75, which is the average cost of the items on hand for Item 1010. The average cost calculation can be viewed from the Item Card by drilling down on the Unit Cost field:

Item Card example

Inventory Valuation report

Running the Inventory Valuation report as of 5/1/2021 will show that it is now $105.75 less:

Inventory Valuation report

The costing period restricts how the average is computed:

costing period table

The Average Cost Calculation Period is Month. Therefore, all the inventory purchased during the month is averaged together. Since nothing was sold in the month of April, the average of $105.75 was rolled forward into May with a weight of 4 units.

However, if a quantity of 2 had been sold in April and 4 more units had been purchased in May, then:

  • April’s sales would still leave inventory with an average cost of $105.75 per unit for cost of goods sold
  • May’s sales would need to be adjusted based on the purchase price of the new units

average cost calculation overview

The May sale from the example above will have its cost of goods sold adjusted from $105.75 to $103.53 to account for each of the purchases made in May:

Item Ledger Entry

Item Ledger Entry example

Value Entry

Value Entry example

Customer Ledger Entry

Customer Ledger Entry example

General Ledger Entries

General Ledger Entries example

Average Costing in Dynamics 365 Concept Question

The following item ledger entries are made in Business Central for an item:

ledger entries table

Average Cost Calculation Period is Monthly.

Average Cost Calculation Type is Item.

[WPSM_AC id=5332]

 

Contact Us

  • This field is for validation purposes and should be left unchanged.