• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

tip pool worksheet

robfl22

New Member
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!
 
Rob
I am little confused here,
you are dividing total amount by total hours to get $ per hour.
you have employee hours too, if employee worked 2 hrs, he will be paid 4.66*2=$9.32 , which includes both funny money and cash tip money. now it employee worked only 1hr, how you will be paying him, do you do math like 4.66*1=4.66 or ($146/81.5)=$1.79 per hour, no funny money included here.

please clarify or let us know from above example numbers, how much Pat (2hrs) , Robert (9)and Bart (1hr) will get.
 
Pat 2*4.66=9.32 Will round to 9 dollars
Robert 9*4.66=41.94 will round to 42 dollars
Bill 12*4.66=55.92 will round to 56 dollars
MJ 2.5*4.66=11.65 will round to 12 dollars
Rocco 9*4.66=41.94 will round to 42 dollars
Lisa 13*4.66=60.58 will round to 61 dollars
Kirk 13*4.66=60.58 will round to 61 dollars
Bob 9*4.66=41.94 will round to 42 dollars
Abel 2*4.66=9.32 will round to 9 dollars
Greg 9*4.66=41.94 will round to 42 dollars
Bart 1*4.66=4.66 will round to 5 dollars

The total dollars adds to 381 and not 380 but I can live with that. If its within a dollar or two Im ok with it. The important thing to me is to allocate those 9 dollars funny money bills properly. Obviously Bart would not get any funny money bills because he only gets 5 dollars so all of his tip allotment would be cash. Then I go up the ladder from the least amount of hours. The next would be Pat and Abel. They both are at 9 dollars so they would get one 9 dollar funny money bill each. The next one would be MJ who had 2.5 hours which is 12 dollars. So she would get one funny money bill worth 9 dollars and 3 dollars real cash to total 12 dollars. And I keep going up the ladder distributing funny money 9 dollar bills first before any real cash.
 
Remember there are a total of 26 funny money bills at 9 dollars each. If Pat, Abel, and MJ all received one each, that leaves me with 23 to distribute properly. To continue up the ladder, I give the 4 guys with 9 hours one funny money bill each. That leaves me with 19. I give Bill who has 12 hours one funny money bill. That leaves me with 18. I give Lisa and Kirk who have 13 hours one funny money bill each. That leaves me with 16. I go back down and give the 9 hour people one more each. That leaves me with 12. I give Bill, Lisa, and Kirk one more each. That leaves me with 9. I give the 9 hour guys one more each, that leaves me with 5. I give Bill, Kirk, and Lisa one more each. That leaves me with 2. Since I have two left, I will randomly give one each to two of the 9 hour guys to complete the process. Then I will distribute the real cash after that.
 
Hi ,

Since you want to implement this application in Excel , do you already have the data entered into a workbook ? Can you upload this workbook , since that would save others some time ?

Narayan
 
I have attached the Workin progress WB,
i am not convinced with the approach of mine yet
but i can say 80% work is done.
User has to manually edit numbers in yellow column (O,Q,S,U) to match H4 and F1 numbers i.e funny money bill count 26.

Problem: not able to figure out how can i put formula to stop the it at 26. May be VB interface can do that. or find some math constant.
 

Attachments

  • Tip_WB.xlsx
    12.2 KB · Views: 38
Thank you for helping me. As of now, I do not have a workbook for this. I tried to start one but it turned out to be a mess and wouldnt be of any value. I have been doing this by hand and on a sheet of paper for a long time. I did download what you have done so far and it appears that you are definitely on the right track. I wish I could be more helpful. I know the basics of excel but I am far from being an expert.
 
Hi ,

Since you want to implement this application in Excel , do you already have the data entered into a workbook ? Can you upload this workbook , since that would save others some time ?

Narayan
I have been doing this by hand on a sheet of paper. I tried starting a workbook but it turned out to be a mess and would not be of any value whatsoever. Thank you in advance for any help you can provide.
 
Hi ,

Ashhu has done most of the work.

What I suggest is that you manually allocate the funny money bills , based on the order given in the attached workbook in column X , range X8:X18.

Based on what you enter in column Y , range Y8:Y17 , the remaining bills in column Z will change , thus giving you and idea of how much remains to be allocated. The last person in the list automatically gets the remaining number of bills ; thus , you need to manually enter the distribution in Y8:Y17 , and Y18 will get the remaining number.

Now , the values in H8:H18 will be correctly entered as a result of a formula.

See if this helps.

Narayan
 

Attachments

  • Tip_WB.xlsx
    13.2 KB · Views: 22
Thank you Narayan. That will definitely work, The only thing is that emplyee hours change every night so I would have to rerank the order somehow. I definitely see how to manually distribute the funny bills though. If this is as close as I will get to automation it will save me some time. Hopefully others will chime in with other suggestions based on what has already been established.
 
Hi ,

You do not have to rerank the order ; your data entry is first in the range F8:F18 ; once you enter data here , all the other calculations happen through formulae , and the order is reranked in the range X8:X18.

Now , your data entry consists of allocating the funny money bills in the range Y8:Y17 , based on the values you see in the ranges X8:X18 and Z8:Z18.

Once you complete this , the output is available in the ranges G8:H18.

The initial data entry has to be done in the cells F1 and F3.

Narayan
 
Hi ,

You do not have to rerank the order ; your data entry is first in the range F8:F18 ; once you enter data here , all the other calculations happen through formulae , and the order is reranked in the range X8:X18.

Now , your data entry consists of allocating the funny money bills in the range Y8:Y17 , based on the values you see in the ranges X8:X18 and Z8:Z18.

Once you complete this , the output is available in the ranges G8:H18.

The initial data entry has to be done in the cells F1 and F3.

Narayan
Oh ok....I see that now! Thank you so much!
 
Narayan, could you please revise that exact same thing so I could add employees and their hours in column E and F if I need to? E8:E27 and F8:F27 would be enough. I dont want to screw up the formulas and the distribution in columns wxyz. Thanks in advance!
 
Dear Rob,
GM
Just making it little clear, Attached sheet shows Yellow highlighted cells in F and Y column.

Remember:
"F" Column highlighted needs to be updated every time.
"Y" Column highlighted will only change when you want to distribute funny money differently.

Hope we are clear now. Please revert back if any help needed.
if your query is answered and you are happy, please hit like button, much appreciated.
 

Attachments

  • Tip_WB.xlsx
    13.2 KB · Views: 14
Dear Rob,
GM
Just making it little clear, Attached sheet shows Yellow highlighted cells in F and Y column.

Remember:


Hope we are clear now. Please revert back if any help needed.
if your query is answered and you are happy, please hit like button, much appreciated.

Thank you Ashhu. It looks good but I only have one other request. Sometimes, especially on the weekends that I will be splitting the tips between 19-20 people as opposed to just the 11 that are listed. Can you add more rows for the extra employees? I would very much appreciate it. Thanks so much for your help.
 
Narayan, could you please revise that exact same thing so I could add employees and their hours in column E and F if I need to? E8:E27 and F8:F27 would be enough. I dont want to screw up the formulas and the distribution in columns wxyz. Thanks in advance!
Hi ,

See the attached file.

The formulae have been copied down till row 50 , which means this setup can cater to 43 employees.

Two named ranges Employees and Hours have been created , so that you do not need to do anything other than enter input data in the ranges F1 , F3 , E8 downwards , F8 downwards , and Y8 downwards. The last allocation of the funny money bills will come through a formula in Y50.

Verify by entering data for a few more employees.

Narayan
 

Attachments

  • Tip_WB (1).xlsx
    16.8 KB · Views: 15
Hi ,

Sorry , but the formulae had not been extended to cover the entire data range ; see if it is correct now.

Remember to make the adjustments in column Y any time you either change the input data or enter additional data.

Narayan
 

Attachments

  • Tip_WB (1) (2) (1).xlsx
    16.8 KB · Views: 37
Hi ,

Sorry , but the formulae had not been extended to cover the entire data range ; see if it is correct now.

Remember to make the adjustments in column Y any time you either change the input data or enter additional data.

Narayan
Great one,
Thanks Narayan for putting better formulas , Good learning ! new learning for me.

Thanks Rob for believing and putting this task in :awesome: forum .
 
Narayan and Ashhu,

Thank you, Thank you, Thank you!!! You have no idea how much time this will save me! I very much appreciate your help on this. I am extremely happy with the end result!
 
And do you know how much time people have lost due to the fact that you crossposted without links on 3 other forums?
 
Back
Top