Simple Tax Invoice Sample with Tax Rate List

For certain business types you may have to collect excise tax. See Tax and Fee Rates for more information. A working sample template is available at FET Tax Invoice for Truck Tire Business.

If your business is selling to several states or countries, you may need to change the tax rates from invoice to invoice depending on shipping destination. This sample shows you how to implement a drop list to simplify the selection of tax rate.

With a drop down list, you select values from a list, instead of typing in manually. This is a great way to prevent typo errors. With the default sales invoice template shipping with Invoice Manager for Excel, the tax rate is fillable when you create an invoice; but it is not implemented as a dropdown list.

With this Simple Tax Invoice Sample with Tax Rate List, we'll create the drop down list of tax rates by using Excel's data validation features. There are already samples of using drop down list here on Invoicingtemplate.com, such as Auto Repair Invoice Template (2), which adds a "Model" drop down list to the basic template Auto Repair Invoice Template, allowing to select car or vehicle model. Note that the drop down list will not be implemented in the PDF invoice template.

With this sample, we'll focused on the steps of creating the tax rate drop down list. The basic template of this demonstration is c4051 Simple Invoice Template - Discount Amount Field - i.e. we'll use the result of c4051 as the starting point of this customization. The billing form created here can be downloaded for free.

For a template support mixed tax rates on one same billing form / receipt format (for example, beverage tax must be at 3.0% and non-beverage tax rate must be at 5.0%; or if you are selling both general products (goods) and labor (service) to customers, which are two types of taxable items and should be charged with different tax rate), visit Mixed Tax Rates in an Invoice If you need to detail the tax amount for each invoicing item, try Invoice Template with Tax Column.

The base template supports two taxes on the bottom of the form (The default tax names are set to PST - Provincial sales tax, and GST - stands for Goods and Services Tax. It is a federal sales tax, although you can easily change this via the Settings dialog box). For the purpose of this demonstration, we'll use only one tax.

Let's see how the tax rate drop down list is created.

1. Back up the template.
2. Open the template.
3. Click the "Settings" button on the "Invoice" worksheet.
4. Go to the "Taxes" tab.
5. Click to select "One Tax".
6. Change tax name to "TAX". Change Tax rate to the value that you used most often.
7. Click "Apply". This will modify the layout of the invoicing form by hiding the second tax line, so that only one line of tax (including tax name, rate, and amount).
8. Click the "Design Mode" button on the "Invoice" worksheet. This unprotects the spreadsheet, shows gridlines and row/column headings, and also pushes down the "Design" button on Excel "Developer" ribbon tab.
9. Choose an empty location to place the tax rate list on the bill form. In this sample, we choose the range on the column $S and column $T.

   Move out the contents that are already on the columns, i.e. the comment text starting with "To create an invoice" etc. To do this, drag your mouse to select all the content you want to move, and then drag the border of the select area to move it to the new location.

10. Set a slightly different background color (green - blue in this sample) for these two columns.
11. Enter the state names on the first column. Enter the corresponding tax rates on the second column.

    For the purpose of this demonstration, we copied the tax rates from State Sales Tax Rates | Sales Tax Institute (As of 3/1/2016). . Sales and use tax rates change on a monthly basis. This chart is for informational purposes only. As the source origin statement shows, specific questions should be addressed to your tax advisor or a State Department of Revenue, such as Texas Sales Tax. The rate chart gives tax rate for the following states: Alabama, Alaska, Arizona, Arkansas, California, Colorado, Connecticut, Delaware, District of Columbia, Florida, Georgia, Hawaii, Idaho, Illinois, Indiana, Iowa, Kansas, Kentucky, Louisiana, Maine, Maryland, Massachusetts, Michigan, Minnesota, Mississippi, Missouri, Montana, Nebraska, Nevada, New Hampshire, New Jersey, New Mexico, New York, North Carolina, North Dakota, Ohio, Oklahoma, Oregon, Pennsylvania, Rhode Island, South Carolina, South Dakota, Tennessee, Texas, Utah, Vermont, Virginia, Washington, West Virginia, Wisconsin, Wyoming. No matter what business you are running - attorney / legal, medical, catering, restaurant, law firms (whether small or big), health insurance billing or statement / timesheet, car / vehicle sales, graphic designs, freelance, plumbing, marketing, business plans, accounting, house rent (yearly or monthly), or even home businesses for monthly bill, it is import for you to making taxation correct on your invoices.

12. Move the tax name "TAX" one column left to leave a blank cell for state name. In this sample, we move the text from $I$35 to $H$35.
13. Click to select the cell left by moving out "TAX", i.e. $I$35. Name this cell "oknTaxStateName".
14. Switch to Excel ribbon "Data". Choose "Data Validation / Data Validation ...".
15. On the "Data Validation" dialog box, choose "List" from the "Allow" list. Fill the address of the state name list into the "Source" box. In this case, we set it to "=$S$6:$S$56".
16. Right-click the cell, choose "Format Cells" from the shortcut menu. Go to the "Protection" tab, clear the "Locked" option. Click "OK" to close the "Format Cells" dialog box. This makes the cell fillable when the sheet is protected (i.e. when you start to create an invoice).
17. Right-click the tax rate cell ($J$35), choose "Format Cells" from the shortcut menu. Go to the "Protection" tab, check the "Locked" option. Click "OK" to close the "Format Cells" dialog box. This makes the cell not manually fillable as we need to fill it with a formua.
18. Make sure the tax rate cell ($J$35) is selected. Assign it a formula - " =VLOOKUP(oknTaxStateName,S6:T56,2,FALSE)". This "VLOOKUP" formula searches for the state name on the cell named "oknTaxStateName" in the first column of our tax rate table/chart, and return the rate value on the second column. For more information about the VLOOKUP formula, refer to Microsoft document at VLOOKUP function.

    As you can see from this sample, while Microsoft Word (and other programs, like Publisher, Microsoft Works, Adobe Acrobat (PDF), or even Photoshop could be used to create beautiful invoicing forms, Excel is far more easier than others when it comes to do calculations. By using Excel formulas, you can easily define the relationship between fields for Invoice Manager for Excel.

19. Exit design mode by clicking the "Design Mode" button on the "Invoice" ribbon tab.
20. Click the "Invoices" button on the "Invoice" ribbon tab.
21. Click "Custom Fields" to open the "Custom Field Manager" dialog box.
22. Click "Add Field".
23. Fill in the new field information: Field Name - TaxStateName; Type - "Text"; Default Value - "Alabama" (This is the name of the state that you sell to most frequently. When you click the "Clear & New" command to have the bill form ready for next invoice, the cell "oknTaxStateName" will be filled with this default value); Size - 50.
24. Close the "Add New Field" dialog box.
25. Close the "Custom Field Manager" dialog box.
26. Save the template by clicking the "Save" button on Excel quick access toolbar.

With this solution, Invoice Manager for Excel does not save the tax rate table to database along with each invoice. This means that, if you 1) - Save an invoice which sells products to a state, say Iowa; 2) - Several months later, you update the tax rate of Iowa; 3) - You reload the invoice using the Excel billing form; 4) - You save the invoice again (the default implementation of Invoice Manager for Excel does not allow to save paid invoice, but we do have hacks / workarounds for this is a customer asked). In this situation, the bill that is re-calculated with the new rate table will differ from the original invoice. To overcome this, you need to save the entire rate table using custom fields.

A sales tax is a tax paid to a governing body for the sales of certain goods and services. Usually laws allow (or require) the seller to collect funds for the tax from the consumer at the point of purchase. Besides state tax, you may also need to consider local sales tax. For example, Texas imposes a state sales tax on all retail sales, leases and rentals of most goods, as well as taxable services. Texas cities, counties, transit authorities and special purpose districts have the option of imposing an additional local sales tax for a combined state and local tax rate of up to 8 1/4% (.0825). Taxation itself is a complex topic. If your business is located in Australia, try Australian GST Invoice Template.