Calculate Net Funding for Bonus Program

Thanks in advance for the help.

I'm trying to put together a model for a potentional bonus program for hourly employees.

I'm assuming the average employee will earn a 5% Bonus and there's 5% monthly attrition. The number of employees needed per month are set within Row 10. If an employee leaves, his respective funding of the bonus program is cleared. We won't pay bonuses until NEXT YEAR.

Given the calculated attrition, I don't know where these employees stem from. For example, in June we have 6 employees leave. I want to assume that an employee left from equal timeframe. In this respective example, 1 person who was apart of the program since January and their earned bonus $. I want that to be removed from funding of the bonus program.

Thanks again!

Hello,

You will need an identification of if the Subtraction occurred from "Adds" employees # or "Beginning of Period" employees #
Once you have a row that identifies for example -6 = -5(from add) and -1 (from beginning of period),
then you can write an if formula for the bonus calculation.

To keep it simple - let's say everyone actually leaves from that months beginning of period, so with a join date from prior months. What's the best way to structure this?

Resurfacing

Hi Scruz,

Are you saying that an employee i.e. 1 employee left from the month June or the -6 in June all left in the same month. I dont understand this part: "I want to assume that an employee left from equal timeframe." What do you mean. Does that mean that June has Actually 114 plus 11 adds so the attrition for the month is going to be 6*5% or just 1*5%. Please clear what you mean by " I want to assume that an employee left from equal timeframe."

How do you indentify that only 1 person of the 6 that left in June was in Since January? That is why I suggested that you need an identifier to confirm that the left employee was from the same month or from beginning of period. Please advise.

Attached file with function incorporated on row 17

Hey Scruz9, please do let me know if it worked out for you.