Hello,
I am in need of your help. I am a manager of a nightclub and at the end of the night I am responsible for splitting up tips based on each individuals hours worked. I know it seems easy, you would just divide the total amount of tips by the total hours to get an hourly rate and then pay each employee according to their hours. However, I have a bit of a different situation. In my line of work, some employees receive what are called "funny money" tips which come from a customers credit card transaction. These bills look like monopoly money and each bill they receive is worth 9 dollars each. When I split up the tips at the end of the night, these "funny money bills" must be distributed as evenly as possible in the tip pool starting with the employees who work the LEAST amount of hours. Here is my example:
I have the following employees who worked these hours:
Pat 2
Robert 9
Bill 12
MJ 2.5
Rocco 9
Lisa 13
Kirk 13
Bob 9
Abel 2
Greg 9
Bart 1
The amount of "funny money" I have is 26 bills for a total of $234. (26*9)
The total amount of real cash tips is $146.
The total amount in the tip pool is $380. (234 + 146)
The total amount of hours is 81.5.
The hourly would be $4.66 per hour.
Knowing that I MUST evenly distribute the funny money bills first to the employees with the LEAST hours, How can I automate this into an excel worksheet so it does not take me an hour to do every single night. I want to be able to plug in the hours, the funny money bills, and the total cash and have it calculated automatically. The numbers need to be rounded properly so all the money gets paid out.
Note: In this case, the employee who worked one hour would not get a funny money bill worth 9 bucks because his total amount due to him is only $4.66 or rounded to the nearest dollar would be $5.
Thank you in advance for anyone who can help me develop this worksheet. It would enable me to get home to my wife and kids an hour earlier every day!