Calculating contribution of Percentages

[mattalin]

I am working with calculations that result in percentages (Rate = Commission / Sales) based on sales and commission for several products.

1. I would like to take the results one step further by determining the contribution that each product makes to the total rate, i.e. I need to be able to calculate a rate for each product that adds up to the total rate (9.4% for Budget, 8.5% for Actual).
2. Additionally, I'm looking to determine the contribution of the variance between the budgeted and actual rates against the total variance - assume I can simply subtract the budget from the actual results from #1.

I've been struggling to find the appropriate equation to accurately calculate the contribution percentages, yellow-shaded cells.

Below is an example of data:

	Budget		Actual		B/(W)		Contribution Rate Variance
	Sales	Commission	Rate		Sales	Commission	Rate		Rate		Budget*	Actual*	B/(W)
Product A	$1,000	$120	12.0%		$1,000	$100	10.0%		(2.0%)				0.0%
Product B	$750	$45	6.0%		$750	$50	6.7%		0.7%				0.0%
Product C	$1,200	$144	12.0%		$1,200	$125	10.4%		(1.6%)				0.0%
Product D	$800	$56	7.0%		$800	$50	6.3%		(0.8%)				0.0%
Product E	$900	$72	8.0%		$900	$70	7.8%		(0.2%)				0.0%
	$4,650	$437	9.4%		$4,650	$395	8.5%		(0.9%)		9.4%	8.5%	(0.9%)
Sum of Rate column:			45.0%				41.1%		(3.9%)		9.4%	8.5%	(0.9%)
Comments:
Rate = Sales/Commission ; B/(W) = Actual - Budget
* Need to calculate percentage contribution that add up to 9.4% and 8.5% for Budget and Actual, respectively

<tbody>
</tbody>

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Budget				Actual				B/(W)		Contribution Rate Variance
2		Sales	Commission	Rate		Sales	Commission	Rate		Rate		Budget*	Actual*	B/(W)
3	Product A	$1,000	$120	12.00%		$1,000	$100	10.00%		-2.00%		2.58%	2.15%	-0.43%
4	Product B	$750	$45	6.00%		$750	$50	6.70%		0.70%		0.97%	1.08%	0.11%
5	Product C	$1,200	$144	12.00%		$1,200	$125	10.40%		-1.60%		3.10%	2.68%	-0.41%
6	Product D	$800	$56	7.00%		$800	$50	6.30%		-0.80%		1.20%	1.08%	-0.12%
7	Product E	$900	$72	8.00%		$900	$70	7.80%		-0.20%		1.55%	1.51%	-0.04%
8		$4,650	$437	9.40%		$4,650	$395	8.50%		-0.90%		9.40%	8.50%	-0.90%
9
10	Sum of Rate column:			45.00%				41.10%		-3.90%		9.40%	8.50%	-0.90%
11
12	Comments:
13	Rate = Sales/Commission ; B/(W) = Actual - Budget
14	* Need to calculate percentage contribution that add up to 9.4% and 8.5% for Budget and Actual, respectively
15
Sheet3

Cell Formulas
Range	Formula
L3	=D3*B3/$B$8
M3	=H3*F3/$F$8
N3	=M3-L3

 

[mattalin]

Thanks, I might have been over complicating this. My next step is to dive into the elements that make up rate variance for each product such as volume (quantity/mix), price, etc. that affect sales$ and this gives me something to compare against at the total variance level.