You are safe to download the resources. We've added our digital signatures to the files.
Without database file. How to download and use
Download this if you have already installed UIS, but 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.
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
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.
Here are some tips and notes on using the downloadable invoice template.
Note: This template was originally published on office-kit.com as c6-006.
Like other form designs here on InvoicingTemplate.com, Price per Customer is free (offered to you at a price of 0.00USD). It works on Windows XP and all newer versions of Windows, and Excel 2007, 2010, 2013 or 2016.
|Name||Customer Value Based Pricing|
|Category||Sales Invoice Template|
|Release Date||Saturday, March 11, 2017|
|Format (XLS or XLSX)||.xlsx|
|Excel Version||Excel 2003 / Excel 2007 / Excel 2010 / Excel 2013 / Excel 2016|
|Line Height (Points)||15.75|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|