Screenshots and Images
The printed version.
Open the template in Excel.
You are safe to download the resources. We've added our digital signatures to the files.
Download this if you have already installed the Microsoft Store edition of Invoice Manager for Excel, and are looking for additional customized templates.
Download this if you want this design to be set as the default template by the installer program.
This sample Excel invoice template demonstrates how to do special customer pricing based on customer values / types. By default this form design classifies clients as Distributor, Wholesale, Retail, individual and Special categories.
If you have many customers it is likely you need to offer different prices to different types of customers, such as distributor, wholesale, retail, individual, or even special pricing. This invoice template provides a sample on how to implement customer-type based price levels.
By utilizing custom fields, the default invoice template shipped withsetup program was modified / customized to implement the following.
- For each customer, you can set its price type to one of the following: , , , , or .
- For each product, you can set 4 prices, , , and .
- When creating an invoice, the price type is loaded / displayed on the invoice form when you select a customer. It is allowed to modify the price type of this customer for this invoice. This information is not included in the printed invoices.
- When selecting a product / service item, by using Excel formulas one of the 4 prices of the current product / service item is chosen according to the customer's price type.
- If the price level is , the invoice template allows you to manually enter a price for the current product or service item. This is necessary if the customer is very special, say an employee of your company.
What to do to create the invoice template
To implement the features described in the preceding section, firstly we have to define a custom fieldfor the price type of a customer. Since this information should be loaded when the customer is chosen, and to make sure an existing invoice can be reloaded exactly as it was created even if the price type of a customer was changed after the invoice was created, this information should be saved along with the other invoice data when an invoice is saved, for this reason the custom field should be added to both the and database tables by using .
The possible values of the creating invoices, so its a good choice to use a drop list on the invoice form to prevent typo errors and make it easier to choose a customer price type. Microsoft Excel provides a handy tool for creating drop-down list on the invoice form, which is called list. Simply put, all you need to do is to set the valid value of the cell to a , and then provide the values of the list to be one of the following: , , , , or . A detailed tutorial on using drop-down list on an invoice template could be found here, excel invoice template with drop-down list.field are limited to " ", " ", ", " ", or " ". It is not necessary to let the end-user manually type in value on
Secondly, the price level fields should be added to both theand database tables too. This invoice template uses the default field for storing distributor price, so we need 3 additional price fields, , and , for price level of wholesale, retail and individual customers, respectively. For the database table, in addition to the 3 price level fields defined in the table, it requires another price level field for storing price value if the customer's price type is . There are also other custom fields added to the database table to meet the requirements of reporting, discounting, etc.
Thirdly, the link between the cells on the invoice form and the fields in the backend database should be established. This link is created by using names. For example, if a field is namedin the backend database, the cell name should be " ", where " " is the prefix which is always the same for all the custom fields used by , and " " is the database field name. That is, on loading customer information, writes the value of the field to the cell named on the invoice form, and on saving an invoice, the value of the cell is saved to the field named in the database table.
Fourthly, the relationship between the cells on the invoice form should be defined to implement the business logic between cells. For example, the amount ofshould be deducted from . The most important business logic here to implement is how to choose the correct price level according to the customer's price type. The Excel formula is as follows:
Theworksheet function returns the relative position of an item in an array that matches a specified value in a specified order. For example, if is " ", the worksheet function returns the position "2" as " " is the second value in the customer-type dropdown list. The worksheet function uses the first parameter to return a value from the list of the other value arguments. For example, if the first parameter is 2 (the result of the worksheet function), the result of the worksheet function should be because is in the second place in the value parameter list. Detailed documents on these worksheet functions can be found in Excel's help documents.
There are also other simple formulas used in the invoice template, such as those for checking for errors and those for calculating discounts. Download the Excel invoice template and unprotect the invoice software program.form to see the details. With this example, it's clear that Microsoft Excel provides powerful features for formatting and organizing data. By using Excel as it's front-end and thus combining all the features provided by Excel, makes it possible to create your very own
Custom fields added for the invoice template
As detailed in the preceding section, there are a number of custom fields added to the invoice template to implement the features not exist in the default invoice template that shipped with the setup program of. The following chart shows all the fields and their tables and types.
|Table name||Field name||Field type|
|Invoice Header (InvHdr)||CustType||text (50)|
|Invoice Header (InvHdr)||VendorID||text (30)|
|Invoice Header (InvHdr)||TenDayDate||text (30)|
|Invoice Body (InvBdy)||WholesalePrice||decimal (12,4)|
|Invoice Body (InvBdy)||RetailPrice||decimal (12,4)|
|Invoice Body (InvBdy)||IndividualPrice||decimal (12,4)|
|Invoice Body (InvBdy)||SpecialPrice||decimal (12,4)|
|Invoice Body (InvBdy)||InvoicePrice||decimal (12,4)|
|Invoice Body (InvBdy)||NetTotal||decimal (15,4)|
|Invoice Body (InvBdy)||CaseDiscount||decimal (15,6)|
The support for custom fields is a key feature that is available in the, and editions of . The possibility of customizing on the database level, combined with powerful Microsoft Excel based front-end, makes flexible billing software for creating and managing invoices.
Notes on using the invoice template
Here are some tips and notes on using the downloadable invoice template.
- For each product, you can now set 3 prices on the window. The regular is used as " ". You can also set a and a in the tab of the window - To open a window, double click a product line in the window which can be opened by clicking the button on the form.
- When creating invoice, you enter customer information as usual, then choose products by clicking the icon button on the product lines. You can set the Customer Type ( ) by using the drop-down list on the left pane. The price on the invoice will be changed according to the customer type you choose.
- In case you want to enter price manually, choose as the , and then enter prices into the column located on the left pane.
- The " " column has been added to the , but it cannot be printed on a standard A4 paper now. Hide one or two of the other columns if you want to print the on A4 paper.
- There are several columns put outside the printed invoices, but are necessary for the invoice template to function as designed. You can resize or hide one or several of the columns, but cannot delete the columns. of the invoice form. These columns are not included in the
Note: This template was originally published on office-kit.com as c6-006.
Format and Specification
|Name||Customer Value Based Pricing|
|Category||Sales Invoice Template|
|Release Date||Saturday, March 11, 2017|
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||15.75|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|
|Price||Free (0.00 USD)|
|System||Windows 7 and later, and Excel 2007 and later.|