Invoice Template VAT UK

This UK VAT invoice template for service added a VAT rate drop down list to c4010 Service VAT Invoice Template. This allows you to pick a vat rate from the list on creating invoices, instead of type in manually. The drop down is implemented using Excel data validation feature.

The invoice form has a similar layout as c4010 Service VAT Invoice Template, because the major modifications are made outside of the printable worksheet area defined by the Print_Area range name.

When invoicing your customer, you pick a service item in the detail section as usual by clicking the on-sheet picker button or the static rectangle button below the "Product ID" label. And then, you click the cell below "VAT Rate", which shows a drop-down button when the cell is activated. Click the drop down button to pick a proper rate.

The current UK VAT rate could be found on VAT rates - GOV.UK. For example if you are providing children's car seats and home energy, you should choose reduced rate at 5%; if the invoiced item is food or children's clothes, you should choose zero rate (0%).

For a list of templates designed for United Kingdom, visit Invoice Templates for United Kingdom. For a list of VAT invoice templates, visit VAT invoice templates.

This form design was originally published on office-kit.com as C4-010. The following documents disclose design details.

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 billing template is set for UK (United Kingdom) format.

In order to create this form layout from the default template shipped with Invoice Manager for Excel 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.

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.

For a detailed tutorial on adding custom fields, visit this simple example.

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 the template in Excel.
2. Switch to design mode by clicking the "Design Mode" button on Invoice Manager for Excel ribbon tab.
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 or map between an Excel cell and the database is created by names. You name the cell oknCustVatRegNo, Invoice Manager for Excel 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 Invoice Manager for Excel 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, Invoice Manager for Excel does not write to / alter the value of the cell, but will let Excel calculate the cell value.

7. Exit design mode by clicking the "Design Mode" button again.
8. Save your invoice template by clicking Excel menu File -> Save in Excel 2003, or by clicking Office button -> Save in Excel 2007, or by clicking File -> Save in Excel 2010 / 2013 / 2016.

Adding a new column to UK VAT invoice form

Just like adding a field to invoice header, adding a new column to invoice body involves two parts - adding a field to database tables, 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("InvBdy") 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 next step is to name the cells on the invoice form to let Invoice Manager for Excel know where to read/write the custom fields added to database.

1. Open your invoice template.
2. Switch the worksheet to design mode.
3. Decide where to put the column. In this VAT invoice template, we put the VAT Amount column inside the printable area, and put the VAT Rate column outside the printable 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, exit the Excel worksheet from design mode.
14. Save your invoice template.

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 Invoice Manager for Excel 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. 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 worksheet.
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 sheet by exit design mode.

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 worksheet.
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 "£ (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 sheet.
8. Save your template.

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 UK VAT invoice template based on the default invoice template shipped with Invoice Manager for Excel setup program.