Figuring out pay rate.

[dawgpoundfan]

Okay our client has instituted a fixed markup based on the total burden of pay to a consultant - however the fixed markup can not exceed a specific bill rate and the markup is predicated on the total burden of the consultant. So, if I pay Consultant 60.00 per hour, the burden is 16.05% or 69.63. My mark-up is:

Total Burden below 49.99 - 36%
Total Burden 50-74.99 - 28%
Total Burden 75.00 and up - 17%

What I am looking for is formula/function that would give me the PAY RATE based on the Bill Rate. So for the situation above, the candidate's burden is 69.63 so my markup is 28% and therefore my bill rate is $89.13. I have a spreadsheet that will give me this information if we put in pay rate, but the recruiters want list of the pay rates based on the BILL RATE. So, bill rates are as follows:

PM - 74.00 Bill Rate
BSA - 68.00 Bill Rate


They want a spreadsheet listing the pay rates for the NTE Bill Rate - and the ability to change the bill rate so they can see what happens to the bill rate.

I did a worksheet where they can change the pay rate and it gives them the bill rate, I am not able to get my head around what function to use that takes into consideration the three different percentages based on burden because it is backwards to me .

Clear as mud? The attachment shows the yellow box is where they type the pay rate and based on that the Bill Rate is shown - i need it to this in reverse

[rylo]

Hi

Is it right that someone with a lower pay rate / burden rate can have a higher bill rate than some with a higher pay rate?

Take pay rate 64. You have a burden rate of 74.27 and a bill rate of 95.07
Pay rate 65 gives 75.43 and 88.25.

Are these splits really correct???

rylo

[ptm0412]

I think that using 2 IF function is OK for this situation. See attachment.