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 Compustat

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 Compustat. (North America)*

*) I have made an attempt to find the very same balance sheet items as I used for Datastream, thus making both database comparible.

Compustat Global: it is possible that Golbal does not provide certain data items. I will mention it, if this the case. As for currencies: you can check the currency in which Gobal data items are expressed by checking CURCD (Curency code); the native currency  (CURNCD) may differ but will be recalculated to CURCD.
Looking up data items in Compustat can be tedious when you have to scroll along the long lists of data items.
Using the CTRL-F key combination makes it possible to search for items "as you type"

Compustat Banks: seems to have another set of data items altogether.

1. Assets
Assets = liabilities + shareholders' equity

Total Assets = non-current assets + current assets
Data items
Total assets = AT  = Assets - Total (CTRL-F: assets - total)
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 = ACT = Current assets - total
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) > not in Compustat
Total Assets = non-current assets + current assets > AT - ACT = Long term assets

2. Liabilities

Total libalities = Current liabilities (LCT = Current Liabilities - Total) + long term liabilities = LT (liabilities - total). Total liabilities LT = Current liabilities total LCT + (Long term liabilties)
Total liabilities: current liabilities plus long-term debt plus other noncurrent liabilities, including deferred taxes and investment tax credit.
LCT = liabilities due within one year, including the current portion of long-term debt
Long term liabilities have to be calculated: LT - LCT = Long term liabilities

Short term liabilities: accounts payable (APC = Accounts Payable/Creditors - Customer > does not provide data), short term debt (DLCFS = Short Term Debt Total - FS > does not provide data)
Long term liabilities are long term debt (DLTT = Long-Term Debt - Total), deferred taxes (TXDB = deferred taxes, balance sheet > zero or absent, maybe depends on company) and capital leases (in compustat there are CLD2 (due in 2nd yr.; CLD3; CLD4 and CLD5; all capitalized leases > providing data depends on company)
Not in Global: DLCFS 

Accruals (balance sheet)
APALCH Accounts Payable and Accrued Liabilities Increase/(Decrease)
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 (ARTFS  Total Accounts Receivable/Debtors) , goodwill (GDWL ), deferred tax liability (TXNDBL Net Deferred Tax Liability, not in global) and future interest expense (TIE Interest Expense Total, not in Global).

3. Shareholders'equity

Assets - liabilities = shareholders'equity (book value of equity; BKVLPS (book value per share) )
AT - LT = shareholders' equity (BKVLPS)
Common Equity ? Liquidation Value (CEQL) divided by Common Shares Outstanding (CSHO).

CEQL = Common Equity - Liquidation Value

4. Market value

Instead of Book value of equity, Market Value ( MKVALT) is used.
Market Value MKVALT (market value - total fiscal) = share price (*) x number of shares (CSHO - common shares outstanding) (market capitalisation)
PRCC_C = Price Close - Annual - Calendar
PRCC_F = Price Close - Annual - Fiscal
Not in Global: any of these

5. Liquidation of a company

Market to book ratio
Market to book ratio = Market value of equity (MV) : Book value of equity =

Debt equity ratio
DLC (DLC = Debt in Current Liabilities - Total)
DLC represents the total amount of short-term notes and the current portion of long-term debt (debt due in one year).

Total debt / total (shareholder) equity
represents all interest bearing and capitalized lease obligations. It is the sum of long and short term debt. (last no data)

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.
AT - LT = shareholders' equity (BKVLPS)

. Total equity
Total equity = preferred stock (PSTKC) + common equity (CSHO)
CEQ = Common/Ordinary Equity - Total
item represents the common shareholders' interest in the company.
This item is a component of Shareholders' Equity Total (SEQ).
SEQ = This item represents the common and preferred shareholders' interest in the company.
PSTKC = Preferred Stock - Convertible
This item represents the carrying value of outstanding preferred stock which is reported as being convertible

Enterprise values
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.
(Market value + debt) - cash (MKVALT + DLC ) - CH
CH = Cash
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.

Gross profit margin
Gross Income / Net Sales or Revenues * 100 = UGI / SALE x 100
Gross profit: GP (loss)
Gross income: UGI
. the difference between sales or revenues and cost of goods sold and depreciation.
Net sales or revenues: SALE (sales/turnover)
gross sales and other operating revenue less discounts, returns and allowances.
REVT = Revenue - total
GP = UGI / SALE x 100

Earning per share EPS
Net income / shares outstanding
Net income used for EPS
Net Income = NI
(common) Shares outstanding CSHO

Profitability ratio
Gross profit / sales
Return on equity ROE
Net income / book value on equity
ROE = (Net income/ sales) x (sales/ total assets) x (total assets/ total equity)
            net profit margin            assets turnover      equity multiplier

Return on Assets (ROA)
(investopedia) (click)
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".

The formula for return on assets is: Net Income / total assets  ( NI / AT )


Please comment if there are omissions or if I placed wrong information

No comments:

Post a Comment