Calculating and Charting Impact on the Mean

For those of us who don't intuitively know the calculations you are using, can you provide some detail on how you are calculating profit percent, average profit, average percent, etc.? Best might be to put a small sample calculation into a spreadsheet that shows everything that goes into this calculation. That would help those of us not in business finance understand the math you are using so that we might be able to help.

	B	C	D	E	F	G
3	Doctor	Revenue	Cost	Profit $	Margin
4	A	100	(75)	25	25%
5	B	200	(154)	46	23%
6	C	300	(234)	66	22%
7	D	150	(110)	41	27%
8	E	75	(45)	30	40%
9	F	275	(124)	151	55%
10	G	500	(325)	175	35%
11	H	200	(170)	30	15%
12	I	200	(170)	30	15%
13
14	Total	2,000	(1,406)	594	30%
15
16	$1 Cost Increase				-0.05000%	F16: =(E14 - 1) / C14 - F14
17	$1 Revenue Increase				0.03514%	F17: =(E14 + 1) / (C14 + 1) - F14
18	$1 Cost Decrease				0.05000%	F18: =(E14 + 1) / C14 - F14
19	$1 Revenue Decrease				-0.03517%	F19: =(E14 - 1) / (C14 - 1) - F14

shg's analysis suggests to me that, by that measure anyway, one doctor does not have more impact than another doctor on overall profit%. A $1 change in Dr. E's cost/reveniue is the same as a $1 change in Dr. G's cost/revenue. Is that the conclusion you are looking to make?

Data table

	A	B	C	D	E	F
1	Doctor	Revenue	Cost	Margin
2	Anne	100	75	25.000%		D2: =1-C2/B2
3	Barb	200	154	23.000%
4	Cara	300	234	22.000%
5	Dana	150	110	26.667%
6	Ella	75	45	40.000%
7	Fran	275	124	54.909%
8	Gail	500	325	35.000%
9	Hana	200	170	15.000%
10	Iris	200	170	15.000%
11	Total	2,000	1,407	29.650%
12
13	Change	1	1	(0.015%)		D13: =1 - (C11 + C13) / (B11 + B13) - D11
14
15				Cost
16		(0.015%)	-1	0	1	B16: =D13
17		-1	0.015%	(0.035%)	(0.085%)	C17:E19: {=TABLE(C13,B13)}
18	Rev	0	0.050%	0.000%	(0.050%)
19		1	0.085%	0.035%	(0.015%)