Need help with a Multiply Dependent One Cell Formula

[derekblain]

Hello - I'm trying to create a formula that will enable me to do the
following:

All to occur in cell G2

Condition 1.
If F2 is > 0 then multiply F2 by D2.

Condition 2 in the same cell.
If the product of F2 multiplied by D2 is > E2 AND the sum of F2 and F1
multiplied by D2 is > 0, then add the product of D2 multiplied by the sum of
F1 and F2 to E2, otherwise input E2

Condition 3 in the same cell.
If the product of F2 multiplied D2 is < E2 BUT the sum of F2 and F1
multiplied by D2 is > 0, then input the product of D2 multiplied by the sum
of F1 and F2.

Condition 4 in the same cell.
If F2 < 0, and the sum of sum of F2 and F1 multiplied by D2 is < 0, then 0.

This is the best way I could think of the write out my intention. I think if
I can get the above scenario into a one cell formula that should resolve my
pain.

Thanks in advance.

Derek

 

[JLatham]

Maybe I'm not looking at this right, but I don't think it can be done as
stated, at least not with nested IF() statements.

My first question is: What do we do if F2 is exactly zero? Condition 1 says
what to do if F2 > 0 while condition 4 tells what to do if F2 < 0
(conditional based yet another formula).

We may get better results if you kind of laid it out as if it were nested if
statements and see if you run into any dead ends.

An IF() statement has 3 parts: a condition to test, what to do if the test
is true, what to do if the test is false. Either of the what-to-do's can be
another IF() statement with the same constraints. Up to 7 nested IF()
statements.

Just using the first two conditions, we run into this problem:

=IF(F2>0, F2*D2,IF(AND((F2*D2)>E2,((F1+F2)*D2)>0),(D2*(F1+F2))+E2,E2))
IF F2 > 0, (true) F2 * D2,
false (F2 is either 0 or < 0) but <0 is spoken for down in condition 4 but
it would also get handled here and this second condition is a dead end for us
because it does include both a true and false result option, so there's no
where to go to nest another IF() statement it looks like this when broken out:
IF F2*D2 < E2 AND ((F1+F2)*D2)>0, (true) (D2*(F1+F2))+E2, (false) E2

So there we are with no way left to get to condition 3, much less condition 4.
Basic problem seems to be that there are two things to do if F2 < 0 - those
are defined as Condition 2 and Condition 4. Physics says (or used to say)
that no two objects can occupy the same space at the same time. Computer
logic says that no single condition create two solutions - and with
conditions 2 and 4 you have 2 separate conditions to be fulfilled when F2 < 0.

But maybe a wiser head than me will come along and figure different.

Perhaps if you restructured your conditions. Try phrasing them this way:

If (condition 1) then do this, else if (condition 2), do this, else if
(condition 3), do this, else if (condition 4), do this, else if none of the 4
conditions are met, do this!

You may even have to break it down to more than just 4 conditions, or even
resequence them. We'll try to help if we can get a set of definitions that
we can work with. Although it may not be a single cell solvable thing.

 

[derekblain via OfficeKB.com]

JLatham - thanks for the assistance.

I hear your points - as a degreed english major (yawn) it has been a bit
tough for me to even get into words what I'm trying to accomplish
mathematically. Let's see if this helps:

I'm working on a model that will allow me to do the following:
1. Pay monthly bonuses for 2006 financial performance over 2005 monthly
actuals at a
fixed % rate.
2. There is a monthly cap which is reached when the 2006 monthly actual
reaches the 2006 monthly budget.
3. If the manager exceeds his 2006 budget in a given month, he cannot earn
more than the monthly cap unless in a prior month he wasn't paid his full
monthly potential AND the year to date performance exceeds the year to date
target.
4. All year-to-date performance is 'pooled' year-to-date so that I can
determine if the payee is due more than his 'cap' because he's now made up
the prior month overperformance.
4. A positive 'pool' can help the manager to regain any bonuses that were
missed in prior months. A negative pool will not have an impact on the
current month potential.

EXAMPLE
Month 1 Budget = $1M
Month 1 2005 = $750K
Month 1 2006 Actual = $650K
The manager would not qualify for a bonus becasue he didn't reach his 2005 actuals (the target).

Month 2 Budget = $1M
Month 2 2005 = $700K
Month 2 2006 = Actual $1.05M
The manager would earn a fixed percentage rate (I'm using 3.14%) times the amount over 2005 up to the budget. Therefore $300K (1M less 700K) x 3.14%. Now, since the manager was previously $100K under his 2005 target in month 1, the over budget performance of $50K from Month 2 would pool with the shortage of budget -$100K from month 1 reducing the ytd shortage to only -$50K. If the manager beats budget by another

$50K in month 3 he can recapture his lost incentive from month 1 in addition
to his month 3 capped amount.

Where I'm having an issue is that my current spreadsheet (which I can email
anyone if interested ;-) seems to be be paying out more than it should on
months where my manager has exceeded budget but is still under his target for
the year. This is because it is multiplying the full monthly overperformance
amount by my fixed percentage and not recognizing I have a monthly cap.

Hope that helps...

Thanks again,
Derek

 

[derekblain via OfficeKB.com]

Apologies - somehow my example didn't come out right (I've previewed it this
time)

Here goes again.

EXAMPLE
Month 1 Budget $1M
Month 1 2005 $750K
Month 1 2006 Actual $650K
The manager would not qualify for a bonus because he didn't reach his 2005
actual (the target).

Month 2 Budget $1M
Month 2 2005 $700K
Month 2 2006 Actual $1.05M
The manager would earn a fixed percentage rate (I'm using 3.14%) times the
amount over 2005 up to the budget. Therefore $300K (1M less 700K) x 3.14%.

Since the manager was previously $100K under his 2005 target in month 1, the
over budget performance of $50K from Month 2 would pool with the shortage of
budget -$100K from month one reducing the ytd shortage to only -$50K.

If the manager beats budget by another $50K in month 3 he can recapture his
lost incentive from month 1 in addition to his month 3 capped amount.

Where I'm having an issue is that my current spreadsheet (which I can email
anyone if interested ;-) seems to be be paying out more than it should on
months where my manager has exceeded budget but is still under his target for
the year. This is because it is multiplying the full monthly overperformance
amount by my fixed percentage and not recognizing I have a monthly cap.

Thanks.

 

[Dav]

Why not post the file here, it can appear as a zipped attachment.

If a person is under the previous years performance for the first
month, say $50000 on month 1, $100000 on momth 2 and they exceed th
budget by $125000, in month 3, what would you wish to happen?
Pay the bonus on $125000 as previously you were $150000 behind?

Or something else

Regards

Da

 

[Dav]

This may be a start, I have only put the bonus as an amount, no
multiplied it by a fixed percentage. A slight isssue is If I hav
exceeded performance and budget in month 1 but then failed in month 2
can I get bonus in Month 2 if my year to date performance is still a
improvement on the previous year, in this example I have assumed yo
do

Regards

Da

+-------------------------------------------------------------------
|Filename: Bonus.zip
|Download: http://www.excelforum.com/attachment.php?postid=5167
+-------------------------------------------------------------------

 

[derekblain]

Good Evening Da and thanks for the comments.

I have opened your file and made some modifications and notes using my
actual data.

In answer to your two previous posts: If a person is under the previous
year in Month 1 by $50K, under Month 2 by $100K, but over budget in
Month 3 by $125K, they would get their Month 3 capped bonus. They
could only earn more than their cap if their ytd performance was over
'05 by such a number that when multiplied by 3.14% (my rate) the
product was greater than their monthly cap. In that case they'd earn
the difference. There is a caveat, but you'll see that in the
spreadsheet - it has to do with the fact that there are monthly caps
which sum up to YTD caps.

Regarding your 2nd post: If you have exceeded Month 1, but failed
Month 2, you could still earn a bonus *if* your ytd performance
multiplied by 3.14% was greater than what you had earned in Month 1.
You would still cap however at the combined total of the Month 1 and
Month 2 caps.

At the end of the day, I want to pay the employee for hitting monthly
targets. Their ytd performance has no punitive impact on their monthly
performance. However, I give them the option to make up for prior month
'misses' if the ytd perf is such that they are still hitting ytd
targets. They can recapture some of what they would have earned in the
months when they rec'd nothing or accrue it for future months when they
might miss a monthly target.

I appreciate your help.

Here goes the post...

By the way, while I've added the real data and monthy bonus info, I
didn't overwrite your bonus data because I assume you may drop my data
into your existing formulas.

Thanks again,
Derek


+-------------------------------------------------------------------+
|Filename: Bonus_v2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5172 |
+-------------------------------------------------------------------+

 

[Dav]

I think this is what you want

Regards

Da

+-------------------------------------------------------------------
|Filename: Bonus_v2.zip
|Download: http://www.excelforum.com/attachment.php?postid=5173
+-------------------------------------------------------------------

 

[derekblain]

Dav - this looks fantastic! Thank you very much for your help.

I'm going to take the model to work with me and try it on several of
the individual bonus plans I'm currently working on. Each of the plans
has different 2005 and budget numbers, however they all operate under
the same logic.

I'll reply back later and let you know how it goes.

One more question for you...if you don't mind. What if the monthly
bonus amount was fixed rather than using a fixed percentage? In the
case of our previous spreadsheet, let's say each month was worth 1/12
of $45,106. All of the other logistics apply. Can the formula be
modified in such a way that this can be accommodated?

Thank you,
Derek

 

[Dav]

It can be done, but how would the fixed bonus be allocated?

Would the bonus only get paid if the 2006 amount exceeded the budget
for that month or would you get a part bonus eg if 2006 was half way
between the 2005 figure and the budget would you get a 50% bonus.

It becomes a little more complicated reclaiming bonus from the previous
months as the relationship between bonus and sales is not linear. You
would have to provide a logic to deal with this

eg if the maximum bonus was 5 every month and you paid part bonuses


Budget 2005 2006 bonus paid reclaimed bonus
10 8 7 0 0
12 8 9 1.25 0
12 9 15 5 ?

in the first line the rate is 2.5 to every 1, in the second line 1.25
to 1 and in the 3rd line 1.66 to 1 how would you calculate the
reclaimed bonus? You could say the same as now eg

Total Budget =34 Total 2005=25 Total 2006=31 Total Bonus paid
=6.25

(31-25)/(34-25)*15 -6.25

but is this what you want? If so the logic is very similar to what has
already taken place, instead of multiplying by 3.14% you have to diivde
by budget-2005 and multiply by 45106/12

Regards

Dav

 

[Dav]

I decided it was easier to show you!

Regards

Dav


+-------------------------------------------------------------------+
|Filename: Bonus_v3.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5178 |
+-------------------------------------------------------------------+

 

[derekblain]

Dav -

Thanks Again.

I see your points about the need for a new logic if we go to fixe
monthly amounts rather than a fixed monthly rate. I've heard rumbling
from some of the big bosses at my company that they wanted to offer
static monthly goal so that there isn't the dollar fluctuation. But
don't think they've thought it through that the fluctuation is
natural derivation from the differences in the '05 actuals vs '0
Budgets. Such a plan would value performance in an inconsistent wa
unless the budget vs prior year actuals were exactly the same eac
month.

Personally, I prefer the first version you built. I tried it out o
several current plans and it worked perfectly. I've found now that
few people were overpaid using the pre-Dav formulas.

If you're ever out in Los Angeles, I owe you a beer!

Cheers,
Dere