Automating Use Tax Calculation with a Workflow in NetSuite

By - September 8, 2020

Use tax is a sales tax on purchases made outside one’s state of residence for taxable items that will be used, stored or consumed in one’s state of residence and on which no tax was collected in the state of purchase. Calculating and Recording Use Tax in NetSuite can be a daunting task, and often requires the assistance of a third-party Tax application. By using a workflow and the recently released “Create Line” workflow action, we are able to automatically add and calculate Use Tax on a transaction. This blog post will walk you through how to set up automatic Use Tax Calculation on a Vendor Bill using a custom record, two custom transaction fields, and a simple workflow.

First, start by creating a custom record for Use Tax. On this custom record, you can add as many fields as necessary. For this example, I’ve simply used Use Tax Rate and Tax Agency (Vendor). The Use Tax Rate field will store the Rate for a specific Jurisdiction. The Tax Agency (Vendor) field stores the Tax Agency that the tax will be remitted to.

Figure 1: Use Tax Custom Record

Figure 2: Tax Agency (Vendor) Custom Record Field

Figure 3: Use Tax Rate Custom Record Field

You can then add different required tax jurisdictions as a list value using the Use Tax custom record.

Figure 4: Creating a new Use Tax Record

Figure 5: CA – San Francisco Use Tax Record

Figure 6: List of Use Tax Records

 

Next, add two fields to your vendor bill form. The first field is “Tax Jurisdiction”. This field should be a List/Record type using the Use Tax Record as the source list.

Figure 7: Tax Jurisdiction Transaction Field

 

Next, create a field for the Use Tax Rate. In order to have this field automatically source from the associated Tax Jurisdiction, change the source list to “Tax Jurisdiction” and the Source From to “Use Tax Rate (shown below). This enables the use tax rate to automatically populate on a bill when a tax jurisdiction is selected.

Figure 8: Use Tax Rate Transaction Field sourced from the Tax Jurisdiction selection

Figure 9: Sample Vendor Bill with Tax Jurisdiction and Use Tax Rate

 

Once the custom record and the transaction fields are set up, you can create a new workflow. The following criteria should be defined:

  • Record Type: Transaction (Vendor Bill)
  • Initiation: Event Based
  • Events: On Create
  • Condition: Use Tax Rate Is Not Empty

 

Once you have created your workflow, you will need to create two workflow fields. These fields will store the calculated Use Tax based on the Transaction total. Create one field for “Use Tax Amount” and one for “Use Tax Payable”. The type should be Currency, and the Store Value box checked.

Figure 10: Workflow fields to be created

 

You will create 4 workflow actions total. The first action will calculate the Use Tax Amount. Create a new Set Field Value Action. Select Use Tax Amount (Workflow) as the field, and enter the formula {usertotal}*{custbody_use_tax_rate}.

Figure 11: Workflow Action 1 – Set Field Value of Use Tax Amount (Workflow)

 

The next action will also be a Set Field Value action. Select Use Tax Payable (Workflow) as the field, and enter the formula {usertotal}*{custbody_use_tax_rate}*-1.

Figure 12: Workflow Action 2 – Set Field Value of Use Tax Payable (Workflow)

 

For the third action, select “Create Line”. The Item (line) should refer to your UseTax_Payable item and the Quantity should be set to one. Rate (line) should reference the value field Use Tax Payable (Workflow).

Figure 13: Workflow Action 3 – Create Line with Use Tax Payable (Workflow)

 

The last action will follow the same logic as the previous action, except using the UseTax_Expense item.

Figure 14: Workflow Action 4 – Create Line with Use Tax Amount (Workflow)

 

Once all four actions are created, your workflow actions should be in the following order:

Figure 15: Workflow Actions Order

 

When a vendor bill is created and saved, the workflow will automatically add two line items – one for Use Tax Payable and one for Use Tax Expense. Keep in mind that the workflow will only run when the Use Tax Rate field is populated. If a Bill does not need use tax added, simply leave the Tax Jurisdiction/Use Tax Rate field blank and the workflow will not add additional tax lines to the transaction.

Use Tax payable will credit, in this case, General Operating Expense (or whichever account you assign to the Use Tax Payable Item) and Use Tax Expense will debit Sales and Use Tax account (Or whichever other account is assigned to your Use Tax Expense item).

Figure 16: Use Tax Lines Automatically added to a Vendor Bill upon saving

Figure 17: GL Impact of the Use Tax Expense and Payable line items

 

If you aren’t ready to employ a third-party tax application to meet your use tax needs, but are wanting more automation and less manual work, this workflow solution is a great alternative and takes little configuration time.

For additional information on this topic or others related to NetSuite, please contact RSM at netsuite@rsmus.com.

Receive Posts by Email

Subscribe and receive notifications of new posts by email.