The problem
This "Charging GST Tax on Shipping Cost" document was written for Excel Invoice Manager. If you are using Invoice Manager for Excel, This customization of tax formulas is also supported. Visit our GST Invoice Template for Australia for a sample.
The default standard invoice template included in Excel Invoice Manager setup program has two tax names pre-defined - the first is PST, and the second is GST.
The program also offers an option "Tax invoice total including shipping cost" on the Taxes tab of the Settings window that can be opened by clicking the Settings button on the Invocie worksheet. If the option is checked, both taxes are calculated on the sum of the Taxable Subtotal + Shipping Cost.
If you charge GST-only on shipping cost, here is how to customize the default template.
The solutions
There are two ways you can address this.
Solution 1: Customizing the first tax formula to ignore shipping cost
- First make a backup copy of your Excel template. This enables you to start again in case something goes wrong during customization.
- Open your template in Excel.
- Unprotect the Invocie worksheet. (How?)
- Display gridlines and row/column headers. (How?)
- Click to select the first tax cell which is named oknTax1 (You can verify the name at the left-side of the formula bar). The formula bar shows the formula of the cell, as following:
=ROUND(IF(oknTaxType=0,0, oknTax1Rate*(oknLineTotalTaxable+IF(oknTaxTotalIncludingShippingCost=0,0,oknShippingCost))),2)
- Modify the formula, make sure it looks like this:
=ROUND(IF(oknTaxType=0,0, oknTax1Rate*oknLineTotalTaxable),2)
- Now click the Settings button on the Invoice worksheet.
- Activate the Taxes tab.
- Check the "Tax invoice total including shipping cost" option.
- Click Apply.
- Click Close to close the Settings window.
- Hide gridlines and row/column headers.
- Protect the Invoice worksheet.
- Save the invoice template
Solution 2: Customizing the second tax formula
You can also bypass the "Tax invoice total including shipping cost" option by customizing the formula of the GST tax cell to include shipping cost and ignore the option.
- First make a backup copy of your Excel template.
- Open your invoice template in Excel.
- Unprotect the Invoice worksheet.
- Display gridlines and row/column headers.
- Click and select the second tax cell which is named oknTax2. By default, the formula of the GST tax cell looks like:
=ROUND(IF(oknTaxType<>2,0,oknTax2Rate*(oknLineTotalTaxable+ IF(oknTaxTotalIncludingShippingCost=0,0,oknShippingCost)+ IF(oknTax2IsAppliedToTax1=0,0,oknTax1))),2)
- Modify the formula, make sure it looks like:
=ROUND(IF(oknTaxType<>2,0,oknTax2Rate*(oknLineTotalTaxable+oknShippingCost+IF(oknTax2IsAppliedToTax1=0,0,oknTax1))),2)
As you can see, now the second tax (GST) is charged on both taxable subtotal and shipping cost, even if the "Tax invoice total including shipping cost" option is not checked.
- Hide gridlines and row/column headers.
- Protect the Invoice worksheet.
- Save the invoice template.
This second solution also works for many other template available here on InvoicingTemplate.com as it does not require the "Tax invoice total including shipping cost" option.
See also
- See Invoice Manager for Excel "Settings window - Taxes tab" document that describes the options provided on the Taxes tab of the Settings window.
- Mixed tax rates - Invoice Template with Support for Product-Type Specific Tax Rates
A tutorial on customizing the standard invoice template to enable product-type specific tax rates in Invoice Manager for Excel
Downloads
Click the following links to download the templates created in this tutorial: