Help with formula

  • Thread starter Thread starter bp
  • Start date Start date
B

bp

Hi All,
Running Win98SE :-( and Excel 2000.
Is it possible to get assistance with the following formula.
I am currently determining the Profit Margin Percent with the following
((BillRate-Payrate)-(PayRate*Fringe))/BillRate=Profit Margin %
OR
=((20.7-12)-(12*.24867))/20.7=27.61%
I then have to play with the billrate to get a number close to 30%.
Is there a way to rework the formula by plugging in the 30% and working out
the BillRate?
Thanks
Bill
 
Hi Bill,

Look at Tools>Goal Seek

Post again in this same thread if you can't get it done

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi All,
| Running Win98SE :-( and Excel 2000.
| Is it possible to get assistance with the following formula.
| I am currently determining the Profit Margin Percent with the following
| ((BillRate-Payrate)-(PayRate*Fringe))/BillRate=Profit Margin %
| OR
| =((20.7-12)-(12*.24867))/20.7=27.61%
| I then have to play with the billrate to get a number close to 30%.
| Is there a way to rework the formula by plugging in the 30% and working out
| the BillRate?
| Thanks
| Bill
|
|
 
Hi Niek,
Thanks for your solution. I was looking for a formula to accomplish the same
result but giving me the rate instead of the profit margin %. Can you think
of a way to accomplish this with a formula change?
Thanks again for you solution.
Bill
 
=((20.7-12)-(12*.24867))/20.7=27.61%
Click to expand...

so by transposition

=((20.7-12)-(12*.24867))/30%=19.0532

therefore

=((20.7-12)-(12*.24867))/19.0532=30%


is that what you are looking for?
--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
so by transposition

=((20.7-12)-(12*.24867))/30%=19.0532

therefore

=((20.7-12)-(12*.24867))/19.0532=30%


is that what you are looking for?
Click to expand...

Both 20.7 need to change :)
 
Hi Sandy,
Thanks for your reply
If I may go back, the formula below is actual. It works. But I must adjust
the 20.7 until I achieve the 30% instead of the 27.61% displayed.
=((20.7-12)-(12*.24867))/20.7=27.61%

I need to try to establish a new equation to find X below
=((X-12)-(12*.24867))/X=30.00
Hope this makes sense
Can you help?
Thanks
Bill
 
Correct.
Thanks
Bill
so by transposition

=((20.7-12)-(12*.24867))/30%=19.0532

therefore

=((20.7-12)-(12*.24867))/19.0532=30%


is that what you are looking for?
Click to expand...

Both 20.7 need to change :)
 
Re-working original formula
((BillRate-Payrate)-(PayRate*Fringe))/BillRate=Profit Margin %
Billrate = ((PayRate*Fringe)+PayRate)/(1-Profit Margin%)


BillRate = ((12*0.24867)+12)/(1-0.30) = 21.405 %

verify with original figures
BillRate = ((12*0.24867)+12)/(1-0.2761) = 20.7 %
 
Steve...............
You are the man. I could not get my old mind around that formula.
It works like a dream!
Thank you so much.
Thanks to all that responded. You folks are the greatest!
Bill
Re-working original formula
((BillRate-Payrate)-(PayRate*Fringe))/BillRate=Profit Margin %
Billrate = ((PayRate*Fringe)+PayRate)/(1-Profit Margin%)


BillRate = ((12*0.24867)+12)/(1-0.30) = 21.405 %

verify with original figures
BillRate = ((12*0.24867)+12)/(1-0.2761) = 20.7 %
 
ta for the feedback.

Steve...............
You are the man. I could not get my old mind around that formula.
It works like a dream!
Thank you so much.
Thanks to all that responded. You folks are the greatest!
Bill
Re-working original formula
((BillRate-Payrate)-(PayRate*Fringe))/BillRate=Profit Margin %
Billrate = ((PayRate*Fringe)+PayRate)/(1-Profit Margin%)


BillRate = ((12*0.24867)+12)/(1-0.30) = 21.405 %

verify with original figures
BillRate = ((12*0.24867)+12)/(1-0.2761) = 20.7 %
Click to expand...
 
Back
Top