Value needed to reach target percentage

[Jambrose]

Target GM%	25%
Hourly Cost	4.03375
Hourly Pay Rate	$80.00
Hourly Bill Rate	$100.00
Hourly GM	$15.96625
Pay needed for Target GM	$70.96625
Bill needed for Target GM
GM%	15.96625%
Amount off Target GM%	-9.03375%

<tbody>
</tbody>

1

2
3

4
5=B4-B3-B2

6=B3+(B4*B9)
7
8=B5/B4

9=B8-B1

Hey Guys,

This is the actual data from my spreadsheet, the formulas for each cell listed on the right.
I need the correct formula for B7.
I need B7 to give me what the bill rate needs to be in B4 to get me to my Target GM% indicated in B1.
In other words, I need B7 to do what B6 is doing, but for the bill rate. If I put the value from B6 into B3 it gets me to my Target.
B8 is the GM% for the current values.
B9 is simply showing how far off target I am.

Hope this makes sense, thanks for any help you can offer.

 

[JustynaMK]

Hi Jambrose,

Insert this formula into cell B7:
=(B3+B2)/(1-B1)

This will give you the bill that, if inserted into cell B4, will match your target (i.e. cell B9 will be =0).

Hope it helps!

 

[Jambrose]

Thanks! That works perfectly!

Sometimes I have a fee/cost that's a percentage of the hourly bill rate.
So the fee equals the product of the Hourly Bill rate and the Fee percentage.
Since it's a percentage of the Hourly bill rate the fee amount changes every time I change the bill rate which changes my GM %.
Any way to get B7 to work the same way with this fee included?

Target GM%	25%
Hourly Cost	4.03375
Hourly Pay Rate	$80.00
Hourly Bill Rate	$100.00
Hourly GM	$15.96625
Pay needed for Target GM	$70.96625
Bill needed for Target GM
GM%	15.96625%
Amount off Target GM% Hourly Fee (% of Bill Rate) Hourly Fee Amount	-9.03375% 5% $5.00

<tbody>
</tbody>

 

[JustynaMK]

Hi - sorry, didn't quite understand your explanation. Could you please rephrase it?

Currently your "Hourly Bill Rate" is a sum of "Hourly Cost", "Hourly Pay Rate" and "Hourly GM". Would you like it to also include "Hourly Fee Amount"?

 

[Jambrose]

Correct, and since the fee amount is a percentage of the hourly bill rate, it changes every time I change the hourly bill rate.

 

[shg]

	A​	B​	C​
1​	Hourly Cost	$4.03​	B1: Input
2​	Hourly Pay Rate	$80.00​	B2: Input
3​	Target GM%	25%​	B3: Input
4​	Hourly Fee (% of Bill Rate)	5%​	B4: Input
5​	Pay needed for Target GM	$120.04​	B5: =(B1 + B2) / (1 - B3 - B4)