Wednesday, April 13, 2011

Use Tax and Dynamics GP- Match Made in Heaven?

For a lot of businesses, use tax is an annoyance for the most part.  There isn't enough volume to necessitate purchasing a solution, but the need to stay in compliance is vital.  So what to do with little money to spend on the issue, and no ready-made solution in Dynamics GP?

I have to give credit to the following solution to Cindy Boersma at BKD.  After many iterations of trying to find a solution, she came up with this final process -- all I did was go through and document and test.  And it seems to work like a charm.

Step #1
  • Set up a "dummy" tax detail, Microsoft Dynamics GP, Tools, Setup, Company, Tax Detail
  • Type should be set to "Purchases", Based On "Percent of Sale/Purchase", and percentage should be left at zero

Step 2
  • Create the actual Use Tax Tax Details, one for each jurisdiction you need to track Use Tax for
  • The important points here are that the Based On should be set to "Percent of Another Tax Detail", the Percentage should be the actual use tax percentage, and the Based on Detail should be the dummy detail you set up in Step 1

Step 3
  • Enter your invoice information, Transactions, Purchasing, Transaction Entry
  • Then click the Tax expansion arrow to open the Payables Tax Entry window

Step 4
  • Enter use tax information
  • Select the appropriate Tax Detail ID (created in Step 2)
  • Enter the taxable portion of the purchase in the Total Purchase field (yes, yes, I know that is a little odd)
  • Because the tax is based on a 0% tax detail, no taxable amount or tax is calculated.  This is good thing, as you don't want the tax amount to be added to the invoice.
  • Repeat for additional use tax codes.

Step 5
  • Create a custom report to pull the transaction tax detail information (PM10500, PM30700) and join it to the tax detail setup (TX00201).  You can then pull a report with the transaction information, including the total purchases amount (used for the taxable amount) and the tax setup information which includes the percentage of tax.  The report can then calculate the amount of tax owed.
  • This can be done with SmartList Builder or SQL Reporting Services.
Yes, this process requires a custom report...but the value is that the process is very straightforward for users.

Another post to check out, which outlines some other methods of recording Use Tax in Dynamics GP:

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers.


Unknown said...

Hey Christina,

I'm always looking for new Use Tax options so I'm intrigued but I've got a few questions.

It seems like there is no Use Tax liability being created in the GL through this process. How would the liability work? In a related note, folks often ask to put the use tax expense to the same GL location as the purchase(s). How would this work?

Finally, it seems like historical reporting could be a problem if rates change. Given the long lead time to an audit I'd hate to rerun a report now with a higher rate.

Any thoughts would be appreciated.

Christina Phillips said...

Hey Mark--

Good questions.

1. Yes, this does not create a use tax liability in the GL. In this case, the client did not want it created automatically as they want to review what the individual bookkeepers are posting. You could include the GL expense accounts associated with the purchase in the report if needed (although you would have to consider that there might be multiple accounts)

2. For historical reporting, if this was a concern I was thinking you could do a bit of VB to pass the percentage to the tax detail entry window and store it with the tax detail info for the transaction and then use it for reporting. Or the low tech solution would be to save the report to a file, or to a data warehouse if needed.

Take care,