Leveraging Sage Intacct to Automate Use Tax
Chances are your business operates in a state that requires paying use tax on goods purchased from states that don’t include sales tax. If you are not exempt from this collection, tracking the true amount owed can be burdensome and time consuming. However, tracking use tax in Sage Intacct does not need to be as complicated as building a flux capacitor. If you answer yes to any of the three following questions, this article is right up your alley.
- Are you currently tracking use tax in excel?
- Are you constantly making manual entries to recognize additional expenses?
- Do you spend hours during tax season to ensure you are reporting the correct amounts?
What if I told you, you could leverage Sage Intacct to make the entries for you? In this case I am going to demonstrate customizing a standard AP workflow for real time tracking of use tax expense for state as well as local rates.
Customizing an AP Workflow
If you are currently processing all bills through Accounts Payable, no standard intacct tax functionality exists. You could consider adding purchasing, but it may not make a lot of sense to add a whole new module just to get a more automated use tax process. With a few initial customizations you can easily flag individual lines items for use tax of different rates, specify the amount of tax needed to be paid, have the system make additional gl entries, and quickly pull reports to file taxes owed.
What is Needed?
Custom Fields
- Check Boxes for Tax Rates
- Number Field to Enter total tax amount
Smart Event
- Use API call based on Custom Fields to create a journal entry to debit expense (either line item account or constant) and credit a sales tax payable account
Custom Reports
- One Report for each percent needed (typically one state and one local) to tie back sales tax payable account to tax return lines.
Custom Fields
Object:
The custom fields will be setup on the AP bill detail object, giving the end user the most flexibility in marking which line items are taxable.
Fields:
- Checkboxes for Tax Levels: You will want to create Custom Fields (Checkboxes) for each level of tax.
These will be used to help trigger an API call to automatically create a journal entry to recognize the additional expense and liability at time of bill creation. Additionally there will be criteria to filter on custom reports to pull in all bill detail and breakout the tax form lines, tying to the tax liability balance. It is recommended that these fields are named to convey the jurisdiction tax percentage.
Note: when creating the custom checkboxes you will want to set the default to false.
- Total Tax Amount: Due to system limitations and in order to facilitate the auto journal entry an additional field will be needed where the bill creator can enter the total tax amount. Setup as a number, the user can utilize the system calculator to note the correct amount for that transaction line.
Smart Event
One smart event will be required, set to trigger for each line where a tax check box is set to true. (Learn more about setting up smart event api calls here!) The event will be set on the ap bill detail object, use an action type of “api” and only when the event is “Add”. In order to only trigger when desired, the sample condition can be used for when any tax checkbox is checked.
Condition: (({!APBILLITEM.ADD_2!} == “true”) || ({!APBILLITEM.ADD_6!} == “true”))
The actual api body is the most complicated part, but gives the user the most flexibility in how the entry posts. Because bill fields can flow through the API call, we can configure as much or as little detail in our system created journal entry.
API Body:
It is recommended, but not required, to have a separate journal setup (in this case USE) in order to group these entries separately. Additionally, you can have all these entries route to approval prior to posting to double check proper bill coding.
Yellow Highlighting: Used to show that bill level information can be used to tell our JE when to post (same day as bill date) as well as populate various fields in the description for easy identification.
Blue Highlighting: Shows that all dimension values coded on the bill line can be brought back into each line of the journal entry. However, you do not have to use the same coding and can certainly “hard code” desired constant values.
Red Highlighting: These fields are the amounts for the journal entry and must pull from the user calculated custom field.
Midnight Green Arrow: This points out the greatest functionality of being able to automatically code the tax expense to increase the originating bill expense account as opposed to pulling into one use tax expense account (although you still can).
Entering AP Bills
Once the custom fields and smart event have been created you are ready to start posting bills and watching the tax expense entries automatically hit the gl.
- Custom check boxes (as many as you need) to determine on a line by line basis how much tax needs to be added
- Custom number field to calculate the line expense amount
Custom Reports
The last step is to build a few custom reports using the Sage Intacct custom report writer in order to quickly pull the info needed to fill out the tax return. (Learn more about building custom reports here!) These reports should be built from the ap bill detail object and contain the info you want in order to do a review of all taxed amounts. Each report should be filtered for when its retrospective custom field is set to true. In this example you would have a 2% report and a 6% report. Very important is a calculated column that multiplies the checkbox percentage by the line item amount. This will give you what the true amounts should be and can be used to verify correct data entry into the tax amount field.
Altogether, these customizations can lead you to a much simpler use tax workflow. Nevertheless, not all are needed. If you don’t want any auto-posting entries, you can ignore the smart event and just use a combination of check boxes and reports to track Use Tax.
Questions? Comments about this spectacular blog post? Requests for more posts by the author, Malcolm Coate? Submit all of the above here!
Want to ensure you never miss a new CLA Sage Intacct blog post? Click the blue link at the top right to subscribe!
Malcolm E. Coate is a consultant with CLA's Sage Intacct team. CLA is an Intacct Premier Partner with a partnership that spans over 15 years and more than 600 successful implementations. Malcolm has been with CLA for nearly 2 years, implementing Intacct for a wide variety of nonprofit and for-profit clients of different sizes. He has found great excitement in customizing Intacct to fit the needs of clients while thinking outside the box.