"If" Formula Help: Auto Populate a Bonus Based on Return on Sales

[mjrak]

Hi all,

I am trying to prepare a bonus spreadsheet for salaries manager. The spread sheet is based on Return on Sales. Where I have ran into trouble is trying to figure out how to create a formula for the following information.

0-5% Return on Sales will be paid out at 0.35%
5.01-12% Return on Sales will be paid out at 1.5%
12.01%+ Return on Sales will be paid out at 2.5%

At the top of my page I have create cells for the following:

Top Line Sales
Return on Sales
Return on Sales %

The formula I need to figure out is how to take that information based on what the % of return on sales is and put that number into the correct bonus area.

For example:
If top line sales = $10,000
Return on Sales = $2,000
Return on Sales % = 20%

Of that $2,000, the first 5% (of the top line sales NOT of the bonus) needs to go into the 1st line bonus, the next 6.99% (of the top line sales NOT of the bonus) needs to go into the 2nd line bonus and the remaining 7.99% (of the top line sales NOT of the bonus) needs to go into the third line bonus.

Of course, I want the formula to work even if the Return on Sales % is lower.

For example:
If top line sales = $10,000
Return on Sales = $1000
Return on Sales % = 10%

Of that $1,000 the first 5% (of the top line sales NOT of the bonus), needs to go into line 1 of the bonus and the remaining 4.99% (of the top line sales NOT of the bonus) needs to go into line 2 of the bonus while line 3 would be 0.

I'm not sure if I've explained myself that well. If anyone can help me that would be greatly appreciated. I have tried a number of different "if" formulas and searched online and have been unsuccessful.

If anyone needs to see the spreadsheet to be more clear as to what I need I would be happy to provide that.

Thanks in advance,

Mike

[Cutter]

Hi Mike - welcome to the forum. Nice to see another member from my home town.

Unfortunately your post doesn't comply with Rule #1 of the Forum Rules.
Click on the Forum Rules link at top of page and read through the rules. Once you change your title you'll get the help you want.

And, yes, a sample file would definitely help.

[mjrak]

Thanks for the heads up, I was vague b/c I'm not really sure what type of formula will be needed to complete the task. Also, I have attached my sample spread sheet with my last feeble attempt at my formulas.

The cells needing the formulas are: B15, B16 and B17

Once again thanks in advance to anyone able to help!

[Cutter]

Good morning.
I've had a look at your file. I'll just point out a few obvious errors in your formulas to begin with.
In B17 you have: =IF(D6>12,(B6*D6-B16-B15))
The result is: FALSE because D6 is 15%
The 12 should actually be 12% or 0.12 so the (B6*D6-B16-B15) part is skipped. But you haven't provided an alternative result to be given when D6>12 is not true.

In B16 you have: =IF(AND(D6<5,D6>12,D6<12),(B6*0),(B6*D6-B15)*(B6*12%-B15))
In the AND() portion you should have just 5 and 12 instead of 5% and 12%.
Also you have D6>12,D6<12 within the AND(). That will never happen. D6 can not be greater than AND less than a value.
The calculation you have for a TRUE return on the IF() is (B6*0). That will always be 0 so the multiplication is pointless. The (B6*0) can be replaced with just 0.

In B15 you have: =+(B6)*5%
Although not an error it can simply be =B6*5%

Could you amend your sample to show what the expected results for cells B15:B17 would be and outline how/why you got those values?

[mjrak]

Hi,

Thank you for your feedback. Obviously I am over thinking my formulas, at the same time I am also a pretty green user with regards to formulas, so my over thinking was a result of looking up tutorials and trying to figure them out in my own head.

Please find attached a sample spread sheet with the correct figures in cells B15-17.

The math here is as follows:

The 1st line bonus payout (B15) is achieved for any return on sales up to 5% of sales. So the math is B6*5%.

The 2nd line bonus payout (B16) is achieved for any return on sales between 5.01% and 12%. So the math is (B6*12%)-B15.

The 3rd line bonus payout (B17) is achieved for any return on sales from 12-01% and above. So the math is (C6-B16-B15).

On a one to one basis, the formulas are very simple. But, I also want them to work when the return on sales % varies. For example, if the return on sales was only 8% instead of 15% then the math would look like this. I have attached a sample with this math as well, it's #3.

The 1st line bonus payout (B15) is achieved for any return on sales up to 5% of sales. So the math is B6*5%

The 2nd line bonus payout (B16) is achieved for any return on sales between 5.01% and 12%. So the math is C6-B15.

The 3rd line bonus payout (B17) is achieved for any return from 12.01% and above. Since the return on sales % was only 8, this would be 0.

In short, what I'd like to be able to do is simply enter in sales for the quarter, cells B3, 4 and 5; as well as the return on sales for each of those months, cells C3, 4 and 5 and then have a formula that will auto populate the bonus payout cells (B15, 16 and 17), ragardless of the return %.

I hope that makes sense!

Thanks again for the time spent and in advance for helping further!

Mike

[Cutter]

See if the attached is what you want. I have changed a few formulas other than B15:B17, too.

[mjrak]

Hi Cutter,

Thank you SOO much. The only problem I see is if the return on sales is less than 5%, the formula doesn't work.

Please see attached. If the return on sales is between 0 and 5% then the bonus payout for line B15 should reflect only what the actual return was.

I have attached a sample sheet for you to see.

Thank you so much!!

Mike

[Cutter]

Looks like I forgot to change one of the original errors I pointed out.
The formula in B16 has AND(D6>5,D6<=12) instead of AND(D6>5%,D6<=12%).

Try this amended copy:

[mjrak]

Hi Cutter,

This looks great, but the problem with the %'s if less than 5% still remains. Please see the attached example. The payout should be only the $7500 that was actually earned, not a firm 5% of the top line sales since that wasn't achieved.

Thank you again for your help with this...it is greatly appreciated!

Mike

[Cutter]

Ahhhh, I was looking at B16 instead of B15.

Try (in B15) =MIN(C6,B6*5%)

[mjrak]

Success!!!! Thank you so much for all your help. This now works wonderfully!

Thanks again,

Mike

[Cutter]

You're very welcome. Sorry it took so long - having a 'fuzzy' day.