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.
Purchase Cumulative Discount Spreadsheet Example
Purchase the Cumulative Discount Spreadsheet Example by clicking the button below:
After completing your purchase in PayPal you will be sent a download link by email for your Spreadsheet Example.
Follow along the tutorial for the Cumulative Discount Spreadsheet Example
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;
=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.
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.
Spreadsheet Data 3
It’s always nice to take the time to do some formatting on a spreadsheet to make it easier for you and other users to quickly see where to enter data and get well present results.
You can save yourself some more time here by purchasing our pre-formatted Cumulative Discount Spreadsheet Example by clicking the button below.
After completing the purchase with PayPal you will receive a download link to your email address.
You can specify which email address you want the link sent to.
Add a Comment