Design a contributory to the deviation / variance from % calculation

[peter8848]

Hi All,

I am trying to design a formula to see which group of sales has contributed the most to a bench mark (Say as the below and aim is to see which row/material is most impact deviation from the benchmark one in terms of volume effect or % deviation effect) I try to fiter the biggest result from column E but did not achieve what i wish. Does anyone has got any better design? Just want to see which material play the key role in terms of margin / sales % for entire average especially in contrast to a benchmark margin/sales %? thanks a lot!!! Cheers, Peter

	A	B	C	D	E
1​	Sales Material 1	Margin	Margin/Sales %	A1/A7	(C1-C9)*D1
2​	Sales Material 2	Margin	Margin/Sales %	A2/A7	(C2-C9)*D2
3​	Sales Material 3	Margin	Margin/Sales %	A3/A7	(C3-C9)*D3
4​	Sales Material 4	Margin	Margin/Sales %	A4/A7	(C4-C9)*D4
5​	Sales Material 5	Margin	Margin/Sales %	A5/A7	(C5-C9)*D5
6​
7​	Total Sales	Total Margin	Total Margin / Total Sales
9​			Benchmark %

 

[BigBeachBananas]

Do you have a sample example with numerical values?

 

[peter8848]

Do you have a sample example with numerical values?

Hi Mate,

Please find the attached. thanks

I use the contribution factor in Column E and highlight the big one with yellow but some yellow ones does not change the entire average % if they are removed. (My theory is if data from a particular row removed and the average % changed drastically then this is the row of interest.

Contribution Factor.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Sales	Margin	%
2	2,779,665.04	1,779,289.69	64%	15%	-1%
3	3,422,743.22	2,144,740.51	63%	18%	-1%
4	193,660.01	9,739.30	5%	1%	-1%
5	18,967.18	8,885.79	47%	0%	0%
6	3,105,326.20	1,806,025.23	58%	16%	-2%
7	112,354.00	3,881.60	3%	1%	0%
8	224,102.42	121,559.83	54%	1%	0%
9	2,837.70	-5,548.51	-196%	0%	0%
10	4,292.03	3,291.84	77%	0%	0%
11	726.57	402.89	55%	0%	0%
12	343.24	97.51	28%	0%	0%
13	4,100.73	2,814.80	69%	0%	0%
14	1,832.11	1,255.44	69%	0%	0%
15	138.64	101.12	73%	0%	0%
16	8,457.86	5,451.51	64%	0%	0%
17	6,232.40	3,241.53	52%	0%	0%
18	487.20	412.91	85%	0%	0%
19	92,495.95	55,191.06	60%	0%	0%
20	6,750.70	-17,753.50	-263%	0%	0%
21	887,580.10	434,136.45	49%	5%	-1%
22	10,228.96	6,754.13	66%	0%	0%
23	610,831.70	353,092.87	58%	3%	0%
24	630,884.23	216,756.11	34%	3%	-1%
25	145,842.36	94,602.94	65%	1%	0%
26	5,635.58	2,415.30	43%	0%	0%
27	927,239.94	569,253.08	61%	5%	0%
28	193,882.41	119,492.18	62%	1%	0%
29	1,293,936.97	706,320.17	55%	7%	-1%
30	16,708.90	7,206.44	43%	0%	0%
31	404,599.86	247,296.45	61%	2%	0%
32	62,136.34	35,776.62	58%	0%	0%
33	134,195.54	78,792.74	59%	1%	0%
34	3,364,751.48	1,947,922.80	58%	18%	-2%
35	295,268.61	171,906.41	58%	2%	0%
36
37
38
39
40
41	18,969,236.18	10,914,805.24	58%	100%	0%
42
43								A	B	C	D	E
44							1	Sales	Margin	Margin/Sales %	A1/A7	(C1-C9)*D1
45							2	Sales	Margin	Margin/Sales %	A2/A7	(C2-C9)*D2
46			70%				3	Sales	Margin	Margin/Sales %	A3/A7	(C3-C9)*D3
47							4	Sales	Margin	Margin/Sales %	A4/A7	(C4-C9)*D4
48							5	Sales	Margin	Margin/Sales %	A5/A7	(C5-C9)*D5
49							6
50							7	Total Sales	Total Margin	Total Margin / Total Sales
51							9			Benchmark %
52
Sheet1

Cell Formulas
Range	Formula
C2:C35,C41	C2	=+B2/A2
D2:D35,D41	D2	=+A2/$A$41
E2:E35	E2	=(C2-$C$46)*D2
A41:B41	A41	=SUM(A2:A35)
E41	E41	=(C46-$C$46)*D41

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E41	Cell Value	not between 0.005 and -0.005	text	NO

Peter

 

[BigBeachBananas]

Have you tried building a normal or a t-distribution? You can use the mean as your benchmark, and say anything above/below 1 or 2 standard deviations is too high or too low.

 

[peter8848]

Have you tried building a normal or a t-distribution? You can use the mean as your benchmark, and say anything above/below 1 or 2 standard deviations is too high or too low.

Hi BigBeachBanana,

thanks and would you mind showing me how to do it? Is it easily done in Excel or would you mind fixing my example? thanks