Service Charge Formula

A

aaronb110

New Member
Joined
Sep 10, 2015
Messages
3
Hi All

I manage a restaurant and am trying to put together a spreadsheet which will allow me to accurately calculate the how much of the service charge is paid to each employee.

The easy part is that we use a points system where by each job role is assigned a certain number of points ie. Servers 2 points, chefs 4 etc and we work out the value of one point and then multiply that to get each persons payment. However this is causing problems as every member of staff does different hours, so currently someone who works 10 hours a week, gets the same share as someone who does 50 hours a week.

My question is how can I work out the payment based on the points AND the total hours worked? I did try to get a value per hour and multiply that by hours worked, and then multiply by the points value however the final figure was obviously much higher than the total pot of service charge which it can not be. The total payment must equal the service charge pot.

I hope that makes sense!

Look forward to your help guys.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Welcome to the forum.

One way

=$D$1/SUM($B$3:$C$16)*(B16+C16)

Code: 
[TABLE="width: 260"]
<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="class: xl64, width: 65"][/TD]
  [TD="class: xl64, width: 65"][/TD]
  [TD="class: xl63, width: 65"]Service Charge[/TD]
  [TD="width: 65, align: right"]75[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD]Points[/TD]
  [TD]Hours[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Chef[/TD]
  [TD="align: right"]4[/TD]
  [TD="align: right"]30[/TD]
  [TD="class: xl65, align: right"] 5.53 [/TD]
 [/TR]
 [TR]
  [TD]Chef[/TD]
  [TD="align: right"]4[/TD]
  [TD="align: right"]20[/TD]
  [TD="class: xl65, align: right"] 3.90 [/TD]
 [/TR]
 [TR]
  [TD]Chef[/TD]
  [TD="align: right"]4[/TD]
  [TD="align: right"]20[/TD]
  [TD="class: xl65, align: right"] 3.90 [/TD]
 [/TR]
 [TR]
  [TD]Chef[/TD]
  [TD="align: right"]4[/TD]
  [TD="align: right"]10[/TD]
  [TD="class: xl65, align: right"] 2.28 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]50[/TD]
  [TD="class: xl65, align: right"] 8.46 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]30[/TD]
  [TD="class: xl65, align: right"] 5.21 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]25[/TD]
  [TD="class: xl65, align: right"] 4.39 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]15[/TD]
  [TD="class: xl65, align: right"] 2.77 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]35[/TD]
  [TD="class: xl65, align: right"] 6.02 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]50[/TD]
  [TD="class: xl65, align: right"] 8.46 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]40[/TD]
  [TD="class: xl65, align: right"] 6.83 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]10[/TD]
  [TD="class: xl65, align: right"] 1.95 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]40[/TD]
  [TD="class: xl65, align: right"] 6.83 [/TD]
 [/TR]
 [TR]
  [TD]Server[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]50[/TD]
  [TD="class: xl65, align: right"] 8.46 [/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]
 
Upvote 0
Thanks for the formula - can you please let me know what the cells relate too as am looking on my phone and as such cant see cell references.
?
 
Upvote 0
Should have posted
=$D$1/SUM($B$3:$C$16)*(B3+C3)

D1=Service Charge (75.00)
sum(B3:C16)=Points & Hours
B3+C3=Points & Hours for each individual
 
Upvote 0
Hi Aaron and welcome to the forum.

My solution would be to multiply the hours by the points to give effective hours, then multiply the individuals effective hours by the service charge pot and divide that by the total of the effective hours.

As an example:

If in cells B2:B8 you have the hours worked, in C2:C8 you have the points.
In cell C1 is the total pot of service charge. Then in cell E2 enter: {=$C$1*(B2*C2)/SUM($B$2:$B$8*$C$2:$C$8)} note that this formula must be entered with CTRL-SHFT-ENTER as it is an array formula. Then copy and paste E2 to E3:E8.

Hope this helps.

Regards
 
Upvote 0
Ignoring the fact that chefs seem to get more of the service charge than the servers ggrrrr

I'd sum the total of each staff's points times their hours and divide the total service charge by that ... then individually work out each staff members share by multiplying that result by the product of their hours and points. You will probably get rounding differences though
 
Upvote 0
Haha I know what you mean about Chefs getting more. The servers keep 100% of their cash tips though and get paid a very good basic hourly rate for servers (£8ph) so we are trying to make it a bit fairer. Servers will still ultimately get more at the end of the day :)

Thanks for your replays guys!
 
Upvote 0
I can't get the other 2 solutions to work!!

Code: 
[TABLE="width: 390"]
<tbody>[TR]
[TD="class: xl64, width: 65"][/TD]
[TD="class: xl64, width: 65"][/TD]
[TD="class: xl63, width: 65"]Service Charge[/TD]
[TD="width: 65, align: right"]75[/TD]
[TD="width: 65"][/TD]
[TD="width: 65, align: right"]0.004901961[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Points[/TD]
[TD]Hours[/TD]
[TD][/TD]
[TD]PJMorris[/TD]
[TD]WaterGypsy[/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD="class: xl65, align: right"] 5.53[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.588235294[/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="class: xl65, align: right"] 3.90[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.392156863[/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="class: xl65, align: right"] 3.90[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.392156863[/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl65, align: right"] 2.28[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.196078431[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="class: xl65, align: right"] 8.46[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.490196078[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[TD="class: xl65, align: right"] 5.21[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.294117647[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]
[TD="class: xl65, align: right"] 4.39[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.245098039[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]15[/TD]
[TD="class: xl65, align: right"] 2.77[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.147058824[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]35[/TD]
[TD="class: xl65, align: right"] 6.02[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.343137255[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="class: xl65, align: right"] 8.46[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.490196078[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]40[/TD]
[TD="class: xl65, align: right"] 6.83[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.392156863[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl65, align: right"] 1.95[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.098039216[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]40[/TD]
[TD="class: xl65, align: right"] 6.83[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.392156863[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="class: xl65, align: right"] 8.46[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.490196078[/TD]
[/TR]
</tbody>[/TABLE]

Had the wrong range for Peter's!!

Code: 
[TABLE="width: 390"]
<tbody>[TR]
[TD="class: xl64, width: 65"][/TD]
[TD="class: xl64, width: 65"][/TD]
[TD="class: xl63, width: 65"]Service Charge[/TD]
[TD="width: 65, align: right"]75[/TD]
[TD="width: 65"][/TD]
[TD="class: xl65, width: 65, align: right"] 0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Points[/TD]
[TD]Hours[/TD]
[TD][/TD]
[TD]PJMorris[/TD]
[TD="class: xl65, align: right"] WaterGypsy[/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD="class: xl65, align: right"] 5.53[/TD]
[TD="class: xl65, align: right"] 8.91[/TD]
[TD="class: xl65, align: right"] 0.59[/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="class: xl65, align: right"] 3.90[/TD]
[TD="class: xl65, align: right"] 5.94[/TD]
[TD="class: xl65, align: right"] 0.39[/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="class: xl65, align: right"] 3.90[/TD]
[TD="class: xl65, align: right"] 5.94[/TD]
[TD="class: xl65, align: right"] 0.39[/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl65, align: right"] 2.28[/TD]
[TD="class: xl65, align: right"] 2.97[/TD]
[TD="class: xl65, align: right"] 0.20[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="class: xl65, align: right"] 8.46[/TD]
[TD="class: xl65, align: right"] 7.43[/TD]
[TD="class: xl65, align: right"] 0.49[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[TD="class: xl65, align: right"] 5.21[/TD]
[TD="class: xl65, align: right"] 4.46[/TD]
[TD="class: xl65, align: right"] 0.29[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]
[TD="class: xl65, align: right"] 4.39[/TD]
[TD="class: xl65, align: right"] 3.71[/TD]
[TD="class: xl65, align: right"] 0.25[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]15[/TD]
[TD="class: xl65, align: right"] 2.77[/TD]
[TD="class: xl65, align: right"] 2.23[/TD]
[TD="class: xl65, align: right"] 0.15[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]35[/TD]
[TD="class: xl65, align: right"] 6.02[/TD]
[TD="class: xl65, align: right"] 5.20[/TD]
[TD="class: xl65, align: right"] 0.34[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="class: xl65, align: right"] 8.46[/TD]
[TD="class: xl65, align: right"] 7.43[/TD]
[TD="class: xl65, align: right"] 0.49[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]40[/TD]
[TD="class: xl65, align: right"] 6.83[/TD]
[TD="class: xl65, align: right"] 5.94[/TD]
[TD="class: xl65, align: right"] 0.39[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl65, align: right"] 1.95[/TD]
[TD="class: xl65, align: right"] 1.49[/TD]
[TD="class: xl65, align: right"] 0.10[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]40[/TD]
[TD="class: xl65, align: right"] 6.83[/TD]
[TD="class: xl65, align: right"] 5.94[/TD]
[TD="class: xl65, align: right"] 0.39[/TD]
[/TR]
[TR]
[TD]Server[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="class: xl65, align: right"] 8.46[/TD]
[TD="class: xl65, align: right"] 7.43[/TD]
[TD="class: xl65, align: right"] 0.49[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Hi Gaz,

It works on my machine, but you have to enter it as an array formula (if you want to enter it as a single formula).

To my mind, my solution has the advantage of giving as much weight to the points as it does the hours, whereas simply adding the hours and points significantly reduces the impact of the points on the reward given to each staff member.

Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Changing a value when it meets a criteria from a lookup formula
Replies
0
Views
182
Sessa0302
S
S
  • Question
Automatic accrual calculations by day with accrual rate?
Replies
0
Views
353
Sunshine8790
S
B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
151
bh24524
B
P
  • Solved
Trying to calculate payment amount for month when split over 4 payments different fields
Replies
4
Views
295
StephenCrump
S
H
  • Question
'How to' question about a Dashboard
Replies
5
Views
337
etaf
etaf

Forum statistics

Threads
1,216,441
Messages
6,130,643
Members
449,585
Latest member
Nattarinee

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top