Formula to Determine Sales Percentages - But Not That Simple

[cyd000]

We normally report sales as Total Goal & Total Digital Goal against Total Actual Sales & Total Actual Digital sales, weight both at 50% add them together then take that percentage and give a base percentage with an accelerator for any amount over. For example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Goal
[/TD]
[TD]Actual
[/TD]
[TD]% to Goal
[/TD]
[TD]Measured Weight
[/TD]
[TD]Weighted %
[/TD]
[TD]Weighted Performance %
[/TD]
[TD]Payout%
[/TD]
[TD]Total Payout
[/TD]
[/TR]
[TR]
[TD]Total Net Revenue[/TD]
[TD]$32,787
[/TD]
[TD]$51,730
[/TD]
[TD]157.8%
[/TD]
[TD]50%
[/TD]
[TD]78.9%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Digital Net Revenue
[/TD]
[TD]$13,000
[/TD]
[TD]$33,872
[/TD]
[TD]260.6%
[/TD]
[TD]50%
[/TD]
[TD]130.3%
[/TD]
[TD]209.2%
[/TD]
[TD]318.3%
[/TD]
[TD]$10,387.10
[/TD]
[/TR]
</tbody>[/TABLE]

So for both line items you take the Goal/Actual = % to goal. Reduce that by 50% to get the weighted %.

Then add both of them together to get the weighted performance percent.

This number is then compared to a chart and a final payout % is calculated. This is the chart:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]% to Plan
[/TD]
[TD]Incentive Payout %
[/TD]
[TD]Accelerator
[/TD]
[/TR]
[TR]
[TD]<70%
[/TD]
[TD]0%
[/TD]
[TD]0%
[/TD]
[/TR]
[TR]
[TD]70%
[/TD]
[TD]40%
[/TD]
[TD]1%
[/TD]
[/TR]
[TR]
[TD]80%
[/TD]
[TD]50%
[/TD]
[TD]1.5%
[/TD]
[/TR]
[TR]
[TD]90%
[/TD]
[TD]65%
[/TD]
[TD]3.5%
[/TD]
[/TR]
[TR]
[TD]100%
[/TD]
[TD]100%
[/TD]
[TD]0%
[/TD]
[/TR]
[TR]
[TD]110%
[/TD]
[TD]120%
[/TD]
[TD]2%
[/TD]
[/TR]
[TR]
[TD]120%
[/TD]
[TD]140%
[/TD]
[TD]2%
[/TD]
[/TR]
[TR]
[TD]>120%
[/TD]
[TD]140%
[/TD]
[TD]2%
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, if the weighted performance % is 209.2% (as in the example) they would get a base of 100%, which leaves 109.2% to be calculated on the above chart. So for every percentage point above 100% they get an additional 2% so the total percentage to be paid out is 318.3%. In this example the commission paid out to the sales rep was $10,387.10, total.

The problem is that now corporate is wanting us to go back and break out what percentage of that payout amount is attributed to digital. I can't figure how in the world I will figure that out as we base the accelerator on the combined percentage. If you try to break them apart it doesn't work out.

Is there a way to figure the percentage of a percentage?

 

[HolyExcel]

Correct me if I am wrong, but digital is a part from total? If so, then total minus digital is one part and digital×2 is the other of 100%. As weights are equal share for digital would then be: (13000×2)/(13000×2+19787)=26000/45787=0,5678467687=56,78%

 

[jorismoerings]

Hi,

Take a look at this:
If the weighted parts together are creating the payout. So the 209,2% creates the pay out of $10,387.10. Both parts together are 209,2%. So each part will be 78.9% of 209.2% and the other part will be 130.3% of 209.2%.
That's what's been calculated in cells J2 and J3.

Book1
	A	B	C	D	E	F	G	H	I	J
1		Goal	Actual	% to Goal	Measured Weight	Weighted %	Weighted Performance %	Payout%	Total Payout
2	Total Net Revenue	$ 32.787,00	$ 51.730,00	157,8%	50%	78,9%				$ 3.917,57
3	Digital Net Revenue	$ 13.000,00	$ 33.872,00	260,6%	50%	130,3%	209,2%	318,3%	$ 10.387,10	$ 6.469,53
4
5
6
7
8				% to Plan	Incentive Payout %	Accelerator
9					0%	0%
10				70%	40%	1%
11				80%	50%	1,50%
12				90%	65%	3,50%
13				100%	100%	0%
14				110%	120%	2%
15				120%	140%	2%
16				>120%	140%	2%
Sheet1

Cell Formulas
Range	Formula
D2	=C2/B2
D3	=C3/B3
F2	=E2*D2
F3	=E3*D3
G3	=SUM(F2:F3)
H3	=((G3-1)*INDEX(F9:F15,MATCH(G3,D10:D15))*100)+1
J2	=(F2/SUM($F$2:$F$3))*$I$3
J3	=(F3/SUM($F$2:$F$3))*$I$3

Hope this helps.

 

[HolyExcel]

I did understand your calculations. But i think this is a logical issue rahter excel-formula thing...

The question was :"problem is that now corporate is wanting us to go back and break out what percentage of that payout amount is attributed to digital"

So: you have an whatever amount that needs to be seperated by digital and rest. Both parts have the same weight, so you can forget about weighting in this case.

Now lets look at the part. Digital (13000) and Total (which also includes digital with 13000 again - so its digital plus others that sum up to 32787. So for your calculation you use the total +digital. That's 45787. Within this amount there is 2 times digital. So that's 13000×2=26000.

Now which part of your total base is digital? That would be 26000/45787.

Think of the same question what your answer would be, if there was no second line with digital in your calculation. Maybe that helps understanding the issue itaelf.

Hope it helps