How to calculate cumulative discount

Follow the tutorial to master Excel in real business situations, with free downloadable tool and samples.

In this month's spreadsheet tip, we look at how to apply discounts cumulatively to your price list. This allows you to apply percentage reductions to different categories of customers across your whole product range, in an instant. You can download the full Discount Tool with sample data here.

Spreadsheet Data 1 shows the initial setup.

We start by removing the VAT from the retail price, in cell C16 we write;

=B16/(1/1+(\$B\$9/100))

where B17 is the retail price, including VAT, and C10 is the VAT rate. (Remember, we put dollar signs in the VAT cell reference so that it will not change when we copy the formula down the column.) For the first product, Shoes, which have a retail price of £117.50, this gives a retail price excluding VAT of £100.00, as in Spreadsheet Data 2.

To then calculate the first band of discount, for dealers, we type the following into cell D16;

=C16-(C16*(\$B\$10/100))

For the next band of discount, distributors, we type the following into cell E16;

=D16-(D16*\$B\$11/100)

The discount for distributors is based on the dealer price, rather than the retail price, unlike the non-cumulative discount tool, which bases all discounts on the retail price. And for the third band of discount, Key Account, we type the following into cell F17;

=E16-(E16*\$B\$12/100)

Copying each of these four formula down their respective columns gives us the complete discount tool as in Spreadsheet Data 3.