looking to reverse engineer a formula

[andrewvanmarle]

There is a pricing structure I'd like to reverse engineer:

in the example i have there are two choices:

1.6% over a transaction price (that will most likely be between 400 and 425)

or

1.4% over the amount up to 375 and 4,6% over the amount above.
The point where option 2 becomes more expensive than option 1 is exactly at 400

Im assuming the following variables are a given: the 1.6% in option 1, the breakeven point of 400, and the 1.4 % over 375.

How would I set up a calculation/formula, where I fill in those variable to get to 4.6%?


Thanks in advance!

 

[Eric W]

I'm not sure exactly what you're asking for, but here's how to set up those formulas:

Book1
	A	B	C	D	E	F	G	H
1	375	Option 1	Option 2		Percent 1	Percent 2a	Percent 2b	Cutoff
2		381	380.25		1.60%	1.40%	4.60%	375
Sheet8

Cell Formulas
Range	Formula
B2	B2	=A1*(1+E2)
C2	C2	=MIN(A1,H2)*(1+F2)+MAX(0,A1-H2)*(1+G2)

You can change the values in E2:H2 as you like.

 

[andrewvanmarle]

Hi Eric thanks for your reply. I'm looking to see what percentage (the 4.6 in the example) i would get to have the break even point be 400

In other words, I want G1 Percentage 2b to be the solution to the equation.

Oh and B2 would only be A1*E2 (since we get paid a comission over the purchase price, not the purchase price itself...haha I wish....)

Oh and shouldn't A1 be the point where the breakeven is to occur? (in the example at 400)?

I'm going to fiddle with what you gave me in the mean time

 

[andrewvanmarle]

darn, I went past the editing window, so i'll reply to myself:



edit again again again;

I think the formula would be derived from this:

D2*A1=E2*G2_+F2*A2

I must admit I learned in school how to move the whole equation to the left leaving only F2 on the right, but I can't for the life of me remember how.

 

[Snakehips]

@andrewvanmarle

Is this what you are after?

Book1
	A	B	C	D	E
1	Break Even	Percent 1	Percent 2a	Precent 2b	2 Cutoff
2	400	1.60%	1.40%	4.60%	375
Sheet4

Cell Formulas
Range	Formula
D2	D2	=((A2*B2)-(E2*C2))/(A2-E2)

 

[andrewvanmarle]

@Snakehips

yes! that's the one!

thabk you!

 

[Snakehips]

You are welcome.
Do you want the associated maths lesson? ?