asked on

# Help writing a formula to match this chart

Before I start, yes I do wish I'd paid more attention in math's class...!  ;-)

I need to create a formula to sell a product based on the number of people using it.

So, if only 10 people in a company use it, then the company pays a maximum of, say, \$10 per user.  However, at 5000 users they should pay a lot less per-user, say, \$1 per user.  The maximum should be \$10 per user and the minimum should be \$1 per user (for 10 users and 5000 users respectively).  If a company has 10,000 users they should still pay \$1 per user.

I have included a chart to hopefully simplify this:

Can anyone help devise a formula that would give me a price per user based on the number of users entered?
Rich Weissler

For P<=50; price=10
For 50<P<5000; price = 10 - (9/4950)/(p-50) = 10 - (9p-450)/4950
For P>=5000; price=1
Doh.  There it is:  10 - (9/4950)/(p-50) should be 10 - (9/4950)*(p-50)  -- I multiplied it right, and couldn't spot where I'd turned the / into a * when typing.
I apologize.
Razmus got it.  Another way to write it would be:

if number_of_licenses < 50
cost = 10;
else if number_of_licenses > 5000
cost = 1;
else
cost = (-9/4950) * number_of_licenses + (4995/495);

Here is an example of a smooth curve that approximates your function. (The 3 curves are part of one family of curves.)
approximate-curve-fit.PNG

ASKER

Thank you very much for this.

>> Doh.  There it is:  10 - (9/4950)/(p-50) should be 10 - (9/4950)*(p-50)

>> cost = (-9/4950) * number_of_licenses + (4995/495);

I plotted these into Excel and noticed that it was actually cheaper for a customer to buy 5000 user accounts than it is for them to buy 4000 (see code).

Is it possible to stop that happening?

``````Users	Price Per User	Grand Total
10	£10.07		£100.73
15	£10.06		£150.95
50	£10.00		£500.00
100	£9.91		£990.91
150	£9.82		£1,472.73
250	£9.64		£2,409.09
350	£9.45		£3,309.09
500	£9.18		£4,590.91
750	£8.73		£6,545.45
1000	£8.27		£8,272.73
4000	£2.82		£11,272.73
5000	£1.00		£5,000.00 <-- BARGAIN!
``````
Lots of ways
My favorite is:
For P<=50; price=10
For 50<P<4175; price = 10 - (9/4950)*(P-50) = 10 - (9P-450)/4950
For P>=4175; price=2.50

(The only thing I changed was where we leave the downward slope.)
Okay, I take it back... new favorite is to leave the price decreases at 2800 units, at a price of 5 dollars.
Your maximum take ( (-9/4950)*\$^2 + (4995/495)*\$ ) looks to be around 2775 people at 5.05 per person or about 14,001.14.

So, your other options would be to offer
P<= 50; price = 10 per person.
50<P<2800; price = 10 - (9P-450)/4950
P>=2880; price = 1 per person, plus flat fee of \$14,000 for setup.

ASKER

Hmm.  I think I might have an issue here.  When I plot into Excel I get the following results.

What I need is for the cost per user to decrease gradually as the number of users increases, but at the same time the overall grand total to always increase as the number of users increases.  Is this possible to achieve?
``````Users	Price Per User	Total
10 	£10.00		£100.00
15 	£10.06		£150.95
50 	£10.00		£500.00
100 	£9.91		£990.91
150 	£9.82		£1,472.73
250 	£9.64		£2,409.09
350 	£9.45		£3,309.09
500 	£9.18		£4,590.91
750 	£8.73		£6,545.45
1,000 	£8.27		£8,272.73
4,000 	£2.82		£11,272.73
5,000 	£1.00		£5,000.00 <-- grand total needs to be more than the figure above if possible
``````

ASKER

>> Here is an example of a smooth curve that approximates your function. (The 3 curves are part of one family of curves.)

When I plot this into Excel my values don't match your graphs.  Unless I've done something wrong, I get the following (with much higher costs per user):
``````Users	Price Per User	Total
10 	£9.83		£98.29
15 	£9.83		£147.43
50 	£9.83		£491.39
100 	£9.83		£982.64
150 	£9.82		£1,473.75
250 	£9.82		£2,455.51
350 	£9.82		£3,436.67
500 	£9.81		£4,907.24
750 	£9.81		£7,354.96
1,000 	£9.80		£9,798.40
4,000 	£9.67		£38,676.89
5,000 	£9.61		£48,052.94
``````

ASKER

With regards to the above, the formula in Excel is:

=9*(1-1/(1+50*2.718^(-0.00017*(A47-200))))+1

where A47 is the number of users.
> What I need is for the cost per user to decrease gradually as the number of users increases, but at the same time the overall grand total to always increase as the number of users increases.  Is this possible to achieve?

Yes, it's possible, but the current function has a maximum revenue at 2775 users.  Beyond that, the total revenue decreases.  You need to decide what sort of change you wish to make...
1. At some fixed point, say 2250 users, switch to a fixed price at that level, and a new decreasing per user cost for each user above that level, but for example, if you fixed 2250 users at \$23,500 and each additional user were \$6 to \$1 at 5,000... you'll hit a point of decreasing revenue again around 3600 users and will have to do it again.  Basically it'll put kinks in your graph every time your gross revenue would start to decrease.  (Of course, you can set up a fee structure with multiple tiers like this... I don't think it would exceed five tiers... if you really want the marginal cost to be \$1 at 5000 units.)

2. You could switch to a different type of function with a much longer tail, but the point at which the price is effectively \$1 per user will be far beyond 14,000... likely beyond 20,000 units.

3. If you loosen the constraint that the price drop by more than half it's starting price... your existing function could work.

I guess it all comes down to... which constraint are you most willing to loosen?

ASKER

Thanks Razmus for your detailed explanation.

Now I've had chance to digest all of your input, I don't think that a curved model is going to work.
After giving it further thought, what I would really benefit from is a straight line reduction from \$10 to \$1 per user, but with the ability to alter the rate of reduction.

Is that possible based on what we've done so far?

chart.gif
Unfortunately, given a single straight line price function, it's not going to matter how much slope we give line, the total revenue is going to look this this graph.  What I meant in option 2 can be seen in the second graph.  (In this case, I've smoothed the curve out so that revenue stays constant at the higher quantities.
revenue.JPG
altpricing.JPG

ASKER

Hi Razmus

That's really interesting.  At the risk of sounding completely ignorant, I never considered this outcome might occur.  So, naturally my next request is, based on your experience, is there anything that can be done to avoid the overall revenue dropping, other than by always charging a fixed amount per user?

Our problem currently is that large organisations won't pay when the price is fixed per-user, and they want to see big discounts per-user for large purchases.  By the same token, small businesses aren't making us any profit, so we are losing out both ways!
Look at your costs, but rather than giving them a function to compute the price, use pricing tier.  An example might be:
Up to 100 users, \$10 per user.
Up to 500 users, \$9 per user.
Up to 1000 users, \$8 per user.
Up to 2000 users, \$7 per user.
Up to 3000 users, \$6 per user.
Up to 5000 users, \$5 per user.
Up to 10,000 users, \$4 per user.
Up to 20,000 users, \$3 per user.
Up to 50,000 users, \$2 per user.
Up to 100,000 users, \$1.50 per user.
Up to 500,000 users, \$1 per user

Now that assumes the marginal cost of providing services to each additional user is tiny.  (But make certain that you could provide service to 100k users for \$150,000, for example.  Even if the nominal costs are small, there will be break points in your costs where they will spike... for example when you have to add an additional host, or upgrade storage or network infrastructure... so I'd watch out for those while pricing.)  (And I hate to say, I haven't had Marketing since grad school, so this is just looking at the numbers rather than any vast pricing experience.)

ASKER

>>  (And I hate to say, I haven't had Marketing since grad school, so this is just looking at the numbers rather than any vast pricing experience.)

LOL.

Thanks for that.  Pricing tiers are the way we currently operate.  I was just hoping to streamline things, then potentially write a program based on the new formula to calculate prices very quickly.
If we're always going to see the dip in total revenue with the user increase, then that is not the way I should be going with this.
What does your current tier structure look like?  It might be possible to curve fit to that...  (although price tiers have the advantage that they are easier for sales folks to explain to customers.  Most of the time sales folks and people who make purchasing decisions didn't pay much attention in math class either... :-) )

ASKER

Okay let me take some time to plot our ideal values into Excel and I'll get back to you...

Thanks again.

ASKER

Hi Razmus

Sorry for my delay in replying, I am tying myself in knots trying to get my head around this...
Please let me describe the issue in Layman's terms...

The problem at the moment is that we don't actually know exactly what price we want each user figure to be.  We have decided on the upper and lower price boundaries, and just want to see what the intermediary values are using whatever proposal is chosen from here.

The reason for my original post was to get a rough formula that we could then graph, and tweak as needed to give the best outcome.  Unfortunately I think your skills have made me need to revise my plans once I saw the results.

So...

We need to start off at 10 users @ \$10 per user
The price must then fall until we get to 10,000 users @ \$1 per user
Beyond those user total limits I can quite easily stop Excel altering the price.

What would be perfect is if the formula could have two variables that could be adjusted:

- Variable 1 to affect the decrease in price as the user numbers increase from 10 upwards
- Variable 2 to affect the decrease in price when approaching the 10,000 user figure

The two variables would therefore create a 'price curve' where either side could be altered until we were happy with the prices calculated.   Does that sound plausible?

This data would then be compared against proposed running costs to forecast profitability.
I've graphed out two sets of prices/revenues, and if I understand, you want something in the middle.
The Red lines represents a straight line price function until revenue hits \$10,000, which is considered a hard constraint because we have  \$1 price at 10,000 units and we don't want revenue to go down as quantity of units goes up at any point.

The Yellow lines represents a straight line revenue increase as the quantity of units increases.  Price in that case is (x+90)/x.  The revenue graph in that case looks pretty good, but your price drops precipitously in the early units...

These pretty much represent the upper and lower limits of any other function fit within the constraints given... Does that fit the reality of what you want the price function to look like?
Prices.JPG
Revenue.JPG

ASKER

Hi Razmus

The red line in your first image looks to be exactly what I'm looking for.  How the price gradually tails off as the user figure increases is perfect.

Is the second image based on the first?  The concern with that one is whether the total revenue stops increasing after the user figure hits a certain point.

Thanks again...!
Yes, and that's exactly the problem.  Because the revenue should never go negative, and the price drops an order of magnitude, relatively quickly....  so the red curves hit the maximum revenue at around 1,500 units, and the price starts to tail out after that.  It's the constraint of maximum revenue of \$10,000 below 10,000 units which causes it.

That's why I gave you the two sets of curves... anything else you are going to want will be between those two curves, and all of them will have the price dropping off very, very quickly initially.  Again, another set of curves can be drawn between the yellow and red lines, but to the extent they show decent revenue increases across the entire function they will be closer to the yellow (with that precipitous drop in price at low volumes), or asymptotic to \$10,000 revenue which leaves you no reason to increase the volume of sales from a single customer.

ASKER

Thank you very much again for your detailed explanation.
Wow - this really has turned into an (unpleasant!) fact-finding mission.

Looking at your graphs now, I see no benefit to us with using this kind of pricing model.  Clearly, my company not earning more than \$10,000 - regardless of customer size - won't work for us!
Would you agree (as was mentioned earlier) that we would have to use a price-step approach (rather than a price 'curve') to prevent the above problems happening?

>> It's the constraint of maximum revenue of \$10,000 below 10,000 units which causes it.
Is there much difference if that constraint is removed?
> >> It's the constraint of maximum revenue of \$10,000 below 10,000 units which causes it.
> Is there much difference if that constraint is removed?
The constraint comes from \$1 pricing at 10,000 units, and not wanting revenue to decrease while the number of units sold increases.  The constraint which would need to change would be:
"revenue is allowed to decrease as the number of units sold increases" - undesireable
"the lowest price is \$1" (assuming 10,000 units) - possible... the curves don't look nearly as odd if the lowest price is \$4, for example.
"the top pricing occurs at 10,000 units" - Pushing the \$1 pricing out to 100,000 units, for example, raises the maximum revenue ceiling to \$100,000... but if all your contracts are at or below 10k units, it has almost the same effect as just increasing the lowest price...

> Would you agree (as was mentioned earlier) that we would have to use a price-step approach (rather than a price 'curve') to prevent the above problems happening?
Yes, but... (don't you hate that answer?)
If you pricing is something like:
Up to 10 units - \$10/unit.
Up to   1,000 units - \$5/unit
Up to   5,000 units - \$3/unit
Up to 10,000 units - \$1/unit.

Watch out for places, like 5,000 units above, where you bring in more gross revenue selling fewer units.  (Even "Up to 5,000 units - \$2/unit, you have an incentive to sell up to 9,999 units and never, ever sell them that 10,000th unit.  And it's even worse going from 4,999 units to 5,000 units using that example scheme.)

ASKER

Ahh okay, so in a way with the 'stepped pricing' approach we just get a more dramatic version of the former...

Given all the formulae you done for me (which I really appreciate btw), in your own opinion how would you have structured the price if it was your own company?  By that I mean, if you could start fresh with no constraints?  I imagine all of the big players in the industry must have this exact same problem...

Perhaps another approach would be to have a fixed rate-per-user that never changes, but apply some kind of percentage discount on the final figure based on units to keep the big customers happy...

Life would be a lot simpler without money... :-)
ASKER CERTIFIED SOLUTION
Rich Weissler

membership
Create an account to see this answer
Signing up is free. No credit card required.

ASKER

Thanks very much for your valuable help :-)