Creating Commission Calculator Sheet

[Jryan582]

I am trying to create a sales commission calculator template that can be used for all of our employees. The commission structure is:

• 100% to Goal = $75 per admit
• 120% to Goal = $100 per admit
• 140% to Goal = $150 per admit
• 160% to Goal = $175 per admit
• 180% to Goal = $200 per admit

I need fillable cells to put "monthly goal" and "qualified admits" and then allow the formulas to take it from there. I am struggling because of the way the structure is based on % to goal rather than you hit x number and your payout equals y. If someone could help me with these formulas I would be forever grateful! I have browsed through the other threads and found questions that are close but not quite what I am looking for. The table I am currently working with looks like:

Thank you so much for your help!

 

[Michael M]

How about you tell us what information goes in which cell(b1, b2,etc), then we should be able to do the rest.
If you use XL2BB download (see my tag) and poste real data we could fill it in exactly as required.

 

[Jryan582]

How about you tell us what information goes in which cell(b1, b2,etc), then we should be able to do the rest.
If you use XL2BB download (see my tag) and poste real data we could fill it in exactly as required.

Thank you so much for your help Michael.

An example would be a rep has a goal of 20 (b1) admissions/month. b2 would be what they actually achieved for the month, lets say they meet their goal of 20 so they finish at 100% to their monthly goal which should equal a $1500 payout. Is there a way to build a formula that can also calculate the increases over 100% to goal? The payout increases more if you are 120+% to goal:

• 120% to Goal = $100 per admit
• 140% to Goal = $150 per admit
• 160% to Goal = $175 per admit
• 180% to Goal = $200 per admit

THank you again!

 

[Jryan582]

Thank you so much for your help Michael.

An example would be a rep has a goal of 20 (b1) admissions/month. b2 would be what they actually achieved for the month, lets say they meet their goal of 20 so they finish at 100% to their monthly goal which should equal a $1500 payout. Is there a way to build a formula that can also calculate the increases over 100% to goal? The payout increases more if you are 120+% to goal:

• 120% to Goal = $100 per admit
• 140% to Goal = $150 per admit
• 160% to Goal = $175 per admit
• 180% to Goal = $200 per admit

THank you again!

Cells b1 and b2 would be the only ones they (the rep) would typically enter in so that they could see their payout calculated.

 

[Dave Patton]

What would be the payout if they sold 32?

 

[Jryan582]

What would be the payout if they sold 32?

Hi Dave,

If a rep hit 32 on a goal of 20 or 160% that would yield them a payout of $5600.00.

 

[Dave Patton]

Try the formula with the lookup table or the self contained formula.

T202107a.xlsm
	A	B	C	D	E	F
1	Goal	20
2	Achieved	32			1.0	75
3	Amount	5,600.00	5,600.00		1.2	100
4					1.4	150
5					1.6	175
6					1.8	200
7
2e

Cell Formulas
Range	Formula
B3	B3	=B2*LOOKUP(B2/B1,E2:F6)
C3	C3	=B2*LOOKUP(B2/B1,{1,75;1.2,100;1.4,150;1.6,175;1.8,200})

 

[Jryan582]

Dave- wanted to thank you for your help with this. These formulas have been working perfectly for what I needed. THANK YOU!

 

[Dave Patton]

Thanks for the feedback.

 

[Jryan582]

Sir- I was rolling this out to one of my counterparts this morning and something seemed to be giving us some trouble. For some reason it seems to be calculating at 4% less than what it should be? So the actual admit number ended at 23 on a goal of 20 or 115% which should pay out at $75/admit. That should be a total of $1725 but for some reason this is calculating at 71% and only showing a payout of $1650?