free excel invoice templates
You are here: HOME  >>>  Excel Invoice Manager

Charging GST Tax on Shipping Cost

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.

Online-Only Article

Online-only articles are documents that are not included in the help file of the current version of Excel Invoice Manager. However, they provide useful information and may be included in the next version of Excel Invoice Manager.

Related Links:

Download the billing software powered by Excel Now!

A quick tutorial on Excel Invoice Manager

What's new in the latest version

Solution 1: Customizing the first tax formula to ignore shipping cost

  1. First make a backup copy of your invoice template. This enables you to start again in case something goes wrong during customization.
  2. Open your template in Excel.
  3. Unprotect the Invocie worksheet. (How?)
  4. Display gridlines and row/column headers. (How?)
  5. 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)

  6. Modify the formula, make sure it looks like this:

    =ROUND(IF(oknTaxType=0,0, oknTax1Rate*oknLineTotalTaxable),2)

  7. Now click the Settings button on the Invoice worksheet.
  8. Activate the Taxes tab.
  9. Check the "Tax invoice total including shipping cost" option.
  10. Click Apply.
  11. Click Close to close the Settings window.
  12. Hide gridlines and row/column headers.
  13. Protect the Invoice worksheet.
  14. Save the invoice template file by clicking menu File -> Save in Excel 2000/XP/2003, or clicking Office button -> Save in Excel 2007.

Solution 2: Customizing the second tax formula to include shipping cost

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.

  1. First make a backup copy of your invoice template.
  2. Open your invoice template in Excel.
  3. Unprotect the Invoice worksheet.
  4. Display gridlines and row/column headers.
  5. 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)

  6. 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.

  7. Hide gridlines and row/column headers.
  8. Protect the Invoice worksheet.
  9. Save the invoice template.

This second solution also works for the free invoice template as it does not require the "Tax invoice total including shipping cost" option.

See also

Downloads

Click the following links to download the sample invoice templates created in this tutorial:

 
EXCEL INVOICE MANAGER Free Download and Try
LEARN MORE Excel Invoice Manager Home What's New Difference between Editions Quick Start Introduction Setting up Company Information, Tax Invoice Template Working with Excel 2007 Installing / Upgrading Settings Workbook Taxes Misc Data Interface Database In-Cell Lookup Print Customers Creating Customer Editing Customer Customer Detail Products Creating Product Editing Product Product Detail Invoices Creating Invoice Editing Invoice Invoice Detail Payments Adding Payment Editing Payment Payment Detail Reports Generating & Printing Reports Setting Visible Columns Extracting Reports Common Tasks Using the Search Bar Using Data List Windows Importing Data into Excel Invoice Manager Networking Working with Form Properties File Free Invoice Form with Support for Incomplete Lines on Invoice Body Using Credit Note or Credit Memo Copying Report Worksheets from One Invoice Template to Another Two Versions of Customer Statement Report Custom Fields Custom Fields - Overview Custom Invoice Fields - Tutorial Customizing the Invoice Template Customizing the Invoice Template Tutorial: Creating an Invoice Template with Additional Rows Tutorial: Adding a Notes Field to Your Invoice Template Tutorial: Adding or Removing the "Same As Bill To" Button Tutorial: Adding a Clear button to the Report Worksheets Tutorial: Displaying Summary Information on Customer Statement Tutorial: Creating a Receipt Form Using Multiple-Line Address in Invoice Form Printable Invoice - Invoice Printing in a Flexible Way Adding Postcode Cells Manually to Invoice Template More Topics Registration and Services Reference
Online-Only Articles Billing Software Powered by Excel Buttons in the Template Don't Work Sample Invoice Template: Using handwriting Signature Sample Invoice Template: Using Drop-Down List Sample Invoice Template: Long Product Descriptions Sample Invoice Template: Using Line# as Product IDs How to: Password-Protecting an Invoice Template How to: Transferring Excel Invoice Manager to a New System How to: Removing Auto-created Macros from Your Invoice Template Creating a Packing Slip Template Modifying Standard Fields with Database Designer Sample Invoice Template: Replacing the Product IDs with Line Numbers Invoice Template with Support for Product-Type Specific Tax Rates Editing Paid Invoices Working with Multiple Companies or Currencies Sharing/Linking Tables between Access Databases Tutorial: Adding New Fields to Reports Sample Invoice Template: Displaying 'FREE' for Zero Values Customizing Header and Footer of Printed Invoices Customizing Invoice Template for Pre-printed Invoice Forms Charging GST Tax on Shipping Cost Invoice Form with Fixed or Static Product / Service Items
Home Contact Us Privacy Link to Us
copyright © Office-Kit.com, all rights reserved