New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

tip pool worksheet

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

tip pool worksheet

Postby robfl22 » Sun May 01, 2016 8:44 pm

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

robfl22
Rookie
 
Posts: 1
Joined: May 1, 2016
Reputation: 0
Excel Version: 2013

Re: tip pool worksheet

Postby NoSparks » Mon May 02, 2016 12:24 am

Attach a sample mock-up worksheet showing what the Funny Money and Cash tip out results should be for the employees using the hours and tips as you have indicated.

Are you open to a macro solution?

Do you have this question cross posted on different forums?
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: tip pool worksheet

Postby Sisyphus » Mon May 02, 2016 5:43 am

And here I thought that all nightclub managers were divorced. :-)
I did you injustice and hope the attached workbook will help make up.
You may ..
  • Add or delete rows below row 7
  • Change the text in all underlaid cells
  • You can change the value in D3 if they ever change the value of a fun bill.
  • Increase or reduce column widths and row height
  • Format numbers
  • Change the font
You may not ...
  • Delete the first or last row in the list (currently rows 7 and 17.
    Delete rows between them instead, and modify the cells in these two rows.
  • Insert or delete columns A:E
    You can do whatever you like to the right of column E

Attached is a macro enabled workbook of XLSM type. You may have to allow macros to run on your computer. Look at your security settings. By default, many Excel installations do not allow macros to run. You will receive ample warnings when you first open the workbook.

When you click the button the program will sort the list by number of hours worked. The person with the least hours to his credit will rank first. Next, the funny money is distributed. If the person next in the list has 9 dollars to his credit he will be given one FunMon and $9 deducted from his credit. Then it will be the next person's turn, so that no one gets two FunMons in the same round. I didn't check this part very carefully. When programming this sort of thing it would be a common error to either give two in a row or to jump over the next in line. Perhaps that is why the two 9-hour guys have most of the FunMon. You can check that easily enough by repeating the process manually. I can fix it if you tell me exactly what you observed.

Lisa gets 20 cents less than Kirk. This isn't because I didn't like her but because I took the rounding difference from the last person in the list, presumably the richest for that night. In the current example, there was a rounding difference of 20 cents which I took from Lisa's cash.

I hope this works for you.
  • 0

You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: tip pool worksheet

Postby NoSparks » Tue May 03, 2016 9:06 am

Sorry Sisyphus

Had I posted these, instead of giving the OP the opportunity, you wouldn't have wasted your time.

http://www.excelforum.com/excel-general ... sheet.html
http://chandoo.org/forum/threads/tip-po ... eet.29230/
http://www.mrexcel.com/forum/excel-ques ... ost4500105
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: tip pool worksheet

Postby pecoflyer » Tue May 03, 2016 11:38 am

Heavy cross poster - Thread closed
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 62 guests

cron