D365 Reconciliation - Inventory Value (GL and SL on a same page)
Updated: Aug 29
While you are waiting to enable the new feature [Inventory Value Report Storage],
this blog quickly covers setup for the 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 subotals.
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, for example 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 that are controlled with different transaction types and related setups.
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 todays' 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
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 reconciliation of Inventory Value.
Our report displays the General Ledger (Trial Balance), Sub Ledger (Inventory Value) and the Variance (Discrepancy) amounts.