Excel help - Calculate one way if this, another if that (Sales Comp) (1 Viewer)

[Abraham]

I am building a comp plan for sales and a function that I previously knew how to write is eluding me. Needing the following:

Cell 1: Quota

Cell 2: Attainment for the month

Cell 3: Commission per revenue dollar

Cell 5: Commission Earned

At this point it's simple: multiply 2*3 and the amount of commission earned is the result.

HERE'S WHERE THINGS GET WILD

Cell 4: Accelerator

The accelerator kicks in to anything they sell above quota. So the formula I need is...

"If Cell 2 is less than or equal to cell 1, multiply by cell 3 and we are done. But also, any amount in Cell 2 that exceeds the value of cell 1, multiply by cell 4 but only the amount exceeding Cell 1. Then add those two multiples together for the total."

So if OPM gets paid 1 dollar for each of the first 10 pencils he sells at the school pencil sale and 2 dollars for each pencil above 10, and he sells 13 pencils, then the formula pays him $16 to go to the Bakersfield mall and buy a shirt at Hurley.

(THere will be other fun puzzles and challenges later).

 

[Ned]

@ Hurley shirt.

=IF(A2<B2,(B2-A2)*D2+(A2*C2),B2*C2)

 

[MindCrime]

Add some extra columns? So if cell 2 minus cell 1 > 0, then cell 1. (This allows you to see if they sold more than quota. If so, the returned number equals the quota). Multiply this result by cell 3 ( the standard rate)

Then another column that isIf sales minus quota > 0, then sales minus quota, if not then "0". Take this result which is the number over the quota and multiply by accelerated money. Add both rate earnings together to get total earnings.

Eta-or what was said above.

 

[gmbacm]

I am building a comp plan for sales and a function that I previously knew how to write is eluding me. Needing the following:

Cell 1: Quota

Cell 2: Attainment for the month

Cell 3: Commission per revenue dollar

Cell 5: Commission Earned

At this point it's simple: multiply 2*3 and the amount of commission earned is the result.

HERE'S WHERE THINGS GET WILD

Cell 4: Accelerator

The accelerator kicks in to anything they sell above quota. So the formula I need is...

"If Cell 2 is less than or equal to cell 1, multiply by cell 3 and we are done. But also, any amount in Cell 2 that exceeds the value of cell 1, multiply by cell 4 but only the amount exceeding Cell 1. Then add those two multiples together for the total."

So if OPM gets paid 1 dollar for each of the first 10 pencils he sells at the school pencil sale and 2 dollars for each pencil above 10, and he sells 13 pencils, then the formula pays him $16 to go to the Bakersfield mall and buy a shirt at Hurley.

(THere will be other fun puzzles and challenges later).

I'm sure there is a better way but I would just use if function in a hidden column, then do your final calc based on the value in that column. https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

 

[Abraham]

@ Hurley shirt.

=IF(A2<B2,(B2-A2)*D2+(A2*C2),B2*C2)

WELL DONE.

I was going to ask how to add bonuses at specific tiers but I think I figured it out.

 

[Abraham]

Ok, back again.

Trying to create a formula for tiered commission payouts and I'm completely shuked. Here is the goal.

Rep as quota of X.

If they sell up to X in revenue, they get X * .7

For any revenue sold between X and 1.2X they get the amount between what they sold and the original quota * .8 PLUS X*.7.

For any revenue sold between 1.2X and 1.4X they get the amount between what they sold and 1.2X * .9 PLUS the earlier earnings

For any revenue sold over 1.4X they get the amount exceeding 1.4X * 1.0 plus the accumulated earlier earnings.

So on a quota of 10k, a guy that sells 10k gets $7,000 dollars

Sell 11k and he gets the $7k plus another $800 (the 1000 overage times .8)

Sell 13k and he gets the $7k plus another $1600 plus $900 (.9 times the 1k over 12k)

Make sense? How the #### do I put this in excel?

 

[dparker713]

Ok, back again.

Trying to create a formula for tiered commission payouts and I'm completely shuked. Here is the goal.

Rep as quota of X.

If they sell up to X in revenue, they get X * .7

For any revenue sold between X and 1.2X they get the amount between what they sold and the original quota * .8 PLUS X*.7.

For any revenue sold between 1.2X and 1.4X they get the amount between what they sold and 1.2X * .9 PLUS the earlier earnings

For any revenue sold over 1.4X they get the amount exceeding 1.4X * 1.0 plus the accumulated earlier earnings.

So on a quota of 10k, a guy that sells 10k gets $7,000 dollars

Sell 11k and he gets the $7k plus another $800 (the 1000 overage times .8)

Sell 13k and he gets the $7k plus another $1600 plus $900 (.9 times the 1k over 12k)

Make sense? How the #### do I put this in excel?

X being total sales; A1 being quota; P being commission rates

If (x&gt1.4*A1), (P1*A1 + P2*0.2*A1 + P3*0.2*A1 + P4*(x-1.4*A1), if (x>1.2*A1, (P1*A1 + P2*0.2*A1 + P3*(x-1.2*A1), if(x>A1, (P1*A1 + P2*(x-A1), x*P1))))

 

[kardplayer]

Here's my effort:

https://docs.google.com/spreadsheets/d/14r0RDRVybrs-lVraRPd0wMoGlFCO1JuFnvetBmrW0l8/edit?usp=sharing

If you want to combine into one cell, you would just take the components and pull them into one cell.

 

[Ned]

Here's my effort:

https://docs.google.com/spreadsheets/d/14r0RDRVybrs-lVraRPd0wMoGlFCO1JuFnvetBmrW0l8/edit?usp=sharing

If you want to combine into one cell, you would just take the components and pull them into one cell.

Good work!

This is where I was thinking, except I was thinking he would need it in a linear format (assuming multiple reps). I took the liberty to transpose it below your sheet (see link).