Historical Inventory Trial Balance VS the Historical Stock Status Report in Microsoft Dynamics GP

Hi Everyone,

Many times, you hear from support that you should be using the Historical Inventory Trial Balance report (HITB) to tie to your General Ledger or to know what your inventory value is as of today or as of a given point in time.  You should be using this report verses the Historical Stock Status report (HSSR).  But really what is the reason behind why we are telling you this?  Well, today I want to explain to you the importance of why you want to use the HITB vs. the HSSR report.  It’s not to say the HSSR is bad or shouldn’t be used at all. Quite the opposite.  It’s just that Microsoft Dynamics GP has come a long way since the HSSR report was added and the table structures of Inventory had been GREATLY enhanced since then.  Therefore, the HITB is REALLY the best option to get the correct QUANTIY and VALUE.  Where the HSSR may only provide you with the correct QUANTITY. 

To give some background, in older versions of GP, the HSSR was the only option available to get any type of “historical” inventory report.  The report took your quantity on hand as of today and “backed out” any transactions up until the As-of-date to give you what your quantity would have been as of that date.  The report used the only inventory historical table we had at the time which pulled from the IV30300 table.  The challenge is that we didn’t track ALL types of Cost Adjustments to that table so there is a potential the VALUE may not be correct. 

As Microsoft Dynamics GP was enhanced and features added, we added the ability to track outflows records of inventory with the addition of the IV10201 outflow table and the SEE30303 HITB table which recorded EVERY in and out of inventory including ALL cost adjustments.  And we use the SEE30303 table to print the HITB report. So, the HITB report gives you both the correct QUANTITY and VALUE because we track all transactions going in and out of inventory including ALL Cost adjustments.

Visual Example:

So, let me give you a visual example of why using the HITB is better at providing you the actual VALUE as compared to the HSSR and why we use the HITB to tie to the GL.   I have a couple of transactions that illustrate this very easily and one of the main reasons is because specific Cost Adjustments do not show on the HSSR. 

Let’s say we bought and item and received it into inventory at one cost through a shipment receipt in Purchase Order Processing and then sold that item to a customer in Sales Order Processing at that cost.  Later the Purchasing Invoice from the Vendor came in, showing the cost should have been something else.  When you record the Invoice in Purchase Order Processing this creates two types of cost adjustments.  One for the difference between the Shipment and Invoice cost in Purchase Order Processing (which is shown in the distributions on the Invoice transaction).  Another cost adjustment occurs due to the sale of the item that went out at the wrong cost, and you will see it triggered AFTER you post the Purchasing invoice.  This type of cost adjustment does NOT update the IV30300 table/HSSR.

To recreate: (EASIEST TO TEST WITH A NEW FIFO PERPETUAL ITEM)

1. Entered a POP Shipment for your item on 4/12 for $12.00
2. Entered a SOP invoice for your item on 4/12 (which sells that layer of inventory you just brought in)
3. Entered a POP invoice to match against the shipment receipt on 4/15. The cost of the invoice is $15.00.

This last transaction produced 2 cost variances. 

1. One that you see on the actual distributions of the POP invoice (difference between the shipment and the invoice cost). That value is $3.00 which updated the IV30300 the SEE30303 and the GL.  
2. The second cost adjustment updates the SEE30303 and GL tables for $3.00 only. That is to record the cost variance for selling the item at the wrong cost.   This does NOT update the IV history table and therefore will not be included in the cost on the HSSR when you print. 

Below shows the HSSR and the HITB as of 4/1. 

HSSR: Notice when printed, there are zero quantities as of 4/1 which is correct, but the value is incorrect.  It’s showing $-3.00 instead of $0.00.  That is because we are not including the Cost Adjustment for the Sale and backing that out (or adding it in) and thus not affecting the value.    Note: this report works backwards.  Starts with today’s quantity on hand and “backs out transactions up to the as of date”.

HITB: on 4/1 – Nothing shows for quantity and cost because I didn’t have any transactions at that point – Correct.

HITB: as of 4/15:  Notice ALL transactions including both cost adjustments show on the report and both the quantity on hand and value are correct.  As a result, the quantity and cost are correct.

Overall, both reports are great in their own way.  If you are looking for a way to find out what the quantity was as of a point in the past, certainly use the HSSR report.  It’s a great option.   But the HITB report is really the go to report for all quantity and costing information and would be the BEST option for tying to the General ledger.   The HSSR may or may not tie from a value perspective so keep that in mind. 

More info:

On older version (9.0 and before), Microsoft GP did not have the table structure (IV10201/SEE30303) in place to know what the exact costs were.  At that time the HSSR was the only option available which pulled from the History tables (IV30300).   Microsoft has since built table structures to correctly track inventory’s ins and outs including ALL cost adjustments.  We use the SEE30303 table specifically to track those ins and outs and which is what the HITB report uses to pull its data from.  Since the inception of the HITB report, Microsoft recommends using the HITB to track costs and quantities. 

I hope this helps explain why we recommend using HITB versus the HSSR.

Warmest Regards,

Angela Ebensteiner | Sr. Technical Advisor | Microsoft Dynamics GP