Wholesale Invoice Format with Per-Customer Discount Rate

This is a general-purpose wholesale invoice template. It includes both the "Bill To" and "Ship To" sections.

The 5 columns in the detail section include "Item#", "Description", "Quantity", "Price", and "Line Total". The two taxes set by default are "GST" and "PST".

There is a "Taxable" checkbox column attached to the item# column. These checkboxes allow you to specify whether each item is taxable.

When a wholesale customer is picked up, the "Custom Discount" cell is filled with either a special discount rate you set for the customer or zero. Then, we use the following Excel formula to determine the actual discount level applied to this invoice:

=IF(oknCustomDiscount=0,A10, oknCustomDiscount)

The result of the above formula is assigned to the "oknDiscountRate" cell.

We calculate the taxable total by using the following array formula:

{=SUM(oknTaxable_1:oknTaxable_12*oknLineTotal_1:oknLineTotal_12)}

The result of the above formula is filled into D33. Then the discount is applied to calculate the actual taxable amount, like this:

=D33*(1-oknDiscountRate)

The result of the above formula is filled into the cell "oknLineTotalTaxable". Now let's see the first tax cell:

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

This formula is a bit more complex than you might think. It is created this way in order to work with the "Settings" dialog box, where you can set the number of taxes, tax names, and rates.