Tip Pool Calculator

[kala]

Hello, I own a restaurant and would like to develop a tip pool calculator that distributes the tip amounts for all the employees who share in the tip pool. The factors that go into this are the total tip amount, number of hours worked, and a share assigned to each employee. The groups would look something like this:
Back of House (BOH) = 20%
Server = 20%
Lead Server = 25%
Caterer = 35%

Let's say in a given week, the employees worked 140 hours and earned $100 in tips amongst each of them. There groups/hours are shown below:
Employee A (Lead Server): 40
Employee B (Server): 0
Employee C (Server): 10
Employee D (Server): 15
Employee E (Lead Server): 30
Employee F (Server): 0
Employee G (Server): 0
Employee H (BOH): 40
Employee I (Caterer): 5
Employee J (Server): 0

It sounds like it should be easy, but I'm having the hardest time figuring out a formula that accounts for total tips, number of hours worked, and a percentage weight. Would someone provide some guidance? Once I have a strong formula, I think I can handle automating it so the tip allocation changes dependent on who's working... Thank you SO much!

 

[jarjarbingie]

formula you need is:

TipAmount * Department Share * (Employee Hours / Total Hours by Department)

example, for Employee A:

100 * 25% * (40/70)

Hello, I own a restaurant and would like to develop a tip pool calculator that distributes the tip amounts for all the employees who share in the tip pool. The factors that go into this are the total tip amount, number of hours worked, and a share assigned to each employee. The groups would look something like this:
Back of House (BOH) = 20%
Server = 20%
Lead Server = 25%
Caterer = 35%

Let's say in a given week, the employees worked 140 hours and earned $100 in tips amongst each of them. There groups/hours are shown below:
Employee A (Lead Server): 40
Employee B (Server): 0
Employee C (Server): 10
Employee D (Server): 15
Employee E (Lead Server): 30
Employee F (Server): 0
Employee G (Server): 0
Employee H (BOH): 40
Employee I (Caterer): 5
Employee J (Server): 0

It sounds like it should be easy, but I'm having the hardest time figuring out a formula that accounts for total tips, number of hours worked, and a percentage weight. Would someone provide some guidance? Once I have a strong formula, I think I can handle automating it so the tip allocation changes dependent on who's working... Thank you SO much!

 

[kala]

Jarjarbingie, that worked like a charm! Thank you for taking the time. I'm still working on automating it so there's a way to add/remove employees, input different values, etc. If anyone is interested, don't hesitate to reach out!