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
Spreadsheet Data 1 shows the initial setup.
Spreadsheet Data 1
We start by removing the VAT from the retail price, in cell C16 we write;
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.
Spreadsheet Data 2
To then calculate the first band of discount, for dealers, we type the
following into cell D16;
For the next band of discount, distributors, we type the following
into cell E16;
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;
Copying each of these four formula down their respective columns gives
us the complete discount tool as in Spreadsheet Data 3.
Spreadsheet Data 3
The sample discount tool with some extra formatting is available as a free
to always virus scan any file you download from the internet.
..back to top