Invoice Template with Support for Product-Type Specific Tax Rates

If you offer different product types to your customers, such as labor and general products, the default tax system provided by Excel Invoice Manager may not satisfy you because different product types need a different tax rate to be applied automatically.

Fortunately, Excel Invoice Manager supports custom fields, a powerful feature that allows you to add your own fields to the Customer, Product, Invoice Header and Invoice Body database table. In this tutorial, we will create an invoice template that chooses a tax rate automatically depending on the type of the product you add, by utilizing custom fields.

Customizing the Invoice Template

1. Backup your invoice template. This ensures that you can restore to a working template in case the template that you are customizing is damaged.
2. Open the invoice template.
3. Unprotect the Invoice worksheet. (How?)
4. Switch to design mode. (How?)
5. Display gridlines and row/column headers. (How?)
6. Click and select the shape object saying "Fill invoice items continuously......", move it to an empty area by dragging and dropping, such as column AA-AB.
7. Enter "Product Type" in cell R19, Enter "Applied Tax Rate" in cell S19.
8. Assign a name to the cell R20, R21, R22, R23...... R31, respectively. As detailed in the Custom Fields - Overview document, cell names of custom fields in the invoice form must match the field names in the database. In this sample, we name the cells oknProductType_1, oknProductType_2, oknProductType_3, oknProductType_4...... oknProductType_12, where _1, _2, _3, _4......_12 are the line numbers of the current product item.
9. Assign a name to the cell S20, S21, S22, S23......S31, respectively. Similar to the above step, we name these cells oknAppliedTaxRate_1, oknAppliedTaxRate_2, oknAppliedTaxRate_3, oknAppliedTaxRate_4...... oknAppliedTaxRate_12.
10. Drag your mouse to select the range R20:R31. Set a different background color for the range by right-clicking the range and choose Format Cells from the menu - this range will be editable so it needs a different background color than the default. Unlock the range by clearing the Locked option on the Protection tab of the Format Cells window.

    Once finished, set the background color for the range S20:S31 - we will use formulas to choose the applied taxes, so it is better to set it to a grayed color to indicate that the cells are not editable when creating invoices.

    
    (Click to enlarge)

11. Drag your mouse to select the range J33:N34 where we will put the tax information, including labels and formulas. Set a special background color for this range to distinguish it from the other parts.

    
    (Click to enlarge)

12. Drag your mouse to select the range J33:K34, click the Merge and Center button on the Formatting toolbar.
13. Enter "TAX" in the newly merged cell J33. Set a slightly bigger font for the cell.
14. Enter "General" in the cell L33; enter "Labor" in the cell L34, to replace the default label "PST" and "GST" respectively.
15. Drag your mouse from $B to $F on the column headers, right click on of the selected column headers, choose Unhide.
16. Enter the tax rate for your first tax (General) in the cell named oknTax1RateDefault ($C$12); Enter the tax rate for you second tax (Labor) in the cell named oknTax2RateDefault ($C$13). These are the default tax rates that are applied to the tax rate cells in the Print_Area each time you click the Clear & New button.
17. Drag your mouse to select column $C and $D. Right click one of the selected column headers, choose Hide.
18. Drag your mouse to select the range R20:R31.
19. Click Excel menu Data -> Validation.
20. On the Settings tab, choose List from the Allow box; click the button in the Source box.
21. Drag your mouse to select the range where you entered product type names, and then press the Enter key on your keyboard - in this sample, you select the range $L$33:$L$34.

    
    (Click to enlarge)

22. Click OK to apply the data validations. This creates a drop-down list for the Product Type column. For more detailed information about drop-down list, please see Invoice Template Sample - Using Drop-Down List.
23. Enter formulas into the Applied Tax Rate column , from top to bottom. The formula of the first cell is:

    =IF(oknProductType_1="",0,VLOOKUP(oknProductType_1,$L$33:$M$34,2))

    And the formula for the second cell is:

    =IF(oknProductType_2="",0,VLOOKUP(oknProductType_2,$L$33:$M$34,2))

    ......

    The formula for the twelfth line is:

    =IF(oknProductType_12="",0,VLOOKUP(oknProductType_12,$L$33:$M$34,2))

    As you can see, this formula choose a proper tax rate value according to the value of the Product Type cell on the current line.

24. Enter the following formula for the first tax cell:

    =ROUND(SUMIF(oknProductType_1:oknProductType_12,"=General"
    ,oknLineTotal_1:oknLineTotal_12)*oknTax1Rate,2)

    The above formula calculates line totals for all the products whose type is set to General, and then multiplies it by the corresponding tax rate.

    Enter the following formula for the second tax cell:

    =ROUND(SUMIF(oknProductType_1:oknProductType_12,"=Labor"
    ,oknLineTotal_1:oknLineTotal_12)*oknTax2Rate,2)

    
    (Click to enlarge)

25. Enter the following formula for the Total cell:

    =ROUND(oknSubTotal + oknShippingCost + oknTax1+oknTax2,2)

26. Fine tune your invoice template as you like - set the borders, colors, fonts, etc.
27. Hide gridlines and row/column headers. (How?)
28. Exit design mode.
29. Protect the Invoice worksheet.
30. Save the customized invoice template by clicking Excel menu File -> Save.
31. Exit Microsoft Excel.

Customizing the Database

1. Start Database Designer. You can find it on Windows Start menu -> (All) Programs -> OFFICE-KIT.COM -> Excel Invoice Manager -> Database Designer.
2. Click Next to go to the Step 1 screen.
3. Open the database definition file FDINFO10.rst. You can find this file in the folder where you store you database file, or in the installation folder of Excel Invoice Manager.
4. Backup the database definition file.
5. Click Next.
6. Click Product in the Tables list.
7. Click Add Field.
8. Set the new field as the following: Field name, ProductType; Field type, Text; Size, 30.

   
   (Click to enlarge)

9. Click OK.
10. Click Invoice Body in the Tables list.
11. Click Add Field.
12. Set the new field as the following: Field name, ProductType; Field type, Text; Size, 30.
13. Click OK.
14. Click Add Field again.
15. Set the new field as the following: Field name, AppliedTaxRate; Field type, Decimal; Precision, 6; Scale, 4.
16. Click OK.
17. Click Save.
18. Click Next to go to the Step 3 screen.
19. Open your database file by clicking the Select database file button.
20. Backup the database file.
21. Click Apply. This applies the modified database definition file to your database.
22. Click Finish to exit Database Designer.

Test

1. Open the customized invoice template. Please make sure it is connected to the database file that you have applied the new database definition file to.
2. Click the Clear & New button. Make sure the newly created ProductType and AppliedTaxRate column are cleared too. Make sure the tax rates ($M$33, $M$34) are set to the default values.
3. Click the Products button.
4. Click Add Product to add one or two new products. Make sure you have set the ProductType value on the Custom Fields tab to either General or Labor.
5. Try to create an invoice. Make sure all the formulas are working. Also please note that when you select a product, the ProductType field is filled automatically too.
6. Save the invoice, and then reload it by clicking the Invoices button -> Edit in Excel command. Make sure it is reloaded correctly.

Download the Sample Invoice Template

Please note this template works only if your edition of Excel Invoice Manager supports custom fields - you need the Platinum, Pro or Enterprise edition of Excel Invoice Manager. Also please note since this template uses a customized taxing system, the Taxes tab of the Settings window (opened by clicking the Settings button on the Invoice worksheet) does not work.

invoice_template_with_support_for_product_type_specific_tax_rates.zip (119 KB)

To run the customized invoice template:

1. Unzip the file you download. Put all files in the same folder, say C:\MyTestFolder or a subfolder inside "My Documents".
2. Open the template.
3. Test.