How to perform inventory reconciliation in Business Central

Accounting and finance professionals know the General Ledger (G/L) should match the Inventory Subledger at all times, however there are instances when the two can get out of sync and need to be reconciled. There are many reasons why there could be a difference between the numbers, and it’s important that you take the proper steps to identify why.

Dynamics 365 Business Central is an all-in-one business management solution with robust inventory management features. This post includes a detailed demonstration of how to perform inventory reconciliation in Business Central, and reviews common causes of differences between the inventory account on the Chart of Accounts and the inventory value of the Subledger.

Topics covered in this post:

1. Inventory Setup page fields
2. Posting groups, general posting groups, and item cards
3. The three types of entries posted that determine the cost of an item
4. The different inventory reports
5. How to check if your inventory needs to be reconciled
6. Different methods of performing inventory reconciliation in Business Central
7. Detecting system issues

There are six fields that specifically influence the value of inventory on the Inventory Setup page.

Automatic Cost Posting:

If this field is set to Yes, then Business Central will automatically post to the inventory account, adjustment account, and COGS account in the G/L rather than waiting until the Adjust Cost/Post Cost to G/L functions are run. It is still recommended to run the Adjust Cost/Post Cost to G/L functions periodically even if this field is set to Yes. The benefit of setting the field to No is performance related. When Business Central is adjusting cost it locks certain tables to preserve the integrity of the data and can interrupt daily business if a large amount of transactions are being processed. If this is the case, it may be beneficial to set Automatic Cost Posting to No and run the process every evening when users are not actively using Business Central.

Expected Cost Posting to G/L:

When inventory is received and neither invoiced nor shipped, Business Central treats the value as Expected Cost since the cost can be changed before invoicing. If Expected Cost Posting is set to Yes, then Business Central will post these expected costs to interim accounts in the G/L to get an estimate of the cost of items before invoicing the purchase or sales document.

Automatic Cost Adjustment:

Specifies whether to and when to adjust cost changes automatically at the time inventory transactions are posted. For example, if you are using average cost and the cost of the item needs to be averaged, this field will determine when to take the average as of the work date and has the following options:

• Never – Costs are not adjusted when posting
• Day – Costs are adjusted if it is within one day of the work date
• Week – Costs are adjusted if it is within one week of the work date
• Month – Costs are adjusted if it is within one month of the work date
• Quarter – Costs are adjusted if it is within one quarter of the work date
• Year – Costs are adjusted if it is within one year of the work date
• Always – Costs are always adjusted

Note: If Automatic Cost Adjustment is set to Never, then the Adjust Cost/Post Cost to G/L functions will need to be run in order to adjust costs to their proper values.  It is typically recommended to set Automatic Cost Adjustment to either Never and run it manually or through a scheduled task every evening or set it to Always and have the system adjust in real time.

Average Cost Calc. Type:

If an item’s Costing Method is set to Average Cost, this field determines how the Average Cost Calculation is broken down. It can be broken down per Item No. or Item & Location & Variant. Note: It is typically not recommended to change the Average Cost Calc. Type once entries have been posted, as changing this option will recalculate the Average Cost on all items for all open periods.

Average Cost Period:

If an item’s Costing Method is set to Average Cost, this field determines the scope of the Average Cost Calculation. The average cost period can be per day, week, month, or accounting period.

Prevent Negative Inventory:

This field indicates the value in the Prevent Negative Inventory field on individual item cards. The values can be Yes or No. It is recommended to set this field to Yes since if inventory is sold to the negative, Business Central cannot pair the outbound entry with an inbound cost layer.

Posting groups are used to determine which G/L Account(s) an entry should post to so that financial data is reported accurately. The inventory posting setup is per Location/Inventory Posting Group Combination, and specifies which G/L Accounts transactions should post to.

• Inventory Account: The G/L Account that holds actual inventory value for that location/inventory posting group combination.
• Inventory Account (Interim): The G/L Account that holds expected inventory value for that location/inventory posting group combination. Transactions posted to this G/L Account will reflect the expected cost of inventory received but not invoiced. When the inventory is invoiced the expected amount is reversed out and the actual amount is posted to the Inventory Account.

The Inventory Posting Group is specified on the Costs & Posting Tab of the Item Card:

The General Posting Setup is per Gen. Bus. Posting Group and Gen. Prod. Posting Group combination, and specifies which G/L Accounts transactions should post to. The Gen. Bus. Posting Group is specified on either the Vendor or Customer card, while the Gen. Prod. Posting Group is specified on the Item card only.

Now we will illustrate how some of the accounts are utilized for inventory transactions. Both examples assume expected cost posting is turned on. Please note that some of the named accounts are from different posting setups like the Inventory, Vendor, or Customer. All of the posting setups come into play with inventory transactions.

Example 1: A Purchase Order is created and 1 unit is Received into inventory at 5 dollars, then that unit is Invoiced at 6 dollars. The following entries are created in the General Ledger:

Purchase Receipt:

Purchase Invoice:

Example 2: A Sales Order is created and 1 unit is Shipped from inventory at 5 dollars, then that unit is Invoiced at 6 dollars. Unit Cost was 2 dollars. The following entries are created in the General Ledger:

Sales Shipment:

Purchase Invoice:

The Item Card is where all of the general setups come into play. When transactions are done in the system, Business Central will reference the selections on the individual item in order to decide how to treat that item. Since this post focuses on inventory reconciliation, we are going to focus on specific fields in the Invoicing FastTab.

Below is an overview of the fields shown on the Item Card above:

Costing Method: This field specifies how the item’s cost is calculated. There are five costing methods available: FIFO, LIFO, Specific, Average, and Standard.

Cost is Adjusted: This field indicates whether the cost is adjusted for the costing method calculation. If the field is not checked the Adjust/Post Cost to G/L process needs to be run.

Cost is Posted to G/L: This field indicates whether the cost for the item’s Subledger has been posted to the G/L. If the field is not checked the Adjust/Post Cost to G/L process needs to be run.

Standard Cost: Specifies the unit cost that is used as a standard measure, this field is required if the Costing Method is set to Standard.

Unit Cost: Specifies the unit cost of a single item, how this field is calculated depends on the Costing Method. If the costing method is Standard, the field is greyed out. If the costing method is FIFO, LIFO, Specific, or Average, then: Unit cost = inventory value of items on hand / quantity on hand.

Gen. Prod. Posting Group: This field specifies which Gen. Prod. Posting Group Business Central will utilize when determining which G/L accounts to post to for inventory transactions. The Gen. Bus. Posting Group on the Vendor/Customer Card will determine the second part of the matrix, if you remember the matrix combines both to determine which general ledger accounts to post to:

Inventory Posting Group: This field specifies which Inventory Posting Group Business Central will utilize when determining which G/L Accounts to post to for inventory transactions. If you will remember, the inventory posting groups are setup with a Location/Posting Group Matrix, therefore, it is possible to break down your inventory into different G/L Accounts not only per an inventory classification, but an inventory classification per location:

In order to determine the cost of an item there are three types on entries posted: Item Ledger Entries, Value Entries, and General Ledger Entries. Below is an overview of each entry and its respective fields.

#1 Item Ledger Entries

Each time there is an inventory transaction, there are two transactions posted in order to track it: 1) the Item Ledger Entry and 2) Value Tracking. The first transaction can be considered the quantity tracking. It is posted in the Item Ledger Entry table as a record of the inbound and outbound entries for a specific item. Item Ledger Entries are created at the time of the inventory movement not at the time of invoicing. For example, an Item Ledger Entry is created when a Purchase Order is received or a Sales Order is shipped.  There is no Item Ledger Entry created when either document is invoiced.

For the purposes of inventory reconciliation, we draw attention to certain fields on the Item Ledger Entry field page:

Entry Type: Shows which type of transaction the entry was created from:

• Purchases and Positive Adjustments represent an increase in inventory; however, if undo receipt is used the Type of Purchase will be displayed for the inventory decrease and the “Correction” field will be checked
• Sales and Negative Adjustments represent a decrease in inventory, however, if undo shipment is used the Type of Sale will be displayed for the inventory increase and the “Correction” field will be checked
• Transfer represents transfers and reclassifications of inventory
• Consumption and Output describe manufacturing processes
• Assembly Consumption and Assembly Output

Document Type: Describes the source of the entry

Document No.: Describes the Document Number assigned to the document that generated the entry. It is important to note that when an item is received the document number of the receipt is assigned to the item ledger entry, however the document number assigned in the Inventory Account of the General Ledger will be that of the Invoice. This is because the inventory movement takes place on receipt which is what is represented on the Item Ledger.

Item No.: The item number the entry influences.

Quantity: The number of units in the item entry

Quantity Invoiced: Specifies how many units of the item have been invoiced. For example, if you receive 5 units and invoice 2 the Quantity field will display 5 and the Quantity Invoiced field will display 2.

Quantity Remaining: Specifies how many units of the item associated with the Item Ledger Entry are remaining to be applied. For example, if 5 were purchased and 3 sold the Remaining Quantity would be 2.

Sales Amount (Actual): Displays the amount the cost layer has sold for, the sales amount is determined in detail through the Value Entries. You can right click and drill down on the field in order to see how Business Central determined the number.

Cost Amount (Actual): Displays the actual cost realized of the Item Ledger Entry. Costs are considered actual costs when they are invoiced. Like the Sales Amount (Actual) field, the Cost Amount (Actual) field is determined through the Value Entries and you can see the entries which determine the cost by right clicking and drilling down on the Cost Amount (Actual) field.

Cost Amount (Expected): Displays the expected cost of the Item Ledger Entry. Costs are considered expected costs when they are received or shipped. Like the Sales Amount (Actual) field, the Cost Amount (Expected) field is determined through the Value Entries and you can see the entries which determine the expected cost by right clicking and drilling down on the Cost Amount (Actual) field.

Open: Specifies whether the cost layer has been fully applied. Business Central applies inbound and outbound entries against each other for example, if you purchase 1 unit of Item A for $5 and sell 1 unit of Item A for $7 Business Central has functionality to tie the two item ledger entries together so that the cost can be appropriately applied and posted to the General Ledger’s Cost of Goods Sold Account.

Completely Invoiced: Shows if the entry has been fully invoiced.

Entry No.: Shows the entry number that the program has given the entry. Every item ledger entry has a unique entry number, which is assigned when it is posted. This can be useful when trying to determine if entries have been backdated. For example, if you have an item ledger entry with a posting date of 1/1/2022 and an Entry No. of 112 and you have an item ledger entry with a posting date of 12/15/2021 and an Entry No. of 816 you can determine that the entry with a posting date of 1/1/2022 was posted first because it has a smaller entry number.

#2 Value Entries

The second transaction posted when there is an inventory transaction is the value tracking. It is posted in the Value Entry Table as a record of cost and adjustments to a specific Item Ledger Entry. There can be multiple Value Entries per Item Ledger Entry, and it can be considered a Subledger for each Item Ledger Entry. Business Central utilizes Value Entries in order to keep records in a simple, easy-to-read format.

Value Entries can be accessed by selecting Find Entries on Documents or by drilling down on the Cost Amount/Sales Amount fields on an Item Ledger Entry. G/L entries are created on the basis of Value Entries, therefore, Value Entries are important tools to utilize when reconciling inventory.

At the time of receipt or shipment, Value Entries are created for Expected Cost. These entries are created with a Document No. to match the receipt or shipment number.  Note that Value Entries for Expected Cost are always created regardless of whether Expected Cost Posting to G/L is checked in Inventory Setup. That option only controls whether G/L entries will be created based on the Expected Cost Value Entries.

At the time of invoice, Value Entries are created to reverse Expected Cost and to record Actual Cost.  These entries are created with a Document No. to match the invoice number.

Below is an overview of the fields shown above:

Item Ledger Entry Type: The Entry Type that is specified on the related Item Ledger Entry.

Entry Type: Describes the type of Value Entry:

• Direct Cost
• Revaluation
• Rounding
• Indirect Cost
• Variance (only posted for items using the standard costing method)

Adjustment: A checkmark is placed in this field automatically when an adjustment is made to an item’s value during the Adjust Cost – Item Entries batch job. An example of an adjustment would be when adjusting an item’s cost to average if the average costing method is used or if an item’s cost was changed after a Sales Order was Invoiced.

Sales Amount (Actual): Displays the amount the associated Item Ledger Entry has sold for.

Cost Amount (Expected): Displays the calculation for the amount the underlying item cost layer has cost. Expected costs are those that have been received or shipped but not invoiced.

Cost Amount (Actual): Displays the calculation for the amount the underlying item cost layer has cost. Actual costs are those that have been invoiced.

Cost Posted to G/L: Specifies the amount of the actual cost which has been posted the G/L. Expected costs posted to the G/L are not reflected in this field.

Item Ledger Entry Quantity: Specifies the quantity from the Item Ledger Entry associated with the Value Entry.

Valued Quantity: Specifies the quantity valued on the specific Value Entry line.

Invoiced Quantity: Specifies how many units of the item are invoiced on the invoice that the Value Entry line represents. Each partial invoice posting is represented by one Value Entry line.

Cost per Unit: Contains the cost for one base unit of the item in the entry, and is calculated as Cost Amount (Actual) divided by Valued Quantity.

Item No.: The item number the entry influences.

Gen. Bus. Posting Group / Gen. Prod. Posting Group: Specifies which matrix in the General Posting Setup the entries utilized to determine which General Ledger Account to post to.

Source Code: Specifies where the entry was posted. In order to view the Source Code Setup, you can go to Departments / Administration / Application Setup / Financial Management / Trail Codes and click Source Codes.

Entry No.: Shows the entry number that the program has given the entry. Every Value Entry has a unique entry number, which is assigned when it is posted.

Item Ledger Entry No.: Shows the assigned Item Ledger Entry No. and can be used to tie the Value Entries to the Item Ledger Entries.

#3 General Ledger Entries

When inventory transactions are posted, the quantity and value changes to the inventory are recorded in the Item Ledger Entries and the Value Entries, respectively. The third step is to post the inventory values to the inventory accounts in the G/L. There are two ways to do this:

1. Manually, by running the Post Inventory Cost to G/L batch job.
2. Automatically, every time that you post an inventory transaction.

Inventory transactions in the G/L will post with a Source Code of INVTPCOST, therefore, in order to see where the item transaction was generated, you will need to Find Entries and view the Value Entries.

In the following example, we are going to use an item that utilizes FIFO and take it through its life cycle while examining the transactions that Business Central creates. For this example, we created a new item with the following information on the Costs & Posting Tab:

Since this is a new item, we create a Purchase Order and receive 1 unit of the item for $4.13. We can see which entries are created by using the Find Entries tool on the Purchase Receipt:

Two types of entries are generated in order to track the item’s quantity and value.

The Item Ledger Entry:

The Value Entry:

Please note that the cost is currently considered expected and the Cost Posted to G/L is 0, however the Expected Cost Posted to G/L is $4.13.

Despite the Cost Posted to G/L being 0, an interim cost was still posted. The Cost Posted to G/L field is representative of the actual cost in the Value Entries. If Expected Cost Posting is turned on, the interim entries posted to the G/L are a debit to the Inventory (Interim) Account and a credit to the Invt. Accrual Acc. (Interim):

Please note that if Expected Cost Posting is not turned on, no G/L entries would have posted.

Now, we invoice the unit at $4.96. No Item Ledger Entry is created because the Item Ledger Entry to track the quantity was generated at the time of the receipt; however, the Value Entries from before have additional entries to track the change in value of the item:

Notice, the entry for the Invoice reverses the Cost Amount (Expected) of the receipt and records the actual cost. Now, there is a Cost Posted to G/L.

The entries generated in the G/L are as follows:

The $4.13 is reversed out of the interim accounts and the $4.96 posted to the inventory and payable accounts. Business Central chose which G/L accounts to post to via the Gen. Bus. Posting Group from the Vendor and the Gen. Prod. Posting Group from the Item Card.

Now, we sell the item we just bought from a Sales Order. These are the entries generated when shipping the item with a Unit Price of $12.37:

The Item Ledger Entry created:

Notice the Sales Amount (Expected) field is $12.37, the Cost Amount (Expected) is $-4.96 and the Item Ledger Entry is not open. Because the costing method in this example is FIFO, the Cost Amount (Expected) is based on the cost layer of the item purchased in the previous example, which was applied to this outbound cost layer. In order to see what has been applied to closed entries you can click the Applied Entries on the Business Central Menu Ribbon:

The Value Entry created:

Notice that like on the Item Ledger Entry, there is a Sales Amount (Expected) and a Cost Amount (Expected) for the shipment.

If expected cost posting is set to Yes, then the following G/L entries are created:

The Interim Inventory Account is reduced and the Interim Cost of Goods Sold Account is increased.

When the item is invoiced for $12.37, no Item Ledger Entries are generated because one was created to track the quantity when the item was shipped. However, a Value Entry is created to reverse out the Expected Sales Amount and Expected Cost Amount, and record the Actual Sales Amount, Actual Cost Amount. The Costed Posted to G/L is now updated:

The G/L entries created reversed the amounts posted the interim accounts and posted the amounts to the inventory and customer accounts:

Next we will examine Inventory Reports that can help determine if your inventory needs to be reconciled.

Inventory Valuation Report

The Inventory Valuation Report can be used to determine the value of your inventory as per the Subledger, therefore it is a useful tool when comparing the Subledger to the G/L. It includes in its calculation Actual Cost (invoiced cost) + Expected Cost (shipped not invoiced/received not invoiced cost. If you have your Inventory and Inventory (Interim) separated, you will need to take the total of those two accounts in order to compare your G/L to your Subledger. Before running this report, you should run the Adjust Cost/Post Cost to G/L functions in order to ensure the Inventory Valuation Report is up-to-date. The report can be found by searching for “Inventory Valuation” and selecting the report option.

Inventory to G/L Reconcile Report

The Inventory to G/L Reconcile Report can be used to reconcile the inventory printed in the Inventory Valuation Report to the amounts posted to the G/L in the balance sheet inventory accounts. This report is especially useful if the inventory account and inventory interim account are set to different accounts or if the Expected Cost Posting to G/L is turned off. Since the Inventory Valuation Report includes Expected Cost and the Inventory Account does not, it is necessary to back Expected Cost out of the inventory valuation to tie it to the G/L Inventory Account. The report can be found by searching for “Inventory to G/L Reconcile” and selecting the report option.

The report has the following columns:

Inventory Valuation:

The inventory value calculated on the Inventory Valuation Report.

Received Not Invoiced:

The Cost of Inventory that has been Received, not Invoiced on Purchase Orders and Sales Return Orders.

Shipped Not Invoiced:

The Cost of Inventory that has been Shipped, not Invoiced on Sales Orders and Purchase Return Orders.

Total Expected Cost:

The sum of inventory that has been Received, not Invoiced nor Shipped.

Rec. Not Inv. Posted to G/L:

If Expected Cost Posting to G/L is turned on, the amount that has been Shp. Not Inv. will be posted to the Inventory Interim Account and balance against the Invt. Accrual Acc. (Interim) Account. This column should tie to the Invt. Accrual Acc. (Interim) Account.  If Expected Cost Posting to G/L is turned off, then Expected Cost is not posted to the G/L and this column should be 0.

Shp. Not Inv Posted to G/L:

If Expected Cost Posting to G/L is turned on, the amount that has been Shp. Not Inv. will be posted to the Inventory Interim Account and balance against the COGS (Interim) Account. This column should tie to COGS (Interim) Account. If Expected Cost Posting to G/L is turned off, then Expected Cost is not posted to the G/L and this column should be 0.

Expected Cost Posted to GL:

The sum of the Rec. Not Inv. Posted to G/L and Shp. Not Inv. Posted to G/L; this should tie to the Inventory (Interim) Account.

Expected Cost to be Posted:

If there is an amount here, then the Adjust Cost/Post Cost to G/L functions need to be run. This is because there are amounts have been received/shipped not invoiced which have not been posted to the G/L. In order to have Subledger costs in this report tie to the G/L accounts, the Adjust Cost/Post Cost to G/L functions need to be run so that the Expected Cost to be Posted is 0.

Pending Adj.:

If the Adjust Cost/Post Cost to G/L functions need to be run, this field will be set to Yes.

Invoiced Value:

The Cost of Inventory that has been Invoiced; this includes costs that have not yet been posted to the G/L.

Inv. Value Posted to G/L:

The Cost of Inventory that has been Invoiced; this should tie to your Inventory Account.

Inv. Value to be Posted:

If there is an amount here, then the Adjust Cost/Post Cost to G/L functions need to be run. This is because there will be amounts that have been invoiced which have not been posted to the G/L. In order to have Subledger costs in this report tie to the G/L accounts, the Adjust Cost/Post Cost to G/L functions need to be run so that the Inv. Value to be Posted is 0.

The Inventory Subledger and G/L should be reconciled on a monthly basis to ensure that they are in sync. If any differences are found, corrections should be made to bring them back into sync. You can determine if there is a difference between your Subledger and G/L by running the Inventory to G/L Reconcile Report and comparing the Inventory Value Posted to G/L total to the Inventory G/L Account.

Inventory to G/L Reconcile end totals:

Inventory Account:

The balance at the date amount from the Chart of Accounts is $28,547.16 different than the Inv. Value Posted to G/L amount on the report, therefore there is $28,547.16 that has been posted to the G/L that was not posted to the Subledger. Please note if your Inventory (Interim) Account is the same account as your Inventory Account, you will need to combine the Expected Cost Posted to G/L and Inv. Value Posted to G/L columns from the Inventory to G/L Reconcile Report before comparing to the balance of the Inventory Account on the G/L.

Checking Source Codes in the G/L

Typically, when there is a difference between the Subledger and G/L, it is because a user made a direct entry through a general journal. There is a simple way to check if direct entries have been posted.

Open the G/L Account Card and view the Ledger Entries to filter for Source Code.

If an entry with a source code of <Blank> or GENJNL appears, then you have found the entry created from the general journal:

Please note that beginning balances are often brought in through the general journal and beginning balance entries can be ignored and should not be reversed because the Subledger and G/L balances are generally brought in separately during conversion. Typically, beginning balance entries have:

• An obvious description or document number
• A small entry number
• A posting date that matches your conversion date

Making Corrections

There is a built-in functionality to reverse entries made from the general journal. Select the entry that needs to be reversed, then click Process > Reverse Transaction on the Business Central ribbon:

The Reverse Entries Page will open and selecting Reverse or Reverse and Print will reverse the transaction:

Now an entry with a source code of Reversal will be posted with the opposite sign:

The Reversed by Entry No. and Reversed Entry No. will reference the Entry No. in the General Ledger Entries so you can track if that entry has been reversed.

It is recommended to turn off Direct Posting on all G/L accounts that tie to a Subledger. This is a control to disallow postings that would circumvent the Subledger:

Posting Documents Directly to the General Ledger

Another common cause of differences between the G/L and Subledger is purchasing or selling directly to the Inventory G/L account rather than through items:

When a Purchase Line is posted directly to a G/L account, it posts with a source code of PURCHASE rather than INVTPCOST.

In this case the source code is not the best way to identify the lines. The best way to identify the lines would be to run the following tables and filter by Type: G/L Account and the Account Number for your Inventory G/L Account:

• Inv. Line Table
• Cr. Memo Line Table
• Sales Invoice Line Table
• Sales Cr. Memo Line Table

If you do not have access to run tables, a developer can easily add the pages for these tables to the Menu Suite for you. These pages come with Business Central, but are not natively available on the user interface.

Making Corrections

In order to correct a document posted directly to the Inventory G/L Account, you will need to post the opposite type of document for the same amount. For example, if you purchased directly to the G/L account, you will need to create a Purchase Credit Memo to the G/L account for the same amount as the purchase. In order to simplify the process, you can use the Process > Copy Document functionality to copy the original Purchase Document:

It is important to keep track of which documents have been corrected since this information is not easily accessible from the G/L. Not tracking corrections can result in two corrections for the same document, which will throw the G/L out of balance in the opposite direction.

Changing Inventory Posting Groups

The final common difference between the G/L and Subledger for inventory accounts is when there are multiple Inventory G/L Accounts and an item’s Inventory Posting Setup is changed after transactions have been posted:

Business Central allows users to change the Inventory Posting Groups even when there is a quantity on hand. However, Business Central will not retroactively update posted entries to a new posting group. For example, if an item had been accidentally setup with the RAW MAT Inventory Posting Group and after inventory was bought the Item Card updated to the FINISHED Inventory Posting Group. The result will be the Item was bought to the Raw Materials G/L Account and sold out of the Finished Goods G/L Account overstating Raw Materials and understating Finished Goods. When the two G/L Accounts are combined the inventory value is correct, however since the item really should have been bought to Finished Goods, the individual accounts are incorrect.

In order to identify items which have had their setups changed, you will need the Value Entries for all transactions. The Value Entries will need to be exported from the table because the Inventory Posting Group field is not natively available on the page. Export the following fields to Excel:

• Posting Date
• Item No.
• Document No.
• Cost Amount (Expected)
• Cost Amount (Actual)
• Inventory Posting Group

In Excel, create the following pivot table:

Rows: Item No. and Inventory Posting Group

Values: Sum of Cost Amount (Expected) and Sum of Cost Amount (Actual)

The items with two Inventory Posting Groups listed have had their Inventory Posting Groups changed. If there is an amount in Cost Amount (Expected) for an incorrect posting group, you will need to temporarily change the setup back to the old posting setup (when users are not posting in the system) and either complete the Purchase Document/Sales Return Order or undo Receipt on the document and update the item card back to the correct Inventory Posting Group. This is because Business Central will recognize the change in posting group when the cost is updated from Expected to Actual and will not allow the posting routine to complete.

Making Corrections

In order to correct inventory balances where the inventory posting group setups have changed a direct entry, you will need to take the inventory balance out of the old inventory account and place it into the new inventory account.

In the example above, item number 1896’s setup was changed from the Raw Materials G/L Account to the Finished Goods G/L Account. The amount off for this item is $2,103.56. Therefore, you will need to make the following entry from a General Journal:

Corrections posted should be kept documented so that entries are not accidentally corrected twice. Additionally, since this correction involves posting directly to the G/L, the description for why it is being posted will need to be very clear. This is because these entries will be red flags for future inventory reconciliations and the person performing the reconciliation should be able to determine by the description that the entries were corrections which do not need to be reversed.

If none of the scenarios described above resolve the differences between the G/L and Subledger, then it is time to look for a system issue, although these are uncommon in Business Central. A system issue is when there is an issue with the code, typically written by a third party, that is causing the G/L and Subledger to become out of sync. System issues are often difficult and time consuming to identify.

In order to identify a system issue, you must determine what action is causing the discrepancy. The best way to begin is to determine when the discrepancies began. First, you should run the Inventory to G/L Reconcile Report as of the day you were implemented to see if your Inventory Account matched as of conversion. If not, then the beginning balances of inventory may not have been balanced during conversion.

If your inventory balanced as of conversion, then we recommend running for every year since conversion to figure out what year your inventory first went out of balance. Note, this is one reason we always recommend reconciling inventory on a monthly basis so if any issue occurs it can be caught and identified immediately.

Then, run the report for every month until you found out what month you first went out of balance. Then, run the report for every week until you find the week that went out of balance. Finally, run the report until you find the day you went out of balance. Doing this narrows down the transactions you have to sift through to find out what transaction started the discrepancies.

Once you have figured out what day the discrepancy started it is time to compare the G/L and the Value Entries in order to find a discrepancy between the actual cost on the value entries and the total for the document number in the inventory account:

Please note that the Item Ledger Entry is posted with the document number of the receipt and the entries posted to the inventory account in the G/L are posted with the document number of the invoice. Since the expected cost of the receipt is reversed out with the invoice, if your Inventory and Inventory (Interim) accounts are the same, you will need to include the document numbers of all receipts and all invoices for one Purchase Order when determining the amount in the inventory account for that Purchase Order. You can export all the Value Entries to Excel and all the G/L entries for the Inventory Account to Excel and pivot on Document No. and use an Excel formula to find the documents that caused a difference. A developer can write a report to calculate this for you since this can be a very time consuming process.

It is important to note that if you run the Post Cost Process per Posting Group, this method will not work because the entries posted to the G/L will have the Document No. which was specified when the Post Cost to G/L Process was run and the Value Entries will have the original Document No. We recommend always running the Post Cost to G/L Process per entry so that the document number of the entry being adjusted is carried over. If you do post per Posting Group, a developer can write a report to identify the original document number for the G/L entry by writing similar functionality for the Item Ledger Relation on the G/L Register:

Once a difference is identified, it needs to be replicated in a test environment. You will need to try and post the document in the same manner to determine at what point and what function is causing the issue. Were there multiple receipts on the Purchase Order? Was Undo Receipt done on the receipts? Was the receipt and invoice posted on the same day? These are all questions to consider when trying to replicate the discrepancy.

We hope you have taken away an understanding of Inventory Setup and Inventory Transactions, as well as how to identify when your inventory needs to be reconciled and the steps to take in order to bring the G/L and Subledger back into balance.

For assistance with inventory reconciliation in Dynamics 365 Business Central, or with any other processes, contact our team of software experts. We are a certified Gold Microsoft Partner with over 20 years of experience implementing and supporting ERP solutions like Business Central.