GP Controller Series: Addressing Use Tax

Use or Purchase Tax is a self-assessed tax where goods are purchases without taxes applied and the user/company is required to self-assess and remit taxes.     This most often happens with inventory items that are used by the company instead of being sold to customers or with items purchased from suppliers or via jurisdictions not subject to tax. This comes up just enough and is a big enough pain, that I wanted to cover it.

Quick examples, if a grocery store takes bleach for sale of a shelf to help clean up a spill, they owe tax on that. Also, if a US company orders hard drives from a supplier in China to repair some internally used computers, the Chinese supplier has no requirement to collect and remit US tax. Instead, the buyer would be required to remit tax payments to their state.

GP has a small handful of ways to deal with use tax depending on the details, but one option stands out: Negative Tax Rates.

In GP, a Tax Schedule is a collection of Tax Details. A Tax Detail holds the specifics for a single tax rate. For example, Florida has a state sales tax rate and a different county tax rates. Each of those represents a Tax Detail. Those details get combined into a schedule and a schedule is applied to a transaction. So a 7.5% tax from a schedule might be a combination of a 6% state tax, 1% county tax, and .5% city tax.

If tax is included in an invoice, the customer pays both the invoice and the tax to the vendor. In a use tax case, the vendor gets the invoice amount and the taxing authority gets the tax amount. This is what adds a layer of complexity to the process.

GP allows Tax Details to be both positive and negative and each DETAIL can have a separate account so we can use this to create Tax Schedules for use tax. Simply create new tax details that mirror the tax rates with negative rate values. Combine both the positive and the negative rates into a single tax detail.

Now, when this rate is applied to a transaction, the net tax value on the transaction is zero, so the vendor is paid correctly. The positive rates debit an account, usually, an expense account for the tax, and the negative rates credit a liability to the taxing authority to collect those amounts all in one place.

There are some technical setup items to complete, but this is the most straightforward solution. You can find additional details in this free, Use Tax whitepaper.

Links to all the posts in this series can be found at

GP Controller Series: Inventory Reconciliation: Stock Status vs Historical Aged Trial Balance

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