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
Month1	Month2	Variance
Model_1
Model_2
Model_3
Model_4
Model_1
Model_2
Model_3
Model_4

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Month 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Month 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Variance (Month 2 - Month 1)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Market Share[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFE699, align: center"]Rate Contribution[/TD]
[TD="bgcolor: #FFE699, align: center"][/TD]
[TD="bgcolor: #FFE699, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Market[/TD]
[TD="align: center"]Commission[/TD]
[TD="align: center"]Rate[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Market[/TD]
[TD="align: center"]Commission[/TD]
[TD="align: center"]Rate[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Market[/TD]
[TD="align: center"]Commission[/TD]
[TD="bgcolor: #C6E0B4, align: center"]Rate[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFE699, align: center"]Market[/TD]
[TD="bgcolor: #FFE699, align: center"]Rate[/TD]
[TD="bgcolor: #FFE699"]Total[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] 500,000[/TD]
[TD="align: right"] 2,500[/TD]
[TD="align: right"] 0.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 1,000,000[/TD]
[TD="align: right"] (1,500)[/TD]
[TD="align: right"] (0.15%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 500,000[/TD]
[TD="align: right"] (4,000)[/TD]
[TD="align: right"] (0.65%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 29%[/TD]
[TD="align: right"] 29%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"] –[/TD]
[TD="bgcolor: #FFF2CC, align: right"] (0.19%)[/TD]
[TD="bgcolor: #FFE699, align: right"] (0.19%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"] –[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] 300,000[/TD]
[TD="align: right"] 4,500[/TD]
[TD="align: right"] 1.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 700,000[/TD]
[TD="align: right"] 5,500[/TD]
[TD="align: right"] 0.79%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 400,000[/TD]
[TD="align: right"] 1,000[/TD]
[TD="align: right"] (0.71%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 17%[/TD]
[TD="align: right"] 20%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"] 0.03%[/TD]
[TD="bgcolor: #FFF2CC, align: right"] (0.14%)[/TD]
[TD="bgcolor: #FFE699, align: right"] (0.11%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 0.01%[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] 700,000[/TD]
[TD="align: right"] 14,000[/TD]
[TD="align: right"] 2.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 1,050,000[/TD]
[TD="align: right"] 21,000[/TD]
[TD="align: right"] 2.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 350,000[/TD]
[TD="align: right"] 7,000[/TD]
[TD="align: right"] –[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 40%[/TD]
[TD="align: right"] 30%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"] (0.10%)[/TD]
[TD="bgcolor: #FFF2CC, align: right"] –[/TD]
[TD="bgcolor: #FFE699, align: right"] (0.10%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"] (0.10%)[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] 250,000[/TD]
[TD="align: right"] 3,125[/TD]
[TD="align: right"] 1.25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 750,000[/TD]
[TD="align: right"] 9,675[/TD]
[TD="align: right"] 1.29%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 500,000[/TD]
[TD="align: right"] 6,550[/TD]
[TD="align: right"] 0.04%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 14%[/TD]
[TD="align: right"] 21%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"] 0.07%[/TD]
[TD="bgcolor: #FFF2CC, align: right"] 0.01%[/TD]
[TD="bgcolor: #FFE699, align: right"] 0.08%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 0.02%[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 1,750,000[/TD]
[TD="align: right"] 24,125[/TD]
[TD="bgcolor: #C6E0B4, align: right"] 1.38%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 3,500,000[/TD]
[TD="align: right"] 34,675[/TD]
[TD="bgcolor: #C6E0B4, align: right"] 0.99%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 1,750,000[/TD]
[TD="align: right"] 10,550[/TD]
[TD="bgcolor: #C6E0B4, align: right"] (0.39%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 100%[/TD]
[TD="align: right"] 100%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"] –[/TD]
[TD="bgcolor: #FFE699, align: right"] (0.32%)[/TD]
[TD="bgcolor: #FFE699, align: right"] (0.32%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"] (0.07%)[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Contr.[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Contr.[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EDEDED"]Rate Contr.[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 0.14%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] (0.04%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EDEDED, align: right"] (0.19%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 0.26%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 0.16%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EDEDED, align: right"] (0.10%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 0.80%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 0.60%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EDEDED, align: right"] (0.20%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 0.18%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 0.28%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EDEDED, align: right"] 0.10%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"] 1.38%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"] 0.99%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"] (0.39%)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

simple example (2)​

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

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]s[/TH]
[TD="align: left"]={#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)? ????"}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rubberneck]

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