Mix Effect Calculator

	A	B	C	D	E	F	G	I
1	This google sheet accompanies https://www.growthpath.com.au/Business-Growth/separating-price-and-mix-in-changes-to-sales-and-margin
2	Please note: you can download this as Excel: File -> Download As...
3	Or you can File -> Make a copy... to make your own copy in Google Sheets (which is actually very capable)
4	You need access to Google features to work on this online. You can register for a gmail address, but you can actually use any existing email, gmail or otherwise.
5	Visit https://accounts.google.com/SignUp?hl=en and choose the blue text "I prefer to use my current email address"
6
7	SIMPLE PRICE AND MIX EFFECT
8		20X1			20X2			Price change per product
9	PRODUCT GROUPS	Qty	Avg Price	Sales	Qty	Avg Price	Sales
10	Category 0	32824	$107	$3,512,230	21474	$109	$2,342,974	1.87%
11	Category 1	21474	$161	$3,446,639	27037	$163	$4,417,849	1.24%
12	Category 2	32256	$211	$6,814,688	30409	$218	$6,628,260	3.32%
13	Category 3	28602	$263	$7,513,273	34483	$270	$9,302,344	2.66%
14	TOTAL	115156	$185	$21,286,830	113403	$200	$22,691,427
15	Total sales value increase is		$1,404,597		which is growth of		6.6%
16	Price effect
17	if only the quantity changed, sales would have been		$22,136,003	(new qty * old prices) = SUMPRODUCT(F5:F8,D5:D8)
18	which is growth of ...		2.5%	(new qty * new prices) / (new qty * old prices) -1
19	Quantity effect
20	If we sold an average product (no mix)...
21	Due to quantity		-1.5%	Change in Qty =F10/C10-1
22	Mix effect
23	The remaining change explained		5.6%
24
25
26	This is only approximate, but it is reasonably accurate for small changes
27
28
29	see: http://www.growthpath.com.au/index.php/Business-Growth/separating-price-and-mix-in-changes-to-sales-and-margin.html
30
31	Credit to Richard Coppoolse
32
33	A text he contributed to: https://www.taylorfrancis.com/books/e/9780203085684
34	Innovation in Pricing
35	https://www.linkedin.com/in/richard-coppoolse-5428468/?locale=de_DE
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100