The Historical Inventory Trial Balance (HITB) report shows your inventory value as of a specific date or date range and the value of the five different quantity types. You can use this report to balance your inventory value with the appropriate accounts in General Ledger. The HITB was first made available with Dynamics GP 10 as an additional install, so if you have been using Dynamics GP for a while then you may need to install this functionality.

The Historical Inventory Trial Balance report lists quantity balances and inventory values at a certain point in time. You also can use the report to balance your inventory with the appropriate accounts in General Ledger. The HITB report provides a current and historical value of your items in the inventory module as of a certain date. It can be printed by Account Summary, Account Detail, Item Summary, and Item Detail.

This report was created to fill a functionality need. Before the availability of this report, there was not a way to obtain an accurate value for inventory as of a specific date. This report lets you reconcile, both in detail and in summary, historical inventory transactions to the General Ledger to a specific date or date range by account.

The information that prints on the report is stored in the new HITB Inventory Transaction History Detail (SEE30303) table. All inventory transactions and cost adjustments will create records in the SEE30303 table.

Before you can use the Historical Inventory Trial Balance report, your inventory must balance with the appropriate accounts in General Ledger. You must use the HITB Inventory Reset Tool to reset your inventory balance.

Using the Historical Inventory Trial Balance Report

You can use the Historical Inventory Trial Balance report to balance your inventory to your General Ledger. To balance a single inventory account, print the HITB report for that account without using any other restrictions. Then, print the General Ledger Trial Balance report for the same account. Be sure that you use the same date when printing both reports.

If you are balancing your current inventory value to General Ledger, print the Historical Inventory Trial Balance report with no restrictions selected. Then, print the General Ledger Trial Balance report using the same range of accounts as the HITB report.

If the Historical Inventory Trial Balance report and the General Ledger Trial Balance report don’t balance with each other, one of the following situations might have occurred.

• You have posted to an inventory account through General Ledger and not through the subsidiary modules.
• You have posted to General Ledger, but the batch was modified before posting to General Ledger.
• You have posted to an inventory account from a subsidiary module, such as Payables Management, but a record wasn’t created in the SEE30303 table.
• You have deleted the General Ledger batch that would have updated the inventory accounts in General Ledger.
• You ran scripts using Query Analyzer that changed or updated transactions that were related to inventory or item setup information that wasn’t re-created in the SEE30303 table.
• You have made several unsuccessful attempts to close General Ledger and you haven’t addressed it.
• You have customizations or third-party products that create or process transactions that affect Inventory Control, but those customizations or products don’t use Microsoft Dynamics GP inventory scripts for posting quantity movement. The Historical Inventory Trial Balance data won’t be captured.

Understanding the HITB Report

Review the following information to see what can affect how the HITB report functions.

There may be rounding issues between the HITB report and the General Ledger. The following formula is used to determine the currency amount that is posted to General Ledger. The calculation used to determine the impact on General Ledger may not equal quantity * unit cost.

Item Value before posting: ((QTYRECVD – QTYSOLD) * round(DecPlcCur(Cost))) minus Item Value after posting: ((QTYRECVD – QTYSOLD) * round(DecPlcCur(Cost))) equals Total currency amount to General Ledger

• A transaction may have more than one journal entry on the report if the transaction has more than 50 distributions.
• You must use the Receiving Item Detail Entry window to change inventory distributions for the item. You can’t edit the purchasing distribution for the receipt because the account used to post to General Ledger and the account used to post to inventory must match.
• If you override serial numbers, currency amounts are reduced since there is no change in the quantity amount. A serialized item can’t have a negative quantity amount.
• It’s highly recommended that you don’t post multiple invoices matched to a single shipment receipt.
• To ensure that there aren’t rounding issues, enter the unit cost for the line item instead of entering the extended cost when processing a shipment receipt, shipment/invoice receipt, or invoice receipt.
• When a Purchase Order Processing unrealized purchase price variance or purchase price variance is posted, the HITB report displays the variance using the unrealized purchase price or purchase price variance account assigned to the item record or assigned in the Posting Setup window. If you edited the unrealized purchase price or purchase price variance account before posting a Purchase Order Processing transaction, the account displayed on the Historical Inventory Trial Balance report might be different from the account actually used.
• If you use the inventory transfer functionality to transfer items between warehouses, the report will only capture the last moves receipt date for calculating the inventories age.

Other Reporting Options for your Inventory

Mahmoud M. AlSaadi has a couple of great blog posts with SQL views to report historical inventory aging and inventory turn-over analysis as part of inventory reporting.  A historical aged inventory report isn’t available within Dynamics GP.

The SQL view retrieves all open cost layers as of a specific date, for that purpose, we do need to consider two primary tables (IV101200 and IV10201) which are purchase receipt and purchase receipt details. These two tables represent the cost layers of the inventory module, and there is a direct link represented with the (Receipt Sequence Number) and the (Source Receipt Sequence Number).

Aging buckets are predefined in the SQL view for the inventory aging report with the following aging buckets.  This SQL view can be changed to fit your business needs.

• Date Difference < 0 Then (Current)
• Date Difference > 0 and < 30 Then (0- 30)
• Date Difference > 31 and < 60 Then (31-60)
• Date Difference > 61 and < 90 Then (61- 90)
• Date Difference > 91 and < 180 Then (91- 180)
• Date Difference > 181 and < 360 Then (181-360)
• Date Difference > 360 and < 720 Then (1-2 Years)
• Date Difference > 720 and < 1440 Then (1-2 Years)
• Date Difference > 1440 and < 2880 Then (3-4 Years)
• Date Difference > 2880 and < 5760 Then (4-5 Years)
• Date Difference > 5760 Then (Above 5 Years)

Ready to do Even More with Your Data?

Start organizing, knowing and executing on your data today with Dynamics GP and Power BI to drive your business into the future. KTL Solutions works with business leaders every day in helping them lead their organization into becoming a data-driven organization. Need help executing on your data? Contact KTL today.