Customer Value Based Pricing

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 with Excel Invoice Manager setup program was modified / customized to implement the following.

• For each customer, you can set its price type to one of the following: distributor, wholesale, retail, individual, or special.
• For each product, you can set 4 prices, distributor, wholesale, retail and individual.
• 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 Special, 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 field CustType for 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 CustType custom field should be added to both the Customer and Invoice Header database tables by using Database Designer.

The possible values of the CustType field are limited to "distributor", "wholesale", retail", "individual", or "special". It is not necessary to let the end-user manually type in CustType value on 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 Data Validation list. Simply put, all you need to do is to set the valid value of the CustType cell to a List, and then provide the values of the list to be one of the following: distributor, wholesale, retail, individual, or special. A detailed tutorial on using drop-down list on an invoice template could be found here, excel invoice template with drop-down list.

Secondly, the price level fields should be added to both the Product and Invoice Body database tables too. This invoice template uses the default Price field for storing distributor price, so we need 3 additional price fields, WholesalePrice, RetailPrice and IndividualPrice, for price level of wholesale, retail and individual customers, respectively. For the Invoice Body database table, in addition to the 3 price level fields defined in the Product table, it requires another price level field SpecialPrice for storing price value if the customer's price type is Special. There are also other custom fields added to the Invoice Body 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 named CustType in the backend database, the cell name should be "oknCustType", where "okn" is the prefix which is always the same for all the custom fields used by Excel Invoice Manager, and "CustType" is the database field name. That is, on loading customer information, Excel Invoice Manager writes the value of the field CustType to the cell named oknCustType on the invoice form, and on saving an invoice, the value of the oknCustType cell is saved to the field named CustType in the Invoice Header 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 of Case Discount should be deducted from Line Total. 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:

=CHOOSE(MATCH(oknCustType,oknCustTypeList,0),
oknPrice_1,oknWholesalePrice_1,
oknRetailPrice_1,oknIndividualPrice_1,oknSpecialPrice_1)
                                     

The MATCH worksheet function returns the relative position of an item in an array that matches a specified value in a specified order. For example, if oknCustType is "Wholesale", the MATCH worksheet function returns the position "2" as "Wholesale" is the second value in the customer-type dropdown list. The CHOOSE 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 MATCH worksheet function), the result of the CHOOSE worksheet function should be oknWholesalePrice_1 because oknWholesalePrice_1 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 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, Excel Invoice Manager makes it possible to create your very own invoice software program.

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 Excel Invoice Manager. The following chart shows all the fields and their tables and types.

The support for custom fields is a key feature that is available in the Platinum, Pro and Enterprise editions of Excel Invoice Manager. The possibility of customizing on the database level, combined with powerful Microsoft Excel based front-end, makes Excel Invoice Manager 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 Product Edit window. The regular Price is used as "Wholesale Price". You can also set a DistributorPrice and a RetailPrice in the Custom Fields tab of the Product Edit window - To open a Product Edit window, double click a product line in the Products list window which can be opened by clicking the Products button on the Invoice 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 (CustType) 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 Special as the Customer Type, and then enter prices into the Special column located on the left pane.
• The "Customer" column has been added to the Sales Report, 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 Sales Report on A4 paper.
• There are several columns put outside the Print Area of the invoice form. These columns are not included in 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.

Note: This template was originally published on office-kit.com as c6-006.