Price Conversion between Tiers (Weighted Average)

[drob08]

I'm trying to write a formula that will adjust price per tier based on adjusted tier level recommendations.

For instance:
Tier List 1:
Tier 1: 0-10M = $1
Tier 2: 10M-15M = $0.85
Tier 3: 15M-20M = $0.65
Tier 4: 20M-MM = $0.5

But let's say I didn't want to stick with those tiers but I wanted the weighted average for each incremental tax bracket:

Tier List 2:
Tier 1: 0-8M
Tier 2: 8M-17M
Tier 3: 17M-25M
Tier4: 25M-MM

Formula to calculate weighted average price for each new range at Tier List 2:

Tier 1= $1 as 8M fits within Tier List 1 (0-10M)
Tier 2= $0.88 ((2M * $1) + (7M * $0.85)) / (17M-8M)
and so on for tier 3 & 4

It needs to be dynamic so the overlaps +/- are captured at their respective tiers. Unfortunately, I don't use macros so I was hoping a formula would be able to do what I want.

 

[StephenCrump]

Does this help?

	A	B	C	D	E	F	G	H	I	J	K
1
2		Tier List 1					Tier List 2
3		Min	Lookup	Proposed	Diff		Tranche	Cumulative	Lookup	Proposed	Diff
4			0	1.00	1.00		8,000,000	8,000,000	0	1.000	1.00
5			10,000,000	0.85	-0.15		7,000,000	15,000,000	8,000,000	0.893	-0.11
6			15,000,000	0.65	-0.20		5,000,000	20,000,000	15,000,000	0.650	-0.24
7			20,000,000	0.50	-0.15		4,000,000	24,000,000	20,000,000	0.500	-0.15
8
9
10		Volume	List 1 TOV	List 2 TOV	Equal?
11		8,000,000	8,000,000	8,000,000	TRUE
12		15,000,000	14,250,000	14,250,000	TRUE
13		20,000,000	17,500,000	17,500,000	TRUE
14		22,500,000	18,750,000	18,750,000	TRUE
15
16		But ...
17		9,000,000	9,000,000	8,892,857	FALSE
18		12,000,000	11,700,000	11,571,429	FALSE
19
Sheet1

Cell Formulas
Range	Formula
H4	H4	=G4
J4:J7	J4	=(SUMPRODUCT(--(H4>C$4:C$7),H4-C$4:C$7,E$4:E$7)-SUMPRODUCT(G$3:G3,J$3:J3))/G4
E4:E7,K4:K7	K4	=J4-SUM(J3)
H5:H7	H5	=H4+G5
I5:I7	I5	=H4
C11:C14,C17:C18	C11	=SUMPRODUCT(--(B11>C$4:C$7),B11-C$4:C$7,E$4:E$7)
D11:D14,D17:D18	D11	=SUMPRODUCT(--(B11>I$4:I$7),B11-I$4:I$7,K$4:K$7)
E11:E14,E17:E18	E11	=D11=C11

Note that the equality holds only in limited circumstances ... as we're calculating average prices for whole tranches, and those averages won't be correct for part-tranches.

 

[Dave Patton]

A guess of what your require. I adjusted some of the numbers (used an adjustment factor) and then used Goal Seek.
I show before and after views in this and next post.

Commission2022.xlsm
	A	B	C
1	Total sales	20,000,000.00
2
3	Initial
4	Brackets	Rates
5	cell b7 is blank
6	0	100%
7	10,000,000	85%
8	15,000,000	65%
9	20,000,000	50%
10			17,500,000.00
11
12
13	Proposed
14	Brackets	Rates	Factor
15	cell b7 is blank		0.15
16	0	100%
17	8,000,000	85%
18	17,000,000	70%
19	25,000,000	55%
20	1E+308
21
22
23		Initial	Proposed
24		17,500,000.00	17,750,000.00
25
26	Goal	17,500,000.00
27
1d

Cell Formulas
Range	Formula
C10	C10	=SUMPRODUCT(--(B1>A6:A9),B1-A6:A9,B6:B9-B5:B8)
B17:B19	B17	=B16-$C$15
A20	A20	=BigNum
B24	B24	=SUMPRODUCT(--(B1>A6:A9),B1-A6:A9,B6:B9-B5:B8)
C24	C24	=SUMPRODUCT(--(B1>A16:A19),B1-A16:A19,B16:B19-B15:B18)

 

[Dave Patton]

Commission2022.xlsm
	A	B	C
1	Total sales	20,000,000.00
2
3	Initial
4	Brackets	Rates
5	cell b7 is blank
6	0	100%
7	10,000,000	85%
8	15,000,000	65%
9	20,000,000	50%
10			17,500,000.00
11
12
13	Proposed
14	Brackets	Rates	Factor
15	cell b7 is blank		0.166666667
16	0	100%
17	8,000,000	83%
18	17,000,000	67%
19	25,000,000	50%
20	1E+308
21
22
23		Initial	Proposed
24		17,500,000.00	17,500,000.00
25
26	Goal	17,500,000.00
27
1d

Cell Formulas
Range	Formula
C10	C10	=SUMPRODUCT(--(B1>A6:A9),B1-A6:A9,B6:B9-B5:B8)
B17:B19	B17	=B16-$C$15
A20	A20	=BigNum
B24	B24	=SUMPRODUCT(--(B1>A6:A9),B1-A6:A9,B6:B9-B5:B8)
C24	C24	=SUMPRODUCT(--(B1>A16:A19),B1-A16:A19,B16:B19-B15:B18)

 

[drob08]

Thanks for all the replies. This has been extremely helpful and I think there are several solutions out there, and likely more efficient than what I ended up doing. Here was what I ended with which still has slight variances depending on where each price fits within the range. Alternatively a goal seek would work for the final priced tier but I was hoping to not require that.

Tier List Equation.xlsx
	B	C	D	E	F	G	H	I
2	Custom Tiers					List Tiers
3	Min	Max	Proposed Fee			Min	Max	List Tier Adj. Proposed Fee
4	1	10,000,000	0.00600			–	8,000,000	0.00600
5	10,000,001	15,000,000	0.00550			8,000,001	18,000,000	0.00545
6	15,000,001	20,000,000	0.00500			18,000,001	23,000,000	0.00470
7	20,000,001	40,000,000	0.00450			23,000,001	45,000,000	0.00370
8	40,000,001	999,999,999	0.00100			45,000,001	999,999,999	0.00100
9
10
11
12
13	Average Annual Revenue
14	Volume	Custom Tiers	List Tiers	Variance
15	96,210,121	$ 258,710	$ 258,710	$ (0)
16	83,660,975	$ 246,161	$ 246,161	$ (0)
17	72,748,674	$ 235,249	$ 235,249	$ (0)
18	61,836,373	$ 224,336	$ 224,336	$ (0)
19	52,560,917	$ 215,061	$ 215,061	$ (0)
20	44,676,779	$ 207,177	$ 206,303	$ 874
21	37,975,263	$ 193,389	$ 181,477	$ 11,912
Final

Cell Formulas
Range	Formula
G4,H8	G4	=+'List Tiers'!F51
G5:G8,B5:B8	G5	=+H4+1
I4	I4	=IF(B17<H4,"Custom Tier to Low",IF(H4>C4,(SUMPRODUCT((H4<=Final!$C$4:$C$8)*(H4>Final!$B$4:$B$8)*(H4-Final!$B$4:$B$8)*Final!$D$4:$D$8)+SUMPRODUCT(((H4>Final!$C$4:$C$8)*(Final!$C$4:$C$8-Final!$B$4:$B$8))*Final!$D$4:$D$8))/MAX((H4),1),(SUMPRODUCT((H4<Final!$C$4:$C$8)*(H4>Final!$B$4:$B$8)*(H4-Final!$B$4:$B$8)*Final!$D$4:$D$8)+SUMPRODUCT(((H4>=Final!$C$4:$C$8)*((H4<=Final!$C$4:$C$8))*(Final!$C$4:$C$8-Final!$B$4:$B$8))*Final!$D$4:$D$8))/MAX((H4),1)))
I5:I8	I5	=SUM(SUMPRODUCT((G5<=Final!$C$4:$C$8)*(G5>Final!$B$4:$B$8)*(G5-Final!$C$4:$C$8)*-1*Final!$D$4:$D$8),SUMPRODUCT(((H5>Final!$C$4:$C$8)*(G5<=$B$4:$B$8)*($C$4:$C$8-Final!$B$4:$B$8))*Final!$D$4:$D$8),SUMPRODUCT(((H5<=Final!$C$4:$C$8)*((H5>Final!$B$4:$B$8))*(H5-Final!$B$4:$B$8))*Final!$D$4:$D$8))/SUM(SUMPRODUCT((G5<=Final!$C$4:$C$8)*(G5>Final!$B$4:$B$8)*(G5-Final!$C$4:$C$8)*-1),SUMPRODUCT(((H5>Final!$C$4:$C$8)*(G5<=$B$4:$B$8)*($C$4:$C$8-Final!$B$4:$B$8))),SUMPRODUCT(((H5<=Final!$C$4:$C$8)*((H5>Final!$B$4:$B$8))*(H5-Final!$B$4:$B$8))))
C15:C21	C15	=(SUMPRODUCT((B15<=Final!$C$4:$C$8)*(B15>Final!$B$4:$B$8)*(B15-Final!$B$4:$B$8)*Final!$D$4:$D$8)+SUMPRODUCT(((B15>Final!$C$4:$C$8)*(Final!$C$4:$C$8-Final!$B$4:$B$8))*Final!$D$4:$D$8))
D15:D21	D15	=(SUMPRODUCT((B15<=Final!$H$4:$H$8)*(B15>Final!$G$4:$G$8)*(B15-Final!$G$4:$G$8)*Final!$I$4:$I$8)+SUMPRODUCT(((B15>Final!$H$4:$H$8)*(Final!$H$4:$H$8-Final!$G$4:$G$8))*Final!$I$4:$I$8))
E15:E21	E15	=+C15-D15
B15:B16	B15	=+B16*1.15
B18:B21	B18	=+B17*0.85