Please help me with this spreadsheet. Nested IF functions confuse the hell out of me!

[gabemcg]

Editable Google Spreadsheet

I know this is probably child's play for some of you excel/programming gurus, but despite staying up past my bed time researching conditional formulas I still cant translate my work's commission compensation model into something I can add to my personal budget spreadsheet to help me predict exactly what my paycheck will be given the variables.

Thank you in advance to anyone who helps shed some light on my problem!

How my compensation works:

Base Pay (fixed) + Target Incentive Pay (variable) = Total Target Compensation

Target Incentive Pay is $9750/year or $187.5/wk at 100% of plan performance

Plan performance depends on four factors:

1. Margin Attainment (goal/actual)
2. Margin per call (goal/actual)
3. Financing Attainment (goal/actual)
4. Kicker (1x,2x, or 3x) on revenue > 100%

The Kicker is determined by calculating the strategic blend (a 50/50 weighted average of Margin Per Call and Financing Attainment)

1x Kicker: Strategic Blend<= 100%
2x Kicker: Strategic Blend between 100% and 105%
3x Kicker: Strategic Blend > 105%

So a formula to account for actual monthly strategic pay based on revenue attainment and strategic blend would have to incorporate the following conditional equations:

For Revenue Attainment < or = to 100% AND/OR Strategic Blend < or = to 100%
Incentive pay= (revenue attainment*(187.5*#of weeks in month)

For Revenue > 100%, AND strategic Blend > 100% but Less than 105%:
Incentive pay= (100%+(2*(Revenue attainment-100%))*(187.5*#of weeks in month)

For Revenue > 100%, AND strategic Blend > 105%:
Incentive pay= (100%+(3*(Revenue attainment-100%))*(187.5*#of weeks in month)

 

[SJP0tato]

Target Incentive Pay is $9750/year or $187.5/wk at 100&#37; of plan performance

How does it work if you have 90% of plan performance? $168.75/wk? How about 110% plan performance? $206.25/wk?

Plan performance depends on four factors:

1. Margin Attainment (goal/actual)
2. Margin per call (goal/actual)
3. Financing Attainment (goal/actual)
4. Kicker (1x,2x, or 3x) on revenue > 100%

Does that mean:
Plan Performance = (MA + MPC + FA) x K

For Revenue Attainment < or = to 100% AND/OR Strategic Blend < or = to 100%
Incentive pay= (revenue attainment*(187.5*#of weeks in month)

What is "Revenue Attainment"? I don't see that listed anywhere else in your post. Is Revenue Attainment equivalent to Plan Performance?

It's very difficult to follow the logic because of the all the industry terms you're using. There's definitely a lot of moving parts here... 🙂

 

[gabemcg]

How does it work if you have 90% of plan performance? $168.75/wk? How about 110% plan performance? $206.25/wk?

First off, thank you for taking the time to reply, I'm still trying to crack this nut...

You are correct on the first figure. For Plan performance values < 100%, the incentive pay is decreased accordingly.

When you are above 100% of plan performance you would also increase the pay, but not always by a 1:1 ratio because of the kicker see below for clarification...

Does that mean:
Plan Performance = (MA + MPC + FA) x K


What is "Revenue Attainment"? I don't see that listed anywhere else in your post. Is Revenue Attainment equivalent to Plan Performance?

It's very difficult to follow the logic because of the all the industry terms you're using. There's definitely a lot of moving parts here... 🙂

Sorry for the confusion, "Margin Attainment" should read "Revenue Attainment"

Let me try and strip out the industry terms as much as possible, hopefully this will clarify:

Target Pay = $187.5/wk

When MA<100%, Pay=MA*($187.5)
When MA>=100%, AND (.5MPC+.5FA)<=100%, Pay=MA*($187.5)
When MA>=100%, AND 105%>=(.5MPC+.5FA)>100%, Pay=(100%+(2*(MA-100%))*($187.5)
When MA>=100%, AND (.5MPC+.5FA)>105%, Pay=(100%+(3*(MA-100%))*($187.5)