We can set up a Discount Tool in Excel by using arithmetic functions.
Follow the steps below to build your own discount spreadsheet, or if you’d prefer, take advantage of the time we’ve already spent preparing ours!
Purchase the Simple Discount Spreadsheet Example
Click the button below to purchase the Simple Discount Spreadsheet Example
Once you have completed your purchase via PayPal you receive a download link by email.
The Simple Discount Spreadsheet
Spreadsheet Data 1 shows the initial setup.
Spreadsheet Data 1
We start by removing the VAT from the retail price, in cell C17 we write;
=B17*(1/(1+($C$10/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 D17;
=C17-(C17*$C$11/100)
For the next band of discount, distributors, we type the following into cell E17;
=C17-(C17*$C$12/100)
And for the third band of discount, Key Account, we type the following into cell F17;
=C17-(C17*$C$13/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
You can walk through this tutorial and build your own Discount Tool or you can take advantage of the time we’ve already spent and try ours which has some extra formatting to make it nice and easy for you and your team.
[xyz-ihs snippet=”separator”]Click on the button below to purchase the Simple Discount Spreadsheet Example.
Once you’ve completed your purchase in PayPal you receive a download link by email.
[xyz-ihs snippet=”separator”]
Add a Comment