Article

Working with Fundamental and Estimates Data - A DCF Example

Author:

Jason Ramchandani
Lead Developer Advocate Lead Developer Advocate

Overview

This article will explore how you can use company fundamentals and estimates data to conduct a discounted cashflow (DCF) type intrinsic valuation for a company and its peers to provide a relative valuation overlay. We also use some unsupervised ML routines to generate classification groupings for our data.

Refinitiv Company Fundamentals (RCF) is our new strategic content set, with the most comprehensive, accurate and timely data on public and private companies around the world. It aims to provide both company specific data disclosed by the company and also standardized data to enhance the comparability of the financial data of companies from different countries, accounting standards and industries. This is a very large content set that combines the best of Refinitiv Fundamentals and also Worldscope. On average it provides 60% more content than the previous Refinitiv Fundamentals content set.

We will also look at our extensive Estimates content and will go on to use this data in a popular intrinsic valuation example - Discounted Cashflow. We will then extend this out to peer companies to see what that could yield - looking at some popular unsupervised methods of clustering, KMeans and Gaussian Mixure Models.

Sections

Fundamental Data

Estimates Data

Discounted Cashflow

Peer Group Comparison

Relative Valuation

Clustering

Summary

Pre-requisites:

Refinitiv Eikon / Refinitiv Workspace with access to Eikon Data APIs (Free Trial Available)

Python 2.x/3.x

Required Python Packages: eikonpandasnumpysci-kit Learnconfigparser

Fundamental data

Here we will import the libraries we need and set our authorisation using our App Key. We will then simply download full income statement, cashflow statement and balance sheet. This is a vastly improved workflow as we can now request the whole template as one field.

    	
            

import refinitiv.dataplatform.eikon as ek

import numpy as np

from numpy import where

from numpy import unique

import pandas as pd

from sklearn.cluster import KMeans

from sklearn.mixture import GaussianMixture

from sklearn.linear_model import LinearRegression

import pylab as plt

import warnings

import configparser

cfg = configparser.ConfigParser()

cfg.read('rdp.cfg',encoding='utf-8')

%matplotlib inline

plt.style.use("seaborn")

warnings.filterwarnings("ignore")

ek.set_app_key(cfg['eikon']['app_key']) # or just put your appkey in here

Income Statement

    	
            

df, err =ek.get_data(['VOD.L'],['TR.F.IncomeStatement.fieldname','TR.F.IncomeStatement.fielddescription','TR.F.IncomeStatement'], parameters = {'Period': 'FY0','reportingState':'Rsdt', 'curn':'Native', 'Scale':'6','SORTA':'LISeq'})

df

         Instrument Name Description STD Income Statement All
0 VOD.L TR.F.RevGoodsSrvc Revenue from Goods & Services [SNTS] represent... 37871.0
1 VOD.L TR.F.SalesOfGoodsSrvcNetUnclassif Sales of Goods & Services - Net - Unclassified... 37871.0
2 VOD.L TR.F.RevBizRelActivOthTot Revenue from Business-Related Activities - Oth... 7103.0
3 VOD.L TR.F.TotRevBizActiv Revenue from Business Activities - Total [STLR... 44974.0
4 VOD.L TR.F.CostOfOpRev Cost of Operating Revenue [SCOR] represents th... 30682.0
... ... ... ... ...
139 VOD.L TR.F.COGSInclOpMaintUtilTot Cost of Revenue including Operation & Maintena... 30682.0
140 VOD.L TR.F.COGSExclDepr Cost of Revenues excluding Depreciation [SCOGX... 30682.0
141 VOD.L TR.F.IntrExpn Interest Expense [SINTEX] represents Net inter... 2766.0
142 VOD.L TR.F.OpExpn Operating Expenses [SOPEX] represents the tota... 40518.0
143 VOD.L TR.F.SGAExclRnD Selling, General & Administrative Expenses exc... 10284.0

144 rows × 4 columns

Cashflow Statement

 

    	
            

df1, err =ek.get_data(['VOD.L'],['TR.F.CashflowStatement.fieldname','TR.F.CashflowStatement.fielddescription','TR.F.CashflowStatement'],parameters = {'Period': 'FY0','reportingState':'Rsdt', 'curn':'Native', 'Scale':'6','SORTA':'LISeq'})

df1

        Instrument Name Description STD Cash Flow All
0 VOD.L TR.F.ProfLossStartingLineCF Profit/(Loss) - Starting Line - Cash Flow [SPL... -455
1 VOD.L TR.F.NonCashItemsReconcAdjCF Non-cash Items & Reconciliation Adjustments - ... 18834
2 VOD.L TR.F.DiscOpsGLNetOfTaxCF Discontinued Operations - Gain/(Loss) - Net of... 0
3 VOD.L TR.F.EqIncLossInNetEarnCF Equity Income/(Loss) in Net Earnings - Cash Fl... 2505
4 VOD.L TR.F.IncTaxExpnCF Income Tax Expense - Cash Flow - to Reconcile ... 1250
... ... ... ... ...
68 VOD.L TR.F.ComStockBuybackNet Common Stock Buyback - Net [SCSBN] represents ... 814
69 VOD.L TR.F.DeprDeplAmortCF Depreciation, Depletion & Amortization - Cash ... 14174
70 VOD.L TR.F.FreeCashFlowToEq Free Cash Flow to Equity [SFCFE] represents th... 4472
71 VOD.L TR.F.FOCF Free Cash Flow Net of Dividends [SFCFO] repres... 5565
72 VOD.L TR.F.LeveredFOCF Free Cash Flow [SFCFL] represents the differen... 7861

73 rows × 4 columns

Balance Sheet

 

    	
            

df2, err =ek.get_data(['VOD.L'],['TR.F.BalanceSheet.fieldname','TR.F.BalanceSheet.fielddescription','TR.F.BalanceSheet'],parameters = {'Period': 'FY0','reportingState':'Rsdt', 'curn':'Native', 'Scale':'6','SORTA':'LISeq'})

df2

           Instrument Name Description STD Balance Sheet All
0 VOD.L TR.F.CashSTInvst Cash & Short Term Investments [SCSI] refers to... 20373.0
1 VOD.L TR.F.CashCashEquiv Cash & Cash Equivalents [SCAE] represents shor... 11082.0
2 VOD.L TR.F.STInvstTot Short-Term Investments - Total [SSTI] represen... 9291.0
3 VOD.L TR.F.DerivFinInstrHedgeST Derivative Financial Instruments - Hedging - S... 752.0
4 VOD.L TR.F.LoansRcvblNetST Loans & Receivables - Net - Short-Term [SCLR] ... 10075.0
... ... ... ... ...
174 VOD.L TR.F.TotFixedAssetsNet Total Fixed Assets - Net [STNCA] is calculated... 93875.0
175 VOD.L TR.F.UnearnRevTot Unearned Revenue - Total [SUREV] represents th... 2603.0
176 VOD.L TR.F.WkgCap Working Capital [SWCAP] represents a differenc... 188.0
177 VOD.L TR.F.WkgCapNonCash Working Capital - Non-Cash [SNCWC] represents ... -20185.0
178 VOD.L TR.F.WkgCapExclOthCurrAssetsLiab Working Capital excluding Other Current Assets... 7090.0

179 rows × 4 columns

 

Estimates Data

Eikon/Workspace carries 260+ types of estimate related to company financials. These are critical for measuring future performance, risk and valuation of companies. Both analysts and investors closely follow these. Statistically speaking, the accuracy of estimates for longer time frames are subject to increasing error and longer term guidance is generally less reliable than shorter timeframes.

Company Estimates

Each year the company itself provides its own forward-looking guidance by way of releasing forecasts for interim & full year(s) ahead. They update this guidance periodically when the outlook has some material change. They can do this formally for example in earnings releases or mention something materially important in an interview or Q&A session. We capture this information and provide this to you in an easy to consume format - including the source of the guidance.

 

    	
            

df3,e = ek.get_data('VOD.L',['TR.GuidanceMeasure','TR.EstGuidHighValue','TR.EstGuidLowValue','TR.GuidanceText'],  {'Period':'FY1,FY2','GuidMeasure':'REV,EBIT,EBITDA','GuidDataBasis':'VAL'})

 

df3

         Instrument Guidance Measure Guidance High Value Guidance Low Value Guidance Text
0 VOD.L Revenue 14600000000 14400000000 ("stc") is complete (currently planned to occu...
1 VOD.L EBITDA 14881000000 14732190000 And just wondered if you could give some scale...
2 VOD.L Revenue 14600000000 14400000000 Consolidated Revenue (ex. pass-through) €955-9...
3 VOD.L EBITDA <NA> <NA> Partnership will deliver additional EBITDA fro...
4 VOD.L EBITDA <NA> <NA> FY21 may be flat to slightly down, compared to...
5 VOD.L EBITDA <NA> <NA> And if I look at the guidance, it suggests I g...
6 VOD.L EBITDA <NA> <NA> 2020 guidance has been upgraded with EBITDA gr...
7 VOD.L EBIT <NA> <NA> Moreover, we expect to deliver a net reduction...
8 VOD.L Revenue <NA> <NA> Consolidated Vantage Towers FY21 Pro forma(7) ...
9 VOD.L EBITDA <NA> <NA> (excluding pass through revenue) compound annu...
10 VOD.L Revenue <NA> <NA> (excluding pass through revenue) compound annu...

Analyst Estimates

Companies - generally depending on their size - are also followed by analysts in banks who themselves make forecasts for the companies they follow - and also usually some sort of recommendation eg Buy, Sell, Hold and a price target. Now for large companies - where there are lots of analysts covering them - there are likely to be more updates to these figures. This is good as one can usually find a more recent update than for company guidance. In more recent times the list of forecasted items has been expanded to include many more items than the basic ones and include some cashflow and balance sheet related items as well. We also provide expanded coverage of particular industry-specific metrics which allow for deeper dives into relevant industry sectors - see the image below:

 

So lets have a look at how we can get individual analyst estimates for a stock - here we just look at Revenue.

    	
            

df4, err =ek.get_data(['VOD.L'],['TR.RevenueEstValue(Period=FY1).date',

                                 'TR.RevenueEstValue(Period=FY1).brokername',

                                 'TR.RevenueEstValue(Period=FY1).analystname',

                                 'TR.RevenueEstValue(Period=FY1).analystcode',

                                 'TR.RevenueEstValue(Period=FY1)'])

                      

df4

        Instrument Date Broker Name Analyst Name Analyst Code Revenue - Broker Estimate
0 VOD.L 2021-04-09T12:05:00Z CREDIT SUISSE - EUROPE Bluestone, Jakob 653B 43491232000
1 VOD.L 2021-02-03T07:35:00Z GOLDMAN SACHS Lee, Andrew FG3 43990143000
2 VOD.L 2021-02-12T05:10:00Z DEUTSCHE BANK Grindle, Robert 4AIT 43641000000
3 VOD.L 2020-12-07T16:11:00Z REDBURN (EUROPE) LIMITED Delfas, Nick 6VB2 43566821000
4 VOD.L 2021-02-03T14:00:00Z JPMORGAN Dattani, Akhil 3MBD 43930000000
5 VOD.L 2021-04-09T04:57:00Z LANDESBANK BADEN-WUERTTEMBERG Deuscher, Bettina 4O3B 43799980000
6 VOD.L 2021-04-01T13:18:00Z SOCIETE GENERALE Adorisio, Ottavio 4978 44500000000
7 VOD.L 2020-11-10T17:37:00Z ESN/BANCA AKROS (ITALY) De Vita, Andrea 1ABC 43630000000
8 VOD.L 2021-02-22T05:56:00Z BERENBERG Murdock-Smith, Carl 7K6U 43298000000
9 VOD.L 2021-02-23T01:48:00Z MORNINGSTAR, INC. Hodel, Michael 4LNP 43694000000
10 VOD.L 2021-03-22T03:00:00Z UBS Tang, Polo 4Y98 43872349000
11 VOD.L 2021-01-14T08:59:00Z DZ BANK Volkert, Matthias 3820 43500000000
12 VOD.L 2021-03-31T05:36:00Z BOFA GLOBAL RESEARCH Wright, David A 6Z0Y 43563880000
13 VOD.L 2021-04-08T08:08:00Z JEFFERIES Dellis, Jerry 4UPP 43731000000
14 VOD.L 2021-03-30T23:56:00Z BARCLAYS Patrick, Maurice G 4UH7 43351000000
15 VOD.L 2020-07-28T08:45:00Z ODDO BHF Iatrides, Alexandre D 5ZI2 39590000000
16 VOD.L 2020-12-03T07:05:00Z NUMIS SECURITIES Karidis, John 7FS1 43278000000
17 VOD.L 2021-01-18T08:58:00Z ARETE RESEARCH Beale, Andrew C 3ZWO 43558000000
18 VOD.L 2021-02-04T03:44:00Z HSBC Rumley, Adam M 61LF 43751404000
19 VOD.L 2021-02-04T06:31:00Z KEPLER CHEUVREUX Van Leijenhorst, Matthijs 64TX 43688000000
20 VOD.L 2021-03-18T09:01:00Z EXANE BNP PARIBAS Mills, Joshua 7UEA 43736000000
21 VOD.L 2021-03-25T06:13:00Z MORGAN STANLEY Kelly, Emmet B 6YOF 43758404000

And lets now look at how we can get some of these expanded industry metrics - this time for Tech and Telecom - using Verizon. Note these expanded metrics will not be available for all geographies.

 

    	
            

df5, err =ek.get_data(['VZ'],['TR.Subscribers','TR.SubscribersBrokerName',

                              'TR.NetSubscriberAdds','TR.NetSubscriberAddsBrokerName',

                              'TR.AvgRevPerUnitEstValue','TR.AvgRevPerUnitEstBrokerName',

                              'TR.ChurnpctEstValue','TR.ChurnpctEstBrokerName'],

                    {'Period':'FY1','SDate':'-200','EDate':'0','Frq':'D'})

df5

        Instrument No. Of Subscribers / Users - Broker Estimate Broker Name Net Subscriber Additions - Broker Estimate Broker Name Avg Revenue Per Unit / User - Broker Estimate Broker Name Churn Percentage - Broker Estimate Broker Name
0 VZ 121737000 JPMORGAN 2074000 WELLS FARGO SECURITIES, LLC NaN WELLS FARGO SECURITIES, LLC 1.29 RBC CAPITAL MARKETS
1 VZ <NA> SCOTIABANK GBM 1563000 NOMURA INSTINET (HISTORICAL) 42.89 OPPENHEIMER & CO., INC. 1.40 OPPENHEIMER & CO., INC.
2 VZ 121026000 MOFFETTNATHANSON LLC <NA> JPMORGAN NaN JPMORGAN NaN JPMORGAN
3 VZ 77000000 ROBERT W. BAIRD & CO., INC. 2149000 GUGGENHEIM SECURITIES LLC 45.60 MOFFETTNATHANSON LLC 1.24 MOFFETTNATHANSON LLC
4 VZ 121026000 MOFFETTNATHANSON LLC -896000 DEUTSCHE BANK NaN JPMORGAN 1.27 SCOTIABANK GBM
... ... ... ... ... ... ... ... ... ...
1804 VZ <NA>   <NA> ROBERT W. BAIRD & CO., INC. NaN   NaN  
1805 VZ <NA>   1646000 JPMORGAN NaN   NaN

 
1806 VZ <NA>   <NA> GUGGENHEIM SECURITIES LLC NaN   NaN

 
1807 VZ <NA>   1532000 RBC CAPITAL MARKETS NaN   NaN  
1808 VZ <NA>   <NA> DEUTSCHE BANK NaN   NaN

 

1809 rows × 9 columns

Consensus Estimates

As with all ranges of opinion - some analysts are more positive than others - so consensus estimates have developed - whose role is to try to capture the mean of all analysts who are covering the company. Our I/B/E/S (Institutional Brokers Estimates System) is the industry standard, founded in 1976, offering coverage for 22,000 active companies across 90 countries from over 18,000 analysts. Above we mentioned that not all analysts will necessarily report on all measures - so the number of estimates for some of these measures maybe lower than for the most common ones. Additionally - some estimates are not included due to staleness etc. Hence the importance of incorporating # of included estimates as a guide for usefulness of the estimate. One can also get an idea of how varied estimates are by including Estimate High, Low and Median - I haven't included them here but they are all available by searching the Data Item Browser or Code Creator apps on the desktop.

The really great thing here is that we can track these on a daily basis to see realtime changes in expectations for these core metrics. For example if one analyst changes their estimate it will be reflected in the mean. Very often we see clusters of changes together (herding) so the mean expectation can adjust quite rapidly to a new level - in other cases it can take a while. Here we look at these for the last 200 days. To see the impact on shareprice of VOD.L - it is easy just to add a closing shareprice for the last 200 days.

 

    	
            

df6, err =ek.get_data(['VOD.L'],['TR.RevenueMean(Period=FY1).calcdate',

                                 'TR.RevenueMean(Period=FY1)',

                                 'TR.RevenueNumIncEstimates(Period=FY1)',

                                 'TR.GrossIncomeMean(Period=FY1)',

                                 'TR.GrossIncomeNumIncEstimates(Period=FY1)',

                                 'TR.PreTaxProfitMean(Period=FY1)',

                                 'TR.PreTaxProfitNumIncEstimates(Period=FY1)',

                                 'TR.NetProfitMean(Period=FY1)',

                                 'TR.NetProfitNumIncEstimates(Period=FY1)',

                                 'TR.EPSMean(Period=FY1)',

                                 'TR.EPSNumIncEstimates(Period=FY1)',

                                 'TR.DPSMean(Period=FY1)',

                                 'TR.DPSNumIncEstimates(Period=FY1)',

                                 'TR.CLOSEPRICE(Adjusted=1)'],

                                 {'SDate':'-2000','EDate':'0', 'Frq':'D'})

                      

df6.index = pd.to_datetime(df6['Calc Date'])

df6

 Calc Date          Instrument Calc Date Revenue - Mean Revenue - Number of Included Estimates Gross Income - Mean Gross Income - Number of Included Estimates Pre-Tax Profit - Mean Pre-Tax Profit - Number of Included Estimates Net Income - Mean Net Income - Number of Included Estimates Earnings Per Share - Mean Earnings Per Share - Number of Included Estimates Dividend Per Share - Mean Dividend Per Share - Number of Included Estimates Close Price
2013-05-21 VOD.L 2013-05-21 53567306080 27 <NA> <NA> 12862191590 20 9353186580 21 0.35535 28 0.23101 28 204.878696
2013-05-22 VOD.L 2013-05-22 53168608510 27 <NA> <NA> 12803122540 21 9306716290 22 0.34662 29 0.22509 28 201.701487
2013-05-23 VOD.L 2013-05-23 52972963630 27 <NA> <NA> 12762120260 21 9266420960 22 0.34514 29 0.22509 28 198.370543
2013-05-24 VOD.L 2013-05-24 52972963630 27 <NA> <NA> 12762120260 21 9266420960 22 0.34514 29 0.22509 28 199.395449
2013-05-28 VOD.L 2013-05-28 52852044200 27 <NA> <NA> 12762120260 21 9245879010 22 0.34440 29 0.22388 28 202.623903
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2021-04-12 VOD.L 2021-04-12 43496327860 22 14547000000 1 3668495560 16 2169309250 16 0.07630 20 0.08957 23 134.820000
2021-04-13 VOD.L 2021-04-13 43496327860 22 14547000000 1 3668495560 16 2169309250 16 0.07630 20 0.08957 23 134.080000
2021-04-14 VOD.L 2021-04-14 43496327860 22 14547000000 1 3668495560 16 2169309250 16 0.07630 20 0.08957 23 134.120000
2021-04-15 VOD.L 2021-04-15 43496327860 22 14547000000 1 3668495560 16 2169309250 16 0.07630 20 0.08957 23 133.340000
2021-04-16 VOD.L 2021-04-16 43496327860 22 14547000000 1 3668495560 16 2169309250 16 0.07630 20 0.08957 23 135.180000

2001 rows × 15 columns

It is then really simple to visual any of these components impacts on sharerpice - lets do it graphically and then numerically (in this case we will just use a straightforward Pearsons correlation coefficient - but you may need to make the series stationary then calculate the correlation. Generally, over longer periods financial timeseries - particularly shareprices - display non-stationarity).

 

    	
            df6[['Revenue - Mean','Close Price']].plot(subplots=True, figsize=(12,10))
        
        
    
    	
            df6[['Revenue - Mean','Close Price']].corr()
        
        
    
       Revenue - Mean Close Price
Revenue - Mean 1.000000 0.778265
Close Price 0.778265 1.000000

Starmine Smart Estimates

Our Starmine Estimates go a step further by trying to weight more successful (in terms of accuracy) analysts opinions more highly - similarly penalising less accurate analysts. More weight is also allocated to more recent updates whilst those that are stale ie quite old - are dropped completely from the Starmine Smart Estimates. In this way we try to capture a more reliable and timely picture. There have been lots of papers written on the validity and transparency of Starmne Smart Estimates over the years. Please see the links for further information about methodology and results. Again I have used a convenient selection but many more fields are available.

 

    	
            

df7, err =ek.get_data(['VOD.L'],['TR.RevenueMean(Period=FY1).date',

                                 'TR.RevenueSmartEst(Period=FY1)',

                                 'TR.RevenueSENumIncEst(Period=FY1)',

                                 'TR.GrossIncomeSmartEst(Period=FY1)',

                                 'TR.GrossIncomeSENumIncEst(Period=FY1)',

                                 'TR.PreTaxProfitSmartEst(Period=FY1)',

                                 'TR.PreTaxProfitSENumIncEst(Period=FY1)',

                                 'TR.NetprofitSmartEst(Period=FY1)',

                                 'TR.NetProfitSENumIncEst(Period=FY1)',

                                 'TR.EpsSmartEst(Period=FY1)',

                                 'TR.EpsSENumIncEst(Period=FY1)',

                                 'TR.DPSSmartEst(Period=FY1)',

                                 'TR.DPSSENumIncEst(Period=FY1)',

                                 'TR.CLOSEPRICE(Adjusted=1)'],

                                 {'SDate':'-200','EDate':'0'})

                      

df7.index = pd.to_datetime(df7['Date'])

df7

  Date       Instrument Date Revenue - SmartEstimate® Revenue - SmartEstimate Num of Included Estimates Gross Income - SmartEstimate® Gross Income - SENum Inc Ests Pre-Tax Profit - SmartEstimate® Pre-Tax Profit - SmartEstimate Num of Included Estimates Net Income - SmartEstimate® Net Income - SmarteEstimate Num of Included Estimates Earnings Per Share - SmartEstimate® Earnings Per Share - SmartEstimate Num of Included Estimates Dividend Per Share - SmartEstimate® Dividend Per Share - SmartEstimate Num of Included Estimates Close Price
2020-07-02 00:00:00+00:00 VOD.L 2020-07-02T00:00:00Z 44298091090 13 14609872280 2 2993196190 11 2084463470 12 0.06964 11 0.08878 9 129.48
2020-07-03 00:00:00+00:00 VOD.L 2020-07-03T00:00:00Z 44289200020 13 14609872280 2 2985475480 11 2071233560 12 0.06964 11 0.08878 9 129.64
2020-07-06 00:00:00+00:00 VOD.L 2020-07-06T00:00:00Z 44267926090 13 14609872280 2 2973849430 11 2049495100 12 0.06893 11 0.08878 9 130.24
2020-07-07 00:00:00+00:00 VOD.L 2020-07-07T00:00:00Z 44202758620 14 14609872280 2 2929933030 12 1994169530 13 0.06782 12 0.08878 9 127.02
2020-07-07 00:00:00+00:00 VOD.L 2020-07-07T00:00:00Z 44202758620 14 14609872280 2 2842299780 6 1584585510 5 0.06782 12 0.08878 9 125.28
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2021-04-09 00:00:00+00:00 VOD.L 2021-04-09T00:00:00Z 43713837000 18 14547000000 1 3830859090 14 2262080790 13 0.08061 8 0.08914 9 134.82
2021-04-09 00:00:00+00:00 VOD.L 2021-04-09T00:00:00Z 43713837000 18 14547000000 1 3830859090 14 2262080790 13 0.08061 8 0.08914 9 134.08
2021-04-09 00:00:00+00:00 VOD.L 2021-04-09T00:00:00Z 43713837000 18 14547000000 1 3830859090 14 2262080790 13 0.08061 8 0.08914 9 134.12
2021-04-09 00:00:00+00:00 VOD.L 2021-04-09T00:00:00Z 43713837000 18 14547000000 1 3830859090 14 2262080790 13 0.08061 8 0.08914 9 133.34
2021-04-09 00:00:00+00:00 VOD.L 2021-04-09T00:00:00Z 43713837000 18 14547000000 1 3830859090 14 2262080790 13 0.08061 8 0.08914 9 135.18

201 rows × 15 columns

    	
            df7[['Net Income - SmartEstimate®','Close Price']].plot(subplots=True,figsize=(12,10))
        
        
    
    	
            df7[['Net Income - SmartEstimate®','Close Price']].corr()
        
        
    
             Net Income - SmartEstimate® Close Price
Net Income - SmartEstimate® 1.000000 0.791268
Close Price 0.791268 1.000000

Discounted Cashflow

We will now look at how to implement this popular approach to valuation. There are many ways to approach this sort of task - as evidenced by the number of books on the subject. The approach I am using here does not require us to forecast free cashflow - rather we use IBES consensus estimates for these. Typically these forecasts go out 4 years or so. So we can use these for our forecasted period. After this period forecasting becomes more challenging - but the company will likely generate more cashflows after the end of our 4 year forecast window. To capture this we will generate what is known as a terminal value for cashflow - based on an exit multiple. We take the most recent EV / Free Cash Flow multiple and then assign that to our last forecasted cashflow number. So lets see how we can do this.

First we will get some selected fields that we need to help us with our calculation.

 

 

    	
            

df8,err = ek.get_data('VOD.L',['TR.F.DebtTot','TR.WACC','TR.F.ComShrOutsTot(Period=FY0)', 'TR.EVtoFCFSmartEst(Period=FY1)'])

df8

            Instrument Debt - Total Weighted Average Cost of Capital, (%) Common Shares - Outstanding - Total EV / Free Cash Flow (SmartEstimate ®)
0 VOD.L 74718000000 3.283748 26772164544 21.305401

Next we will get our IBES consensus free cashflow forecasts going out 4 fiscal years. I also include the most recent actual value at the start.

 

    	
            

fcf,err = ek.get_data('VOD.L',['TR.FCFMean(Period=FY0)','TR.FCFMean(Period=FY1)','TR.FCFMean(Period=FY2)',

                     'TR.FCFMean(Period=FY3)','TR.FCFMean(Period=FY4)'])

fcf.columns =['RIC','FCF-FY0','FCF-FY1','FCF-FY2','FCF-FY3','FCF-FY4']

fcf

          RIC FCF-FY0 FCF-FY1 FCF-FY2 FCF-FY3 FCF-FY4
0 VOD.L 5349500000 4744226510 4975148260 5287482800 5785428570

Next we want to calculate the net present value of these cashflows using WACC as the discount rate. Here we simply use the numpy net present value function.

 

    	
            

dfcf = np.npv(df8['Weighted Average Cost of Capital, (%)'].values,fcf.iloc[0,2:5].values.tolist())

dfcf

6193765783.604535

Now we want to calculate our terminal value using the exit multiple we identified - in this case Enterprise Value / Free Cashflow multiple, and apply this to the last Free Cashflow forecast (FY4).

    	
            

tv_exit = df8['EV / Free Cash Flow (SmartEstimate ®)'] * fcf.iloc[0][5]

tv_exit

0 1.232609e+11
Name: EV / Free Cash Flow (SmartEstimate ®), dtype: float64

Now we are getting to the final stages of our valuation. We now set our flow value to be the sum of our discounted cashflow plus our terminal value.

    	
            

flo_val = dfcf + tv_exit

flo_val

0 1.294546e+11
Name: EV / Free Cash Flow (SmartEstimate ®), dtype: float64

We need to subtract the debt portion from the enterprise value so we our left with our Equity portion - which we can then divide by shares outstanding to get our valuation per share.

    	
            

model_price = (flo_val - df8['Debt - Total'])/ df8['Common Shares - Outstanding - Total']

model_price

0 2.044535
dtype: float64

As this is a UK share we need to multiply the equity value (which is in pounds) to pence so we can compare it to the quoted shareprice.

    	
            model_price*100
        
        
    

0 204.453545
dtype: float64

Now we can just compare it to the most recent closing price.

    	
            

df9, err = ek.get_data('VOD.L','CF_CLOSE')

df9

         Instrument CF_CLOSE
0 VOD.L 135.18

Peer Group Comparison

The Eikon Data API provides a really convenient Peers function which allows you to retrieve a list of peer RICs for any company RIC you enter. The peers are global in scope and allow one to perform quite nice relative valuation or competitor analysis. In our case we will simply replicate our previous DCF analysis across the peer companies of Vodafone. You can of course replace this with metrics such as PER, PCF, PTS, EV/EBITDA, Dividend Yield etc.

In the case of VOD.L we had all the numbers we needed and all the calculations worked fine - however, as we will see this may not always be the case. All expected forecasts may not be present or other data maybe NaN or missing. So dealing with these kinds of exceptions is part of the process. I actually avoid most issues by dropping any peer which has any NA values. Thankfully we have enough other peers - but in cases where this is not possible you can start handling things in more detail. I include an example of this (though its not used in our as I have already removed the NA offenders) when I am calculating the terminal value below. The simplest way is to use an exit multiple - where we do not have a valid multiple - we will calculate the terminal value based on the perpetual growth method (assuming a long run growth rate of 1%) - again we don't utilise it here - but I included it so you could structure your own exception handling.

Just a note on context here, no two companies are identical so we are never comparing apples with apples exactly. Differences in balance sheet structures, asset holdings, efficiencies, innovation, performance stability any many other factors all play major roles in divergent outcomes of companies - plus there are also the interactions between different economic phases, growth & inflation etc. More recently phenomenon such as quantitative easing or central bank credit creation can also impact DCF analysis / valuations - as evidenced by the increasing number of zombie companies - kept alive by ZIRP/NIRP etc. Therefore - we should use a wide variety of valuation measures and techniques when looking at and selecting investments. The DCF analysis is but one tool in our armoury.

Get all data we need for target and its peers - I also drop any peers with NAs

 

    	
            

ric = 'VOD.L'

peers, err = ek.get_data([ric,"Peers("+ric+")"],fields=['TR.CompanyName','TR.ExchangeCountry','TR.PeersRank','TR.F.DebtTot','TR.WACC','TR.F.ComShrOutsTot(Period=FY0)', 'TR.EVtoFCFSmartEst(Period=FY1)','TR.FCFMean(Period=FY0)','TR.FCFMean(Period=FY1)','TR.FCFMean(Period=FY2)', 'TR.FCFMean(Period=FY3)','TR.FCFMean(Period=FY4)','TR.PRICECLOSE'])

 

peers.columns =['RIC','Name','Country','Peers Rank','Debt - Total','WACC %','Shs Out','EV/FCF','FCF-FY0','FCF-FY1','FCF-FY2','FCF-FY3','FCF-FY4','Close Price']

 

peers['EV/FCF'] = peers['EV/FCF'].astype(float)

peers['Peers Rank'][0] = 0

peers.dropna(axis=0, how='any',inplace=True)

peers.reset_index(drop=True, inplace=True)

peers

            RIC Name Country Peers Rank Debt - Total WACC % Shs Out EV/FCF FCF-FY0 FCF-FY1 FCF-FY2 FCF-FY3 FCF-FY4 Close Price
0 VOD.L Vodafone Group PLC United Kingdom 0 74718000000 3.283748 2.677216e+10 21.305401 5349500000 4744226510 4975148260 5287482800 5785428570 135.1800
1 BT.L BT Group PLC United Kingdom 1 25894000000 2.959715 9.882207e+09 33.605718 2103627670 905576000 1299896220 1119447560 1438500000 154.3500
2 DTEGn.DE Deutsche Telekom AG Germany 2 136397000000 3.380618 4.743456e+09 30.848817 6479575000 6998878750 9918995600 12735923890 15245500000 16.1240
3 TEF.MC Telefonica SA Spain 3 51029000000 2.023875 5.629920e+09 24.674585 3588630390 3168370200 3526639620 3319636330 2720590000 3.7520
4 ORAN.PA Orange SA France 4 45758000000 1.517126 2.658792e+09 24.670979 2503097830 2477910080 3027773550 4160788330 3573000000 10.3500
5 SCMN.S Swisscom AG Switzerland 6 8940000000 2.655604 5.180194e+07 22.556896 1434527220 1396138900 1475277220 1591734750 1443000000 479.3000
6 O2Dn.DE Telefonica Deutschland Holding AG Germany 7 5132000000 3.171687 2.974555e+09 14.108313 1002784900 766270580 789812180 773813710 923666670 2.3490
7 TLIT.MI Telecom Italia SpA Italy 8 30256000000 2.869483 2.119600e+10 30.246899 1462747000 976660000 677979000 1317928330 2934000000 0.4455
8 LBTYA.OQ Liberty Global PLC United States of America 9 14997700000 6.815464 5.804985e+08 17.670730 1124418130 1405976780 1804686130 1835726670 1736400000 26.4700
9 ILD.PA Iliad SA France 11 11865000000 1.379590 5.876208e+07 150.369952 -277458750 111278220 251991110 842630000 -4927000000 158.5500
10 PROX.BR Proximus NV Belgium 13 2954000000 1.734783 3.226900e+08 27.249599 565700000 382711670 309079860 265199200 211000000 18.5500
11 KPN.AS Koninklijke KPN NV Netherlands 14 7437000000 2.666071 4.197235e+09 22.410055 735772670 827193200 899913180 945791400 1284000000 2.7460
12 DRIG.DE 1&1 Drillisch AG Germany 15 1080934000 4.991861 1.762646e+08 16.703991 262563170 297920000 232600000 198875000 184500000 23.5600
13 TEL.OL Telenor ASA Norway 16 150805000000 2.125287 1.399458e+09 19.846735 22094554700 18133822640 20093370000 21292140000 18233000000 153.9000
14 TELIA.ST Telia Company AB Sweden 17 104392000000 2.111820 4.089632e+09 21.670525 11623496200 10310630500 11943235690 13447109170 12916000000 36.5000
15 TELA.VI Telekom Austria AG Austria 18 3397697000 1.633824 6.640848e+08 15.835604 682833330 459666670 503000000 584666670 523000000 6.8900
16 TNET.BR Telenet Group Holding NV Belgium 19 5417900000 3.764590 1.092433e+08 17.980048 457512800 507941500 508564330 522005400 543500000 35.8600
17 TEL2b.ST Tele2 AB Sweden 20 29995000000 2.507502 6.886276e+08 23.314429 5042527890 4769539100 5402207000 5932257430 6052000000 118.9500
18 ELISA.HE Elisa Oyj Finland 21 1426800000 1.352343 1.600829e+08 27.473798 330238600 340941750 364354500 383504820 415000000 48.4000
19 OBEL.BR Orange Belgium SA Belgium 22 509688000 1.992162 5.994476e+07 18.187373 139615000 96398570 109388380 103606250 109500000 22.1000
20 INWT.MI Infrastrutture Wireless Italiane SpA Italy 23 3833619847 2.992875 9.601634e+08 44.957274 260615000 247121900 403258300 479012630 443480000 9.4460
21 VIV.PA Vivendi SE France 24 7648000000 4.022022 1.092830e+09 31.646602 1111803640 1315034300 1514001300 1477213290 1551666670 28.5100
22 FNTGn.DE freenet AG Germany 25 1477488000 4.631036 1.251048e+08 15.409570 314344440 223445000 227211250 227564000 234500000 20.1200
23 TMUS.OQ T-Mobile US Inc United States of America 26 81543000000 4.201657 1.241806e+09 47.579256 2013927500 4745816060 7271491470 12498032290 16492444440 133.0500
24 OTEr.AT Hellenic Telecommunications Organization SA Greece 27 1555600000 3.614797 4.593482e+08 13.422630 473018430 578100000 558971430 581100000 611333330 14.2000
25 GAMA.L Gamma Communications PLC United Kingdom 28 19000000 6.034460 9.540244e+07 33.366940 39475000 50050000 57583330 62666670 67436000 1798.0000
26 RWAY.MI Rai Way SpA Italy 29 50793963 4.744355 2.683746e+08 33.057773 45245500 32463000 35275000 85448000 96000000 4.8100
27 SGEF.PA Vinci SA France 30 32084000000 4.563921 5.620617e+08 23.094869 1551000000 3170877000 3969049000 4283285710 4937500000 90.6500
28 SGOB.PA Compagnie de Saint Gobain SA France 31 15624000000 5.227225 5.300335e+08 19.643294 2040450000 1798117000 2253977000 2332211670 2419500000 53.2200
29 AAF.L Airtel Africa PLC United Kingdom 32 4279000000 6.401978 3.758152e+09 21.049526 306500000 370370000 397916670 433636670 436770000 75.0000
30 GEBN.S Geberit AG Switzerland 33 778800000 5.647370 3.562559e+07 35.666947 660784830 636792250 661919080 679602860 702733330 613.6000
31 MS.MI Mediaset SpA Italy 34 1623700000 8.838729 1.137944e+09 20.142671 253916670 86030000 103043750 124312500 309660000 2.4880
32 SESFd.PA SES SA France 35 3967000000 6.363997 4.555893e+08 29.921604 565393130 185982570 19359250 686488400 391126670 6.8680
33 ETL.PA Eutelsat Communications SA France 36 3522900000 2.064355 2.301507e+08 11.642313 452025500 459978500 435318380 468047000 436393330 10.2600
34 EKTL.MC Euskaltel SA Spain 37 1470076000 3.033608 1.784718e+08 49.493270 100612860 66771670 79015000 86056670 108000000 11.0600
35 CRH.I CRH PLC Ireland; Republic of 38 13850000000 4.341718 7.848000e+08 17.862562 2675429520 2485218090 2676537150 2886679810 2964650720 40.4600
36 VZ.N Verizon Communications Inc United States of America 39 129062000000 3.415964 4.138130e+09 18.869454 20822281400 18992386240 19079453900 19857580390 22227777780 58.2800
37 T.N AT&T Inc United States of America 40 157245000000 4.066687 7.125922e+09 14.980400 26146916630 25085545460 24805461540 25147571430 25981166670 29.9500
38 VON.AS VEON Ltd Netherlands 42 10056000000 4.581417 1.749127e+09 15.860783 429000000 671000000 806500000 1118500000 1087280000 1.4740
39 RG.F Rogers Communications Inc Germany 48 21257000000 3.049163 5.049250e+08 33.619432 2153930000 1670772730 2349777780 2805000000 2994000000 40.0000

Conduct DCF for all companies and compare closing price with model price

 

    	
            

peers['m_price']= np.nan

peers['method'] = np.nan

peers['prem/disc'] = np.nan

 

for i, peer in enumerate(peers['RIC']):

    multiple = peers.iloc[i][7]

    

    for col in range(12,9,-1):

        if pd.isnull(peers.iloc[i][col]):

            pass

        else:

            last = col

            break

    

    dfcf = np.npv(peers.iloc[i][5],peers.iloc[i,9:last].tolist())

    if not pd.isna(dfcf):

        if dfcf>0:

            if pd.isnull(multiple) or multiple == "NaN":

                tv_exit  =  (peers.iloc[i][last] * (1 + 0.1))  /  ((peers.iloc[i]['WACC %']) - 1)     

                peers['method'][i] = 'perpetual growth'

            elif peers.iloc[i][last] <0:

                tv_exit = multiple * peers.iloc[i,9:last].mean()

                peers['method'][i] = 'multiple - fcfAvg'

            else:

                tv_exit = multiple * peers.iloc[i][last]

                peers['method'][i] = 'multiple'

            

            flo_val = dfcf + tv_exit

            model_price = (flo_val - peers.iloc[i]['Debt - Total'])/ peers.iloc[i]['Shs Out']

            peers['m_price'][i] = model_price

            

            if peers.iloc[i][2] == 'United Kingdom':

                peers['m_price'][i] = peers['m_price'][i]*100

            

            if not pd.isnull(peers.iloc[i]['Close Price']):

                if not pd.isnull(peers.iloc[i]['m_price']):

                    peers['prem/disc'][i] = (peers.iloc[i]['Close Price'] / peers.iloc[i]['m_price']-1) *100

    else:

        peers.iloc[i]['m_price'] = np.nan

 

peers

         RIC Name Country Peers Rank Debt - Total WACC % Shs Out EV/FCF FCF-FY0 FCF-FY1 FCF-FY2 FCF-FY3 FCF-FY4 Close Price m_price method prem/disc
0 VOD.L Vodafone Group PLC United Kingdom 0 74718000000 3.283748 2.677216e+10 21.305401 5349500000 4744226510 4975148260 5287482800 5785428570 135.1800 204.453545 multiple -33.882291
1 BT.L BT Group PLC United Kingdom 1 25894000000 2.959715 9.882207e+09 33.605718 2103627670 905576000 1299896220 1119447560 1438500000 154.3500 240.362085 multiple -35.784381
2 DTEGn.DE Deutsche Telekom AG Germany 2 136397000000 3.380618 4.743456e+09 30.848817 6479575000 6998878750 9918995600 12735923890 15245500000 16.1240 72.486277 multiple -77.755790
3 TEF.MC Telefonica SA Spain 3 51029000000 2.023875 5.629920e+09 24.674585 3588630390 3168370200 3526639620 3319636330 2720590000 3.7520 3.694211 multiple 1.564317
4 ORAN.PA Orange SA France 4 45758000000 1.517126 2.658792e+09 24.670979 2503097830 2477910080 3027773550 4160788330 3573000000 10.3500 17.575235 multiple -41.110320
5 SCMN.S Swisscom AG Switzerland 6 8940000000 2.655604 5.180194e+07 22.556896 1434527220 1396138900 1475277220 1591734750 1443000000 479.3000 492.808090 multiple -2.741045
6 O2Dn.DE Telefonica Deutschland Holding AG Germany 7 5132000000 3.171687 2.974555e+09 14.108313 1002784900 766270580 789812180 773813710 923666670 2.3490 2.991856 multiple -21.486863
7 TLIT.MI Telecom Italia SpA Italy 8 30256000000 2.869483 2.119600e+10 30.246899 1462747000 976660000 677979000 1317928330 2934000000 0.4455 2.817905 multiple -84.190380
8 LBTYA.OQ Liberty Global PLC United States of America 9 14997700000 6.815464 5.804985e+08 17.670730 1124418130 1405976780 1804686130 1835726670 1736400000 26.4700 29.892755 multiple -11.450115
9 ILD.PA Iliad SA France 11 11865000000 1.379590 5.876208e+07 150.369952 -277458750 111278220 251991110 842630000 -4927000000 158.5500 832.929344 multiple - fcfAvg -80.964772
10 PROX.BR Proximus NV Belgium 13 2954000000 1.734783 3.226900e+08 27.249599 565700000 382711670 309079860 265199200 211000000 18.5500 10.309752 multiple 79.926729
11 KPN.AS Koninklijke KPN NV Netherlands 14 7437000000 2.666071 4.197235e+09 22.410055 735772670 827193200 899913180 945791400 1284000000 2.7460 5.356037 multiple -48.730750
12 DRIG.DE 1&1 Drillisch AG Germany 15 1080934000 4.991861 1.762646e+08 16.703991 262563170 297920000 232600000 198875000 184500000 23.5600 13.293822 multiple 77.225174
13 TEL.OL Telenor ASA Norway 16 150805000000 2.125287 1.399458e+09 19.846735 22094554700 18133822640 20093370000 21292140000 18233000000 153.9000 169.925453 multiple -9.430872
14 TELIA.ST Telia Company AB Sweden 17 104392000000 2.111820 4.089632e+09 21.670525 11623496200 10310630500 11943235690 13447109170 12916000000 36.5000 46.713701 multiple -21.864465
15 TELA.VI Telekom Austria AG Austria 18 3397697000 1.633824 6.640848e+08 15.835604 682833330 459666670 503000000 584666670 523000000 6.8900 8.461644 multiple -18.573747
16 TNET.BR Telenet Group Holding NV Belgium 19 5417900000 3.764590 1.092433e+08 17.980048 457512800 507941500 508564330 522005400 543500000 35.8600 45.695546 multiple -21.524081
17 TEL2b.ST Tele2 AB Sweden 20 29995000000 2.507502 6.886276e+08 23.314429 5042527890 4769539100 5402207000 5932257430 6052000000 118.9500 171.204072 multiple -30.521512
18 ELISA.HE Elisa Oyj Finland 21 1426800000 1.352343 1.600829e+08 27.473798 330238600 340941750 364354500 383504820 415000000 48.4000 65.840656 multiple -26.489190
19 OBEL.BR Orange Belgium SA Belgium 22 509688000 1.992162 5.994476e+07 18.187373 139615000 96398570 109388380 103606250 109500000 22.1000 27.130954 multiple -18.543226
20 INWT.MI Infrastrutture Wireless Italiane SpA Italy 23 3833619847 2.992875 9.601634e+08 44.957274 260615000 247121900 403258300 479012630 443480000 9.4460 17.166029 multiple -44.972713
21 VIV.PA Vivendi SE France 24 7648000000 4.022022 1.092830e+09 31.646602 1111803640 1315034300 1514001300 1477213290 1551666670 28.5100 39.468221 multiple -27.764669
22 FNTGn.DE freenet AG Germany 25 1477488000 4.631036 1.251048e+08 15.409570 314344440 223445000 227211250 227564000 234500000 20.1200 19.240093 multiple 4.573301
23 TMUS.OQ T-Mobile US Inc United States of America 26 81543000000 4.201657 1.241806e+09 47.579256 2013927500 4745816060 7271491470 12498032290 16492444440 133.0500 571.555498 multiple -76.721421
24 OTEr.AT Hellenic Telecommunications Organization SA Greece 27 1555600000 3.614797 4.593482e+08 13.422630 473018430 578100000 558971430 581100000 611333330 14.2000 16.058871 multiple -11.575352
25 GAMA.L Gamma Communications PLC United Kingdom 28 19000000 6.034460 9.540244e+07 33.366940 39475000 50050000 57583330 62666670 67436000 1798.0000 2401.023842 multiple -25.115279
26 RWAY.MI Rai Way SpA Italy 29 50793963 4.744355 2.683746e+08 33.057773 45245500 32463000 35275000 85448000 96000000 4.8100 11.789286 multiple -59.200245
27 SGEF.PA Vinci SA France 30 32084000000 4.563921 5.620617e+08 23.094869 1551000000 3170877000 3969049000 4283285710 4937500000 90.6500 152.953858 multiple -40.733760
28 SGOB.PA Compagnie de Saint Gobain SA France 31 15624000000 5.227225 5.300335e+08 19.643294 2040450000 1798117000 2253977000 2332211670 2419500000 53.2200 64.379257 multiple -17.333621
29 AAF.L Airtel Africa PLC United Kingdom 32 4279000000 6.401978 3.758152e+09 21.049526 306500000 370370000 397916670 433636670 436770000 75.0000 142.273254 multiple -47.284540
30 GEBN.S Geberit AG Switzerland 33 778800000 5.647370 3.562559e+07 35.666947 660784830 636792250 661919080 679602860 702733330 613.6000 702.789730 multiple -12.690813
31 MS.MI Mediaset SpA Italy 34 1623700000 8.838729 1.137944e+09 20.142671 253916670 86030000 103043750 124312500 309660000 2.4880 4.140331 multiple -39.908191
32 SESFd.PA SES SA France 35 3967000000 6.363997 4.555893e+08 29.921604 565393130 185982570 19359250 686488400 391126670 6.8680 17.422288 multiple -60.579230
33 ETL.PA Eutelsat Communications SA France 36 3522900000 2.064355 2.301507e+08 11.642313 452025500 459978500 435318380 468047000 436393330 10.2600 9.600703 multiple 6.867169
34 EKTL.MC Euskaltel SA Spain 37 1470076000 3.033608 1.784718e+08 49.493270 100612860 66771670 79015000 86056670 108000000 11.0600 22.226750 multiple -50.240138
35 CRH.I CRH PLC Ireland; Republic of 38 13850000000 4.341718 7.848000e+08 17.862562 2675429520 2485218090 2676537150 2886679810 2964650720 40.4600 53.763641 multiple -24.744680
36 VZ.N Verizon Communications Inc United States of America 39 129062000000 3.415964 4.138130e+09 18.869454 20822281400 18992386240 19079453900 19857580390 22227777780 58.2800 76.047712 multiple -23.363901
37 T.N AT&T Inc United States of America 40 157245000000 4.066687 7.125922e+09 14.980400 26146916630 25085545460 24805461540 25147571430 25981166670 29.9500 36.896867 multiple -18.827796
38 VON.AS VEON Ltd Netherlands 42 10056000000 4.581417 1.749127e+09 15.860783 429000000 671000000 806500000 1118500000 1087280000 1.4740 4.596871 multiple -67.934712
39 RG.F Rogers Communications Inc Germany 48 21257000000 3.049163 5.049250e+08 33.619432 2153930000 1670772730 2349777780 2805000000 2994000000 40.0000 162.047325 multiple -75.315853

Relative Valuation

Now we can look at Vodafone and place it in a DCF relative valuation space amongst its peers. This sort of approach can easily be expanded into all manner of other measures and factors. Where you have outliers - which is quite common - you can deal with these using the set_xlim or set_ylim parameters and also perhaps excluding these explicitly may also be required - both are shown below. We then just put a linear regression line through to see how that fits the data and in our case it does a reasonable job of defining a steep linear relationship between EV/FCF and prem/disc.

 

    	
            

ax1 = peers.plot.scatter(x='prem/disc',y='EV/FCF',figsize=(12,10))

 

 

for i, ric in enumerate(peers['RIC']):

    if not pd.isnull(peers['prem/disc'][i]):

        if not pd.isnull(peers['EV/FCF'][i]):

            ax1.set_ylim(10,55)

            ax1.set_xlim(-100, 100)

            if not ric=='ILD.PA':

                ax1.text(x=peers['prem/disc'][i]+0.3,y=peers['EV/FCF'][i]+0.3,s=peers['RIC'][i])

 

X, Y = peers['prem/disc'].values.reshape(-1,1), peers['EV/FCF'].values.reshape(-1,1)

ax1.plot(X, LinearRegression().fit(X, Y).predict(X))

Clustering

Now we can extend our relative valuation study to try to group companies into clusters that are similar. We saw that there there seemed to be a steep negative relationship between our two variables from the linear regression. But there are other - perhaps more useful - ways of classifying groups of observations. For example, we can ask do our observations that have very high EV/FCF multiples really belong in the same group as those observations that have low or more reasonable EV/FCF? Thankfully we can answer these types of question using generic techniques that one can apply to classifying any data. The key here is it is unsupervised - that is - we are not labelling the classifications - the clustering algorithm is generating them form the observed data. There are many ways of doing this - for brevity we will look at two, KMeans clustering and Gaussian Mixture Models which are popular in the literature. We have tried to visually limit the impact of outliers through guille and tried to focus on the bulk of the distribution. As we progress lets formally remove outliers and use this as the main body for our analysis.

    	
            

exoutlier = peers[(peers['prem/disc'] < 50) & (peers['EV/FCF'] < 55)]

X = exoutlier[['prem/disc','EV/FCF']].values

KMeans Clustering

Firstly we need to try to determine the optimal amount of clusters to use so we simply conduct the KMeans analysis 10 times and then we graph the inertia_ property which tells us that probably around 2 or 3 clusters or where the elbow is is the optimal number of clusters to use. We then use n_clusters = 3 to generate the Kmeans classification and plot it.

    	
            

inertia = []

K = range(1,10)

for k in K:

    km = KMeans(n_clusters=k)

    km.fit(X)

    inertia.append(km.inertia_)

 

plt.plot(K, inertia, 'bx-')

    	
            

km = KMeans(n_clusters=3)

km.fit(X)

pred =km.predict(X)

clusters = unique(pred)

for cluster in clusters:

    row = where(pred == cluster)

    plt.scatter(X[row, 0], X[row, 1])

plt.show()

Gaussian Mixture Models

In the same way we start with a cluster size of 3 and then fit our data, then we generate predictions of cluster for each data point then plot each of the cluster groupings.

    	
            

model = GaussianMixture(n_components=3)

model.fit(X)

pred = model.predict(X)

clusters = unique(pred)

for cluster in clusters:

    row = where(pred == cluster)

    plt.scatter(X[row, 0], X[row, 1])

plt.show()

In terms of classification - in our example I believe the simple linear regression works rather well, however, I also see some merit in the groupings generated by KMeans with 3 clusters - though there are also merits to the classification generated by the Gaussian Mixture Model. I suppose which to use depends on your use case but I hope you can use these techniques to generate further information for your 'quantamental' analyses.

 

Summary

In this article we have covered how to download various fundamental data (Income Statement, Cashflow Statement and Balance Sheet) that are critical for analyst workflows - using our new and improved Refinitv Company Fundamentals content. We then looked at how to get at the different types of estimate data we provide, Company Guidance, Analyst Estimates, Consensus Estimates and Starmine® Smart Estimates. We showed how you can monitor changes in these critical metrics and how to chart them against instrument prices as well as access our expanded industry-specific metrics.

We then applied this knowledge by conducting a Discounted Cashflow for an indiviual company. We then scaled this up to calculate DCF-derived model prices for all the peers of a company. We visualised this as a relative valuation in a scatterplot and generated a simple linear regression line to see how that fit. Often linear regression models don't really fit data that well (though in our case it was reasonable) - so finally we went a step further and tried to classify data into groups using 2 unsupervised machine learning models, KMeans and a Gaussian Mixture Model.

Further Resources for Eikon Data API

For Content Navigation in Eikon - please use the Data Item Browser Application: Type 'DIB' into Eikon Search Bar.