Excel Formula to Calculate Year End Bonus

User's Picture

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

Member's Profile

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.

Member's Profile

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

Proformative Advisor
Member's Profile

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

Topic Expert
Member's Profile

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

Topic Expert
Member's Profile
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

Member's Profile

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

Member's Profile

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.

Topic Expert
Member's Profile

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.

Member's Profile

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