Formula to calculate profit sharing

evotsi
evotsi used Ask the Experts™
on
I need a formula to calculate a persons share of an arbitrary number, say 1000 based on two variables.  The first variable will be the number of hours a person worked.  The second variable will be a number between 1 and 10.  This will be a number used to determine a persons weight of the profit share.  For example, someone with a 4 would end up with the same share as someone else with a 4, providing they both had the same amount of hours.  A person with a 4 would have twice the size share as someone with a 2.

This formula would need to work for any number of people, from 1 to 100.  

This is important!  The total shares for everyone would need to add up to the total amount, 1000 in this case.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
a_b
Top Expert 2009

Commented:
Formula -
(Total Profit)(Hours worked * rating)/ (Total of each persons hours worked*rating)
N = number persons
Ri = random number of ith person (1..10)
Hi = hours worked of ith person
Si = shares of ith person

Wi = Ri * Hi
Si = 1000 * Wi / [ sum(Wi) for i = 1..N ]

- Must be real, not integer calculations

Author

Commented:
This does not seem to be working.  For example we have three people:
Person A worked 80 hours and has a rating of 5
Person B worked 80 hours and has a rating of 5
Person C worked 80 hours and has a rating of 2
The profit available to share is $1000.00

Can you please perform the calculation with the previous figures and show the results?  Thanks.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

A = 5/12 * 1000
B = 5/12 * 1000
C = 2/12 * 1000

Can you please show your steps and indicate what is not working?

Author

Commented:
I put  80 hours for everyone to make it simpler.  Can you please show what the calculation would be if A had 60 hours, B had 20 hours, and C had 80 hours?
Can you please show your steps, and if something is wrong, please indicate what is not working? We can then try to work with you on the problem you are having.
Is there anything about the http:#26443305 that you would like to discuss.
Please realize that the denominator: [ sum(Wi) for i = 1..N ] is the same for A, B, and C.

In http:#26457329, you say that the previous post is not working. Could you please explain what problem you had, and maybe I can figure out what is going wrong, either with the formula or a misunderstanding.

The solution:

share/person = (no. of hrs worked * rating) * 1000 / sum of (no. of hrs worked * rating) for all people

Person A worked 80 hours and has a rating of 5
Person B worked 40 hours and has a rating of 5
Person C worked 20 hours and has a rating of 2
The profit available to share is $1000.00

share of A = (80*5)*1000/((80*5)+(40*5)+(20*2)) = 625
share of B = (40*5)*1000/((80*5)+(40*5)+(20*2)) = 312.5
share of C = (20*2)*1000/((80*5)+(40*5)+(20*2)) = 40
@bathla_kamal:

I see that you are working a problem with some different hours than requested in http:#26457329 .
Possibly you have a typo somewhere since sum of the shares != $1000.

In any case, evotsi is saying that something is wrong and I asked evotsi to explain what is wrong by showing the calculation. No response so far.

Person A worked 60 hours and has a rating of 5
Person B worked 20 hours and has a rating of 5
Person C worked 80 hours and has a rating of 2

I get:
SA =  535.714
SB =  178.571
SC =  285.714
535.714 + 178.571 + 285.714 = $999.999 (close enough - truncation errors)

Other checks are to take the ratios of SA/SB, SB/SC, and SA/SC and verify that they match the hours and ratings.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial