Line-item discounts, taxes, and special charges

®	QuickBooks Online can apply a discount percentage to the entire invoice, but it is not able to apply discounts to groups of lines or to individual lines, as demonstrated in this topic.

®	Xero supports discounting individual lines on an invoice. A FullSpeed macro however, can use IF function logic for determining how (or whether) a discount is applied.

QuickBooks desktop editions have special-purpose subtotal and discount items, so the techniques shown in this topic will seldom be needed. The exception is when complex calculations are involved, such as applying a tiered-rate tax (a tax that is not a single percentage but a different percentage depending on the dollar amount) to specific items on an invoice.

This example is based on QuickBooks Online. It builds on ideas from the Column subtotals and totals (QBOL) topic.

Sometimes you may need to calculate discounts, a special tax, or separate charges on an invoice, based on just part of the items on the invoice; maybe on a group of lines, or maybe on just a single line. Some examples:

®	In the UK, Construction Industry Scheme (CIS) taxes must sometimes be deducted on invoices. The tax is calculated on non-material costs—i.e., on just the labor portion of an invoice—and deducted from the invoice total.

®	Fuel surcharges are sometimes based on the weight of items shipped, the dollar amount of shipped materials (i.e., excluding labor), etc. Often they apply to only part of the invoiced items.

Setting up a FullSpeed macro for applying a discount or special charge is fairly simple. Basically, you use a text-triggered formula to subtotal the group of items on which the calculation is to be based, then use another text-triggered formula immediately below the first one to calculate the discount or charge and apply it to the invoice (or bill, or other form). And if the discount or charge applies to a single row of the target form you don't need a subtotal: just add the discount calculation's trigger text on the row immediately below the item's row in the invoice.

Text which "triggers" a calculation is often referred to as trigger text. In QuickBooks Online the text is usually provided by a Products and Services item, which can be referred to as a trigger item.

If you will need to subtotal several rows of items before calculating the discount or charge, set up a SubtDisc item:

®	If you have different discount items for triggering different kinds of discounts, it is a good idea to make their names reflect the kind of discount they represent: Discnt2 and Discnt5 items might represent 2% and 5% discounts, respectively.

Also set up a Discount item, and assign an account appropriate for recording discounts:

Here is the detail section of a QuickBooks Online invoice, with the items in place for calculating a discount on the first three invoice lines.

The screenshot below illustrates the formulas, trigger text, Begin field, Match column, and other appropriate worksheet settings.

®	The Discount formula subtotals the Amount column, multiplies it by .02 (the decimal equivalent of 2%), then multiplies by -1 to make it a negative number. That way, the discount will appear with a minus sign and will be deducted on the invoice.

Right-click the formula cell on the SubtDisc row of the worksheet, then select Format cell from the popup menu.

Then select currency formatting with two decimal places, and checkmark the Append results to field contents box, to add the subtotal amount to the item's Description.

Also format the Discount row's formula, but this time select Number format and 2 decimal places, not Currency format. (Currency format would add a dollar sign ($) which QuickBooks Online would reject/ignore.)

In this example the Qty cell was selected as the Begin field (shown in bright green in the macro worksheet).

Here is the invoice after the macro has finished. Notice the subtotal and the discount—which is a negative amount, as it should be.

Do we really need the subtotal line?

No. The Discount formula does not reference the subtotal in its calculations, so why have a subtotal line at all? Because additional information on sales forms often improves communication with your customers.

If you want to do without the subtotal line, see the section below.

Here's an alternate approach, similar to the one above but using only a single Discount line on the invoice.

This time the Discount item will trigger two formulas. One will append the subtotaled Amount to the Description field, so the Discount item needs a different Description:

This step is the same as before except that you only need the Discount item on the invoice, below the lines to be discounted.

Everything—including the formulas—is the same as before except that both formulas are now on the Discount line, and the SubtDisc line is gone.

This step is the same as before except that now both formulas are on the same row of the worksheet.

Now the subtotal and the discount are on the same line—the same result as before, presented more compactly.

Here's how to calculate a discount for just one line—the immediately prior line on a sales form.

The same Discount item created for the example above will work for this example.

As before, select the Discount item on the invoice below the line to be discounted.

This time you cannot subtotal the Amount column to get an amount for calculating the discount. Why? Because a subtotal would include all prior lines on the invoice, and you only want the Amount from the immediately prior line. The solution is to use FullSpeed's mini-spreadsheet feature.

First, show the anchor rows for text-triggered formula row #1 by clicking the [ A ] button in the editing toolbar, then selecting the Text-triggered #1 item.

Then enter formulas and sample data as shown below. These are essentially the same formulas as before, but instead of summing a range of cells in the Sandbox they now refer to a single anchor row in the Amount column, cell G15.

This is where the mini-spreadsheet concept gets its name: formulas on the current row can refer to data fields on prior rows as if those prior rows were simply cells in a spreadhseet. The difference is that this "spreadsheet" is always relative to whichever target application row the macro is currently processing.

Since this macro will only be processing two rows (the item row, and the Discount row), you could actually start in the Qty field on the item's row, and the macro would work just fine.

The amount and the discount are shown on the same line, and the discount only applies to the item on the immediately prior row.