Need help with an employee bonus program (worksheet function)

[msaffer]

Hello,

I would appreciate some help with a bonus structure sheet that I am
having trouble with.

The plan is basically this: Pay an employee based on how well they
meet their expense goals. On a payable base of $2500 in a given month,
I want to pay an employee 30% of that ($750) if expenses no greater
than 5% over budget, 100% of that ($2500) if they are on budget, and
150% of that ($3750) if they are 5% or more under budget.

My sheet looks like this.
Headers are on row 1. Numbers are on row 2.


A B C D E F G
5%over Even 5%under Goal Expenses % Bonus
68,250 65,000 61,750 65,000 67,421 103.7% (formula?)


Based on the expense goal in cell D2, I need the formula to be located
in cell E2 and reference the other numbers in row 2. In this example,
the employee would earn $750 by being 5% or less over expenses.

Is there is anyone who can assist me with this? That would be great.

Regards,
Michael
Excel 97

 

[JulieD]

Hi

two options (and there are more)

=IF(E2=B2,2500,IF(AND(E2>B2,E2<=A2),750,IF(E2<=C2,3500,0)))
=IF(F2=100%,2500,IF(AND(F3>100%,F2<=105%),750,IF(F2<=95%,3500,0)))

what concerns me however, is the employee whoes expenses come in at 62000 -
according to your structure they seem to get no bonus.

BTW - can i have a job

Cheers
JulieD

 

[Sandy Mann]

Michael,

Assuming that, above $68,250 the bonus is zero, (surely you don't pay a
bonus no matter how high the expenses?), from $65,000 to $68,250 the
percentage of bonus decreases proportionally, and between $65,000 and
$61,750 the bonus increases proportionally, (at a different rate to the
above rate), then, with the Actual Expenses in A13, the Target Expenses
($65,000) in B13, Base Bonus ($2,500) in C13

=IF(A13>B13*1.05,0,IF(A13>=B13,C13*0.3+((B13*1.05-A13)*(C13-C13*0.3)/(B13*1.
05-B13)),C13+((B13-(MAX(B13*0.95,A13)))*((0.5*C13)/(B13-B13*0.95)))))

Gives the answers you want but I can't help but think that there must be a
more elegant solution.

HTH

Sandy

PS send me a job application form as well <g>