Simple Sample - Discount Amount Field

If your business applies discounts frequently, it is better to list the discount on the bottom on the invoicing form. This customized simple invoice template demonstrates the steps required to add a Discount (Amount) field.

The result of this customization is a simple invoice template that have a discount amount field on the bottom of the invoice form, which could be downloaded for free here on InvoicingTemplate.com.

If you use discounts rarely, a discount could be added as an item on the invoice detail section. To do this in Invoice Manager for Excel, you first define the discount as a product (i.e. invoicing item). On creating an invoice if a discount must be given, you enter the item# into the proper cell to show the discount description, of pick the item by using the on-sheet picker. One of the advantages of using discount as a product / service item is that it could be shown on the "Product" report. The disadvantage is that it does not shown on other reports - such as Sales Report, Customer Report,

However if you use discount frequently, or you would like to have discount figures show on the sales report and customer report etc., it could be added to the invoice form as a custom field on a fixed location, usually the bottom of the invoice form under the "Subtotal" field. It is common for attorney / legal, medical, catering, restaurant, law firms (whether small or big), health insurance billing or statement / timesheet, graphic design, freelance, plumbing, marketing, business plans, accounting, house rent (yearly or monthly), or even home businesses for monthly bill. The most obvious advantage of this method is that you don't have to pick the discount as an invoicing item each time you need it. And by adding the discount as a custom field to the "Invoice Header" database table, it is possible to add the discount to Sales Report and Customer Report too.

Discount could be implemented as an amount field, or as a percentage field. In this sample, we'll define it as an amount value on the bottom of the template.

There are two part of work involved to add the discount amount field - customizing the invoice template, and customizing the database.

Before begin, first make sure you have backed up both the Excel template file and the Access database file (Or the SQL Server database - in case you are running Invoice Manager for Excel enterprise edition and using SQL Server database). To do this, simple close Excel (and all the other apps and programs that could use the billing template and database), copy the files to make a backup.

Compared to online web-based billing solutions (even those open source solutions), one of the advantages of a template (including those based on Google Docs) is easy-customizing. Here are the steps on customizing the simple invoice template. This sample is based on c4049 Simple Invoice Template - Moving Item# Column.

1. Backup your template (If you have not done so yet).
2. Open the template.
3. Click the Design Mode button on the "Invoice" ribbon tab to push the button down. In design mode, Invoice Manager for Excel will not response to your clicks on buttons. In design mode, it also shows the gridlines and row/column headings of your Excel worksheet.
4. Right-click the row heading of the first tax row on the bottom of the invoicing form. This is sample, it is R35.
5. Choose Insert from the short-cut menu. This inserts a new row above the first tax row.
6. Enter "DISCOUNT (AMOUNT)" underneath the "SUBTOTAL" label, i.e. $J$35.
7. Click to select the cell where the discount amount value should be entered. In this sample, you click $K$35.
8. Enter the cell name "oknDiscountAmount" into the name box on the formula bar. All cells names that work with Invoice Manager for Excel should start with the "okn" prefix, except "Print_Area" and "Print_Titltes".
9. Right-click the cell named "oknDiscountAmount" cell at $K$35, choose Format Cells from the shortcut menu. On the Format Cells dialog box, go to the "Protection" page, clear the "Locked" property. Click "OK" to close the dialog box. By clearing the "Locked" property, the discount amount field will be manually fillable / edition when you are creating an invoice.
10. Click to select the cell next to the label "SUBTOTAL", i.e. the cell named "oknSubtotal" at $K$34. Modify its formula to be:

    =SUM(oknLineTotal_1:oknLineTotal_12) - oknDiscountAmount

11. To deduct the discount amount from taxable amount, modify the first tax formula (cell address $K$36) to be:

    =ROUND(IF(oknTaxType=0,0, oknTax1Rate*(oknLineTotalTaxable - oknDiscountAmount+IF(oknTaxTotalIncludingShippingCost=0,0,oknShippingCost))),2)

    and modify the second tax formula (cell address $K$37) to be:

    =ROUND(IF(oknTaxType<>2,0,oknTax2Rate*(oknLineTotalTaxable - oknDiscountAmount+IF(oknTaxTotalIncludingShippingCost=0,0,oknShippingCost)+IF(oknTax2IsAppliedToTax1=0,0,oknTax1))),2)

12. Click "Design Mode" button on the "Invoice ribbon tab again to exit design mode.
13. Save the template by clicking the Save command on Excel quick access toolbar.

The steps required to customize the database are detailed below.

1. Open the invoice template.
2. Click "Invoices" on the "Invoice" ribbon tab.
3. Click "Custom Fields".
4. Make sure the "Database Table" field shows "Invoice Header". Click "Add Field".
5. Enter "DiscountAmount" as "Field Name". The field name should match the cell name defined on the invoicing template. In this example, we defined cell name "oknDiscountAmount"; and thus the field name should be "DiscountAmount". It is this name that creates the relationship between an Excel cell and a database field. In other words, the name maps the cell name to a database field.
6. Choose "Decimal" from the "Field Type" drop down list, enter "10" as "Precision", and enter "2" as "Scale". The "Precision" and "Scale" field determine how many digits, in total or after the decimal pointer, could be stored by the field. The default value of a decimal value will be zero if not defined here.
7. Click "OK" to close the "Add New Custom Field" dialog box; Click "Close" to close the "Custom Field Manager" dialog box.

That it! Now you have a simple invoice template that have a Discount Amount field on the bottom of the invoicing form. Feel free to download and test it to see if it meets your business requirements.