Basic information on financial databases: cook books, tips and tricks & economic news

This blog contains schematic easy to grasp - hands on - help in performing searches in economic databases, making work sets and making them inter-exchangeable between the databases.

* Disclaimer. I am not a finance professional. Most posts are the result of personal findings.

All presented images are scaled and can be enlarged to original size (click the picture).

Search This Blog


Ratios, values and other instruments from the balance sheet - Amadeus

In 2011 I posted two items on databases containing data from the balance sheet: Datastream and Compustat.
At that time I left out te option to search for such data in database Amadeus, mainly because Amadeus doesn't cover financials (insurance companies and banks)
However. Since Amadeus is a fairly easy database to use I'll show you which key data from the balance sheet can be found and added to your research.
In he first part of this post I will emphasize on Amadeus and its contents. In the second part I will compare Amadeus to Datastream and Compustat.

Amadeus: Only European companies. No banks and no insurance companies
Datastream: only companies noted on stock exchanges
Compustat: only companies noted on stock exchanges; no ratios (DIY)

Ratios, values and other instruments from the balance sheet - Amadeus

(Bureau van Dijk interface)

It is interesting to investigate which balance sheet data can be retrieved from the databases the library offers. Sometimes the data are ready for download, sometimes they have to be calculated, and sometimes they are absent altogether. This post aims at offering an oversight of commonly used balance sheet figures and how they can be found in database Amadeus.
Amadeus provides simplified balance sheet data, easy to get, but few in number.

Section 1 The Balance Sheet - Amadeus

For insight in using Amadeus I refer to the following items:
  1. From the library Blackboard page: click here (only available to VU users)
  2. Amadeus tutorials (4) (Bureau van Dijk)
  3. Amadeus official user manual : here
After creating your search :

Press view list of  results
A basic list (of clickable companies shows):
On the right hand side of the screen there is a menu. 
Now click List format

Choose: New Format. 

The next screen shows the menu that pops up:


Key financials and ratios can be opened by pressing the +
The next menu offers three options:   Key financials & employees, global format and ratios.
Within the options further selections can be made by unfolding the folders (pressing the +) Selection happens by pressing the box.

List of all balance sheet items and ratios in Amadeus

Key Financials and employees:

Operational revenue
P/L before tax
P/L for period [= Net Income]
Cash flow
Total assets
Shareholders funds
Current ratio
Profit margin %
Return on shareholder funds %
Return on capital employed %
Solvency ratio %
Number of employees

Balance sheet :
Fixed asset
Intangible fixed assets
Tangible fixed assets
Other fixed assets
Current assets
Other current assets
Cash & cash equivalent
Total assets
Shareholders funds
Other shareholders funds
Non-current liabilities
Long term debt
Other non-current liabilities
Current liabilities
Other current liabilities
Total shareholder funds & liabilities
Memo lines
Working capital
Net current assets
Enterprise value
Number of employees

Profit and loss account

Profit and loss account
Operating revenue (Turnover)
Costs of goods sold
Gross profit
Other operating expenses
Operating P/L [=EBIT]
Financial revenue
Financial expenses
Financial P/L
P/L before tax
P/L after tax
Extr. and other revenue
Extr. and other expenses
Extr. and other P/L
P/L for period [=Net income]
Memo lines (not all companies provide data)
Export turnover
Material costs
Costs of employees
Interest paid
Research & Development expenses
Cash flow
Added value

Ratios :
Profitability ratios
Return on shareholder funds %
Return on capital employed %
Return on total assets %
Profit margin %
Gross Margin %
EBITDA Margin %
EBIT Margin %
Cash flow / Operating revenue %
Enterprise value / EBITDA
Operational ratios
Net assets turnover
Interest cover
Stock turnover
Collection period days
Credit period days
Export revenue / Operating revenue %
R&D expenses / Operating revenue %

*) Units can be chosen in thousands, milllions etc.
*) P/L = Profit / Loss

Section 2: the balance sheet ready  in Amadeus or calculated

The next section of this post deals with the very same balance sheet data as the ones I used in the Datastream and Compustat examples.

Data types available in Amadeus are presented in red.

1. Assets
Assets = liabilities + shareholders' equity

Total Assets = non-current assets + current assets
Amadeus: Yes (Key financials)
TOTAL ASSETS represent the sum of total current assets, long term receivables, investment in unconsolidated subsidiaries, other investments, net property plant and equipment and other assets.

Current assets total = Yes (Global format > Assets)
CURRENT ASSETS - TOTAL represents cash and other assets that are reasonably expected to be realized in cash, sold or consumed within one year or one operating cycle.

Generally, it is the sum of cash and equivalents, receivables, inventories, prepaid expenses and other current assets.
For non-U.S. corporations, long term receivables are excluded from current assets even though included in net receivables.
Long term (non-current assets)
Total assets - current assets = Long term assets (calculate with the components)

2. Liabilities (not all here)
Amadeus: (Global format > liabilities & equity)
Total libalities = Current liabilities + long term liabilities (not ready in Amadeus)
Current liabilities total  =  current liabilities + other current liabilities
Short term liabilities: accounts payable (), short term debt ()
Long term liabilities are long term debt , deferred taxes (not in Amadeus) and capital leases (not in Amadeus)
Long term liabilities per share: book value per share / number of share outstanding .
Total liabilities: = Current liabilities total: + Long term liabilities
Total liabilities + shareholders' equity = Assets
Assets - shareholders equity (book value per share x number of shares) = total liabilities

Accruals (balance sheet) Not downloadable in Amadeus.

Accrual (accumulation) in finance is the accumulation of interest or different
investments over a period of time. used in accrual-based accounting:
accounts payable, accounts receivable, goodwill, deferred tax liability
and future interest expense.

3. Shareholders'equity
Total Assets - (total) liabilities = shareholders'equity (book value of equity)

Book value per share = Book value per equity / number of shares outstanding
Book value per equityBook value per share x number of shares outstanding
Book value
Net asset value of a company, calculated by total assets minus intangible assets (patents, goodwill) and liabilities.

4. Market value
Instead of Book value of equity, Market Value ( MV) can be used.
Market Value = share price x number of shares (market capitalisation)
In Amadeus only closing price frequencies monthly and weekly, not daily
And: market value per year can be downloaded
(Nominal value (Annual stock data = price x times number of shares)

5. Liquidation of a company

Market to book ratio
Market to book ratio = Market value of equity (MV) / Book value of equity =
Market value (annual) / (Total assets - (book value per share x number of shares outstanding) )
note: Market to book ratio does not include certain intangible assets like intellectual property
Its inverse: Book to market ratio = book value / market value

Debt equity ratio
Total debt / total (shareholder) equityTotal shareholder equity (Book value per equity = Book value per share x number of shares)
Total debt = short term debt + long term debt
Short term debt = Notes payable / total liabilities (Assets - share holder equity)

Total debt
represents all interest bearing and capitalized lease obligations. It is the sum of long term debt and short term debt.

Total shareholder equity
 represents the sum of Preferred Stock and Common Shareholders Equity.
This item is available in the annual time series and the quarterly, semi-annual and trimester interim time series. It is only available at the company level.
Not in Amadeus

Enterprise values (alternative to market capitalization) 
Market Capitalization at fiscal year end date + Preferred Stock + Minority Interest + Total Debt minus cash. Cash represents Cash & Due from Banks for Banks, Cash for Insurance Companies and Cash & Short Term Investments for all other industries.

CASH represents money available for use in the normal operations of the company. It is the most liquid of all of the company's assets. 
Annually : (Market value + debt) - (total cash)
Annually: ( (Annual stock data = price x times number of shares) + debt) - (cash + cash equivalent)

Gross profit margin
Gross profit
Gross Income / Net Sales or Revenues * 100 = gross profit margin

Earning per share EPS (only annually)
Earnings per share (Current stock data)
= Net income / shares outstanding = P/L for period [= Net Income] / Shares outstanding
The number of shares outstanding at the company's year end. It is the difference between issued shares and treasury shares.

Profitability ratio
Gross profit / sales

Return on equity ROE  
ROE (%)
Net income / book value on equity =  
P/L for period [= Net Income] / Book value per share x number of shares outstanding
Net income = P/L before tax - taxation

Return on Assets (ROA)
ROA (%) and Return on total assets (%)
The formula for return on assets is: Net Income / total assets  

An indicator of how profitable a company is relative to its total assets. ROA gives an idea as to how efficient management is at using its assets to generate earnings. Calculated by dividing a company's annual earnings by its total assets, ROA is displayed as a percentage. Sometimes this is referred to as "return on investment".
Source: Investopedia

6. Other ratios from the balance sheet
Net Sales or revenues : sales
Operating income (sales - total operating expenses (OPEX) ) = (sales - sum of all expenses)
Income before tax (not in Amadeus)
Quick ratio (Cash & cash equivalent  + receivables (net)  / current liabilities total
Current ratio (current assets total   / current liabilities  )
Sales ratio: Cash flow / sales
Operating profit margin : (not in Amadeus)
Pretax margin (P/L before tax / net sales or revenues x 100)
Net margin (Net Income before Preferred Dividends / Net Sales or Revenues x 100) (not in Amadeus)
Return on invested capital (not in Amadeus)

Coverage Amadeus , what can you find in Amadeus?  (link)
User Guide Amadeus (link)
Quick tour Amadeus  (link)


No comments:

Post a Comment