Line-item discounts, taxes, and special charges

Top  Previous  Next

This example demonstrates:

®Calculating a discount, tax, or added charge based on a group of items
®Appending calculated results to a text field
®Accessing prior-row data using the mini-spreadsheet feature

Application notes:

®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.

Calculating Discounts, Taxes, and Special Charges

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:

®A "special sale" discount on part of the items you sell, calculated when an invoice or sales receipt is prepared.
®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.

Getting Ready

Set up Products and Services items

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

Notes:

®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.
®The Income Account is not important; this item won't be used for recording income or expense.

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

Use the items on the invoice or other form

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.

Set up the macro worksheet formulas, trigger text, etc.

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

See the Tutorial if you need basic information on these items.

Notes:

®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.

Format the formula cells

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.)

Running the Macro

In QuickBooks Online:

1.Click in the Begin field.

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

2.Press the hotkey you assigned to the macro.

 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.

Discount and Subtotal All on One Line

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

Set up the Discount item...with a different Description

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:

Use the item on the invoice or other form

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

Set up the macro worksheet formulas, trigger text, etc.

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.

Format the formula cells

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

Running the Macro

In QuickBooks Online:

1.Click in the Begin field:  the Qty field in this example.

2.Press the hotkey you assigned to the macro.

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

Discounting the Prior Line (no Subtotal)

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

Set up a Discount item

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

Use the item on the invoice or other form

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

Set up the macro worksheet formulas, trigger text, etc.

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.

Format the formula cells

Both cells should be formatted as before.

Running the Macro

In QuickBooks Online:

1.Click in the Begin field:  the Qty field in this example.
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.

2.Press the hotkey you assigned to the macro.

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