HI! I'm building a formula for the computation of birthday bonus. Essentially, BONUS = ((Standard Cash Gift*years in service)+(Additional cash gift*age) but the standard cash gift and additional cash gift vary for different levels of management so this has to be looked up in a table. There's a total of 5 tables with different bonus multipliers for different levels of management (ie top, upper middle, middle, lower, rank&file). What should I use? Index match or vlookup? what other functions? Thank you in advance!!!
Formula for excel bonus computation
Answers
One piece of advice.....(although unsolicited)
Keep your policy SIMPLE. Don't make it too complicated that you will even have difficulty putting it on an excel formula. (See what I did there? :D)
Keep the SPIRIT close to the intended purpose. A Bday is a Bday is a Bday. It is the same day for a staff member or for your Managers or for your executives.
Example....(1) you are factoring in "age"? Really? (2) you are factoring "level of management"? BOTH opens to a lot of CULTURE issues, of course aside from making it unneccessarily complicated. Oh, btw, factoring age may even be discriminatory. To drive home the point (just being sarcastic)....why not factor in HEIGHT? or WEIGHT or HEALTH (blood pressure?)
The more important point is this......Give the bonus because they are your employees. PERIOD. Not computed based on how old they are or the level of their position. Well, there are a few more concerns but I think you get my point. Both can have the opposite effect to what you intend it to have.
If I may....stay on % of salary (or a fixed amount) ...both for cash or gift card...
Example, If you want to factor in length of employment...make it a broader band. Say, 30% for 0-5 years, then 35% for 6+ yrs. And if you are on the fixed amount....a higher dollar figure.
On the positive side......KUDOS on giving your staff B-day bonuses.
As a P.S., I just realized you are a "consultant" (not sure which type, "compensation?" ) and our goals (me as a CFO/COO) may be different.
Since there are so many tables involved, I think I would try to use Access instead
Thanks for your inputs Emerson. True enough, I am doing this with the lens of a consultant and not a CFO/COO. Developing the formula or spreadsheet and not weigh in on the spirit of which it was meant for (more the "how" than the "why", well the tools for the "how" at least) is all I am to do.
Much as I agree on your points, this CEO has a penchant for numerology and isn't so much of your conventional CEO. Best practices will be best practices which he is fully aware of but I guess the very nature of birthday bonuses lies very well within his prerogative... and how he wants to play things up.
In any case, I am still trying to find the best approach for this, be it vlookup, index match so any help will still be much appreciated.
Thanks!
Kat