How to create a peer group for valuation

[tim-hahn]

Thank you very much for taking time to read this post.

I have a column "ICBSUN" with industry classification codes and a separate column "EV/EBITDA" with an EBITDA multiple.

Now I would like to create a new column "Peer Multiple" that takes the Harmonic mean of the "EV/EBITDA" Coulomn of all Companies with the ICBSUC Code 452010 except the one company of the row itself.

So for example to get the first Value in the "Peer Multiple" Column for the table below it should take the Harmonic mean of the rows 2-12 from column "EV/EBITDA". 

However the amount of companies companies in each industry varies and may not be sorted like this example.

Can anyone help? Thank you very much! 🙂

ISIN	NAME	ICBSUN	ICBSUC	EV	EBITDA	EV/EBITDA	Peer Multiple
US7163821066	PETMED EXPRESS	Personal Care, Drug and Grocery Stores	452010	479179	55915	8,569775552
US58155Q1031	MCKESSON	Personal Care, Drug and Grocery Stores	452010	22314940	1646000	13,55707169
US1266501006	CVS HEALTH	Personal Care, Drug and Grocery Stores	452010	96725580	6743000	14,34459143
US03073E1055	AMERISOURCEBERGEN	Personal Care, Drug and Grocery Stores	452010	15863410	1909030	8,309670356
US9314271084	WALGREENS BOOTS ALLIANCE	Personal Care, Drug and Grocery Stores	452010	65059276	8293000	7,845083323
US1475281036	CASEY'S GENERAL STORES	Personal Care, Drug and Grocery Stores	452010	4725076	487930	9,683921874
US5010441013	KROGER	Personal Care, Drug and Grocery Stores	452010	22844120	5229000	4,368735896
US8718291078	SYSCO	Personal Care, Drug and Grocery Stores	452010	32623406	3047247	10,70586205
US9111631035	UNITED NATURAL FOODS	Personal Care, Drug and Grocery Stores	452010	533852	316847	1,684888921
US1630861011	CHEFS' WAREHOUSE	Personal Care, Drug and Grocery Stores	452010	1156331	70795	16,33351225
US85208M1027	SPROUTS FARMERS MARKET	Personal Care, Drug and Grocery Stores	452010	2274470	334931	6,790861401
US8472151005	SPARTANNASH	Personal Care, Drug and Grocery Stores	452010	517638	155371	3,331625593
US92240M1080	VECTOR GROUP	Tobacco	451030	1982857	287700	6,892099409
US02209S1033	ALTRIA GROUP	Tobacco	451030	92731859	9375000	9,891398293
US9134561094	UNIVERSAL	Tobacco	451030	1349999	208009	6,490098986
US2186811046	CORE MARK HOLDING	Tobacco	451030	1226642	133100	9,2159429
US7181721090	PHILIP MORRIS INTL.	Tobacco	451030	132391077	12515000	10,57859185
US0394831020	ARCHER DANIELS MIDLAND	Food Producers	451020	25816950	2847000	9,068124341
US4571871023	INGREDION	Food Producers	451020	6210685	949000	6,544452055
US1344291091	CAMPBELL SOUP	Food Producers	451020	9929990	885000	11,22032768
US2058871029	CONAGRA BRANDS	Food Producers	451020	8348107	1294300	6,449901105
US2372661015	DARLING INGREDIENTS	Food Producers	451020	4598805	366367	12,55245423
US90328M1071	USANA HEALTH SCIENCES	Food Producers	451020	1701000	208389	8,162618948
US3703341046	GENERAL MILLS	Food Producers	451020	23095314	3139800	7,355664055

[PaigeMiller]

So let's start by referring to this example at Wikipedia of the harmonic mean of three numbers.

Here's an outline of how to do this:

You want the sum of the reciprocals of all of the values. You can get this by groups, by first computing the inverse of the ev_ebitda in a SAS data step and them summing these inverses by group using PROC SUMMARY. This will give you the denominator of the entire group harmonic mean, the numerator is of course N for each group. Next, to adjust the harmonic mean by removing one row at a time, again in a SAS data set, you can compute the numerator as N-1 and the denominator as the denominator of the group harmonic mean computed above MINUS the inverse of ev_ebitda for this one row.

[tim-hahn]

Thank you very much @PaigeMiller .

my code now looks like this.

data work.Sas_test;
set work.Sas_test;
EV/EBITDA=EV/EBITDA;
run;

data work.Sas_test;
set work.Sas_test;
KReciprocal=1/EV/EBITDA;
run;

However I am stuck at the point where I group the reciprocals by industry to get the denominator.

data work.Sas_test;
set work.Sas_test;
if ICBSUC = 201030 then NEW=....
run;

do you have any suggestions to finish the code?

[PaigeMiller]

Let's back up. You can't have code EV/EBITDA=EV/EBITDA; because variable names can't have a slash in the name (and even if you could, this statement is meaningless and doesn't do anything). Run the code, get it working, then show us where you get stuck.

[tim-hahn]

its just a name as I named it in the excel file one could just call it Multiple

data work.Sas_test;
set work.Sas_test;
Multiple=EV/EBITDA;
run;

data work.Sas_test;
set work.Sas_test;
KReciprocal=1/Multiple;
run;

[PaigeMiller]

UNTESTED CODE, assumes sas_test is sorted by icbsuc

data work.Sas_test;
    set work.Sas_test;
    inverse=EBITDA/EV;
run;
proc summary data=sas_test nway;
    class icbsuc;
    var inverse;
    output out=sum_inverse sum=sum_inverse n=n;
run;
data want;
    merge sas_test sum_inverse;
    by icbsuc;
    peer_multiple = (n-1) / (sum_inverse - inverse);
run;