Ever-increasing discount curve

[toddbeck]

I have a unit list price of $10. At 100,000 units, I want the price to be $2.00.

How can I calculate an ever-increasing discount curve between those two points? I want it to be that the more you buy, the deeper the discount.

My amateur efforts so far result in curves that go negative, so I'm paying the customer to buy my stuff. Also, I don't want the total (units x price) to reach a point where it reverses and starts to go down--meaning you pay less total for 300 units than you would total for 200 units.

Does that make sense? Please help. Thank you!

 

[GlennUK]

Hi todd,

how about something that increases logarithmically? You'd get the steepest discount curve at the beginning but it would still decrease slowly as the number of units gets really high.

Something like this would calc the price per unit:

=10-MAX((MIN(LOG10(ROUNDDOWN(K3,IF(K3>100,-2,-(K3>10)))),5)-1)*2,0)

assuming the number of units is entered into K3. The price adjusts for more than 20 units ( every 10 units), all the way up to 100 units, and then adjusts every 100 units after that. The min price of $2.00 applies to any amount over 100,000 units.

 

[toddbeck]

Thanks, Glenn. I like what you did except that I want it to be the exact opposite. So instead of the curve being steepest at the beginning, I'd like the discount to be greater as volume went up--maxing out at the $2.00 price point. Is there a way to "flip" what you did to reverse the curve?

I'm embarrassed to say that I manually built my curve using two plug numbers. It somewhat accomplishes my goal but it's ugly and I can't get below $6.64 as a minimum price, no matter how much I change my two plug numbers. That just proves I don't even understand the math, let alone the Excel formula. Here's what I did.

PLUGA 1.03
PLUGB 0.085813.

Then the equation in the column "discount from higher band" is

=(PLUGA^(UNITS/1000*PLUGB))-1

<table style="border-collapse: collapse; width: 245pt;" width="327" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <col style="width: 53pt;" width="71"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 57.6pt;" height="77"> <td style="height: 57.6pt; width: 48pt;" width="64" height="77">
</td> <td class="xl68" style="width: 48pt;" width="64">Per unit</td> <td class="xl68" style="width: 53pt;" width="71">Total</td> <td class="xl69" style="width: 48pt;" width="64">discount from higher band</td> <td class="xl69" style="width: 48pt;" width="64"> </td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl69" style="height: 14.4pt; width: 48pt;" width="64" height="19">Units</td> <td class="xl70"> </td> <td class="xl71"> </td> <td class="xl70" align="right">1.03</td> <td class="xl70"> </td> </tr> <tr style="height: 13.2pt;" height="18"> <td style="height: 13.2pt;" align="right" height="18">1000</td> <td class="xl66" align="right">10.00</td> <td class="xl67"> $ 10,000 </td> <td class="xl70" align="right">0.085813</td> <td class="xl70"> </td> </tr> <tr style="height: 13.2pt;" height="18"> <td style="height: 13.2pt;" align="right" height="18">2000</td> <td class="xl66" align="right">9.99</td> <td class="xl67"> $ 19,990 </td> <td class="xl72"> 0.0051 </td> <td class="xl73"> $ 9,990 </td> </tr> <tr style="height: 13.2pt;" height="18"> <td style="height: 13.2pt;" align="right" height="18">3000</td> <td class="xl66" align="right">9.99</td> <td class="xl67"> $ 29,962 </td> <td class="xl72"> 0.0076 </td> <td class="xl73"> $ 9,972 </td> </tr> <tr style="height: 13.2pt;" height="18"> <td style="height: 13.2pt;" align="right" height="18">4000</td> <td class="xl66" align="right">9.98</td> <td class="xl67"> $ 39,908 </td> <td class="xl72"> 0.0102 </td> <td class="xl73"> $ 9,946 </td> </tr> <tr style="height: 13.2pt;" height="18"> <td style="height: 13.2pt;" align="right" height="18">5000</td> <td class="xl66" align="right">9.96</td> <td class="xl67"> $ 49,822 </td> <td class="xl72"> 0.0128 </td> <td class="xl73"> $ 9,913 </td> </tr> </tbody></table>
I experimented with the plugs until I got the shape of the curve I need. Like I said, though, it's not an equation so it makes my job harder (I'm stuck using a table) AND I can't get the price below $6.64 before the curve turns and I start losing money as volume goes higher.

I welcome your feedback--on the equation, that is. Please no feedback on how amateur my attempt, above, is. I know that already.

Thanks!

 

[ThePeter]

if I set the two on a power plot, I get y=10x^.8602

You could

IF(x<100000,10*x^.8602,x*2)

 

[toddbeck]

Thanks, ThePeter.

As I understand that, it still seems to reduce the discount as volume increases. It's that ever-decreasing curve like Glenn made. Is there a way to reverse that so the curve starts slowly and gets steeper?

Thanks again!

 

[ThePeter]

Can you post a paint picture of the graph of what you expect this curve to do? (before $2/unit)

 

[GlennUK]

Hi todd,

this would give an increasingly steep curve, abrubtly flattening at $2.00 for 100,000:

=MAX(2,ROUNDUP((-0.00000000000001*(A2^3))+(0.0000000004*(A2^2))-(0.00002*A2)+10,2))

Is that any use?

 

[toddbeck]

Glenn, that's exactly what I asked for. THANK YOU!

Now, though, I'm realizing that my original request must be flawed. Notice how that once you hit $6.64 per person, the total the client pays actually goes down as volume increases. It would be cheaper for them to order more units than they want just to get a lower total price. I'll have to adjust my minimum price above $2.00 and play with your equation to see if I can stop that negative outcome.

Thank you very much, to you and ThePeter, for your replies. This has all been very helpful.

 

[jbeaucaire]

Now, though, I'm realizing that my original request must be flawed. Notice how that once you hit $6.64 per person, the total the client pays actually goes down as volume increases. It would be cheaper for them to order more units than they want just to get a lower total price. I'll have to adjust my minimum price above $2.00 and play with your equation to see if I can stop that negative outcome.

I think you'll find this impossible to really do as long as you apply a SIMPLE discount method, i.e. buying more means ALL items cost less. In every model I've ever seen there is always clearly seen points where buying MORE costs the buyer less money. That's the point, actually. It's encouraging brand loyalty and gets more of your product into the field by offering a clear savings to the user who buys 3 times as much product.

The only way to NOT ever have a situation where discounted items create a TOTAL that is LESS when more is purchased it by implementing a TIERED discount schedule. On a tier, the first item is always full cost and each additional item purchased at the same time gets progressively cheaper.

So the first 99 items are always $10, but items 100-199 are $9.95, 200-299 are $9.90...and so on. This insures each invoice total is higher as items are added, even in spite of the discounts being offered at each tier.

 

[jbeaucaire]

Here's a sample file that shows you an example of TIERED discounting structure:

Complex vs Simple discount techniques​