In this article, we will provide lucid explanation for Sales and Purchase Tax import. For most of us, Tax manipulation is a big riddance to be solved. Excel Transactions provides enticing solution to import your Sales and Purchase Tax with relevant transactions.
Kindly go through below articles to setup a Tax in QuickBooks Online.
If you are using U.S QuickBooks Online, please go through below article for Sales Tax Import and you can skip this article.
If you are using CA/UK/AU/IN (NON U.S) QuickBooks Online, you may have different TAX Models such as GST, VAT, CST & etc. But the basics of Tax Import is common across all versions of QuickBooks.
For simplicity, we can take “GST” as a tax model for further discussions. “GST” can be added in Sales and Purchase Transactions.
How to record Sales Tax and Purchase Tax?
In order to record the Tax for a transaction, you have to do the following:
1. Tax Code should be given in Line Transaction (Product or Service Line Item or Account Line Item)
2. Need to mention Tax Calculation Model such as Exclusive, Inclusive or Not Applicable
Example of Product or Service Line Item with Tax in QuickBooks
Example of Account Line Item with Tax in QuickBooks
QuickBooks needs “Tax Code” to add taxes in your transactions. So “Excel Transactions” does not ask you to provide TAX AMOUNT directly in the file. Instead, you will be asked to provide “Tax Code” and our application will calculate the tax for you.
The tax information is given to "Excel Transactions" by mapping relevant fields from File. In the next section, we will see how the fields are mapped.
Please go through this link if you are new to Field Mapping concept.
In the Field Mapping Screen, for each Transaction Type (Invoice, Sales Receipt, Estimate, Credit Memo, Expense, Check, CreditCard, Purchase Order), you will see the following QuickBooks Fields:
1. Line Item Tax Code or Expense Tax Code
2. Global Tax Calculation
3. Line Item Amount or Expense Line Amount
1) Line Item Tax Code (Application for Product Based Line Transaction) or Expense Tax Code (Applicable for Account Based Line Transaction)
This field takes PROPER Name of your Sales Tax or Purchase Tax. You can find the tax code name in QuickBooks UI. Tax codes can be found in QuickBooks by navigating to
Sales Tax [OR] VAT [OR] GST (left navigation bar) > Rates & settings
You can also refer the tax code in Sale or Purchase Transactions UI (QuickBooks). Check this Invoice Screen. The Sales Tax Code is "20.0 % S" in the example.
2) Global Tax Calculation
This field defines the logic behind the tax calculation. It takes following values only (Case Sensitive and No SPACE).
The amount mentioned in “Line Item Amount” will be treated as Inclusive of Tax.
The amount mentioned in “Line Item Amount” will be treated as Exclusive of Tax. The tax amount will be added to the total amount.
The particular line transaction will be relived from tax.
If you don’t want to add an new column in your file for “Global Tax Calculation”, there are two workarounds for you.
You can map “Default Column 1” to “Global Tax Calculation” and provide default value such as TaxInclusive, TaxExcluded or NotApplicable in the text box.
You can set the “Global Tax Calculation” in Import Settings also. Please check this link.
3) Line Item Amount / Expense Line Amount
This field is used to record the cost associated with Product / Service or Account. As we mentioned earlier, our app does not need the tax amount. Our app calculates the tax using given Tax Code and Tax Calculation Model.