D365 Reconciliation – Inventory Value (GL and SL on a same page)

By - September 30, 2021

While you are waiting to enable the new feature [Inventory Value Report Storage], this blog quickly covers the setup for the existing feature: Inventory Value Report.

We will also briefly discuss some important posting profile concepts.

The Inventory Value report provides the ability to see both the General Ledger (Trial Balance) and Sub Ledger (Inventory Value) on the same page without the need to separately run a Trial Balance report for comparison.

The Inventory Value report also has the option to print subtotals.

The Inventory Value report has the above benefits vs. the newer version (Inventory Value Storage) that requires the Trial Balance report to be run and does not provide subtotals.

Excerpt from Microsoft’s Documents 

Understanding Posting Profiles is important so that setup provides balanced transactions. Only two things happen with Inventory, Increases and Decreases. D365 names the increases as “Receipts” and D365 names the decreases as “Issues”.

Thus, coding “Loss” or “Gain” (not a “Receipt” nor “Issue”) to an Inventory Main Account will cause out-of-balance.

There are many different reasons for the Increases and Decreases, and they are controlled with different transaction types and related setups.

Example:

  • Sales Orders
  • Purchase Orders
  • Production Orders
  • Inventory Journals

Today we will address a few of the posting setups, but our primary focus will be on creating Inventory Value reporting with a link to the Trial Balance total for Inventory.

It is important to understand which Main Accounts and Posting Profiles relate to Inventory. For today’s discussion, we will assume your Main Accounts related to Inventory are 1401xx through 1407xx

(Note: Contoso sandbox uses 140100 thru 14998)

The Posting Profiles related to these Main Accounts should be coded to “Inventory” posting types (circled in the examples)

Failure to assign Inventory Main Accounts (1401xx-1407xx) to circled Posting Profiles will cause an out-of-balance.

Assigning non Inventory Main Accounts to the circled Posting Profiles will cause an out-of-balance.

Assigning Inventory Main Accounts (1401xx-1407xx) to the circled Posting Profiles will enable balanced accounting and related reporting on the Inventory Value report.

Inventory management > Setup > Posting > Posting

Example Purchase Order Inventory Accounts

Example Sales Order Posting Profiles
Example Inventory Posting Profiles

1. Chart of Accounts, Total Account

Create a new account.

This account will be used on the Inventory Value report’s setup.

General ledger > Chart of accounts > Accounts > Main accounts

Account Description

140990 Total Inventory

Note: Contoso sandbox uses 140999 Total Inventory

Choose Main Account Type = Total (1)

Choose the Totals Sum icon (2)

Assign the Main Accounts which are to be totaled.
Then Save your new 140990 Main Account.

2. Inventory Value report ID

The Inventory Value report has several different setups that can be reused. These different reusable setups are called “ID”s.

We will create an ID called SUM (for Summary) and will link it to the related Trial Balance via the Total Main Account we created in Step 1 (above).

Note: Contoso sandbox uses ID = “10” which does not have the Main Account assigned.

Inventory management > Inquiries and reports > Inventory value reports > Inventory value

Right-click and choose ‘View details’ for the ID

Create a new ID “SUM” , as exhibited then click Save(3).
Then fill out the remaining ID setup as exhibited.
Click Save when finished the setup of the SUM ID.

Then click Close (“x”) so that we may assign the SUM ID to our Inventory Value Report.

Click the drop-down to assign the “SUM” to your Inventory Value report.

3. Inventory Value Report

Fill out your desired report dates and click OK

Example Inventory Value Report from Contoso sandbox
We have successfully created a report to use for the reconciliation of Inventory Value.

Our report displays the General Ledger (Trial Balance), Sub Ledger (Inventory Value) and the Variance (Discrepancy) amounts.

This blog was originally posted on 365financearchitect.com

Receive Posts by Email

Subscribe and receive notifications of new posts by email.