more-arw search

Q&A Forum

Formula for excel bonus computation

Katrina Manahan's Profile

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!!!

Answers

EMERSON GALFO
Title: CFO
Company: C-Suite Services
LinkedIn Profile
(CFO, C-Suite Services) |

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.

Steve Sheridan
Title: Associate
Company: Dean Lewis Associates
(Associate, Dean Lewis Associates) |

Since there are so many tables involved, I think I would try to use Access instead

Katrina Manahan
Title: consultant
Company: BcS
(consultant, BcS) |

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

540 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.