I need a formula that calculates the compensation of a 4-employee team
based on individual skill level and the amount of work performed by
each employee as a percentage of the total job.  The following
information is known:

1)  The contract labor bid
2)  The total hours expended to complete the contract
3)  The total hours expended by each employee to complete the contract

Each of the four employees in the group has a different experience
modification.  The least skilled employee is 23%, then 33%, 43% and
finally 49% for the team leader.

Using Microsoft Excel, what formula should be used to calculate each
employees share of the labor bid based on time on the job and
experience?

This formula must prove out whether the team is reduced to one
employee or increased to eight employees.

Thanks in advance.  I'm up all night waiting to answer any questions
to help this go faster.

---

Request for Question Clarification by efn-ga on 09 Dec 2003 22:16 PST

Hi spencercat,

Let me see if I understand the problem correctly.

The end result is a cost per employee that satisfies the following conditions:

a.  The sum of the costs per employee for all employees is equal to
the total cost for the contract ("the contract labor bid").  The total
cost for the contract is an input to the calculation.

b.  The cost for an employee is the product of the number of hours the
employee spends on the contract and the employee's hourly rate.  The
number of hours is an input to the calculation and the hourly rate is
not.

c.  The hourly rates are proportional to specified weights.  For
example, using your numbers, the team leader's hourly rate is 49/23 of
the least skilled employee's hourly rate.

You are looking for an Excel spreadsheet design where the inputs are
the weight for each employee, the number of hours for each employee,
and the total cost, and the outputs are the costs per employee.  The
hourly rate may enter into this calculation, but it is not the output
you are looking for.

Is all this correct?  If not, please explain where I went astray.

--efn

---

Clarification of Question by spencercat-ga on 09 Dec 2003 23:45 PST

Hey...thanks for working on this.

I'll try to quickly explain my problem.

I have 20 technician's in the field.  They work individually and in
teams, and not always on the same team.  Timekeeping is tracked in a
relational database and we do track actual hours worked on each
contract by employee.

Our current methodology is to start with the total Contract Labor Bid.
 Every employee has what we call an "ExMO" or experience modification
that determines what percentage of the total Contract Labor Bid would
be allocated to pay a technician if that technician performed 100% of
the work.  (i.e., a $10,000 Contract Labor Bid with one technican at a
43% ExMO pays that techncian $4,300).  Team Leaders are at 49%.  In
some ways the ExMo does follow their hourly wage rates, but the ExMO
allows us to calculate earnings based on sales.

When we calculate the compensation for a team, we use the same
formula.  Except, we determine what percentage of time each technican
charged to the contract in relation to the other team members.  So to
use the example above, if that technician charged 25% of the total
contract time, our current formula will calculate $4,300 x 25% or
$1,075.

As I mentioned before, we have hourly wage rates for these
technician's, but the above formulas determine performance
compensation.  The better the performance, the higher the
compensation.  They can really improve their gross wages by looking
for other ways to serve the customer and completing the job quicker.

I guess my question is...is there a better way to weight the formula
or is there another approach to the entire formula that would be
better?

Thanks.

Spencer

---

Request for Question Clarification by efn-ga on 10 Dec 2003 01:14 PST

Hi Spencer,

Thanks for your response.  It looks like I was on the wrong track, but
now I am more confused.

In your clarification, you explained how you do these calculations now
and said you were looking for a better way.  My question now is
"better how?".  In other words, what's wrong with the current method,
and how would you evaluate whether an alternative was better or worse?
 I don't think there's a mathematical answer--it depends on what you
are trying to accomplish.

--efn

---

Clarification of Question by spencercat-ga on 10 Dec 2003 01:43 PST

Hi efn...

It may be that what I have is the best I can do for now.  My goal is
to have a formula that is fair and equitable beyond question, protect
my gross margin, and motivate my employees to make more money for both
the company and themselves.

I posed the question here because I am often pleasantly surprised by
the level of sophistication in the answers.

Thanks for your help.

Spencer

The problem can be solved this way.

No of days it take complete the Job by A = At
No of days A worked on the project = Ac
Total Money for the project = Mt

So total money to be paid to A = Ac * Mt/At.

If you have multiple people then also the rule will be same.
For person B the money to be paid will be = Bc*Mt/Bt.


Here is the important assumption that the total number of effort does
not go greater than 1 as the formula below..

Ac/At + Bc/Bt + Cc/Ct +Dc/Dt+.....  = Factor of Total work done
(should not reach more than for condition to hold good).


Now the challenge as you have stated the problem is how to transalte
"experstise" or skill set to fit the above formula?

The easiest was is to ask the person how many days it would take him
to do the job alone. Higher the number of days lower the skill. This
gives the figure like At, Bt ...

The assumption here is that all these skills are interoperable and lie
in the same segment. In real world Software engineer will take
infinite amount of time to lay oil pipelines.
Do let me know if this problem is addressed or needs a different route.

Ciao