Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Help writing a formula to match this chart

Posted on 2010-09-03
Medium Priority
513 Views
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?
0
Question by:Rouchie
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 13
• 12
• +1

LVL 30

Expert Comment

ID: 33599527
For P<=50; price=10
For 50<P<5000; price = 10 - (9/4950)/(p-50) = 10 - (9p-450)/4950
For P>=5000; price=1
0

LVL 30

Expert Comment

ID: 33599578
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.
0

LVL 2

Expert Comment

ID: 33599698
Razmus got it.  Another way to write it would be:

cost = 10;
cost = 1;
else
cost = (-9/4950) * number_of_licenses + (4995/495);

0

LVL 32

Expert Comment

ID: 33601808
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
0

LVL 25

Author Comment

ID: 33602511
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!
``````
0

LVL 30

Expert Comment

ID: 33602908
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.)
0

LVL 30

Expert Comment

ID: 33604708
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.
0

LVL 25

Author Comment

ID: 33606896
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
``````
0

LVL 25

Author Comment

ID: 33606923
>> 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
``````
0

LVL 25

Author Comment

ID: 33606925
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.
0

LVL 30

Expert Comment

ID: 33607159
> 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?
0

LVL 25

Author Comment

ID: 33610851
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
0

LVL 30

Expert Comment

ID: 33611327
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
0

LVL 25

Author Comment

ID: 33611710
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!
0

LVL 30

Expert Comment

ID: 33611979
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.)
0

LVL 25

Author Comment

ID: 33612146
>>  (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.
0

LVL 30

Expert Comment

ID: 33613951
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... :-) )
0

LVL 25

Author Comment

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

Thanks again.
0

LVL 25

Author Comment

ID: 33647622
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.
0

LVL 30

Expert Comment

ID: 33653648
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
0

LVL 25

Author Comment

ID: 33660291
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...!
0

LVL 30

Expert Comment

ID: 33660965
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.
0

LVL 25

Author Comment

ID: 33661154
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?
0

LVL 30

Expert Comment

ID: 33661370
> >> 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.)
0

LVL 25

Author Comment

ID: 33661487
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... :-)
0

LVL 30

Accepted Solution

Rich Weissler earned 2000 total points
ID: 33661845
If it were me, I'd keep in mind the costs (startup and marginal).  When you give price breaks or discounts, there will be points at which you lose a little gross revenue, but the idea is to get the customer to see that price break and chooses to stretch a little to reach that next price break... committing just a little more to pay a little less per unit, anticipating that they will use the additional units later.  What's happening here, you have a pricing scheme that spans an entire order of magnitude, from \$1 to \$10 for presumably the same product and different quantities.

> Life would be a lot simpler without money... :-)
Naw, then we'd have to come up with a whole scheme of exchange rates converting chickens to eggs to keyboards to doctor services/hour.

Off the cuff, it were me, I'd do something like:
``````Standard Account pricing includes a fixed number of accounts per tier, plus the option to add additional accounts.
Up to 10 units 	\$ 100	\$10.00 per user above 10
Up to 50 units	\$ 450	\$ 9.50 per user above 50
Up to 100 units	\$ 875	\$ 9.00 per user above 100
Up to 500 units \$3500	\$ 7.50 per user above 500
Up to 1000 units\$6500	\$ 7.00 per user above 1000

Priority Account pricing
Add priority technical support (which you would be doing for your best customers anyway) 7x24
below 5k units	\$5000	\$ 5.00 per user

For quantities above 5,000 units, please call our contract office for a quote for a service plan customized to meet your needs.
``````
0

LVL 25

Author Closing Comment

ID: 33689946
Thanks very much for your valuable help :-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Guide to the PMT, FV, IPMT and PPMT Functions In MS Excel we have the PMT, FV, IPMT and PPMT functions, which do a fantastic job for interest rate calculations.  But what if you don't have Excel ? This article is for programmers looking to reâ€¦
Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, anâ€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
###### Suggested Courses
Course of the Month5 days, 23 hours left to enroll