Value needed to reach target percentage

[Jambrose]

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Target GM%[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD]Hourly Cost[/TD]
[TD]4.03375[/TD]
[/TR]
[TR]
[TD]Hourly Pay Rate[/TD]
[TD]$80.00[/TD]
[/TR]
[TR]
[TD]Hourly Bill Rate[/TD]
[TD]$100.00[/TD]
[/TR]
[TR]
[TD]Hourly GM[/TD]
[TD]$15.96625[/TD]
[/TR]
[TR]
[TD]Pay needed for Target GM[/TD]
[TD]$70.96625[/TD]
[/TR]
[TR]
[TD]Bill needed for Target GM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GM%[/TD]
[TD]15.96625%[/TD]
[/TR]
[TR]
[TD]Amount off Target GM%[/TD]
[TD]-9.03375%[/TD]
[/TR]
</tbody>[/TABLE]
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?

[TABLE="class: cms_table, width: 500, align: left"]
<tbody>[TR]
[TD]Target GM%[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD]Hourly Cost
[/TD]
[TD]4.03375[/TD]
[/TR]
[TR]
[TD]Hourly Pay Rate[/TD]
[TD]$80.00[/TD]
[/TR]
[TR]
[TD]Hourly Bill Rate[/TD]
[TD]$100.00[/TD]
[/TR]
[TR]
[TD]Hourly GM[/TD]
[TD]$15.96625[/TD]
[/TR]
[TR]
[TD]Pay needed for Target GM[/TD]
[TD]$70.96625[/TD]
[/TR]
[TR]
[TD]Bill needed for Target GM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GM%[/TD]
[TD]15.96625%[/TD]
[/TR]
[TR]
[TD]Amount off Target GM%

Hourly Fee (% of Bill Rate)
Hourly Fee Amount[/TD]
[TD]-9.03375%

5%
$5.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]Hourly Cost[/td][td]

$4.03​

[/td][td]B1: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Hourly Pay Rate[/td][td]

$80.00​

[/td][td]B2: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Target GM%[/td][td]

25%​

[/td][td]B3: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Hourly Fee (% of Bill Rate)[/td][td]

5%​

[/td][td]B4: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Pay needed for Target GM[/td][td="bgcolor:#CCFFCC"]

$120.04​

[/td][td="bgcolor:#CCFFCC"]B5: =(B1 + B2) / (1 - B3 - B4)[/td][/tr]
[/table]