Complex Pricing formula

Hi guys,

About ready to jump off a cliff. Here is the scenario. I need to build a formula in excel that calculates the cost for software license. I need to take into consideration two factors, users and assets of the company I am licensing to.

I have 5 tiers of Assets for companies (in millions):
0-100
100-300
300-600
600-1,000
1,000+

I have several pre-determined allotments of user:
5 user license
10 users
25 users
50 users
100+ users

At these price points I need to ensure I am hitting these marks.(or close to them)

5 user @ 100 mil = $12,000
10 user @ 300 mil = $24,000
25 user @ 600 mil = 42,500
50 user @ 1 bil = 60,000
100 user @ 1 bil + = 125,000

I need to make this flexible, so in other words,

Company A is 100M in assets and wants 10 users..
Company B is 290M in assets and wants 10 users.
Company C is 200M in assets and wants 10 users.
I want to ensure that it’s a sliding scale, so Company A would pay less than Company B and Company C would be right down the middle. This is true for all the tiers...

I have been playing with Progressive pricing models and using an array to try and calculate this, but I keep hitting brick walls.

so in A1 I want to be able to put in the desired user count
and in A2 want to be able to put assets for company in Million (290,000,000 = 290 in A2)
and then in A 3 gives me the sliding scale price I should be charging.

Another example:

Company A is 99 Mil in assets and wants 5 user licenses, should be very close to 12k. If I then put in 110 Mil company and 5 user license, would be just slightly more.

Not sure if this is helpful, but the price per user cost at the levels I mentioned above are:

Assets:
1-100 @ 2400 per user @ 5 users
100-300 @ 2400 per user @ 10 users
300-600 @ 1700 per user @ 25 users
600-1000 @ 1200 per user @ 50 users
1000 + @ 1200 per user @ 100 users

I hope this is clear enough and please feel free to correct any logic issues and/or best practice for the formula. Thanks for your help in advance and look forward getting some good help before I go completely insane.

Thanks, Jon

You could start with a lookup table with assets down the left side, number of licenses across the top, and unit license price in the body. Then you could do either a lookup or interpolation as desired.

Please Login or Register  to view this content.