When reconciling inventory to GL in GP, there are two primary report options that companies use. Both of them have some pretty significant pros and cons. The two reports are the Historical Stock Status (HSS) report and the Historical Aged Trial Balance (HITB).
For a long time, all we had was the point in time stock status report in GP. To get this right, it needed to be run at month end. Miss the timing, and you were just stuck. Along came a historical version of that report that finally allowed going back in time, but HSS but it had its own issues. Enter the HITB, a report designed to tie to GL, but with a big implementation caveat.
Historical Stock Status is not designed to reconcile to the subledger. It may, and it’s often close, but the design of this report prevents a perfect reconciliation. For a long time, however, it was the only reasonable option. HSS starts with the current cost of an item multiplied by the quantity and then subtracts transactions to get back to the date selected. Working back to a date is the historical component. This report may not tie to the GL because it ignores the cost layers with that initial value. The current cost is really the last cost and often not representative of the full value.
For example, current cost is $100. Quantity is 11 units. The report would start with a valuation of $1,100. But in inventory, this product was bought in two purchases, one purchase of 10 units at $80. The second purchase was a single unit at $100.
$80 x 10 =$800 + $100 =$900 and $900 does not equal $1,100 from the HSS. This can work in both directions. Also, HSS works with transactions and does not take into account inventory cost adjustment. It’s usually close except when there are large price swings.
HITB was introduced with GP 10 and is designed to be the reconciling companion for inventory. However, for HITB to work, it required some data not previously present in GP, which means that it got additional tables. These tables were unpopulated by existing inventory transactions. For HITB to work, Microsoft provided an IV reset tool that would populate the tables from existing data but it required resetting all the inventory cost layers to a new starting layer. For some companies, this would result in a significant change to their inventory valuation and they were unwilling to do that.
HITB collects data whether the reset tool is used or not, it’s just not accurate if there are pre-HITB items still in inventory. For most folks, this should no longer be a problem since HITB was released with GP 10 in 2007, but there are some long inventory manufacturers out there (think Christmas trees, aged bourbon, etc.) along with plenty of people who do a poor job cleaning out obsolete inventory.
So here is the bottom line.
- If your company first implemented GP with GP 10 or higher, use the HITB.
- If your company ran the IV Reset Tool somewhere along the way, use the HITB.
- If every item in inventory has turned at least once since 2007, you should be good with HITB.
- If you are on a version prior to 10, the only option is HSS.
- If your company started with GP prior to GP 10, you have not run the IV reset tool, and you still have a few leftover items in inventory, consider running the tool to evaluate how much your inventory would change. The tool provides this information prior to finalizing anything. If the number is small enough, consider resetting the inventory layers to be able to use HITB.
One more note, both of these reports are often requested in Excel. They are both very hard to get from GP into any kind of reasonable format in Excel. The best native option is to use the SSRS versions and export them, but even that requires a lot of clean up.
The best option is to purchase and use Historical Excel Reporting to run these reports directly in Excel.
Links to all the posts in this series can be found at http://mpolino.com/gp/gp-controller-series-index/