Impact by element of total variance (percentage example)

[Rubberneck]

I'm struggling with a problem dealing with %'s and how to properly calculate the contribution that each element has to the total variance between 2 periods (Month 2 - Month 1), i.e. determine each element's impact against the total impact, in this case -0.39%. Obviously, I can not simply add the variance of each element as they will not sum to -0.39%. Below is what I'm trying to resolve:

1. What is correct contribution formula for Rate variance contribution to get to -0.39% between models 1-4? Are calcs under cells M11-M14 correct from an overall rate variance perspective?
2. Furthermore, I want to determine the impact of Market and Rate changes to total variance for each element. What are correct formulas (cells R4:S7)? Currently calculating -0.32% vs. -0.39%
   • I would like to be able to sum Market & Rate changes to equal each respective Rate Contribution per Model (example R4 + S4 = T4 for Model 1) as well as ultimately sum all models to overall rate variance of -0.39% (cells R4:S7).

Excel 2012

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2		Month 1				Month 2				Variance (Month 2 - Month 1)				Market Share			Rate Contribution
3		Market	Commission	Rate		Market	Commission	Rate		Market	Commission	Rate		Month1	Month2		Market	Rate	Total		Variance
4	Model_1	500,000	2,500	0.50%		1,000,000	(1,500)	(0.15%)		500,000	(4,000)	(0.65%)		29%	29%		–	(0.19%)	(0.19%)		–
5	Model_2	300,000	4,500	1.50%		700,000	5,500	0.79%		400,000	1,000	(0.71%)		17%	20%		0.03%	(0.14%)	(0.11%)		0.01%
6	Model_3	700,000	14,000	2.00%		1,050,000	21,000	2.00%		350,000	7,000	–		40%	30%		(0.10%)	–	(0.10%)		(0.10%)
7	Model_4	250,000	3,125	1.25%		750,000	9,675	1.29%		500,000	6,550	0.04%		14%	21%		0.07%	0.01%	0.08%		0.02%
8		1,750,000	24,125	1.38%		3,500,000	34,675	0.99%		1,750,000	10,550	(0.39%)		100%	100%		–	(0.32%)	(0.32%)		(0.07%)
9
10				Contr.				Contr.				Rate Contr.
11	Model_1			0.14%				(0.04%)				(0.19%)
12	Model_2			0.26%				0.16%				(0.10%)
13	Model_3			0.80%				0.60%				(0.20%)
14	Model_4			0.18%				0.28%				0.10%
15				1.38%				0.99%				(0.39%)

<tbody>
</tbody>

simple example (2)​

Worksheet Formulas Cell Formula O4 =C4/$C$8 P4 =G4/$G$8 O5 =C5/$C$8 P5 =G5/$G$8 O6 =C6/$C$8 P6 =G6/$G$8 O7 =C7/$C$8 P7 =G7/$G$8 R4 =(P4-O4)*SUMPRODUCT($I$4:$I$7,$P$4:$P$7) S4 =((I4*P4-E4*P4)/$P$8) T4 =SUM(R4:S4) R5 =(P5-O5)*SUMPRODUCT($I$4:$I$7,$P$4:$P$7) S5 =((I5*P5-E5*P5)/$P$8) T5 =SUM(R5:S5) R6 =(P6-O6)*SUMPRODUCT($I$4:$I$7,$P$4:$P$7) S6 =((I6*P6-E6*P6)/$P$8) T6 =SUM(R6:S6) R7 =(P7-O7)*SUMPRODUCT($I$4:$I$7,$P$4:$P$7) S7 =((I7*P7-E7*P7)/$P$8) T7 =SUM(R7:S7) R8 =SUM(R4:R7) S8 =SUM(S4:S7) T8 =SUM(T4:T7) V4 =M11-T4 V5 =M12-T5 V6 =M13-T6 V7 =M14-T7 V8 =M15-T8 E11 =D4/$C$8 E12 =D5/$C$8 E13 =D6/$C$8 E14 =D7/$C$8 E15 =SUM(E11:E14) I11 =H4/$G$8 I12 =H5/$G$8 I13 =H6/$G$8 I14 =H7/$G$8 I15 =SUM(I11:I14) M11 =I11-E11 M12 =I12-E12 M13 =I13-E13 M14 =I14-E14 M15 =SUM(M11:M14) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To s ={#N/A,#N/A,TRUE,"1? ????";#N/A,#N/A,TRUE,"1-2? ?????";#N/A,#N/A,TRUE,"2? ??";#N/A,#N/A,TRUE,"2??? ????";#N/A,#N/A,TRUE,"3(1)? ????";#N/A,#N/A,TRUE,"??????";#N/A,#N/A,TRUE,"3(1)?7 ????";#N/A,#N/A,TRUE,"5? ???";#N/A,#N/A,TRUE,"5?2 ???(?)";#N/A,#N/A,TRUE,"6? ????";#N/A,#N/A,TRUE,"6? ??(?)";#N/A,#N/A,TRUE,"6? ??(?)";#N/A,#N/A,TRUE,"????";#N/A,#N/A,TRUE,"6-6(3)? ??(??)";#N/A,#N/A,TRUE,"9? ???(?)";#N/A,#N/A,TRUE,"9? ???(?)";#N/A,#N/A,TRUE,"10(3)? ????";#N/A,#N/A,TRUE,"10(4)? ????"} <tbody> </tbody>

<tbody>
</tbody>

 

[Rubberneck]

Not that it matters, but Commission and Market data reflect dollars.