• Invoice Template #: C4-010

This service vat invoice template does not use Ship To fields, but added several new fields on invoice header, such as VAT Registration No., Application Type, VAT Total. It also added two new columns, VAT Rate and VAT Amount. For each product or service item, you can set a VAT percentage rate, which is loaded when you pick up a product or service item by clicking one of the icon buttons. For a product where there is no VAT payable there simply will be no figure in the VAT section, therefore, where VAT is zero the price and the total will read the same. The currency symbol of this VAT invoice template is set for UK (United Kingdom) format.

In order to create this VAT invoice template from the default invoice template shipped with Excel Invoice Manager setup program, there are 3 types of tasks to do, including adding new fields to the invoice header, such as the VAT Registration No. field, adding new columns to the invoice body, such as the VAT Rate column and the VAT Amount column, and finally, moving cells / fields and controls to create the desired invoice form layout you like, as well as formatting cells.

The first two tasks are all related to custom fields. Detailed documents on custom fields can be found by clicking the following links, Custom Invoice Fields - Overview, and Custom Invoice Fields - tutorial.

Adding a new field to the invoice header

If a field appears on the invoice form only once per invoice, you should add the field to the Invoice Header database table (i.e. InvHdr). If the field is related to customer and must be loaded on the invoice form when you pick up a customer, the field should be added to Customer database table too. To comply with this rule, we should add the VAT Registration No. field to both the Customer and Invoice Header database tables, as detailed below.

  1. Start Database Designer. This little utility program is located at Windows Start menu -> (All) Programs -> OFFICE-KIT.COM -> Excel Invoice Manager -> Database Designer.
  2. Click "Next" until you reach the Step 1 - Open and backup database definition file screen.
  3. Click Open definition file to browse to and open the database definition file, which is always named FDINFO10.rst. The folder where the FDINFO10.rst file stores varies depending on your Windows version etc., as detailed in Installing / Upgrading document.
  4. Backup the database definition file.
  5. Click Next to go to the Step 2 - Define custom fields in database definition file screen.
  6. On the Tables list, click Customer to select it. The right pane displays the custom fields defined in the Customer database table.
  7. Click Add Field to open the Add a New Field window.
  8. In the Field Name, Field Type and Size boxes, enter CustVatRegNo, Text, 50, respectively. This means that we are creating a custom field named CustVatRegNo, which is a text field and the max allowed length of the text field is 50.
  9. Click OK on the Add a New Field window to create the new field. You are now returned to the Step 2 screen; here you can find that the CustVatRegNo field now is a custom field of the Customer database table.
  10. Click Invoice Header on the left pane to select this database table. The right pane lists the custom fields added to this table.
  11. Click Add Field to open the Add a New Field window.
  12. In the Field Name, Field Type and Size boxes, enter CustVatRegNo, Text, 50, respectively. This means that we are creating a custom field named CustVatRegNo, which is a text field and the max allowed length of the text field is 50.
  13. Click OK on the Add a New Field window to create the new field. You are now returned to the Step 2 screen; here you can find that the CustVatRegNo field now is a custom field of the Invoice Header database table.

    Since we want the field (VAT Registration No.) to be loaded when a customer is selected, and to be saved along with the other invoice fields when the invoice is saved, the custom field added to the Customer and Invoice Header database tables must be exactly the same name (CustVatRegNo), the same type (text), and the same size (50). This is necessary to make sure the field loaded from the Customer database table can be saved to the Invoice Header database table.

  14. Click Save to save the modified database definition file.
  15. Click Next to go to the Step 3 - Apply database definition file to databases screen.
  16. Click Select database file to browse to and open the database file.
  17. Click Backup database to backup your database. This allows you to easily return to a working database in case something goes wrong.
  18. Click Apply to apply the new (modified) database definition file to the database you specified. This adds, in this case, the VAT Registration No. custom field to both the Customer and Invoice Header database tables.

    Tip: You should exit Excel if Excel is running, to make sure that the database to be modified is not locked by a running instance of Excel Invoice Manager.

  19. Click Finish to exit Database Designer.

After adding the custom field to database, the next step is to specify a cell on your invoice form to put the VAT Registration No. value. That is, the task here is to specify a cell, so that when a customer is selected, the customer's VAT Registration No. is loaded into this cell; and when you save an invoice, the value of this cell is saved into the VAT Registration No. field of the Invoice Header database table. This is implemented by naming a cell, as detailed below.

  1. Open your service VAT invoice template in Excel.
  2. Unprotect the Invoice worksheet.
  3. Click to select a not-used cell, i.e. the cell where you want to put the VAT Registration No. information of a customer.
  4. Name the cell oknCustVatRegNo. (See Customizing Invoice Template - Names)

    Tip: The link between an Excel cell and the database is created by names. You name the cell oknCustVatRegNo, Excel Invoice Manager automatically loads the value of database field CustVatRegNo into this cell, and saves the value of this cell into CustVatRegNo field on saving an invoice. "okn" is the name prefix of all custom fields.

  5. Right-click the cell, choose Format Cells.
  6. Switch to the Protection tab, and then uncheck the Locked box. This makes sure that Excel Invoice Manager can write/alter the cell value when the invoice form is protected.

    Tip: If the cell of the custom field contains a formula, the Locked box must be checked to protect the formula. This way, Excel Invoice Manager does not write to / alter the value of the cell, but will let Excel calculate the cell value.

  7. Now protect the Invoice worksheet.
  8. Save your invoice template by clicking Excel menu File -> Save in Excel 2000/XP/2003, or by clicking Office button -> Save in Excel 2007, or by clicking File -> Save in Excel 2010.

Adding a new column to the invoice body

Just like adding a field to invoice header, adding a new column to invoice body involves two parts - adding a field to database tables using Database Designer, and naming the cells on the invoice body so that when you pick up a product or service, the custom field can be loaded into the named cells, and when you save an invoice, the named cells are saved into the corresponding database field.

Unlike adding a field to invoice header, where you add database field to the Invoice Header database table and if a field is related to customer then it should be added to the customer database table, adding a new column to invoice body requires you to add the field to the Invoice Body database table, and if the field is related to product (i.e. the value of the field must be loaded into the invoice form when you pick up the product or service item) it should be added to the Product database table.

Considering this VAT invoice template, we use two custom columns (i.e. custom fields), VatAmt and ProdVat. ProdVat means the VAT percentage rate of product or service; VatAmt means VAT amount, which is calculate from VAT percentage rate and Price (as this is a service VAT invoice template and the Quantity is assumed to be 1). Based on this usage analysis, it is clear that ProdVat should be added to both Product and Invoice Body database tables, and VatAmt should be added to Invoice Body database table. The brief steps are listed below, and for detailed instructions please refer to the preceding section Adding a new field to the invoice header.

  1. Start Database Designer.
  2. Open database definition file - i.e. the file named FDINFO10.rst. Backup it.
  3. Select Invoice Body database table, add custom field ProdVat, with the type set to decimal (10, 5); add custom field VatAmt, with the type set to decimal (10, 2).
  4. Select Product database table, add custom field ProdVat, with the type set to decimal (10, 5).
  5. Save the modified database definition file.
  6. Open database file. Backup it.
  7. Apply the modified database definition file to database.
  8. Exit Database Designer.

The next step is to name the cells on the invoice form to let Excel Invoice Manager know where to read/write the custom fields added to database.

  1. Open your invoice template.
  2. Unprotect the Invoice form.
  3. Decide where to put the column. In this VAT invoice template, we put the VAT Amount column inside the Print Area, and put the VAT Rate column outside the Print Area. People in the UK know the VAT percentage rate; therefore, stating the amount due such as (Fee) + (VAT) = (total) is better than putting a percentage.
  4. Name the cells. For the VAT Amount column, you name the cells oknVatAmt_1, oknVatAmt_2, oknVatAmt_3 ...oknVatAmt_12; And for the Vat Rate column, name the cells oknProdVat_1, oknProdVat_2, oknProdVat_3 ...oknProdVat_12.
  5. Drag your mouse to select all the oknProdVAT_xx cells.
  6. Right-click one of the select cells, choose Format Cells.
  7. On the Protection tab, uncheck Locked option.
  8. Drag your mouse to select all the oknVatAmt_xx cells.
  9. Right-click one of the selected cells, choose Format Cells.
  10. On the Protection tab, check the Locked option.
  11. Fill in formula "=ROUND(oknProdVat_?*oknPrice_?,2)" to oknVatAmt_xx cells. For example, for the oknVatAmt_1 cell, the formula should be "=ROUND(oknProdVat_1*oknPrice_1,2)"; and for the oknVatAmt_2 cell, the formula to fill in should be "=ROUND(oknProdVat_2*oknPrice_2,2)", and so on. This formula calculates VAT amount automatically when you load product or service items.
  12. Tip: Since this is a service VAT invoice template, the Quantity of product or service is filled with 1.
  13. Once finished, protect the Invoice worksheet.
  14. Save your invoice template by clicking Excel menu File -> Save in Excel 2000/XP/2003, or by clicking Office button -> Save in Excel 2007, or by clicking File -> Save in Excel 2010.

Moving cells and controls, formatting cells with UK based currency symbol

It's clear that the invoice format of the VAT invoice template is very different from what offered by the default invoice template installed by Excel Invoice Manager setup program, yet it is still easy to create the new invoice form from the default invoice form. The basic tasks are moving cells and controls, formatting cells.

If you don't need a cell or control to appear on the printed invoice, move it out of the Print Area. Print Area is an Excel defined range name, which tells Microsoft Excel what range to print when you execute the Print command, as detailed in Printable Invoice - Invoice Printing in a Flexible Way. For example, the VAT invoice template does not use the Quantity column, so we move it out of the Print Area. Moving cells is also necessary if you want a cell/control to appear on a new location.

To move a cell or a control on the invoice template, follow the steps below.

  1. Unprotect the Invoice form.
  2. Click to select the cell or control you want to move, or drag your mouse to select all the cells you want to move (such as a column), or if the cells / controls to move are not adjacent, push CTRL key on your keyboard and click the cells / controls one by one.
  3. Drag the selected cells / controls to the new location.
  4. Move other cells or controls as needed.
  5. Once finished, protect the Invoice form.

Excel provides comprehensive tools for formatting the invoice form and cells. Many of the tools can be found on the toolbars in Excel 2000~2003, or be found on the ribbon tab in Excel 2007~2010, yet there are still some options can be found only on the Format Cells dialog box. The following procedure describes how to set a cell to use UK currency symbol "?隆锚", to replace the default US-dollar currency symbol "$".

  1. Unprotect the Invoice form.
  2. Right-click the cell for which you want to set its format, or drag your mouse to select multiple cells and right-click one of the selected cells, choose Format Cells command from the menu.
  3. On the Number tab, click "Currency" on the Category list.
  4. Choose "?隆锚 English (United Kingdom)" from the Symbol list.
  5. Set the other options as required.
  6. Click OK to close the Format Cells dialog box.
  7. Protect the Invoice form.
  8. Save your invoice template by clicking Excel menu File -> Save in Excel 2000/XP/2003, or by clicking Office button -> Save in Excel 2007, or by clicking File -> Save in Excel 2010.

There are many other options you can adjust/set on the Format Cells dialog box. Once you are familiar with various formatting tools provided by Microsoft Excel, it is easy to create the service VAT invoice template based on the default invoice template shipped with Excel Invoice Manager setup program. The downloadable VAT Service Invoice Template implements what described in this document and is ready-to-use, it can also be used as a starting point of customizing your own VAT invoice template.

See also