Volume Pricing Discount Sensitivity Analysis
![volume pricing discount sensitivity analysis 1]()
![volume pricing discount sensitivity analysis 2]()
![volume pricing discount sensitivity analysis 3]()
![volume pricing discount sensitivity analysis 4]()
![volume pricing discount sensitivity analysis 5]()
![volume pricing discount sensitivity analysis 6]()
The template has two tabs. The first is for a single starting price point and the second is for up to 10 different starting price points. Everything else in each tab works with the same methodology, which is:
1. Enter starting price per unit
2. Enter cost of goods sold per unit
3. Enter Percentage additional discount per 1 unit of volume
4. Enter unit increase lever
The above will then create a sensitivity table that shows the total gross profit at up to 10 different volume levels / order amounts. Also, a curve (linear) will automatically populate and let the user enter any quantity ordered amount and get the resulting discount.
In general, the idea is that you can offer greater discounts with more volume, but only until you stop increasing gross profit with 1 new unit sold. This is a function of starting price and cost of goods sold as well as the increase in discount per unit.
There are visuals and conditional formatting to make it easy to see what volume/discount amount gives the maximum gross profit total.
Additionally, the user can drive all the way down to net operating income by inputting expected operating costs as well as expected total units sold. In this way, it is easy to see what operating profits would look like if the majority of sales were at each volume bucket level.
The second tab shows all of these metrics and visuals, but for 10 different starting price points with all other logic applied accordingly.
The template has two tabs. The first is for a single starting price point and the second is for up to 10 different starting price points. Everything else in each tab works with the same methodology, which is:
1. Enter starting price per unit
2. Enter cost of goods sold per unit
3. Enter Percentage additional discount per 1 unit of volume
4. Enter unit increase lever
The above will then create a sensitivity table that shows the total gross profit at up to 10 different volume levels / order amounts. Also, a curve (linear) will automatically populate and let the user enter any quantity ordered amount and get the resulting discount.
In general, the idea is that you can offer greater discounts with more volume, but only until you stop increasing gross profit with 1 new unit sold. This is a function of starting price and cost of goods sold as well as the increase in discount per unit.
There are visuals and conditional formatting to make it easy to see what volume/discount amount gives the maximum gross profit total.
Additionally, the user can drive all the way down to net operating income by inputting expected operating costs as well as expected total units sold. In this way, it is easy to see what operating profits would look like if the majority of sales were at each volume bucket level.
The second tab shows all of these metrics and visuals, but for 10 different starting price points with all other logic applied accordingly.
