Equitable Pricing Model

[kachauya]

Hello everyone,

I wonder if you can help me. I've been looking everywhere for a solution to this problem, but so far haven't been able to find a suitable one.

A company (selling services) wants to charge their clients a fee based on the client's number of units they manage. The clients are charged on a banding system like this:

Band1 less than 50 units: $650
Band2 51 to 150 units: $950
Band3 151 to 250 units: $1600
Band4 250 units and over: will be charged $2.00 per unit over the Band2.

Charges are per year increasing by a percentage based on inflation.

As we move from smaller to larger bands, the cost per unit for the client decreases. This structure benefits larger clients disproportionately, with smaller clients paying a much higher cost per unit. This is not fair to small clients who end up paying a lot more in terms of their size as larger clients pay less as they can absorb the cost easier as they get more income. Is there a way to make it more equitable? How can we make a spreadsheet to calculate this and make it fair for everyone?

I have seen a few sliding scale formulas here and tried some but the problem is that large clients (over 150 units) would be paying exorbitant amounts (way more than the current $1600) which goes against of making charges equitable to all.

Would setting up a base rate (for everyone) then applying a formula to increase fee as the number of clients' units increase, work?

Thank you for your help.

 

[kachauya]

I tried this calculation with a linear discount e.g. in cell D8

=BASE_RATE * C8 * (1 - C8 / (MAXUNITS + C8)) but as I said above once the clients' units go over 120 the fee gets way too big.

Charges2025 - proposed.xlsx
	B	C	D
2	Base Unit Rate	$ 26.00	< BASE_RATE
3	UpperEndUnits	300	< MAXUNITS
4
5		Clients Units	Total Fee
6		30	$ 709.09
7		40	$ 917.65
8		50	$ 1,114.29
9		60	$ 1,300.00
10		80	$ 1,642.11
11		120	$ 2,228.57
12		150	$ 2,600.00
13		250	$ 3,545.45
14		300	$ 3,900.00
15		500	$ 4,875.00
16		600	$ 5,200.00
17		700	$ 5,460.00
18		800	$ 5,672.73
Sample

Cell Formulas
Range	Formula
D6:D18	D6	=BASE_RATE * C6 * (1 - C6 / (MAXUNITS + C6))

Named Ranges
Name	Refers To	Cells
BASE_RATE	=Sample!$C$2	D6:D18
MAXUNITS	=Sample!$C$3	D6:D18

 

[StephenCrump]

Welcome to the Forum!

This is not an Excel question we can help you with until you are clear about what you mean by "equitable", "too big" and "exorbitant".

I suggest you focus on cost per unit, and not total cost. It's not clear why you're scaling fees the way you do in Post #2, nor why you regard the fee for 800 units (say) as exorbitant when it's actually only $7.09 per unit compared to the $26 base.

What are the company's costs of managing the units? If it can expect economies of scale, it might price in bands along the lines of:

First i units: $A per unit
Next j units: $B per unit
Next k units: $C per unit
Higher amounts: $D per unit

where A>B>C>D.

Should there perhaps be a minimum dollar charge?

What are the company's competitors charging, and how are the fees structured? Does the company want to follow that structure? If it doesn't, will competitive shoppers be able to select against the company, e.g. if competitors charge a minimum fee and the company doesn't, it may attract the very small and unprofitable customers.

 

[kachauya]

Thank you for the answer.

You raised very good questions. I'll need some time to think them over and will get back with answers.