If you are a business that wants to figure out how to maximize discounts on volume in an optimal way, this Excel template will help give a lot of perspective and allow the user to test various sensitivities to see what is feasible based on cost of goods sold and operating expenses.
$45.00 USD
Both tabs have the same methodology for the user to play around with. The inputs are:
- Starting Sales Price per Unit
- Cost of Goods Sold per Unit
- Existing Margin per Unit (output)
- % Discount per 1 New Added Item - main driver of the discount feasibility
- Unit Increase Lever - up to 10 buckets (this can be expanded if desired)
You will see a curve populate and the top of the curve is a discount level where you are still increasing the total gross profit when adding 1 more unit sold. After that point, if the discount keeps rising the total gross profit will start going down.
The point of the model is to see how much you can keep increasing your discount with added volume until the discount becomes too great and you start making less gross profit per new unit of volume sold.
The model also drives down to a sales volume sensitivity, which shows the resulting revenue/gross profit if your average sales are at each volume level. The user can then input operating expenses and see the resulting net operating income per each volume level. There are visualizations to help show this as well.
The second tab works the same as the above, except you have 10 different starting prices so there are 10 different rows of results for each volume bucket.
This works for low (1,2,3 etc..) volume situations as well as high volume (1,000, 10,000, etc..)
I also included an input that lets the user enter any volume and based on the discount curve populate by input #4, it will show the resulting discount.
You might also like:
