XBRL and on-the-fly financial analysis

01 November 2014 Written by 

In this post, we will further test drive the XBRL for its data consumption functionalities / features and see how easy it is to perform financial analysis on-the-fly.

Based on the chart in an earlier post ( reprodcued below for ease of reference ) showing free cash flows for 2 pharmaceutical companies, we would be interested to drill-down to get answers to the following questions:

xbrl2 fcf

  1. Why is there huge fluctuation in FCFs for PharmCo1?
  2. Is Pharmco1 performing better than PharmCo2?
    1. Earnings and profitability
    2. Asset protection
    3. Debt capacity and flexibility

In 2013, total debt of US$118 billion was raised by the 12 pharmaceutical companies under review with combined revenues of US$ 276 billion. It will be of interest to the capital providers as well as seekers of capital to get optimal pricing for this scarce resource. For the capital providers working with an 8% tier 1 capital adequacy requirement, it will need to set aside US$ 9.44 billion. With an expected return on equity ( ke ) of 20%, the cost of equity works out to US$ 1.9 billion. There is plenty of room for cost savings. These capital providers will want to optimise the return on risk while seekers of capital will need to understand the underlying risk dynamics in order to improve the business value proposition that will allow them to obtain capital at the lowest possible cost.

We pulled[1] 2 more charts to help us seek explanation to the above questions.

     xbrl2 sales                  

PharmCo1’s revenues were 3.5X of PharmCo2, although the former’s revenues have declined somewhat. PharmCo2’s revenues have been on an upward trend. Based on trend, PharmCo2 would be given more weightage when it comes to valuation.

The next chart is a visualisation of the DuPont analysis model[2].

 xbrl2 Dupont

Pharmco1’s ROE has been declining steadily to 10.2% for 2013 from previous high ranging between 22 to 25%; while PharmCo2’s ROE is now better than PharmCo1 at 15.3% for 2013 (holding fairly constant compared to prior periods).

Based on the information available thus far, we may be tempted to infer that PharmCo1’s value added-ness is somewhat inferior to PharmCo2 and hence the market capitalisation may converge despite the difference in scale ( Revenue differential is 3.5X with PharmaCo1 having higher revenues ).

Let’s pull another chart showing the ROICs to clue us in as to the value creating processes.

xbrl2 roic

It is clear now that PharmCo1’s ROIC of 5.8% is way inferior to PharmCo2’s of 16.8%. In fact, PharmCo1’s ROIC has been declining at a rather alarming rate whilePharmCo2’s has been making steady progress.

While preparing the datasets for the R&D expenses, we noted that PharmCo1 has used a different concept name i.e. us-gaap:ResearchAndDevelopmentExpenseExcludingAcquiredInProcessCost[3]. We made a quick change to the code and ran the routine ( all within 2 minutes ). We reverted to the analysis while the routine ran its course. When the routine was done, we extracted the chart – exhibited below.

 xbrl2 rndpersale

It is evident that PharmCo1’s R&D to Sales is lower to PharmCo2 and in 2013 dropped precipitously to 6%. It begs the questions as to whether this will erode PharmcoCo1’s competitiveness in the longer run.

To round up the assessment on the Earnings / Profitability category, we take a look at the next chart showing interest coverage ratio.

 xbrl2 interestcover

Both companies exhibited robust interest servicing in excess of 15X for 2013.

Asset Protection

To obtain the TO days for inventory and account payables in order to work out the operating cycle, [4]cash conversion cycle and Net Working Capital ( NWC) per Sale.

In the case of account payables we noted that are 2 differing concept names: a)us-gaap:AccountsPayableTradeCurrent,  b) us-gaap:AccountsPayableCurrent.


So we added the filter and re-run the code and have no problem extracting the facts.

We have now the data points[5] for:

  • Accounts receivable days
  • Inventory days
  • Accounts payable days
  • Operating cycle
  • Cash conversion cycle


Accounts receivable and   Inventory days

Accounts payable days

    xbrl2 arinvdays

    xbrl2 apdays

Operating   cycle

Cash   conversion cycle

    xbrl2 opcycle

    xbrl2 ccc

PharmCo1 management of its AR is less efficient at 67 days than PharmCo2’s 51 days - while its inventory management is more efficient, 98 days versus 131 days. In addition, PharmCo1 enjoyed less favourable credit term from its suppliers of 37 days versus 130 days. Although operating cycle is more or less than same at 165 – 182 days, PharmCo1’s cash conversion cycle of 127 days lagged behind PharmCo2’s 52 days.

Debt Capacity / Flexibility

Next, we looked at the gearing scenario

 xbrl2 debtcapflexibility

PharmCo1 gearing ratio was higher than PharmCo2 from 2009 through 2012, but in 2013 the gearing improved significantly besting PharmCo2.

We also would like to review the debt capacity / flexibility of these companies.

 xbrl2 debttoffo

We noted that PharmCo1 has taken significant steps to reduce its gearing and in 2013 with the implied years to service the debt improving to less than 2 years, comparable to PharmCo2.

Financial analysis –   PharmCo1

Credit risk rating scoring

Earnings / profitability

Asset protection

Debt capacity / flexibility

Overall score




Very Good








  • A rather low ROIC may further erode value creating   capability; trending downward
  • Excellent interest coverage
  • Balance sheet – exhibited good quality in short term liquidity;
  • Also will be good to review the current ratio
  • Very good debt servicing with less than 2 years to service the debt
  • Very good debt capacity with low / robust debt to equity ratio
  • need to examine the earnings / profiability segment more closely to better understanding the value creation and revenue growth strategy

The above processes show the natural flow on integrated thinking to drill-down up to a point where it is possible to form a preliminary view of PharmCo1’s credit risk ranking. The table above shows a conventional rating sheet with PharmCo1 having an overall credit risk rating of 3 ( Good ) subject to the comment under "Overall score".

Using the same XBRL-based data source / structure, we can also run a model to determine the probability of default (pdf) of these companies and calibrate its risk ranking accordingly as well as the appropriate pricing to apply.


Overall, save for the initial setup and exceptions with regard to naming convention for the taxonomy concepts for which satisfactory workarounds have been found, we noted that XBRL-based financial analysis flows naturally allowing us to form a big picture view of the company under review quickly.

What is more impressive is the ability to slice-and-dice to get deeper insights about the company’s financial attributes as well as “adjacent” peers in the industry setting the stage to build a storyline for subsequent more advanced work e.g. building a framework for integrated thinking / reporting for sustainable value creation, creating an enterprise social network platform to bring together like-minded individuals to expand the scope for XBRL data consumption, interaction with senior executives of the company by capital providers etc.

The advantages of the XBRL-based data consumption can be summarised as follows:

  1. facts are one keyboard away
  2. easy, fast and repeatable
  3. once the processes have been set up, the bulk of the time will be focused on value-added analytical and related tasks.

With the foundation nicely in place we can proceed to perform more advanced analysis e.g. identifying and realising value creating potential ( future cash generation capabilities ) – and this will take us to realm of integrated thinking and reporting. We will cover more on this in the next post.


[1] There is a distinct advantage here: data is consumed as and when needed. The need to consume more data is driven by the thought process of the analyst. This flows naturally and is aligned to the integrated thinking process – one big idea that is a game-changer!. Analysts can draw comfort that the data is only one keyboard away and there is little downtime to disrupt the train of thought. The purpose of analysis is to seek out inconsistencies through triangulation and “bad” data such as mis-tagging will surface soon or later – with so many pathways available in the marketplace, there is plenty of redundancies ( parallel processing ), layers of sanity checks so much so that one should not be too concerned about the quality of the data source.

Overtime, data consumption will align to the most effective and efficient pathway – no price for guessing which it will be!  

[2] Return of equity = net income from continuing operations / equity; or (net income from continuing operations/sales) x (sales/assets) x (asset/equity)

[3] PharmCo1 has nil return for the datasets and a quick look using the cloud viewer provided by SEC/Edgar showed PharmCo1 used a different taxonomy concept name. We simply added the concept name into the filtering process and the R&D expenses were picked up. As more and more are on-board consuming XBRL-based data, such inconsistencies are expected to go away. It is in the interest of filers as well as consumers of data to be aligned in wanting data that can be sourced accurately and consistently. Beliefs and behaviours will pivot toward the “if you're going to be naked, you'd better be buff “mind-set.

[4] In the case of Cost of Revenue, some companies use their own taxonomy ( extension ) and this involves declaring namespace. Need to use XML editor to lookup the namespace: strEntireInstanceDocument_TEMP = oInstance.XML

Set oInstance = New MSXML2.DOMDocument60

oInstance.async = False

oInstance.validateOnParse = False

oInstance.Load (strInstanceLocation)

e.g. xmlns:abc="http://www.abcinc.com/20091231"

[5] Have checked PharmCo1 and PharmCo2 with another third party source and the numbers were all correct.

Read 16982 times Last modified on Monday, 03 November 2014 09:10
Rate this item
(0 votes)

Related items

Leave a comment

Comment moderation has been enabled. All comments must be approved by the blog author.