Backward culculating percentages in excel

[kwinsw]

Hi,

I wonder if anyone can help: I want to backward calculate percentages in Excel. For instance, if an agency charges me $230 a day for a temp, and I know that the agency's markup is 12.5%, I want to be able to work out what the fee was before the agency added its markup.

If feels like it should be easy, but nothing I try works. Can anyone help?

Thanks

Karl

 

[gizmo1973]

Couple of ways you can do this assuming your gross price (230 USD) is in cell A1

=(A1/100)*12.5

This will give you the 12.5% then take the starting price of 230 USD from the result of the equation to equal net price (handy if you want to also know how much the agency charged you)

or

=A1*87.5%

This will give you the same outcome but does not show charge made

Each time though you will have to change the % charged so you could well look at referencing that to another cell where you hold the % charged and just change that unless you pay a flat 12.5% charge

Regards, Phil.

http://www.manchester-medical-services.co.uk

 

[AnotherHiggins]

Phil,
If A1 contains 230, then A1*87.5% = 201.25. But 12.5% of 201.25 is 25.16. 201.25 + 25.16 = 226.41, not 230.

Karl,

You know that 230 is 112.5% of the original 'wholesale' price. So divide 230 by 112.5%. In general terms, you can use the following to find the original 'wholesale' price:

A1: 230
B1: 12.5%

[COLOR=blue white]=A1/(1+B1)[/color]

That gives you 204.44.
204.44 * .125 = 12.5% of 204.44 = 25.56
204.44 + 25.56 = 230

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[gizmo1973]

I still get it to be 201.25
230 / 100 = 2.3 (which we can see is 1%)
If we then say 2.3 X 12.5
That equals 28.75 (or 12.5%)
230 - 28.75 = 201.25

Am I going mad?

I was taking it that 230 USD was the gross figure and Karl neede the Net figure



Regards, Phil.

http://www.manchester-medical-services.co.uk

 

[AnotherHiggins]

28.75 is 12.5% of 230. That isn't the question.

What we're after is the wholesale price, to which a 12.5% markup is added, resulting in a total of $230.

Company B has a temp employee (hired through Company A, a Temp agency). Company B is paying Company A $230/day for that employee. What we want to know is how much is Company A paying the employee/day (no doubt so they can steal the employee away for as little money as possible).

Company A pays the employee $204.44/day. They charge a 12.5% markup to company B, for whom the employee will work.

12.5% of 204.44 is 25.56.

So you have the $204.44 Company A is paying the employee. Add to that the $25.56 that Company A is charging to be the intermediary. The result is $204.44 + $25.56 = $230.00.

- - -

Consider this:

I'm paying a total of $100 for a phone. I know that sales tax in my state is 10%. How much does the phone cost before tax?

Using your formula, you'd get the following:

100 / 100 = 1 (which we can see is 1%)
If we then say 1 X 10
That equals 10.00 (or 10.0%)
100 - 10 = 90.00

But 10% of $90 is $9. That would mean the phone costs $99. But it didn't, it cost $100.

The actual pre-tax price of the phone is $90.91. 10% tax on $90.91 is $9.09. $90.91 + $9.09 = $100.00.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[gizmo1973]

I'm on the bus now!!!

I was Gross'ng and Net'ng away without a care in the world forgteting that when a % is taken off you simply can't add that % back on to come back to the orignal figure!

Regards, Phil.

http://www.manchester-medical-services.co.uk

 

[AnotherHiggins]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[kwinsw]

Hi,

Thank you very much, that is perfect.

And no, i'm planning on driving down anyone's wages or poaching employees. I want to design a spreadsheet into which I can enter a day rate and the number of days a person will be working and which will show me, in different columns, how much I can save if I manage to bargain the agency down to 10/9/8% and so on. That way, if I'm on the phone to an agency, as soon as they give me their figures, I can see what I should be trying to talk them down to.

Thanks again.

Cheers


Karl

 

[AnotherHiggins]

I was just picking about the "stealing employees" bit.

Glad we could help.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.