Calculate product percentage impact on total percentage

[Sundance_Kid]

Hi, I am trying to understand the impact of one product (with/without) on the overall percentage.

I have listed a table with 10 products that are sold and the relevant, revenue, cost of sales and profit/margin % of each product. Obviously some products will have a higher impact (maybe weighted average is the saying I'm not sure????) on the overall numbers.

For example, how can I put a value on the impact of one product on the overall number? It is maybe more a maths questions but I'm sure someone here understands

Book1
	D	E	F	G	H
3	Product	Revenue	COS	Profit	Margin %
4	A	715	300	415	58%
5	B	650	350	300	46%
6	C	600	320	280	47%
7	D	550	310	240	44%
8	E	500	250	250	50%
9	F	450	230	220	49%
10	G	400	200	200	50%
11	H	350	150	200	57%
12	I	300	75	225	75%
13	J	250	50	200	80%
14
15	Overall	4,765	2,235	2,530	53%
Sheet1

Cell Formulas
Range	Formula
G4:G13	G4	=E4-F4
H4:H13,H15	H4	=G4/E4
E15:G15	E15	=SUM(E4:E14)

 

[etaf]

does this site help with working out a weighted margin

Weighted Ave Contribution Margin

ACF3.04.07

www.elucidate.org.au

you dont have a volume metric

so selling say 100,000 items with a low margin
will be different to selling 1 item with a high margin

when i was in business many many years ago in business planning / Business MI we often talked about selling baked beans (we were an big IT service company) even with a small margin selling millions was better then say 1 service with an 80% margin

and then forecast volume based on historical data and any future promotions , trends, market impacts

so your % contribution to the revenue - and % profit
is as follows

Book18
	A	B	C	D	E	F	G	H	I	J	K
1
2
3				Product	Revenue	COS	Profit	Margin %		% of revenue	% of profit
4				A	715	300	415	58%		15%	16%
5				B	650	350	300	46%		14%	12%
6				C	600	320	280	47%		13%	11%
7				D	550	310	240	44%		12%	9%
8				E	500	250	250	50%		10%	10%
9				F	450	230	220	49%		9%	9%
10				G	400	200	200	50%		8%	8%
11				H	350	150	200	57%		7%	8%
12				I	300	75	225	75%		6%	9%
13				J	250	50	200	80%		5%	8%
14
15				Overall	4765	2235	2530	0.530954879
Sheet1

Cell Formulas
Range	Formula
G4:G13	G4	=E4-F4
H4:H13,H15	H4	=G4/E4
J4:J13	J4	=E4/$E$15
K4:K13	K4	=G4/$G$15
E15:G15	E15	=SUM(E4:E14)