Working with Fundamental and Estimates Data - A DCF Example

Income Statement

144 rows × 4 columns

Cashflow Statement

73 rows × 4 columns

Balance Sheet

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.

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.

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.

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.

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).

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.

201 rows × 15 columns

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.

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.

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.

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).

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.

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.

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.

0 204.453545
dtype: float64

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

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

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

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.

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.

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.

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.

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

• Overview
• Quick Start
• Documentation
• Downloads
• Tutorials
• Q&A Forums

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