more-arw search

Q&A Forum

Excel Formula to Calculate Year End Bonus

excel formula to calculate year end bonusI'm hoping someone can help me with an excel formula issue. I'm trying to create a spreadsheet that can calculate a bonus based on three different tiers. Each tier has two objectives that must be met to reach that bonus. So, for example, if net profit is $1,000 and gross profit is 15%, then bonus is $500. Next tier would be net profit of $1,500 and gross profit of 18% equals a bonus of $1000 and so on. I'm trying to create a spreadsheet that would allow the user to type in the net profit and gross profit and it will automatically calculate the bonus. Can anyone help with the correct excel formula to use? Any help would be greatly appreciated!

Answers

Mark Canes
Title: President
Company: Blue Link Associates Limited
(President, Blue Link Associates Limited) |

You'd probably want to set up the tiers in a tabular format, with columns for Net, GP% and bonus, and then 3 rows (or more if you add more tiers). Then you'd want to use a lookup type of function to lookup the first row that matches (in this case >=) both your criteria.

You cannot use the basic lookup functions (like VLookup) because they only match one column. One approach would be to use the INDEX function, and next a MATCH function inside it. Sounds complicated, but it's really not. I suggest you Google something like "Excel Index function nested Match" or variations, and you'll find some tutorials on how to do this.

Sarah Jackson
Title: Associate Editor
Company: Proformative
(Associate Editor, Proformative) |

Here's a treasure trove of free excel spreadsheets, free accounting spreadsheets, plus all kinds of other excel resources, such as tests, tutorials and more:

https://www.proformative.com/resources/free-accounting-spreadsheet

Plus, this free "Excel Shortcuts Cheat Sheet"

https://www.proformative.com/whitepapers/excel-shortcuts-cheat-sheet

Enjoy!

Best... Sarah

Topic Expert
Patrick Dunne
Title: Chief Financial Officer
Company: Milk Source
(Chief Financial Officer, Milk Source) |

You could also use nested @if statements in excel to accomplish this. It may be more complicated if you have more than five tiers. You may want to use the @if to identify if both conditions have been met and make the result the bonus. That's the great thing about excel....a lot of different options

Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

I've added a simple worksheet to the Resources section here:
https://www.proformative.com/resources/excel-lookup-worksheet

I think it has what you're looking for.

Charley

Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

Charley's method is excellent.

Here is an alternative also posted in the Resources section, using VLOOKUP and MIN if you're more comfortable with those functions. Of course you may use named ranges if you like.

In short, you use VLOOKUP to get the bonus for each criterion independently and then use MIN to show the lowest bonus amount. This ensures that the bonus calculated is for the figures that meet all of the criteria.

https://www.proformative.com/resources/calculation-multi-criteria-bonus

Gerard Brinkman
Title: Owner
Company: Gerard J. Brinkman CPA
(Owner, Gerard J. Brinkman CPA) |

I always tell Human Resources not to create compensation and bonus plans so complicated that Accounting cannot calculate the amounts owed. Keep it simple.

Jeffrey Chalmers
Title: President, Financial Effectiveness Team ..
Company: Silicon Valley Accountants
(President, Financial Effectiveness Team Leader, Silicon Valley Accountants) |

Agree with Gerard.

Work hard with the executive team to simplify bonus calculations so they don't delay the close or create complex Excel with opportunities for errors. You are often able to create plans which achieve the same result with a simple calculation.

Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

By the way, the lookup sheet I posted would use identical logic for working with quantity discounts in Excel.

For example, you might have one price at 100 units and another price at 500 units. So if someone buys 350 units, the table would give you the price. A similar table could calculate a discount based on the amount of the total order.

This type of calculation wouldn't be needed day-to-day, of course. But for planning and analytical worksheets, it could be very handy.

Randall Bolten
Title: CEO
Company: Lucidity
LinkedIn Profile
(CEO, Lucidity) |

Dear Anonymous--
As I think you can see, this is not a complicated Excel problem. What really concerns me is that the plan sounds poorly thought-out as you described it. For example, what happens if you fall just short of one objective and knock the other one out of the park? Also, it sounds very binary -- very small changes in a number, from just under the threshold to just over it, will make a big difference in the amount of the bonus. That's almost always a mistake, potentially leading to moral hazard -- i.e., unethical conduct motivated by large potential payoff for small bumps in financial results -- and unhappy employees, who missed out on a big bonus because of a tiny shortfall in a financial result.
Regards, R

44841 views

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.

Excel @ Excel - 30 Courses Available