Simple Sample - Discount Percentage Field

Giving discount on a percentage basis is a common practice in many businesses. This simple invoice template demonstrates how to add the fields / feature.

With c4051 Simple Invoice Template - Discount Amount Field, we learnt how to add a discount amount field, where the discount amount is subtracted directly from "Subtotal" by using Excel formulas. This new simple invoice template will be based on c4051 Simple Invoice Template - Discount Amount Field, and by adding a discount percentage field, you enter the discount percentage instead of discount amount, and then calculate the discount amount based on discount percentage by using Excel formula. This sample, just like other invoicing templates and forms available here on InvoicingTemplate.com, demonstrates how flexibility it could be by using Excel and Invoice Manager for Excel as your invoicing tool.

The implementation of c4051 Simple Invoice Template - Discount Amount Field adds the discount amount field on the bottom of the form at J35 and K35. In this new sample, we'll add percentage fields on the same row but before the discount amount fields, at G35 and H35.

Now let's look at the steps required to add the new Discount Percentage field.

1. First, back up the template and database file. You can do this by simply copying the files in Windows Explorer.

   Unlike the other pure Excel-based invoicing solutions, Invoice Manager for Excel stores data (customers, products, invoices and payments) in a backend database. So you need to back up the database before modifying its structure or definitions (like in this sample, we'll add the discount percentage field).

2. Open your invoice template in Microsoft Excel.
3. Click the "Design Mode" button on the "Invoice" ribbon tab.
4. Click cell G35, enter the text label "DISCOUNT PERCENTAGE". You may like to make the cell format "Right-aligned" so that the text won't cover the next cell H35. To do this, with G35 still selected, click the "Align Right" button in the "Alignment" group on the "Home" ribbon tab.
5. Click to select the cell H35. Enter the cell name "oknDiscountPercentage" into the name box on the left-side of Excel formula bar.
6. Right-click the same cell, and choose Format Cells from the shortcut menu. On the "Format Cells" dialog box, go to the "Protection" tab and clear the "Locked" property.

   A locked cell is not manually editable / fillable when the worksheet is protected. So we have to clear the "Locked" property because on creating invoices we need to be able to fill in the percentage value.

   Now go to the "Number" tab of the "Format Cells" dialog box. From the "Category" list choose "Percentage". Adjust the "Decimal Place" if you like. In this sample, we just keep the default value "2".

   Click OK to close the "Format Cells" dialog box.

7. Click the "oknDiscountAmount" cell at K35, modify its formula to be "=ROUND( SUM ( oknLineTotal_1:oknLineTotal_12 ) * oknDiscountPercentage,2)".
8. Right-click the K35 cell and choose "Format Cells" dialog box. Go to the "Protection" tab. Check the "Locked" property. Since we're calculating discount amount using a formula, so there is no need to make it manually fillable or editable on creating invoices. By enabling the "Locked" property, we prevent breaking the formula accidently by manually entering values into this cell on creating invoices.

   We've already added proper formulas for both tax cells that consider the discount on tax calculations. So in this sample we won't customize the tax formulas again.

9. Now exit design mode by clicking the "Design Mode" button again on the "Invoice" ribbon tab because we need the Custom Field Manager to modify the database to create the new percentage field.
10. Click the "Invoices" button on the "Invoice" ribbon tab.
11. Click "Custom Fields".
12. On the "Custom Field Manager" dialog box, make sure "Invoice Header" is selected on the "Database Table" list. Click "Add Field" to open the "Add New Custom Field" dialog box.
13. Fill in these values - Field Name: DiscountPercentage; Type: Decimal; Precision: 6; Scale: 4.

    Note that the field name ("DiscountPercentage") must match the cell name defined on the "Invoice" spreadsheet. Invoice Manager for Excel determines which database field to store each cell value by using names. It is the names (Excel cell name and database field name) creates the relationship between an Excel cell and database field.

    Also note since we allow 2 digits after the decimal place on the "oknDiscountPercentage" field, the "Scale" of this custom field must be 4 or greater in order to store the value completely.

14. Click OK to close the "Add New Custom Field" dialog box.
15. Click "Close" to close the custom field manager dialog box.
16. Click the "Save" button on Excel quick access toolbar to save the customized template.

Is your business giving discounts by percentage? The sample invoice template created with this tutorial might be helpful. With this tutorial, we also learnt how to using custom fields (also called user-defined fields) with Invoice Manager for Excel.