The place to find business sales tax information

— as well as solutions, services and jobs!

Tax Automation Can Reveal Your Transaction Data Secrets

author photo of Suzy Soo

Diving into the world of tax automation can do strange things to a tax person. Whereas once you may have felt ambivalent about the “system” - that faceless beast that churns all documents and numbers and belches out a report every month - you now find yourself trolling it with hungry ... curiosity.

Tax automation can help you see the patterns that exist in the transactional minutiae captured with every sale and purchase your company makes. The “system” becomes the Oracle (pun very much intended) of all the secrets you both protect and fear when you lay awake at night, wondering if a) you let the cat in, and b) is your use tax reserve going to cover your current audit?

Whether your company is using sophisticated tax software or relying on rate tables and a thumb in the wind, every tax person has the right and duty to know what is really going on with the business events that trigger a tax obligation.  And every system has the ability to tell you that – you just have to know what to ask for and what to do with it.

Step 1: Get a really good data dump

Cozy up to your IT folks because you may need a developer to write the data extract. If you want to analyze your AP spend, here are some tips on what to ask for – start with a 1-3 month sample:

  • Line level details -invoice summary is too obscure
  • Vendor number and name – most of the time, you buy the same thing from each of your vendors
  • Material Group or Item Category
  • GL account – it’s really important they get the expense account, which you may need to get from the PO or receipts posting.
  • User IDs of the requisitioner, buyer and A/P processor
  • Line item description – this might tell you what’s really being bought
  • Line item amount
  • Tax aid to the vendor – if its captured
  • Tax accrued – ask for it on the same line as the rest of the data – this one can be tricky to get…
  • Tax Code – in SAP and Oracle – this may define type of tax calculated by the system
  • Location Code or Ship-to jurisdiction/address
  • Cost center, dept, project, asset or other data that might show how the purchase is accounted for

Step 2: Sample for a Micro View

  • Identify a list of sample A/P invoices to pull – cover a range of states, vendors, GLs, and amount stratas. I’m thinking 200-300 at least.
  • Get the invoices and review them for actual ship-to state, product/service purchased and tax charged
  • Note what tax should have been paid or accrued
  • Note if the GL account is a good or bad indicator to what was actually purchase
  • Add this information to columns in the extract

Step 3: Pivot for a Macro View

Drop the extract into Excel and start pivoting the data to see visions like:

  • Spend by Vendor
  • Spend by GL account
  • Spend by actual product/service purchased
  • Spend by state
  • Spend where tax is underpaid
  • Spend where tax is overpaid
  • Spend where the GL does and does not match the products/service purchased – also see this by requisitioner
  • Tax paid and accrued by state and product/service
  • And the list goes on – go nuts I say!

Alas, there’s no secret here - the steps are pretty logical and the execution can be time-consuming - but the insight yielded can help you justify your business case for automation, provide a basis for refunds, and give you the proof you need to effect change in your organization. Or, it could simply, quite beautifully, help you sleep a little better at night…

Other recent “Sales Tax Automation & Software” posts by Suzy Soo:

NOTE: All blog content, comments, and participation subject to disclaimer at bottom of page.


2 Responses to Tax Automation Can Reveal Your Transaction Data Secrets

  • Posted by Sarah on February 8, 2012 2:28am:

    We are working on automating our sales reports. Are there any suggestions you have regarding the best source for data: a cash balancing system, enterprise data warehouse, etc.? Is it better to remit sales tax calculated from dollars or sales tax collected? (We refer to this internally as an over/short method of remitting sales tax.)
    Thank you,

    • Posted by Author photo of Suzy SooSuzy Soo on February 13, 2012 11:47pm:

      We typically see sales/use tax reports coming two places - your ERP system or your tax system.
      From your ERP (or Billing) system, using your A/P and A/R transactions is a good baseline and given that it is your system of record, it is a closer source to the "truth". However, your ERP reports may not properly capture nuances such as tax due at origin locations and reduced rate/basis variables.
      From your tax engine, these reports can be very comprehensive in terms of providing detailed tax and jurisdiction information, but may not be as usable as they may not include all transactions and are one-step removed from the ERP system.
      In both cases, your ERP or tax engine sales tax reports should be reconciled with your tax GL account detail before you file your returns, because it is possible for direct tax adjustments to be made to your GL (and these may not have gone through your A/R and A/P process).
      As for remitting sales tax - it is most common for companies to remit sales tax "actually" collected and the tax base is reverse-calculated from that. To do otherwise, would result in reporting issues because the sale tax actually charged and recorded on your GL would not match what you actually remit.


Access to any portion of is contingent upon your acceptance of our Terms of Use. This Web Site and content provided by STS Publishing, LLC and its third party content providers, including, but not limited to information, documents, forms, comments, advice and opinions, is for informational purposes only, and is not a substitute for professional advice, nor does the use of this Web Site constitute a professional-client relationship. The Web-Site also includes advertisements, directory listings, job postings and links to third party web sites, all of which are provided for your convenience only and in no way constitute a referral, endorsement, or warranty by of any product or service provided by such third parties. All content is provided “as is” with no guarantee regarding accuracy, suitability, or timeliness. Your reliance on any content accessed on or through the Web Site, or on any product or service provider is strictly at your own risk.