|
Newsletter July 2010
|
- Spreadsheet Tips - Calculate several layers of discount instantly
to give your customer a fast and accurate price using our
Discount Tool.
This tool calculates discount non-cumulatively based on the retail
price with VAT removed.
Follow the example below using your own data.
Alternatively, the sample
timesheet included in this example is available as a free
download.
|
|
Discount Tool (Non-Cumulative)
|
|
We can set up a Discount Tool in Excel by using arithmetic functions.
|
|
|
Fig 1 shows the initial setup.
|
|
|
Fig 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 Fig 2.
|
|
|
Fig 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 Fig 3.
|
|
|
Fig 3
|
|
|
|
|
The sample discount tool with some extra formatting is available as a free
download. Remember
to always virus scan any file you download from the internet.
|
|
| ..back to top |
|
|
 |
|
 |
|
|
