BoostExcel main content

Download

DescriptionSizeDownload

The template works with both the Microsoft Store edition and the desktop edition of the app. Make sure you have installed one of them.

How to use:

  1. Download the EXE package.
  2. Double-click to extract the files into a folder. Make sure you have full access privileges to the folder, such as a subfolder inside “My Documents”.
  3. Open the template.
371 KBDownload

License: Private Use (not for distribution or resale). See our Terms of Use.

Screenshot

Simple Sample: Discount Amount Field

Detail

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

This tutorial is based on the Simple Invoice Template: Moving Item# Column (c4049).

Before beginning, first make sure you have backed up both the Excel template file and the Access database file (Or the SQL Server database if you are running our invoicing app with a SQL Server database).

The process of adding the discount field consists of two steps: modifying the Excel template and updating the database.

Modify the Excel template

  1. Open the template.
  2. Click the Design Mode button on the ribbon tab.
  3. Right-click the row heading of the first tax row at the bottom of the form. In this example, it is R35.
  4. Choose Insert from the shortcut menu. This inserts a new row above the first tax row.
  5. Enter "DISCOUNT (AMOUNT)" below the "SUBTOTAL" label, i.e., $J$35.
  6. Click to select the cell where the discount amount value should be entered. In this example, you click $K$35.
  7. Enter the cell name "oknDiscountAmount" into the name box on the formula bar. All cell names that work with Invoice Manager for Excel should start with the "okn" prefix, except "Print_Area" and "Print_Titltes".
  8. Right-click the cell named "oknDiscountAmount" and choose Format Cells from the shortcut menu.
  9. On the Format Cells dialog box, go to the "Protection" page.
  10. Clear the "Locked" property.
  11. Click "OK" to close the dialog box.
  12. Click to select the cell next to the label "SUBTOTAL", i.e., the cell named "oknSubtotal" at $K$34.
  13. Modify its formula to be:

    =SUM(oknLineTotal_1:oknLineTotal_12) - oknDiscountAmount

  14. To deduct the discount amount from the taxable amount, modify the first tax formula to be:

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

  15. Modify the second tax formula to be:

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

  16. Click the "Design Mode" button again to exit design mode.
  17. Save the template.

Update the database

  1. Open the template.
  2. Click "Invoices" on the ribbon tab.
  3. Click "Custom Fields".
  4. Make sure the "Database Table" field shows "Invoice Header".
  5. Click "Add Field".
  6. Enter field information as below:
    • Field name: DiscountAmount

      Note that The field name should match the cell name defined in the template. In this example, we defined the cell name as "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.

    • Field type: Decimal
    • Precision: 10
    • Scale: 2
  7. Click "OK" to close the "Add New Custom Field" dialog box.
  8. Click "Close" to close the "Custom Field Manager" dialog box.

That it! Now you have a simple template that has a Discount field at the bottom of the sheet. Feel free to download and test it to see if it meets your requirements.

Format and Specification

Template#c4051
Belongs to

Sales

Format (XLS or XLSX).xlsx
Columns4
Lines12
Line Height (Points)18.00
Print Area$D$2:$K$47
Papaer Size / OrientationPortrait
Default Margins (Points)
Left22.68
Right22.68
Top45.35
Bottom45.35
PriceFree (0.00USD)

Related

Sales Invoice Template with Discount Amount Column
Sales Invoice Template with Discount Percentage Column
Business Invoice with Customer-Specific Discounting
Service Bill with Discount Percentage
Service Invoice with Discount Amount
Simple Sample: Discount Percentage Field
Simple Sample: Discount Amount on Sales Report