I'm hoping someone can help me with an
Excel Formula to Calculate Year End Bonus
Answers
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.
1
Here's a treasure trove of free excel spreadsheets, free
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
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
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
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
I always tell
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.
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.
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
Filed Under:
Human Capital